I have about 50 Checkpoint firewall clusters being monitored with my LibreNMS instance. I’d like to create some alerting around the percentage of concurrent connections allowed on each gateway. I found that the current connections are stored in the sensors table with a sensor_index of “fwNumConn.0” and the connection limit is stored in another row with a sensor_index of “fwConnTableLimit.0”.
My question is, How to I do math against two different rows/OID’s in a macro or alert rule? I’d like LibreNMS to mark firewalls with over 80% of the limit used as “Warning” and 90% as “Critical”. I’m really rusty with TSQL so I’m struggling a bit to get this done in a single statement/macro.
If no sensor_limit and it MUST be obtained from another row being “fwConnTableLimit.0” then it’s a far harder problem to solve with SQL and is well beyond my knowledge.
It probably points to the device discovery/parsing of these devices needing to put those limit values in the right spots in the sensors module so the native alerting works.
No, unfortunately I must get the limit from another sensor OID. In Nagios I wrote a custom script that would pull both OID’s and do the math in a bash script but I’m unable to figure out how to do it properly in LibreNMS.
MariaDB [librenms]> select device_id, sensor_index, sensor_descr, sensor_current, sensor_limit from sensors where (sensor_index = 'fwNumConn.0' or sensor_index = 'fwConnTableLimit.0') and device_id = 351;
+-----------+--------------------+----------------------------------+----------------+--------------+
| device_id | sensor_index | sensor_descr | sensor_current | sensor_limit |
+-----------+--------------------+----------------------------------+----------------+--------------+
| 351 | fwNumConn.0 | Number of concurrent connections | 3509 | NULL |
| 351 | fwConnTableLimit.0 | Limit of Connections table | 0 | NULL |
+-----------+--------------------+----------------------------------+----------------+--------------+
2 rows in set (0.009 sec)
Got me thinking … what if we join to the same table and get specific about the terms so we can match across rows for a device.
In the below I’ve just had to use some index I could find to simulate your issue, as I have no similar examples, however assume:
‘panSessionActiveUdp.0’ is your ‘fwNumConn.0’ (VALUE)
‘panSessionActive.0’ is your ‘fwConnTableLimit.0’ (LIMIT)
select
curr.device_id,
curr.sensor_index,
curr.sensor_current,
max.sensor_index,
max.sensor_current,
round(curr.sensor_current/max.sensor_current*100,2) as perc
from
sensors curr, sensors max
where
curr.device_id = max.device_id
and
curr.sensor_index = 'panSessionActiveUdp.0' <-- VALUE
and
max.sensor_index = 'panSessionActive.0' <-- LIMIT
If you get that working, then you can strip it down to select *, filter for 80% in the where clause, and drag in the device table to do the normal disabled/ignored checking - in my example case, something like:
select * from sensors curr, sensors max, devices where devices.device_id = curr.device_id and curr.device_id = max.device_id and curr.device_id = ? and max.sensor_index = 'panSessionActive.0' and curr.sensor_index = 'panSessionActiveUdp.0' and curr.sensor_current/max.sensor_current >= 0.8 and (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1;
Thank you! Your initial query worked great. I’m trying it out in an alert rule now. I’ll update with the results if/when it fires. I really need to dust off my SQL hat!!!
Taking the statement you gave me I applied this query:
select * from sensors curr, sensors max, devices where devices.device_id = curr.device_id and curr.device_id = max.device_id and curr.device_id = ? curr.sensor_index = 'fwNumConn.0' and max.sensor_index = 'fwConnTableLimit.0' and max.sensor_current > 0 and curr.sensor_current/max.sensor_current >= 0.80 and (devices.status = 1 && (devices.disabled = 0 && devices.ignore = 0)) = 1;
Added max.sensor_current > 0 because we have some firewalls set to automatically manage the connection limit and SNMP returns 0 for the limit value on those.