Hi community,
I am looking to create a rule to detect port flapping. After digging I found that this can be done using custom sql query alert.
So the SQL query I am trying to get working in alert is the following.
SELECT
e.device_id,
p.ifName,
COUNT(e.message) AS message_count
FROM
eventlog e
JOIN
ports p ON e.reference = p.port_id
WHERE
e.device_id = ?
AND e.type = ‘interface’
AND e.message LIKE ‘ifOperStatus: up%’
AND UNIX_TIMESTAMP(e.datetime) >= UNIX_TIMESTAMP(NOW() - INTERVAL 30 MINUTE)
GROUP BY
e.device_id, p.ifName
HAVING
COUNT(e.device_id) >= 5;
If I execute this database it work fine, but when I place it in the alert rule I am getting an alert in the eventlog
Error in alert rule Port is Flapping (33): SQLSTATE[HY093]: Invalid parameter number (Connection: mysql, SQL: SELECT e.device_id, p.ifName, COUNT(e.message) AS message_count FROM eventlog e JOIN ports p ON e.reference = p.port_id WHERE e.device_id = 17 AND e.type = ‘interface’ AND e.message LIKE ‘ifOperStatus: up%’ AND UNIX_TIMESTAMP(e.datetime) >= UNIX_TIMESTAMP(NOW() - INTERVAL 120 MINUTE) GROUP BY e.device_id, p.ifName HAVING COUNT(e.device_id) >= 2)
Alert rule configuration…
Thanx for your help! 
