Post Syndicated from Aigars Kadiķis original https://blog.zabbix.com/summarize-devices-that-are-not-reachable/13219/
In this lab, we will list all devices which are not reachable by a monitoring tool. This is good when we want to improve the overall monitoring experience and decrease the size queue (metrics which has not been arrived at the instance).
Tools required for the job: Access to a database server or a Windows computer with PowerShell
To summarize devices that are not reachable at the moment we can use a database query. Tested and works on 4.0, 5.0, on MySQL and PostgreSQL:
SELECT hosts.host, interface.ip, interface.dns, interface.useip, CASE interface.type WHEN 1 THEN 'ZBX' WHEN 2 THEN 'SNMP' WHEN 3 THEN 'IPMI' WHEN 4 THEN 'JMX' END AS "type", hosts.error FROM hosts JOIN interface ON interface.hostid=hosts.hostid WHERE hosts.available=2 AND interface.main=1 AND hosts.status=0;
A very similar (but not exactly the same) outcome can be obtained via Windows PowerShell by contacting Zabbix API. Try this snippet:
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]" $headers.Add("Content-Type", "application/json") $url = 'http://192.168.1.101/api_jsonrpc.php' $user = 'api' $password = 'zabbix' # authorization $key = Invoke-RestMethod $url -Method 'POST' -Headers $headers -Body " { `"jsonrpc`": `"2.0`", `"method`": `"user.login`", `"params`": { `"user`": `"$user`", `"password`": `"$password`" }, `"id`": 1 } " | foreach { $_.result } echo $key # filter out unreachable Agent, SNMP, JMX, IPMI hosts Invoke-RestMethod $url -Method 'POST' -Headers $headers -Body " { `"jsonrpc`": `"2.0`", `"method`": `"host.get`", `"params`": { `"output`": [`"interfaces`",`"host`",`"proxy_hostid`",`"disable_until`",`"lastaccess`",`"errors_from`",`"error`"], `"selectInterfaces`": `"extend`", `"filter`": {`"available`": `"2`",`"status`":`"0`"} }, `"auth`": `"$key`", `"id`": 1 } " | foreach { $_.result } | foreach { $_.interfaces } | Out-GridView # log out Invoke-RestMethod $url -Method 'POST' -Headers $headers -Body " { `"jsonrpc`": `"2.0`", `"method`": `"user.logout`", `"params`": [], `"id`": 1, `"auth`": `"$key`" } "
Set a valid credential (URL, username, password) on the top of the code before executing it.
The benefit of PowerShell here is that we can use some on-the-fly filtering:
What is the exact meaning of the field ‘type’ we can understand by looking on the previous database query:
CASE interface.type WHEN 1 THEN 'ZBX' WHEN 2 THEN 'SNMP' WHEN 3 THEN 'IPMI' WHEN 4 THEN 'JMX' END AS "type",
On Windows PowerShell, it is possible to download the unreachable hosts directly to CSV file. To do that, in the code above, we need to change:
Out-GridView
to
Export-Csv c:\temp\unavailable.hosts.csv
Alright, this was the knowledge bit today. Let’s keep Zabbixing!