zabbix_db_query
The following two tabs change content below.
Hi, I have written and developed this site to share my experience and ideas with other colleagues. I also started to prepare interview questions and answers for job seekers. I hope it will help you a lot.

As we know zabbix is very vast growing opensource framework for server/network devices/url etc. monitoring. But still zabbix not providing daily routine reports. Like last week added/deleted device list, List of device who’s SNMP not working etc.

Admin team every day wants lots of report to share the status with management or with any  other concern team. May be zabbix will add these reports in upcoming version but till that time we can create those reports why own. So I am planning to share few importtent query to get those report data from zabbix database by running below queries.

Note: In this example I am using 3.4 zabbix version. So mostly these query will run in all version zabbix but may be some/new version came up with new DB schema.

Data model for zabbix 3.4 has been attached.

1. Added host in last month

Note: In this example I have given last month date so that from last month to till now we can get data. You can change data according to your requirement.

SELECT h.name,h.host,hd.date FROM zabbix.hostdetails hd, hosts h 
where hd.hostid = h.hostid and date > '2019-05-01 00:00:00';

 

2. Deleted host in last month

select a.resourcename, a.details,u.name from auditlog a, users u
where a.userid = u.userid and a.action = 2 and a.resourcetype = 4 and FROM_UNIXTIME(a.clock) > '2019-05-01 00:00:00';

 

3. Last month kept devices in maintenance window

select h.name,h.host,m.name as 'maintenance name',FROM_UNIXTIME(m.active_since),FROM_UNIXTIME(m.active_till) from maintenances m, hosts h, maintenances_hosts mh
where m.maintenanceid = mh.maintenanceid and mh.hostid = h.hostid and FROM_UNIXTIME(m.active_since) > '2019-05-01 00:00:00';

 

4. Get all URL which we are monitoring in zabbix.

use zabbix;
select h.name,h.host,i.name, hs.name,hs.url,hs.status_codes from httpstep hs, hosts h, httpstepitem hsi, items i
where
hs.httpstepid = hsi.httpstepid and
hsi.itemid = i.itemid and
i.hostid = h.hostid;


5. As we have Action log in zabbix but in that performance is very slow and also not allowing to filtter based on host etc. So we can use below query to get required fields based on any filter. To get information like who enable/disable the host.

SELECT resourcename, from_unixtime(clock), u.name,u.surname FROM zabbix.auditlog a, users u where resourceid = <<HOST_ID>> and a.userid=u.userid;

 

6. List of host where zabbix agent giving connection issue.

select name,host from hosts where available = 2;

 

7. List of host where SNMP giving connection issue.

select name,host from hosts where snmp_available = 2;

 

Please share your comment or let me know if you need any more query also for report. I will be happy to help you.

216 total views, 2 views today

Leave a Reply

Your email address will not be published. Required fields are marked *