Create a device group / alert for sensor NOT existing

[ekki]

Hey, I am trying to create an alert to notify me about Juniper switches that have only 1 power supply.
So for example a device with two power supplies will look like this:

But a device with only one will look like this:

Power Supply 0 @ 0/0/* sensor is not even created.

I did find a bunch of info about Power Supply 0 @ 0/0/* anf Power Supply 1 @ 0/1/* sensors using API but I don’t know how I can use the “sensor_index” or “sensor_oid” to create an alert that would work for devices which have one of the two sensors, but not both.

[laf]

If the sensor doesn’t exist in the database then you can’t alert on it by default. You’d need to write a custom SQL query that does a count on rows matching the description of the power supplies and alert if they are < 2.

[ekki]

OK, with the help of ChatGPT and MySQLWorkbench I ended up with:

SELECT *
FROM devices
JOIN (
    SELECT devices.device_id, COUNT(DISTINCT sensors.sensor_descr) AS unique_power_supply_sensor_count
    FROM devices
    JOIN sensors ON devices.device_id = sensors.device_id
    WHERE sensors.sensor_descr LIKE '%Power Supply%' AND devices.device_id = ? 
    GROUP BY devices.device_id
    HAVING unique_power_supply_sensor_count < 3
) AS subquery ON devices.device_id = subquery.device_id;

I don’t really have any SQL experience, it seems a bit long, but also seems to work fine. If anyone has any ideas how to simplify the query please let me know.