NexTool Solutions

Mean time to resolution measures who already left the queue. Average ticket age measures who is still in it - and that is where the forgotten backlog hides. This guide shows where the number comes from in GLPI, why the average alone misleads, how to separate age from staleness, and how to turn it into a weekly backlog review in the environments we support.

What average age measures - and what it hides

Average ticket age is the mean number of days between opening and today, counting only tickets that are still open. Unlike MTTR, which only sees what was already resolved, it photographs the current state of the queue. The catch is that the mean, on its own, is a poor summary of a real queue: a typical backlog is bimodal - dozens of brand-new tickets a few hours old live alongside a tail of tickets stuck for weeks. The mass of new tickets pulls the average down and hides exactly the old ones you need to find. That is why the average never travels alone: it comes with a distribution and a median.

Where the number comes from in GLPI

Everything lives in the glpi_tickets table. Age uses the date column (opening date); the "open" filter uses status. The states that matter:

• 1 - New (not yet triaged)
• 2 - Processing (assigned)
• 3 - Processing (planned)
• 4 - Pending (waiting on a third party)
• 5 - Solved and 6 - Closed (out of the backlog)

The most common mistake here is throwing everything that is not 5 or 6 into the same pot. Tickets in Pending (status 4) are legitimately stopped waiting on a supplier, the customer or a part - they inflate the average age without anyone having forgotten anything. Segmenting by status is the first honest filter:

-- Average age by status (segment: "Pending" inflates the average)
SELECT
  status,
  ROUND(AVG(DATEDIFF(NOW(), date)), 1) AS avg_age_days,
  COUNT(*)                             AS total
FROM glpi_tickets
WHERE status NOT IN (5, 6)   -- exclude Solved and Closed
  AND is_deleted = 0
GROUP BY status
ORDER BY avg_age_days DESC;
-- status: 1 New, 2 assigned, 3 planned, 4 Pending

Age is not the same as staleness

A ticket opened 40 days ago but with a task logged yesterday is very different from one opened 40 days ago that nobody has touched. Age (since opening) measures seniority; staleness (since the last human action) measures abandonment - and it is staleness that points to the forgotten ticket.

In support work, the mistake we have seen cost dearly is using date_mod as the "last action". The ticket's date_mod column is updated by anything: a notification sent, a follower added, the SLA cron recomputing a deadline, a business rule firing. In other words, it gives the illusion of movement on tickets nobody actually worked. The real last human action lives in the followup and the task - glpi_itilfollowups and glpi_tickettasks. When we swapped date_mod for the maximum date of those two tables, one customer's stale list jumped from 3 to 17 tickets: the other 14 looked alive only because an automation brushed against them every day.

-- Real staleness: days since the last human interaction
-- date_mod is unreliable (notifications, followers and cron bump it)
SELECT
  t.id,
  t.name,
  DATEDIFF(NOW(), t.date) AS age_days,
  DATEDIFF(NOW(), GREATEST(
      t.date,
      COALESCE(MAX(f.date), t.date),
      COALESCE(MAX(k.date), t.date)
  ))                       AS idle_days
FROM glpi_tickets t
LEFT JOIN glpi_itilfollowups f
       ON f.itemtype = 'Ticket' AND f.items_id = t.id
LEFT JOIN glpi_tickettasks k
       ON k.tickets_id = t.id
WHERE t.status NOT IN (4, 5, 6)   -- exclude Pending, Solved, Closed
  AND t.is_deleted = 0
GROUP BY t.id, t.name, t.date
HAVING idle_days >= 7
ORDER BY idle_days DESC;

Distribution, not just the average

Before reacting to an average that went up, look at the shape of the queue. The query below breaks the backlog into age brackets and reveals the long tail the mean flattens:

-- Backlog distribution by age bracket
SELECT
  CASE
    WHEN DATEDIFF(NOW(), date) <= 3  THEN '0-3 days'
    WHEN DATEDIFF(NOW(), date) <= 7  THEN '3-7 days'
    WHEN DATEDIFF(NOW(), date) <= 15 THEN '7-15 days'
    WHEN DATEDIFF(NOW(), date) <= 30 THEN '15-30 days'
    ELSE '30+ days'
  END        AS bracket,
  COUNT(*)   AS tickets
FROM glpi_tickets
WHERE status NOT IN (4, 5, 6)
  AND is_deleted = 0
GROUP BY bracket
ORDER BY MIN(DATEDIFF(NOW(), date));

As an honest reference range (calendar days, not business days): under 3 days is healthy, 3 to 7 days is acceptable, 7 to 15 days raises a flag, and above 15 days calls for a ticket-by-ticket review. These are not universal targets - the fair number depends on the contract and the type of operation.

From the number to action: a decision matrix

High age is not a problem; it is a symptom with several causes. What fixes it is reading the technical signal and classifying before acting:

One silent case worth highlighting: tickets stuck in New because the assignment business rule did not match the category and never set a group. They age invisibly - they drop off the per-group dashboards and only surface when you look at the average age with no group filter.

Automating the weekly review

Running the query by hand does not scale. We save the staleness query in a .sql file and schedule it in the system cron, reading credentials from a .cnf file (chmod 600) so a password never sits on the command line. The result is a weekly CSV that becomes the agenda of the backlog review:

# /etc/cron.d/glpi-idade  ->  every Monday 07:30, as www-data
# Builds the list of stale tickets (7+ days) for the backlog review
30 7 * * 1 www-data /usr/bin/mysql --defaults-file=/etc/glpi/kpi-ro.cnf glpi \
  < /opt/glpi-kpi/estagnados.sql \
  > /var/log/glpi-kpi/estagnados-$(date +\%Y-\%m-\%d).csv 2>&1

Common field mistakes

• Looking only at the mean: it flattens the tail. Always track the distribution and, when possible, the median.
• Mixing Pending into the pot: status 4 is a legitimate wait; on its own it lifts the average and triggers a false alarm.
• Using date_mod as the last action: automations update it. Use the followup and task dates.
• Comparing entities with different calendars: DATEDIFF counts calendar days; entities with different working hours are not comparable one to one.
• Forgetting is_deleted = 0: the GLPI trash bin does not leave the table and contaminates any average.

Next step

Combine average age with the rest of your Service Desk KPIs and put it all on a dashboard in Metabase or Grafana with entity and group filters. That way age stops being a number at month end and becomes an alert that fires while there is still time to act.

At NexTool, backlog age and staleness go into the monthly support report we deliver to clients, along with the list of tickets to recover. If you want that oversight on your GLPI, take a look at our support and sustaining service.

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