NexTool Solutions

Visibility isn't about having more reports - it's about seeing the state of the queue while you can still act. GLPI already stores everything you need; the problem is that the data is scattered and the native dashboard only shows the past. This guide shows where real visibility lives in GLPI, which diagnostic queries we run when we take over the support of an environment, how to anticipate an SLA breach before it happens, and how to turn all of that into an automatic alert.

The visibility GLPI already gives you - and where it stops

GLPI ships with dashboards on the Central homepage and a powerful filtered search. For day-to-day work, that solves a lot: counts by status, by technician, by category. The limit shows up in three places:

• It's on demand, not proactive. The dashboard only refreshes when someone opens the screen. Nobody is warned when a bottleneck forms - you have to go look.
• It counts what your profile can see. The number on the dashboard depends on the entity and profile of whoever is logged in. An entity coordinator and the global admin see different totals for the same queue.
• It shows the aggregate, not the forgotten ticket. "42 open tickets" doesn't tell you which one has been stuck for 20 days.

Real visibility starts when you stop looking at the total and start looking at the shape of the queue and at what is about to go wrong.

Step 1: where the backlog is piling up

Before any pretty dashboard, the first x-ray is the backlog distribution by group and status. Everything lives in glpi_tickets; the group assignment lives in glpi_groups_tickets (type 2 = assigned). The statuses that matter: 1 New, 2/3 Processing, 4 Pending, 5 Solved, 6 Closed.

-- Open backlog by assigned group (type 2 = assigned)
SELECT
  g.completename                    AS team,
  COUNT(*)                          AS open_tickets,
  SUM(t.status = 1)                 AS new_untriaged,
  SUM(t.status = 4)                 AS pending
FROM glpi_tickets t
JOIN glpi_groups_tickets gt
     ON gt.tickets_id = t.id AND gt.type = 2
JOIN glpi_groups g ON g.id = gt.groups_id
WHERE t.status NOT IN (5, 6)
  AND t.is_deleted = 0
GROUP BY g.id
ORDER BY open_tickets DESC;

In seconds you see which group is overloaded and how much of the backlog is new tickets nobody has triaged - the number the native dashboard hides behind the total.

Step 2: the SLA that is about to breach before it breaches

This is where a reactive operation and an anticipating one part ways. When an SLA matches the ticket, GLPI writes the deadline into dedicated columns of glpi_tickets: time_to_resolve (resolution deadline) and time_to_own (assignment deadline). That's a clock - you can look at what expires in the next few hours, not just what already expired:

-- Open tickets whose resolution SLA breaches within the next 4h
SELECT
  t.id,
  t.name,
  t.time_to_resolve,
  TIMESTAMPDIFF(MINUTE, NOW(), t.time_to_resolve) AS minutes_left
FROM glpi_tickets t
WHERE t.status NOT IN (4, 5, 6)        -- exclude Pending, Solved, Closed
  AND t.is_deleted = 0
  AND t.time_to_resolve IS NOT NULL
  AND t.time_to_resolve > NOW()
  AND t.time_to_resolve <= NOW() + INTERVAL 4 HOUR
ORDER BY t.time_to_resolve ASC;

The field trap is in that IS NOT NULL. The time_to_resolve column is only filled when an SLA rule matched the ticket at creation. A ticket that matched no rule keeps a NULL deadline - and silently drops off the risk dashboard. In other words: tickets with no SLA, precisely the ones nobody is measuring, are invisible to any deadline-based alert. When we take over an environment, the first count we run is how many open tickets have time_to_resolve IS NULL. We've seen a client with 60% of the backlog carrying no SLA at all: the dashboard said "everything on time" because half the queue simply had no deadline.

Step 3: the owner who doesn't exist

Another classic blind spot: a ticket assigned only to a group, with no named technician. On screen it looks assigned; in practice, nobody feels like the owner. Those tickets vanish from personal dashboards ("my tickets") and age invisibly. The person assignment lives in glpi_tickets_users with type = 2:

-- Open tickets assigned to a group but WITHOUT a named technician
SELECT COUNT(*) AS no_owner
FROM glpi_tickets t
WHERE t.status NOT IN (5, 6)
  AND t.is_deleted = 0
  AND NOT EXISTS (
    SELECT 1 FROM glpi_tickets_users tu
    WHERE tu.tickets_id = t.id AND tu.type = 2
  );

The layers of visibility: what to use for what

There is no single tool. Each layer solves a problem and has a limit. This is how we decide where to put each indicator:

Step 4: turn diagnosis into an automatic alert

Running a query by hand doesn't scale and, worse, relies on someone remembering to look. Two ways to close that loop in GLPI:

1. Native, no extra infrastructure: save the "SLA expiring" or "pending for more than 7 days" search and create a saved-search alert. GLPI's cron runs the search periodically and fires an e-mail when the count crosses the threshold you set.
2. Outside GLPI, for a wall board and history: schedule the diagnostic query in the system cron, reading the credential from a .cnf file (chmod 600) so you never leave a password on the command line.

# /etc/cron.d/glpi-visibility  ->  every business hour, as www-data
# Lists tickets whose SLA breaches within the next 4h for the on-call queue
0 8-18 * * 1-5 www-data /usr/bin/mysql --defaults-file=/etc/glpi/kpi-ro.cnf glpi \
  < /opt/glpi-kpi/sla-breaching.sql \
  > /var/log/glpi-kpi/sla-risk-$(date +\%Y-\%m-\%d-\%H).csv 2>&1

Before scheduling, make sure GLPI's own cron is actually running in external mode (the system cron calling front/cron.php) - otherwise SLA deadlines and native alerts aren't even recalculated, and you'd be reading a stopped clock.

The turning point, from support practice

When we take over a client's GLPI, the first x-ray is never the native dashboard - it's the count of tickets assigned only to a group (no owner) and of tickets with a null time_to_resolve. In one case, 40% of the backlog was "assigned" to a group of eight people and nobody felt responsible; none of those tickets showed up on a personal dashboard and the oldest was 90 days old. The fix wasn't a new dashboard - it was a business rule forcing a named technician at assignment, plus an alert for SLAs 4 hours from breaching. Visibility didn't come from buying a tool; it came from looking at the right fields.

Common field mistakes

• Trusting the dashboard total: it aggregates and hides the stuck ticket; look at the distribution by group and by age.
• Assuming every ticket has an SLA: time_to_resolve NULL = outside any deadline alert. Count the ones without an SLA first.
• Treating "assigned to a group" as "has an owner": without a row in glpi_tickets_users with type = 2, nobody is responsible.
• Comparing entities by the same number: the native dashboard already filters by profile and entity; the total changes with who is logged in.
• Forgetting is_deleted = 0: the GLPI trash doesn't leave the table and pollutes any count.
• Measuring with the cron stopped: without the external cron running, SLA and native alerts don't update.

Next step

Start with the three counts in this guide (no owner, no SLA, SLA about to expire), push those cuts to a dashboard in Metabase or Grafana and wire your Service Desk KPIs to alerts. That way the queue stops being a number at month-end and becomes a signal that fires while there's still time to act.

At NexTool, this visibility x-ray is the first deliverable when we take over the support of a GLPI: we map the blind spots, wire up the alerts and hand over the dashboard. If you want that oversight on your own environment, take a look at our support and maintenance service.

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