SQL queries directly to db for reporting

[voculus]

Hi,

We are trying to develop custom SQL queries to be integrated into a report generation system (e.g. jasper reports).

Where can we find documentation on the description of the MySQL tables of the Zabbix database?

For example, how would the SQL query look like to produce a list of the top 10 alerts in the past week (or some user-specified time range)? Which tables would the data need to be pulled from?

Any pointers or hints would be greatly appreciated.

Thanks.

[jan.garaj]

SQL schema is not documented officially. API is the official recommended approach. Some community effort has been made on zabbix.org, so if you really want to use SQL, then start on

http://zabbix.org/wiki/Docs/DB_schema

http://zabbix.org/wiki/Database_Schemas

That zabbix.org DB schema information may not be updated and there may be also a dependency on used DB (MySQL, PostgreSQL, Oracle, ...).

[khannah2018]

This is a query that I wrote for integrating with the Zabbix db, it may be what you are looking for.
It joins the hosts and host groups and then left joins unresolved events for those hosts (acknowledged or unacknowledged).
Host status are those that are enabled (0) or in an unreachable state (2). When an event gets the "resolved" status, it will drop off this list.

select

h.name as hostname,
h.status as hoststatus,
g.name as groupname,
hi.alias as hostalias,
hi.location as hostlocation,
hi.os as hostos,
dt.idescription as itemdescription,
dt.ikey as itemkey_,
dt.iname as itemname,
dt.hsurl as httpstepurl,
dt.hsname as httpstepname,
dt.tcomments as triggercomments,
dt.tdescription as triggerdescription,
dt.tpriority as triggerpriority,
dt.eventclock as eventclock,
dt.eacknowledged as eventacknowledged


from
hosts h
inner join hosts_groups hg on h.hostid=hg.hostid
inner join groups g on hg.groupid = g.groupid
left join host_inventory hi on h.hostid=hi.hostid

LEFT JOIN
(SELECT
i.hostid as ihostid,
i.itemid as iitemid,
i.description as idescription,
i.key_ as ikey,
i.name as iname,
hs.url as hsurl,
hs.name as hsname,
t.description as tdescription,
t.url as turl,
t.comments as tcomments,
t.priority as tpriority,
from_unixtime(e.clock) as eventclock,
e.acknowledged as eacknowledged
from items i
left join functions f on i.itemid = f.itemid
left join triggers t on f.triggerid = t.triggerid
right join events e on t.triggerid = e.objectid
left join httpstepitem hsi on i.itemid = hsi.itemid
left join httpstep hs on hsi.httpstepid = hs.httpstepid
inner join problem p on e.eventid = p.eventid

WHERE
((e.acknowledged='0' AND i.status='0' AND r_clock='0') OR (e.acknowledged='1' AND i.status='0' AND r_clock='0'))


) dt ON h.hostid = dt.ihostid

where (h.status='2' or h.status='0');