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!

Subscribe
Notify of
3 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
Gavin Hill
Gavin Hill
3 years ago

Great post. But is this only relevant for SNMP/Agent checks, what about basic ICMP checks?

Trashcan4UD
Trashcan4U
5 months ago

Zabbix 6 with PostgreSQL says:

ERROR:  column hosts.available does not exist
sTicKs23
sTicKs23
1 month ago

For Zabbix 6.4 the following should work. I’m using phpmyadmin for a prettier table.
SELECT 
   h.hostid, 
   h.name AS host_name, 
   i.ip, 
   i.dns, 
   CASE i.available 
       WHEN 1 THEN ‘available’
       WHEN 2 THEN ‘unavailable’
       ELSE ‘unknown’ 
   END AS interface_status,
   i.type AS interface_type,
   i.main AS is_main_interface
FROM 
   hosts h
JOIN interface i ON h.hostid = i.hostid
WHERE 
   h.status = 0 AND i.main = 1
   AND i.available != 1
ORDER BY 
   i.available, h.hostid;

If you’re using FQDNs in your DNS Name box of interface you can filter them by adding the below line in the WHERE section:

   AND i.dns LIKE ‘%example.com’

3
0
Would love your thoughts, please comment.x
()
x