This is really cool! Thanks for writing & sharing :)
I’m curious about a couple of things:
How do you recommend actually issuing alerts based on the query? I could imagine having a cron job execute the query and text/email/PagerDuty if there’s an anomaly returned for given period, but I’m wondering if there’s a better existing solution here.
You mentioned at the end there are some tools that provide similar functionality, I’m wondering if you could give a few examples? I know Datadog has great alarm tooling but I would imagine those are less general-purpose than a technique like this.
How do you recommend actually issuing alerts based on the query?
Just like you said. A cron job executing the query at regular intervals and sending an email/text/whatever if it detects an anomaly. I know that there are some reporting tools (I use Redash for example) that have this ability as well.
I’m wondering if you could give a few examples?
I imagine any monitoring tool should have this functionality. Datadog and Scout are two that come to mind.
The main point I wanted to convey in this article is that you set up a pretty descent monitoring system with plain SQL, zero dependencies and no $$$. From my experience, this simple method can go a long way.
We use https://www.anodot.com/ (somewhere in the org). It’s downstream from our metrics gathering that I maintain (Graphite, Prometheus) but supports both AFAIK. I also don’t know if there’s a free/open source version or it’s a paid tool.
This is really cool! Thanks for writing & sharing :)
I’m curious about a couple of things:
How do you recommend actually issuing alerts based on the query? I could imagine having a cron job execute the query and text/email/PagerDuty if there’s an anomaly returned for given period, but I’m wondering if there’s a better existing solution here.
You mentioned at the end there are some tools that provide similar functionality, I’m wondering if you could give a few examples? I know Datadog has great alarm tooling but I would imagine those are less general-purpose than a technique like this.
Hey Jeff, glad you liked it.
Just like you said. A cron job executing the query at regular intervals and sending an email/text/whatever if it detects an anomaly. I know that there are some reporting tools (I use Redash for example) that have this ability as well.
I imagine any monitoring tool should have this functionality. Datadog and Scout are two that come to mind.
The main point I wanted to convey in this article is that you set up a pretty descent monitoring system with plain SQL, zero dependencies and no $$$. From my experience, this simple method can go a long way.
We use https://www.anodot.com/ (somewhere in the org). It’s downstream from our metrics gathering that I maintain (Graphite, Prometheus) but supports both AFAIK. I also don’t know if there’s a free/open source version or it’s a paid tool.
Nice concept and write-up!
Tangent: what did you use to make the plots/charts?
Thanks man. I used PopSQL. There is also a link and a mention at the top of the article.
https://popsql.com/queries/-MECQV6GiKr04WdCWM0K/simple-anomaly-detection-with-sql?access_token=2d2c0729f9a1cfa7b6a2dbb5b0adb45c
Great article. Feels like a DBA working in a monitoring team!
I really like the idea where you use something already present in the stack and makes it do more things to gain in incidence response.
Thanks ;) Glad you liked it.
I am a DBA, but just one working with some flaky third party services…