What are the requirements for a custom SQL alert to show on dashboard?

Help
1

I have created a warning alert for possible layer 2 loops.
I have a working query and when I run a capture for alerts it also shows as “matches” but doesn’t show up anywhere. I was wondering if there are required fields I need to include in custom SQL alerts?

Here is my SQL:

SELECT 
       devices.device_id,
       ports_fdb.mac_address,
       vlans.vlan_vlan AS vlan,
       GROUP_CONCAT(DISTINCT ports.ifDescr SEPARATOR ', ') AS ports
FROM ports_fdb
JOIN vlans ON ports_fdb.vlan_id = vlans.vlan_id
JOIN ports ON ports_fdb.port_id = ports.port_id
JOIN devices ON ports_fdb.device_id = devices.device_id
WHERE ports_fdb.device_id = ?
GROUP BY ports_fdb.mac_address, ports_fdb.vlan_id
HAVING COUNT(DISTINCT ports_fdb.port_id) > 1

And here is my capture when I run the alert on a device that should alert:

Rule name: Possible Layer 2 Loop
Alert rule: Custom SQL Query
Alert query: SELECT 
       devices.device_id,
       ports_fdb.mac_address,
       vlans.vlan_vlan AS vlan,
       GROUP_CONCAT(DISTINCT ports.ifDescr SEPARATOR ', ') AS ports
FROM ports_fdb
JOIN vlans ON ports_fdb.vlan_id = vlans.vlan_id
JOIN ports ON ports_fdb.port_id = ports.port_id
JOIN devices ON ports_fdb.device_id = devices.device_id
WHERE ports_fdb.device_id = ?
GROUP BY ports_fdb.mac_address, ports_fdb.vlan_id
HAVING COUNT(DISTINCT ports_fdb.port_id) > 1
Rule match: matches

It never shows in alert logs or dashboard for this device even though it confirms a match.

Edit: Also tried adding *, to the select query but it still isn’t showing.

2

Run lnms device:poll <device_id> -vvv and see if that tells you anything different.

Have you run the query manually just to see what’s returned?

3

I’m getting shown an error from that output. It seems to want ports_fdb.ports_fdb_id in my GROUP BY?

Error: SQLSTATE[42000]: Syntax error or access violation: 1055 'librenms.ports_fdb.ports_fdb_id' isn't in GROUP BY (Connection: mysql, SQL: SELECT
       *,
       ports_fdb.mac_address,
       vlans.vlan_vlan AS vlan,
       GROUP_CONCAT(DISTINCT ports.ifDescr SEPARATOR ', ') AS ports
FROM ports_fdb
JOIN vlans ON ports_fdb.vlan_id = vlans.vlan_id
JOIN ports ON ports_fdb.port_id = ports.port_id
JOIN devices ON ports_fdb.device_id = devices.device_id
WHERE ports_fdb.device_id = 1
GROUP BY ports_fdb.mac_address, ports_fdb.vlan_id
HAVING COUNT(DISTINCT ports_fdb.port_id) > 1)
SQL[insert into `eventlog` (`reference`, `type`, `datetime`, `severity`, `message`, `username`, `device_id`) values (?, ?, ?, ?, ?, ?, ?) [null,"alert","2025-04-25 13:55:00",5,"Error in alert rule Possible Layer 2 Loop (36): SQLSTATE[42000]: Syntax error or access violation: 1055 'librenms.ports_fdb.ports_fdb_id' isn't in GROUP BY (Connection: mysql, SQL: SELECT \r\n       *,\r\n       ports_fdb.mac_address,\r\n       vlans.vlan_vlan AS vlan,\r\n       GROUP_CONCAT(DISTINCT ports.ifDescr SEPARATOR ', ') AS ports\r\nFROM ports_fdb\r\nJOIN vlans ON ports_fdb.vlan_id = vlans.vlan_id\r\nJOIN ports ON ports_fdb.port_id = ports.port_id\r\nJOIN devices ON ports_fdb.device_id = devices.device_id\r\nWHERE ports_fdb.device_id = 1\r\nGROUP BY ports_fdb.mac_address, ports_fdb.vlan_id\r\nHAVING COUNT(DISTINCT ports_fdb.port_id) > 1)","",1] 3.29ms]
4

I was able to resolve it by removing the wildcard in the SELECT and adding vlans.vlan_id to the GROUP BY

However, this leads to a new problem. The alert when expanded only shows “#1:”

The result of the SQL query above looks like

mac_address   vlan  ports
dc21480a6095  211   Port-channel3, Port-channel9
5

Not enough data is being returned to be useful to LibreNMS. You need to add more fields to the select and make sure the query is valid.

6

@laf your help is appreciated but I can’t find any documentation on what fields are required. As I mentioned earlier I had tried a wildcard in the SELECT statement but librenms gets upset when you don’t include a column in the GROUP BY.
As asked in the title of the thread, what fields are required to show up on the alerts?
There is no documentation about this in docs.librenms.org

7

Whatever you return from your select will be used in the alert output afaik

8

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.