Syslog Alert rule with Count

Help
1

I am having an issue with my syslog alert rule that contains a count. It never seems to trigger. Here is my alert’s override SQL statement:

SELECT * FROM devices,syslog WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND syslog.program = “SURICATA” HAVING COUNT(*) > 2

When I run a debug on “alerts” it matches, however, when I run a debug on “poller” I see the following message:

Rule #32 (Syslog - Resistance - Suricata - Over 5 Alerts in the last 5 minutes):

Error: SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause (Connection: mysql, SQL: SELECT * FROM devices,syslog WHERE (devices.device_id = 1 AND devices.device_id = syslog.device_id) AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND syslog.program = “SURICATA” HAVING COUNT(*) > 2
)

SQL[insert into eventlog (reference, type, datetime, severity, message, username, device_id) values (?, ?, ?, ?, ?, ?, ?) [null,“alert”,“2024-11-05 08:03:29”,5,“Error in alert rule Syslog - Resistance - Suricata - Over 5 Alerts in the last 5 minutes (32): SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),…) with no GROUP columns is illegal if there is no GROUP BY clause (Connection: mysql, SQL: SELECT * FROM devices,syslog WHERE (devices.device_id = 1 AND devices.device_id = syslog.device_id) AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND syslog.program = "SURICATA" HAVING COUNT(*) > 2\r\n)”,“”,1] 6ms]

Any help would be appreciated.

2

The reason is in the output you provided, your SQL query isn’t compliant so is causing issues. https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

3

Im in over my head, Is there any bounty program for LibreNMS that I can pay someone to help me with this?

4

Here’s a freebie - be a good human and you’ve paid me back. Inspired by something I did once long ago but no longer use - no warranty :smiley:

It uses the same alert query in a subselect as a final clause to check the count. The subselect group by needs the column named, so I’ve just chosen syslog.device_id in this case.

So why not just add group by to the end of the normal alert SQL? Most SQL servers need the columns explicitly named in the select for group operations - then it gets messy keeping up with schema changes and replicating what select(*) outputs so everything works as normal. You technically can get around that, but you need another join and it gets really messy again - move on …

SELECT * FROM devices,syslog WHERE ( devices.device_id = ? AND devices.device_id = syslog.device_id ) AND syslog.program = "SURICATA" AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) AND ( SELECT syslog.device_id FROM devices,syslog WHERE (devices.device_id = ? AND devices.device_id = syslog.device_id) AND syslog.program = "SURICATA" AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) GROUP BY syslog.device_id HAVING count(*) >= 2 )

Pretty explanation version:

SELECT * FROM devices,syslog 
  WHERE 
   ( devices.device_id = ? AND devices.device_id = syslog.device_id ) 
  AND 
   syslog.program = "SURICATA"
  AND 
   syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) 
  AND
   (
    SELECT syslog.device_id FROM devices,syslog
      WHERE 
        (devices.device_id = ? AND devices.device_id = syslog.device_id) 
       AND 
         syslog.program = "SURICATA" 
       AND 
         syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE)) 
       GROUP BY syslog.device_id 
       HAVING count(*) >= 2
    )

You can test this by running just the subselect on your DB with a host ID entered instead of ? (1 below) and leaving off the group by - increase the interval if it’s too hard getting a confirmation:

SELECT syslog.device_id FROM devices,syslog WHERE (devices.device_id = 1 AND devices.device_id = syslog.device_id) AND syslog.program = "SURICATA" AND syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 5 MINUTE));

You’ll get results like this - 3 syslog entries for example:

-------------
| device_id |
-------------
|         1 |
|         1 |
|         1 |
-------------

Then add on the group by and play around with >=1 >=2 etc. - it will give you a single line from the above output if it’s matching.

BTW this is something graylog does particularly well using grace conditions - if your syslog needs start getting more complex, I’d suggest pumping data out to another system to alert from.

5

Thank you so much! This worked with one minor tweak, the second substation for devices.device_id = ? did not seem to work. I hard set it to “1”, which is fine. The working code looks like this:

SELECT * FROM devices,syslog 
  WHERE 
   ( devices.device_id = ? AND devices.device_id = syslog.device_id ) 
  AND 
   syslog.program = "SURICATA"
  AND
   syslog.msg REGEXP ".*Priority:.*"
  AND
   syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 10 MINUTE)) 
  AND
   (
    SELECT syslog.device_id FROM devices,syslog
      WHERE 
        (devices.device_id = 1 AND devices.device_id = syslog.device_id) 
       AND 
         syslog.program = "SURICATA" 
       AND 
	     syslog.msg REGEXP ".*Priority:.*"
       AND
         syslog.timestamp >= (DATE_SUB(NOW(),INTERVAL 10 MINUTE)) 
       GROUP BY syslog.device_id 
       HAVING count(*) >= 10
    )

I really appreciate your help!