# 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="/files/cJsaZ2PQySFDgF31UYD9" 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="/files/5Mgabvhnf0aQd4rHi5xE" alt="images/Advanceddashboard-2.png"><figcaption></figcaption></figure>

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

<figure><img src="/files/EyQj609n5UK5ElLXfwoz" 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="/files/swPpIPOV2ZaEUZPbE9uA" 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="/files/S3MupsAGO2bYGLym5FJR" 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="/files/nUh1t4292bYgO06zUTq9" alt="images/Advanceddashboard-6.png" width="366"><figcaption></figcaption></figure></div>

* Your tile is now ready

<div align="left"><figure><img src="/files/ZbMH1ajaficaFYdnFhKg" 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="/files/vuCKjUQVWGRDCOTyCIKW" 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="/files/v2Ot78FnjsebiPutMo48" 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="/files/LsUUrtLz1fQoAgqsvGCh" alt=""><figcaption><p>Perform a saved search</p></figcaption></figure>

* Click the save icon ![](/files/5AtCESlwyh0rIW15NilY)
* 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="/files/lBl6FDSjpbYiQo4lAX5R" 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="/spaces/IiEHKRx5AeABNDLcRlWX/pages/HXXMuaLs3STGCKAHy8ca" %}
[Saved Searches](/documentation/readme-1-1/saved-searches.md)
{% 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="/files/us9EnEVChcmX6MV16F2m" 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="/files/G4w1FnUcqAH876oBH2VG" 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="/files/CheRZP47R71qDeypY9XA" 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="/files/lVgS6Zi1nS1lF9eboDXn" alt=""><figcaption><p>Add the search results</p></figcaption></figure></div>

<figure><img src="/files/XEnQEVdGUGJwOEbM2qLI" 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="/spaces/SvoJ3lioglS2UZLcWWyP/pages/XewlzvERUzs4rbQQFDSM" class="button secondary">Go to FAQ</a>


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://help.glpi-project.org/doc-plugins/plugin-glpi-network/advanceddashboard.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.