# Advanced dashboards

## Requirements (self-hosted)

| GLPI Version | Minimum PHP | Recommended |
| ------------ | ----------- | ----------- |
| 10.0.x       | 8.1         | 8.2         |
| 11.0.x       | 8.2         | 8.4         |

{% hint style="info" %}
A [GLPI Network STANDARD](https://services.glpi-network.com/#offers) subscription (or higher) is required. This plugin is also available for all GLPI Network [Cloud instances](https://glpi-network.cloud).
{% endhint %}

***

## Install the plugin

* Go to the marketplace. Download and install the "**Advanced dashboard**" plugin.

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FXcKsmFdoPrAnXZBtfubd%2Fadvanceddashboard.png?alt=media&#x26;token=5a619de8-b006-4821-9220-cdb80656cbf7" alt="images/Advanceddashboard-1.png"><figcaption></figcaption></figure></div>

***

## Setup the plugin

{% hint style="warning" %}
This plugin requires **a minimum knowledge of SQL** in order to be able to set certain criteria for the various queries that will appear in the dashboards. It is also recommended that you **know the structure of GLPI tables**.
{% endhint %}

### Create an SQL query

The below example gives us the number of tickets on the root entity over the last 2 years:

```sql
SELECT YEAR(t.date_creation) as 'Year', count(*) as 'Volume of tickets'
FROM glpi_tickets t
JOIN glpi_entities e on t.entities_id = e.id
WHERE e.completename LIKE "Root entity"
    AND YEAR(t.date_creation) > YEAR(CURRENT_DATE()) - 5
GROUP BY YEAR(t.date_creation)
UNION
SELECT
    CONCAT('Estimated total end ', YEAR(CURRENT_DATE())),
    ROUND(
        (
            SELECT count(*)
            FROM glpi_tickets t4
            JOIN glpi_entities e4 on t4.entities_id = e4.id

            WHERE e4.completename LIKE "Entité racine" (root entity)
                AND YEAR(t4.date_creation) = YEAR(CURRENT_DATE()) - 1
        ) *
        (
            (
                SELECT count(*)
                FROM glpi_tickets t3
                JOIN glpi_entities e3 on t3.entities_id = e3.id
                WHERE e3.completename LIKE "Root entity"
                    AND YEAR(t3.date_creation) = YEAR(CURRENT_DATE())
            )
            /
            (
                SELECT count(*)
                FROM glpi_tickets t2
                JOIN glpi_entities e2 on t2.entities_id = e2.id
                WHERE e2.completename LIKE "Root entity"
                    AND YEAR(t2.date_creation) = (YEAR(CURRENT_DATE()) - 1)
                    AND (
                        MONTH(t2.date_creation) < MONTH(CURRENT_DATE())
                        OR MONTH(t2.date_creation) = MONTH(CURRENT_DATE()) AND DAY(t2.date_creation) <= DAY(CURRENT_DATE())
                    )

            )
        )
    )
```

### Setting query-related parameters

* From **`Tools`** > **`Advanced dashboard`** (or **SQL for dashboards on GLPI 10)**, click on **add**
* Name your query (the one that will be visible from the dashboards) and identify it as **active**.
* In the SQL Editor tab, paste the above query, taking care to change the name of the entity if you have changed it.
* Click on the floppy icon to save the query and then on play icon to run the query.

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Fgit-blob-0c3aeccea4f600bf54d3acd15e026c1758845dac%2FAdvanceddashboard-2.png?alt=media" alt="images/Advanceddashboard-2.png"><figcaption></figcaption></figure>

An insert at the bottom will display the result of the query:

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Fgit-blob-c65e51dda6f9fddb6a668cc451136ac777a4fe76%2FAdvanceddashboard-3.png?alt=media" alt="images/Advanceddashboard-3.png"><figcaption></figcaption></figure>

* In the **set up fields** tab, define the label, the number and the main label (optional, this is the name your table will have). In our case, *the label will be "year"*, *the number will be "number of tickets"* and the *main label will be ""number of tickets per year"*.
* Select the type of graph (this is for information purposes only, so that you can preview your table; you will be asked to select the type of graph you want to appear in the tile later),
* You can limit the amount of data to be displayed.
* Then click on **Save fields and preview**.

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Fgit-blob-f328dfc4a552cff2eb389ce17035d080015365d4%2FAdvanceddashboard-4.png?alt=media" alt="images/Advanceddashboard-4.png" width="489"><figcaption></figcaption></figure></div>

### Dashboard settings

* From **Assistance** > **Dashboard**, select the dashboard that is to receive the new tile
* Click on edit icon
* Add a new tile with plus icon
* Select the query you have just created
* Choose a colour for your tile

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Fgit-blob-bfeef3330ea79c46070ca33cf72af048f4d91a4e%2FAdvanceddashboard-5.png?alt=media" alt="images/Advanceddashboard-5.png"><figcaption></figcaption></figure></div>

* You will then be asked to select the chart type
* Some dashboards may ask you to use colours and display values and labels.

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Fgit-blob-524e67e6849df95e295aa18fe88b557a51b17fc5%2FAdvanceddashboard-6.png?alt=media" alt="images/Advanceddashboard-6.png" width="366"><figcaption></figcaption></figure></div>

* Your tile is now ready

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Fgit-blob-d8d7c59eb2022babdf5a66b37d3304fdfac127dd%2FAdvanceddashboard-7.png?alt=media" alt="images/Advanceddashboard-7.png"><figcaption></figcaption></figure></div>

***

## Adding a New Database

You can add another GLPI database to query.

* Click **`Tools`** > **`Advanced Dashboard`** (or **`SQL for Advanced Dashboard`**).
* In the top menu, click **`Database`**, then **`+ Add`**.
* Enter your database information and click **`+ Add`**.

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FXQ1jYP4eYJchU32g5zdc%2Fadvanced_dashboard-add-database.png?alt=media&#x26;token=2b9acf5f-2c38-4014-b0ea-03cb358327e9" alt=""><figcaption><p>Add a new database</p></figcaption></figure>

From **`Tools`** > **`Advanced Dashboard`** (or **`SQL for Advanced Dashboard`**), you can select the database to query.

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2Ff5rsXCGB1RORNq5GANlm%2Fadvanced_dashboard-select-database.png?alt=media&#x26;token=8b5c0519-efbc-454e-888c-ec434b71a1c2" alt=""><figcaption><p>Select the new database</p></figcaption></figure>

***

## Saved Searches

With Advanced Dashboard, you can display the results of your saved searches from a dashboard. This can be done with both asset inventory and ticket items.

{% hint style="info" %}
Only public saved searches can be integrated into a dashboard.
{% endhint %}

We will use tickets with a **New status** as an example.

### Performing and Saving a Search

The first step is to perform a search and save it.&#x20;

* From **`Assistance`** > **`Tickets`**, perform your search using the desired criteria.

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FemLDjxHmSUPzKdFjWxKb%2Fadvanced_dashboard-saved-search.png?alt=media&#x26;token=e22c8b57-e46b-489e-bc2f-af6f442cbf0c" alt=""><figcaption><p>Perform a saved search</p></figcaption></figure>

* Click the save icon ![](https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FeY0kokRfVvoYZ4NBcOMw%2Fadvanced_dashboard-saved-your-search.png?alt=media\&token=63a40960-2f8e-49a5-b266-10de2f945161)
* In the new window that appears, adjust the settings:
  * **Name**
  * **Count**: Auto / Yes / No
  * **Visibility**: Your search must be public.
  * **Entity**: Select the desired entity.
  * **Child-entities**: Allow or deny access to sub-entities.

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FYe0kZvw0vBEN7fIGubBP%2Fadvanced_dashboard-public-search.png?alt=media&#x26;token=762588b0-64bb-42ae-86b0-e865a0f68416" alt=""><figcaption><p>Make research public</p></figcaption></figure>

### Setting up the saved search

\
You will then be redirected to your new saved search. You can adjust certain settings as needed.

You can also add alerts. See the dedicated article for more information.

{% content-ref url="<https://app.gitbook.com/s/IiEHKRx5AeABNDLcRlWX/readme-1-1/saved-searches>" %}
[Saved Searches](https://app.gitbook.com/s/IiEHKRx5AeABNDLcRlWX/readme-1-1/saved-searches)
{% endcontent-ref %}

### Integration into the dashboard.

* From your dashboard, click the edit icon <i class="fa-pen-to-square">:pen-to-square:</i>
* Click an empty tile to add your search

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FSnljMIbQa9830x6CJgRd%2Fadvanced_dashboard-add-search.png?alt=media&#x26;token=98e81934-de90-42ff-8fdb-5c826f6e4213" alt=""><figcaption><p>Add a new card</p></figcaption></figure>

* In the dropdown list, select **Public saved searches** and then the desired search.

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FgrMh7JwQierpvph4FXzg%2Fadvanced_dashboard-add-card.png?alt=media&#x26;token=de6c4cf4-2f67-4ab4-98e8-92cee3117602" alt=""><figcaption><p>Select the saved search</p></figcaption></figure></div>

Several display modes are available. You can choose a number to display, and clicking on that number will redirect you to the search results.

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FCN0rXa4GCW1z7Im2esS3%2Fadvanced_dashboard-search-number.png?alt=media&#x26;token=c79f05af-7e70-49dc-8d25-c20df6b437a2" alt=""><figcaption><p>Add the result as a number</p></figcaption></figure></div>

You can also display the results directly in the dashboard. You will need to adjust the maximum number of results to avoid overloading your dashboard.

<div align="left"><figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FLFRkrn6dwtuGsrPAfiWK%2Fadvanced_dashboard-search%20result.png?alt=media&#x26;token=0a50b2e8-06af-43e9-af60-5fe9fea66c39" alt=""><figcaption><p>Add the search results</p></figcaption></figure></div>

<figure><img src="https://3573604317-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2FuUCcURk2xlvtpVGbiRZf%2Fuploads%2FgEtqcMcCALO11fTTPglj%2Fadvanced_dashboard-result.png?alt=media&#x26;token=11d17250-6ef5-4c10-a169-65a54137d158" alt=""><figcaption><p>Display in number and result</p></figcaption></figure>

***

## FAQ

If you have any questions about using the plugin, please consult our FAQ

<a href="https://app.gitbook.com/s/SvoJ3lioglS2UZLcWWyP/plugins/advanced-dashboards" class="button secondary">Go to FAQ</a>