How to find out reachability for a device from vertica database?

Document ID : KB000056680
Last Modified Date : 14/02/2018
Show Technical Document Details

How to find out reachability for a device from vertica database?

IM 2.3.4, IM 2.4.1 and IM 2.5.0
Linux 6 64bits


The CAPC will show as "NOT REACHABLE" if the value for reachability from the supplied query is less than 100.

That is, as designed, we ping the device every minute. If the value is less than 100, it means that there are ICMP pings not reaching the device in the specified time range.

1.
If you know the ip address, you can run this command to find out the item_id of that device:

(Please note that to login to the vertica database, need to know the <dr admin_user> name and <dr admin_user> password.  In example below <dr admin_user> = druser, password = dapss)

/opt/vertica/bin/vsql -U dauser -w dapass -c "SELECT item_id, v6_ntoa(primary_ip_address)FROM device where v6_ntoa(primary_ip_address)='192.168.XXX.22';"

 item_id |   v6_ntoa
---------+--------------
     755 | 192.XXX.XXX.22
(1 row)

2.
If you would like to find out a few devices's item_id, run this command to find them out:

/opt/vertica/bin/vsql -U dauser -w dapass -c "SELECT i.item_id, i.item_name FROM item i, device d WHERE i.item_id = d.item_id AND i.item_name LIKE '%CAIM%';"

 item_id |         item_name
---------+---------------------------
     740 | CAIM2811-96.XXX.XXX
     741 | CAIM3640-96.XXX.XXX
     744 | CAIM7505-96.XXX.XXX.com
     745 | CAIM720496-6.XXX.com
     746 | CAIM720496-5.XXX.com
     750 | CAIM2600-96.XXX.XXX.com
     752 | CAIMco2621-96.XXX.XXX.com
     753 | CAIM3810-96.XXX.XXX.com
     755 | CAIM3640-96.XXX.XXX.com
     756 | CAIM3810-96.XXX.XXX.com
     757 | CAIM2514-96.XXX.XXX.com
     758 | CAIM2524-blue-96.XXX.XXX.com
     759 | CAIM2524-blue-96.XXX.XXX.com
     760 | CAIM2524-cyan-96.XXX.XXX.com
     761 | CAIM7505-96.10.XXX.XXX.com
     762 | CAIM2524-blue-96.XXX.XXX.com
    6565 | CAIM7606-96.36.XXX.XXX.com


3. Take a note of the device item_id.

4. Get the reachability of the device for last hour using this device's item_id by running this command:

/opt/vertica/bin/vsql -U dauser -w dapass -c "SELECT avg(rr.im_reachability) FROM reach_rate rr join poll_item pi on pi.item_id=rr.item_id and pi.is_filtered=0 join item i on i.item_id=pi.device_item_id where pi.device_item_id=755 and rr.tstamp <extract(epoch from now()) and rr.tstamp > extract(epoch from now()) - 3600;"

 avg
-----
   0
(1 row)


If we can change the item_id to another one, for example 6565, then we will get different result:
 avg
-----
 100
(1 row)

If the query results in a number less than 100, then the status in device details view will be shown as "NOT REACHABLE".


5.
We also can check the last few polling cycle's reachability of the device:

A.
/opt/vertica/bin/vsql -U dauser -w dapass -c "select item_id, to_timestamp(tstamp),im_reachability,im_avgresponse from reach_rate where item_id=755 ORDER BY tstamp desc limit 10;"

 item_id |    to_timestamp     | im_reachability | im_avgresponse
---------+---------------------+-----------------+----------------
     755 | 2015-06-16 16:11:00 |               0 |
     755 | 2015-06-16 16:10:00 |               0 |
     755 | 2015-06-16 16:09:00 |               0 |
     755 | 2015-06-16 16:08:00 |               0 |
     755 | 2015-06-16 16:07:00 |               0 |
     755 | 2015-06-16 16:06:00 |               0 |
     755 | 2015-06-16 16:05:00 |               0 |
     755 | 2015-06-16 16:04:00 |               0 |
     755 | 2015-06-16 16:03:00 |               0 |
     755 | 2015-06-16 16:02:00 |               0 |
(10 rows)

Which it shows this device that we can not ping for it.

B.
/opt/vertica/bin/vsql -U dauser -w dapass -c "select item_id, to_timestamp(tstamp),im_reachability,im_avgresponse from reach_rate where item_id=6565 ORDER BY tstamp desc limit 10;"

 item_id |    to_timestamp     | im_reachability | im_avgresponse
---------+---------------------+-----------------+----------------
    6565 | 2015-06-16 16:14:00 |             100 |             37
    6565 | 2015-06-16 16:13:00 |             100 |             16
    6565 | 2015-06-16 16:12:00 |             100 |             18
    6565 | 2015-06-16 16:11:00 |             100 |             16
    6565 | 2015-06-16 16:10:00 |             100 |             17
    6565 | 2015-06-16 16:09:00 |             100 |             16
    6565 | 2015-06-16 16:08:00 |             100 |            107
    6565 | 2015-06-16 16:07:00 |             100 |             17
    6565 | 2015-06-16 16:06:00 |             100 |             16
    6565 | 2015-06-16 16:05:00 |             100 |             24
(10 rows)

Above result shows that IM2.0 can ping the device and get the reachability back as 100%.