Ad Widget


SQL queries directly to db for reporting

  • Filter
  • Time
  • Show
Clear All
new posts

    SQL queries directly to db for reporting


    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.


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

    That DB schema information may not be updated and there may be also a dependency on used DB (MySQL, PostgreSQL, Oracle, ...).
    Devops Monitoring Expert advice: Dockerize/automate/monitor all the things.
    My DevOps stack: Docker / Kubernetes / Mesos / ECS / Terraform / Elasticsearch / Zabbix / Grafana / Puppet / Ansible / Vagrant


      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 as hostname,
      h.status as hoststatus, 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

      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

      i.hostid as ihostid,
      i.itemid as iitemid,
      i.description as idescription,
      i.key_ as ikey, as iname,
      hs.url as hsurl, 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

      ((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');



      No announcement yet.