Latest posts by khushvinder (see all)
- How to get overall CPU utilization in zabbix - June 15, 2019
- How to write discovery rule in zabbix and create prototype items - June 15, 2019
- important zabbix database queries (MySQL) for daily routine reports - June 15, 2019
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.
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.
127 total views, 1 views today