How to Get Only Active Zabbix Alarms via SQL (PostgreSQL)?

[gabrielcs_dev]

Has anyone managed to create an SQL query for PostgreSQL that returns exactly the active alarms from Zabbix, as shown in the "Incidents" tab? I'm trying to ensure that the query reflects only truly open incidents — with no duplicates, no resolved events, and correctly respecting the current state of each trigger.

I've gotten close, but the query still returns more incidents than it should and misses some actual ones.
Could someone please help me out?

WITH last_items_per_trigger AS (
  SELECT DISTINCT ON (f.triggerid)
    f.triggerid,
    f.itemid
  FROM functions f
  ORDER BY f.triggerid, f.itemid
),
active_problems AS (
  SELECT DISTINCT ON (p.objectid)
    p.eventid,
    p.objectid    AS triggerid,
    to_timestamp(p.clock) AS event_time,
    p.name        AS trigger_name,
    i.name        AS item_name,
    COALESCE(it.ip, '') || COALESCE(it.dns, '') AS ip_dns,
    h.name        AS host,
    p.severity
  FROM problem p
  JOIN events e ON e.eventid = p.eventid AND e.value = 1
  JOIN triggers t ON t.triggerid = p.objectid AND t.value = 1 AND t.status = 0
  JOIN last_items_per_trigger f ON f.triggerid = t.triggerid
  JOIN items i ON i.itemid = f.itemid
  JOIN hosts h ON h.hostid = i.hostid AND h.status = 0 AND h.maintenance_status = 0
  LEFT JOIN interface it ON it.hostid = h.hostid
  WHERE
    p.r_eventid IS NULL
    AND p.object = 0
  ORDER BY p.objectid, p.clock DESC
)
SELECT
  ROW_NUMBER() OVER (ORDER BY event_time DESC) AS incidente_n,
  ap.host,
  ap.trigger_name,
  ap.item_name,
  ap.ip_dns,
  ap.event_time,
  CASE ap.severity
    WHEN 0 THEN 'Not classified'
    WHEN 1 THEN 'Information'
    WHEN 2 THEN 'Warning'
    WHEN 3 THEN 'Average'
    WHEN 4 THEN 'High'
    WHEN 5 THEN 'Disaster'
  END AS severidade,
  CONCAT(
    DATE_PART('day', NOW() - ap.event_time)::int, 'd ',
    (DATE_PART('hour', NOW() - ap.event_time)::int % 24)::int, 'h ',
    (DATE_PART('minute', NOW() - ap.event_time)::int % 60)::int, 'm'
  ) AS duracao,
  STRING_AGG(pt.tag || ': ' || pt.value, ', ') AS tags
FROM active_problems ap
LEFT JOIN problem_tag pt ON pt.eventid = ap.eventid
GROUP BY
  ap.host, ap.trigger_name, ap.item_name, ap.ip_dns, ap.event_time, ap.severity
ORDER BY event_time DESC
LIMIT 1001;​

[gabrielcs_dev]

I was able to resolve the issue.

SELECT
    ROW_NUMBER() OVER (ORDER BY TO_TIMESTAMP(p.clock) DESC) AS "Linha",
    TO_CHAR(TO_TIMESTAMP(p.clock), 'YYYY-MM-DD HH24:MI:SS') AS "Horario",
    CASE
        WHEN p.severity = 0 THEN 'Not classified'
        WHEN p.severity = 1 THEN 'Information'
        WHEN p.severity = 2 THEN 'Warning'
        WHEN p.severity = 3 THEN 'Average'
        WHEN p.severity = 4 THEN 'High'
        WHEN p.severity = 5 THEN 'Disaster'
        ELSE 'Unknown'
    END AS "Severidade",
    p.name AS "Problema",
    FLOOR(EXTRACT(DAY FROM NOW() - TO_TIMESTAMP(p.clock))) || 'd ' ||
    MOD(EXTRACT(HOUR FROM NOW() - TO_TIMESTAMP(p.clock)), 24) || 'h ' ||
    MOD(EXTRACT(MINUTE FROM NOW() - TO_TIMESTAMP(p.clock)), 60) || 'm' AS "Duracao",
    h.host AS "Host",
    it.ip || it.dns AS "IP"
FROM problem p
INNER JOIN (
    SELECT MAX(itemid) AS itemid, triggerid
    FROM functions
    GROUP BY triggerid
) f ON p.objectid = f.triggerid
INNER JOIN items i ON f.itemid = i.itemid
INNER JOIN hosts h ON i.hostid = h.hostid
LEFT JOIN interface it ON it.interfaceid = i.interfaceid
INNER JOIN triggers t ON f.triggerid = t.triggerid
-- Join correto com a tabela 'events'
INNER JOIN events e ON e.eventid = p.eventid
-- Exclusão de suprimidos
LEFT JOIN event_suppress es ON es.eventid = e.eventid
WHERE
    t.value = 1
    AND t.status = 0
    AND p.r_clock = 0
    AND i.status = 0
    AND h.status = 0
    AND es.eventid IS NULL  -- ❌ exclui problemas suprimidos
ORDER BY "Horario" DESC;