Zabbix Reporting Queries

[Erik]

Hello everyone,

We are using Zabbix now for quite a while within my company.
We are quite happy with the system, the only downside is the lack of reporting.
I noticed that there is a huge group of people which has the same issue.

I have created a dashboard at our company. This dashboard is filled with information regarding our environment.

It holds information about Active directory, our Servicedesk system, our patching system, our Anti virus solution and also about Zabbix.

In this tread, and this is my first post on this forum so I might put it in the wrong location(sorry for that) I will share queries which we created or collected in the past. I will try to update this from time till time, but my goal is to create a thread where all of you do the same.

As mentioned at this moment it seems that the lack of reporting is one of the downsides of Zabbix. I got noticed they are working on this(not official).

Still in the meantime it would be great if we could help each other out.

[Erik]

What is important to know is that we first export the necessary information from the Zabbix DB to a SQL database.
So if you want to run the query directly on the Zabbix DB you might need to tweak it a little bit.

Here is a query which will show you all the open issues at the moment:

Select Distinct
  hosts.host as Asset,
  triggers.description as Error,
  DateAdd(S, triggers.lastchange, '1970-01-01') As 'LastChange'
From
  triggers Inner Join
  functions On functions.triggerid = triggers.triggerid Inner Join
  items On items.itemid = functions.itemid Inner Join
  hosts On items.hostid = hosts.hostid Inner Join
  events On events.objectid = triggers.triggerid
Where
  (triggers.value = 1 Or
    (triggers.value = 0 And
      DateDiff(minute, (DateAdd(S, triggers.lastchange, '1970-01-01')),
      GetDate()) <= 1)) And
  (events.object - 0) = 0 And
  hosts.status = 0 And
  items.status = 0 And
  triggers.status = 0
Order By
  'LastChange' Desc

[Erik]

What is important to know is that we first export the necessary information from the Zabbix DB to a SQL database.
So if you want to run the query directly on the Zabbix DB you might need to tweak it a little bit.
Here is query which will show you the TOP 30 assets with the most incidents within the last 30 days.

Select Top 30
  hosts.name,
  triggers.description as Error,
  Count(Distinct events.eventid) As Incidents
From
  triggers Inner Join
  events On events.objectid = triggers.triggerid Inner Join
  functions On functions.triggerid = triggers.triggerid Inner Join
  items On items.itemid = functions.itemid Inner Join
  hosts On items.hostid = hosts.hostid Inner Join
  hosts_groups On hosts.hostid = hosts_groups.hostid
Where
  DateDiff(day, (DateAdd(S, events.clock, '1970-01-01')), GetDate()) <= 30  and
  triggers.flags In ('0', '4') and
  events.source = 0 And
  events.object = 0 
Group By
  hosts.name, triggers.description, triggers.priority
Order By
Incidents Desc

[SBO]

Hi Erik,

Thanks for creating this topic and sharing your script/queries.
Is there a reason for choosing SQL queries instead of API calls ? (Just being curious on your choice)

[Erik]

Hi Erik,

Thanks for creating this topic and sharing your script/queries.
Is there a reason for choosing SQL queries instead of API calls ? (Just being curious on your choice)

Hi,

Good question, main reason is our knowledge.
Also by doing this we are able to compare the data with other systems.
If there is a proper API which we could integrate with our dashboard.

Br,

Erik

[SBO]

As far as I know, pyZabbix is pretty popular :

https://github.com/lukecyca/pyzabbix

And you have also the Zabbix Gnomes (https://github.com/q1x/zabbix-gnomes), some snippets based on pyZabbix, pretty useful to have some examples of how to use the API with this tool.

I'm working on the IT services these days, I'll probably share some snippets too, based on pyzabbix.