List events from a service for SLA reporting

[GPD]

Good afternoon,
I need to list the events of a particular service or sub-service for data extraction for SLA (evidence) reporting.
How do I search all events of a service/subservice for a given period?
It can be via API or directly in the database (MySql), if you can help with the relationship of the tables to achieve this result,
I appreciate it.

[splitek]

I'm PostgreSQL "boy" so I don't know if it help you.
In Zabbix 5.0 to have SLA calculated trigger must be linked to the IT service object. So you need to find all events generated for this trigger. For one service it is easy. If you need to get events from all sub-tree leafs then first you need a list of all triggers in this particular sub-tree.
Here is function to get all servicces from sub-tree for PostgreSQL: https://github.com/splitek/zabbix-it_service_tree
This function return "servicedownid" and this can be connected with triggers (connecting service object with trigger). Next... triggers need to be connected with events. This way we find events for all sub tree objects.
In the future (Zabbix 6.0) I think it will be easier because IT service tree will use tags (so we will search events by tags).
Below is SQL that I use with that function to get events for all IT services in "MY_IT_SERVICES_TREE", events for selected time:

SELECT "Start", "End", duration, services.name, "Problem", items_ids
FROM
(
   SELECT to_char(to_timestamp(e.clock) AT time ZONE 'Europe/Warsaw','YYYY-MM-DD HH24:MI:SS') AS "Start",
                to_char(to_timestamp(e2.clock) AT time ZONE 'Europe/Warsaw','YYYY-MM-DD HH24:MI:SS') AS "End",
                (e2.clock-e.clock) AS duration,
                e."name" AS "Problem",
                array_to_string(array_agg(DISTINCT items.itemid),',') AS items_ids,
                e.objectid
   FROM events e
   LEFT JOIN event_tag t ON t.eventid = e.eventid
   LEFT JOIN event_recovery er ON er.eventid = e.eventid
   FULL OUTER JOIN events e2 ON e2.eventid = er.r_eventid
   INNER JOIN functions f ON f.triggerid = e.objectid
   INNER JOIN items ON items.itemid = f.itemid
   WHERE
   e.objectid IN
                (
                SELECT triggerid FROM services
                WHERE serviceid IN (SELECT servicedownid FROM its_servicetree_byname(2,'MY_IT_SERVICES_TREE'))
                )
   AND e."object" = 0
   AND e."source" = 0
   AND e.value = 1
   AND
   (
                (e.clock BETWEEN 1627167850 AND 2627249150) -- events that start between dates
                OR
                (e2.clock BETWEEN 1627167850 AND 2627249150) -- events that ends between dates
                OR
                (e.clock < 1627167850 AND (e2.clock >2627249150 OR e2.clock IS NULL)) -- overlapping events
   )
   GROUP BY e.clock, e2.clock, e."name", e.objectid
) AS x
INNER JOIN services ON x.objectid = services.triggerid
ORDER BY "End" DESC NULLS FIRST;