Announcement

Collapse
No announcement yet.

SQL queries directly to db for reporting

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

    SQL queries directly to db for reporting

    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.

    #2
    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, ...).
    Devops Monitoring Expert advice: Dockerize/automate/monitor all the things.
    My DevOps stack: Docker / Kubernetes / Mesos / ECS / Terraform / Elasticsearch / Zabbix / Grafana / Puppet / Ansible / Vagrant

    Comment


      #3
      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');

      Comment

      Ask questions to Zabbix Dev Team in person at the Zabbix Summit 2018!
      Working...
      X