NexTool Solutions

A good metric is one whose origin you can point to in the database. This guide shows which Service Desk KPIs are actually worth tracking in GLPI, which table and column each number comes from, which ranges we use as reference and the traps that have already skewed decisions in the environments we maintain for clients.

Before the KPI, the data model

The mistake we see most when taking over an existing GLPI is measuring "from the surface": comparing raw dates instead of using the fields GLPI already computes. Each ticket in the glpi_tickets table carries ready-made statistical columns, the same ones the Statistics module consumes:

• takeintoaccount_delay_stat - seconds until first take-into-account (basis for response time / TTO).
• solve_delay_stat - seconds until resolution, already subtracting the "Pending" time and adjusted by the entity work calendar (basis for MTTR / TTR).
• waiting_duration - total seconds the ticket spent waiting.
• time_to_resolve and time_to_own - the SLA deadlines for resolution and for take-into-account.
• status - the ticket state, which decides what does or does not count as backlog.

This changes everything: MTTR computed as solvedate - date differs (and is always larger) from what solve_delay_stat returns, because the raw subtraction adds nights, weekends and pauses. Choosing the right column is already half the work.

The KPIs that matter and where each one lives

It is not about having ten indicators on the dashboard; it is about having the ones that drive action. The matrix below ties each KPI to its technical source in GLPI and to an honest reference range. A range, not a universal target: the fair number depends on the contract and the type of operation.

Pulling the numbers straight from the database

The Statistics module (under Assistance) works well for ad-hoc queries and for drilling down by category, group, technician and SLA. But it does not version history month by month, nor cross several indicators into a single comparable row. When we need the consolidated snapshot that goes into the client monthly report, we go to the database with a read-only user:

-- Current-month KPIs in a single row (use a read-only user)
-- Source: glpi_tickets, the same fields the Statistics module reads
SELECT
    COUNT(*)                                                AS volume,
    SUM(status NOT IN (5,6))                                AS backlog,
    ROUND(AVG(NULLIF(takeintoaccount_delay_stat,0))/60, 1) AS tto_min,
    ROUND(AVG(NULLIF(solve_delay_stat,0))/3600, 1)         AS tmr_horas,
    ROUND(
      100 * SUM(solvedate IS NOT NULL AND solvedate <= time_to_resolve)
          / NULLIF(SUM(time_to_resolve IS NOT NULL AND solvedate IS NOT NULL), 0)
    , 1)                                                    AS sla_ttr_pct
FROM glpi_tickets
WHERE is_deleted = 0
  AND date >= DATE_FORMAT(CURDATE(), '%Y-%m-01');

Reopening has no column of its own: it lives in the state-change history. In glpi_logs, a reopening is a ticket that left Solved (status 5) and came back to work. The log stores the value as "5 Solved", so we filter by the numeric prefix to avoid depending on the install language:

-- Reopenings this month: tickets that left Solved (5) and went back
-- to work. In glpi_logs, id_search_option 12 = the status field
SELECT COUNT(DISTINCT items_id) AS reabertos
FROM glpi_logs
WHERE itemtype = 'Ticket'
  AND id_search_option = 12
  AND old_value LIKE '5 %'
  AND new_value REGEXP '^[123] '
  AND date_mod >= DATE_FORMAT(CURDATE(), '%Y-%m-01');

Automating the monthly collection

Running the query by hand every 1st does not scale. We schedule the extraction with the system cron, pointing to a credentials file .cnf (chmod 600) so a password never sits on the command line, and we store the CSV outside GLPI. That is how the time series the native dashboard does not keep is born:

# /etc/cron.d/glpi-kpi  ->  day 1 at 06:00, as www-data
# Credentials in the .cnf file (chmod 600), never on the command line
0 6 1 * * www-data /usr/bin/mysql --defaults-file=/etc/glpi/kpi-ro.cnf glpi \
  < /opt/glpi-kpi/kpi_mensal.sql >> /var/log/glpi-kpi/kpi-$(date +\%Y-\%m).csv 2>&1

Targets: honest ranges, not magic numbers

• SLA compliance (TTR): above 90-95% is healthy for most contracts. A constant 100% usually signals an SLA that is too loose, not excellence.
• Reopens: below 5%. Above that, investigate incomplete resolution or a rushed close to "beat" the deadline.
• First-level resolution: 70-80%. Below 60% points to a lack of training or poor categorization; well above 90%, suspect complex tickets being misclassified.
• Satisfaction (CSAT): above 4.0/5.0, but always read alongside the survey response rate.

Traps that have already caused on-call incidents

In client environment maintenance, the first thing we do when taking over a GLPI that was already running is not to trust the native dashboard until we validate the base. At one client with high monthly ticket volume, the "official" MTTR looked great until we noticed that a large share of tickets had the same user as author and technician right at opening: a self-assignment that zeroes out takeintoaccount_delay_stat and masks the real response time. We standardized the extraction via scheduled SQL precisely because the Statistics module keeps no month-by-month history; without a time series, you compare against memory, not data.

The field traps that most distort the reading:

• Measuring MTTR by date difference. solvedate - date inflates the number: nights, weekends and "Pending" time all creep in. Use solve_delay_stat, which GLPI already adjusts by the calendar.
• Confusing Solved with Closed. If there is auto-close after X days, measuring only status = 6 hides the solved tickets awaiting confirmation. The backlog must exclude 5 and 6.
• Celebrating a low TTO from self-assignment. A ticket opened already assigned zeroes the response time; the number drops without the operation improving at all.
• Reading satisfaction without the response rate. When only the dissatisfied answer, the average deceives. Cross satisfaction with how many surveys were actually answered.

If your GLPI shows numbers nobody trusts, the problem is rarely GLPI itself: it is the base and the way you extract. NexTool GLPI maintenance includes modeling these indicators and the consolidated monthly report that leadership reads without a spreadsheet.

This content was produced with the aid of artificial intelligence and reviewed by the Nextool Solutions team.