Summarize devices that are not reachable

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 proxy.host AS "proxy",
       hosts.host,
       interface.ip,
       interface.dns,
       interface.useip,
       CASE hosts.available
           WHEN 0 THEN 'unknown'
           WHEN 1 THEN 'available'
           WHEN 2 THEN 'down'
       END AS "status",
       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
LEFT JOIN hosts proxy ON hosts.proxy_hostid=proxy.hostid
WHERE hosts.status=0
  AND interface.main=1;

A very similar outcome can be obtained via Windows PowerShell by contacting Zabbix API. Try this snippet:

Add-Type @"
using System.Net;
using System.Security.Cryptography.X509Certificates;
public class TrustAllCertsPolicy : ICertificatePolicy {
    public bool CheckValidationResult(
    ServicePoint srvPoint, X509Certificate certificate,
    WebRequest request, int certificateProblem) {
        return true;
    }
}
"@

[System.Net.ServicePointManager]::CertificatePolicy = New-Object TrustAllCertsPolicy

# Set Tls versions
$allProtocols = [System.Net.SecurityProtocolType]'Ssl3,Tls,Tls11,Tls12'
[System.Net.ServicePointManager]::SecurityProtocol = $allProtocols

$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/json")
$url = 'http://demo.zabbix.demo/api_jsonrpc.php'
$user = 'Admin'
$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

# download all proxy IDs and names
$allProxies=Invoke-RestMethod $url -Method 'POST' -Headers $headers -Body "
{
    `"jsonrpc`": `"2.0`",
    `"method`": `"proxy.get`",
    `"params`": {
        `"output`": [`"proxyid`",`"host`"]
        
    },
    `"auth`": `"$key`",
    `"id`": 1
}
" | foreach { $_.result }  

# download availability of devices which are enabled and the monitoring has been scheduled
$allHosts=Invoke-RestMethod $url -Method 'POST' -Headers $headers -Body "
{
    `"jsonrpc`": `"2.0`",
    `"method`": `"host.get`",
    `"params`": {
        `"output`": [`"interfaces`",`"error`",`"name`",`"available`",`"proxy_hostid`"],
        `"selectInterfaces`": `"extend`",
        `"filter`": {`"status`":`"0`"}
    },
    `"auth`": `"$key`",
    `"id`": 1
}
" | foreach { $_.result }  

# this will be an object which stores a data. it's not a text string!
$totalResult = @()

foreach($res in $allHosts) {

# go through each host
$totalResult += [PSCustomObject]@{

# create a loop to find what is name of Zabbix proxy
Proxy = $allProxies | where {($_.proxyid -like $res.proxy_hostid )} | foreach { $_.host }
# there is a possibility that no name has found. in this case the field will remain empty

# put a host visible name, if visible name is not configured then grab host name
Host = $res.name

# while working with interface block it's required to locate the main interface with ($_.main -like 1)
Type = $res.interfaces | where {($_.main -like 1 )} | foreach { $_.type -replace "1", "ZBX" -replace "2", "SNMP" -replace "3", "IPMI" -replace "4", "JMX" }
Port = $res.interfaces | where {($_.main -like 1 )} | foreach { $_.port }

Status = $res | foreach { $_.available -replace "0", "unknown" -replace "1", "monitored" -replace "2", "not reachable" }

# some environments prefer an IP address for passive checks, some environments prefer DNS entry
# the following command will determine the connection (IP or DNS) which is configured and print IP or DNS
"Connection" = switch ($res.interfaces | where {($_.main -like 1 )} | foreach { $_.useip }){0 {$res.interfaces | where {($_.main -like 1 )}|foreach { $_.dns }} 1 {$res.interfaces | where {($_.main -like 1 )}|foreach { $_.ip }}}

# if host is not available then we need to know why
"Last error" = $res.error

}
}

# log out
Invoke-RestMethod $url -Method 'POST' -Headers $headers -Body "
{
    `"jsonrpc`": `"2.0`",
    `"method`": `"user.logout`",
    `"params`": [],
    `"id`": 1,
    `"auth`": `"$key`"
}
" 

$totalResult | Out-GridView

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:

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!

About Aigars Kadiķis

Technical Support Engineer at Zabbix

View all posts by Aigars Kadiķis