Grafana + OTRS Integration: Monitoring SLA and IMS Tickets in Real Time
In environments driven by governance and certified by ISO 9001 and ISO 20000 standards, IT ticket management and SLA (Service Level Agreements) compliance must be closely monitored. Traditional use of OTRS provides excellent logging features, but lacks real-time, consolidated visualization dashboards for executive management.
In this article, I show how I structured a transparent integration between the relational database of OTRS and dynamic Grafana dashboards for monitoring the Integrated Management System (IMS).
Data Modeling
To feed Grafana, we directly connect to a read replica of the MySQL/PostgreSQL database of OTRS. We write SQL queries focused on extracting: First Response Time. Volume of open vs. closed tickets by service queue. Critical tickets at risk of breaching the SLA window.-- Example query to calculate the percentage of tickets within the SLA
SELECT
queue.name AS 'Fila',
COUNT(ticket.id) AS 'Total',
SUM(CASE WHEN ticket.ticket_state_id IN (2, 3) THEN 1 ELSE 0 END) AS 'Resolvidos'
FROM ticket
JOIN queue ON ticket.queue_id = queue.id
GROUP BY queue.name;Tickets about to miss the SLA
The per-queue volume query does not say which ticket will breach the window. This lists what is still open and already past 4 hours, the number the OTRS SLA panel paints red.
SELECT t.tn,
q.name AS queue_name,
TIMESTAMPDIFF(MINUTE, t.create_time, NOW()) AS minutes_open
FROM ticket t
JOIN queue q ON q.id = t.queue_id
WHERE t.ticket_state_id = 1
AND TIMESTAMPDIFF(MINUTE, t.create_time, NOW()) > 240
ORDER BY minutes_open DESC;In Grafana that query becomes a stat panel with a threshold at 240. The alert leaves through the contact point operations already uses, without waiting for the weekly OTRS report.
Practical Benefits
Agile Decision Making: With monitors installed in support rooms, the infrastructure team sees incident peaks before they affect the SLA. Audit Compliance: Presenting automated and historical dashboards during ISO 9001/14001 audits demonstrates transparency and systemic control. Bottleneck Reduction: We easily identify which queues (e.g., SAP B1 ERP support vs. cloud infrastructure) required team reinforcement or L1 level automation.Related Articles
IT Governance Guidelines: Aligning Operations with ISO Standards
How to create corporate security and infrastructure policies that are in full compliance with ISO 9001, 14001, 45001, and 50001 standards.
Multi-cloud for Banks: Separating the Core, Data, and the Audit Trail
Multi-cloud architecture for banks: a locked region, a core isolated from the lab, and an audit trail nobody can delete. SCP, CloudTrail, and Object Lock commands.