Alert rule based on new or unacknowledged alerts
We have our normal alerting set up and working fine, but we want a custom one that will text us if there are 10+ new alerts in the last 15 minutes or something like that.
I am not familiar enough to figure this out, has anyone else done this or know how? I have the texting part figured out it is just the alert matching I don't know how to do. TIA!
Edit: In case anyone needs this in the future the solution in the comments works. Create an alert with an Override SQL query of
SELECT * FROM (SELECT COUNT(id) AS num_open_alerts FROM alerts WHERE alerts.state = 1 AND alerts.timestamp > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 MINUTE)) AS _ WHERE num_open_alerts >= 10;
Posted Feb 24, 2023, 2:14 PM UTC · edited
14
Support for this isn't great, the alert builder will not work as it doesn't show you all tables of the database. But if you go to the advanced pane of the alert rule editor and enable Override SQL you can enter a custom query.
Not a big SQL person, but the best I could come up with off the top of my head was:
SELECT * FROM (SELECT COUNT(id) AS num_open_alerts FROM alerts WHERE alerts.state = 1 AND alerts.timestamp > DATE_SUB(CURRENT_TIMESTAMP(), INTERVAL 15 MINUTE)) AS _ WHERE num_open_alerts >= 10;That will (if I didn't make a typo) alert when there are 10 alerts opened within 15 minutes. Downside is it doesn't tell you which alerts they are, the only information it provides is the number of alerts, so the message you send out won't have that information. It also doesn't filter on alert severity, as that would require a join that I didn't feel like figuring out at the moment :)
I am not the best at SQL either, but this gives me a great place to start so thank you! The type/number/contents of the alerts isn't a big deal at all. Kind of just an extra "oh shit" alert to go along with our other stuff.
One other question though, I can get the SQL override and command pasted in the advanced window, but it won't actually let me save the rule unless something is in the Main window rule area. What should I put there? I tried changing it to SQL and pasting the same command, but it says it could not be parsed.
You can put anything in there, it just gets ignored once you enable Override SQL. I think that's a bug.
Ohh, ok. Have the rule all set up and will start to test it. Thank you again!