Simple Anomaly Detection Using Plain SQL
hakibenita.com
hakibenita.com
[0]: https://en.m.wikipedia.org/wiki/Algorithms_for_calculating_v...
I also separately used a package in R called bsts (Bayesian Structural Time Series) for a different way of projecting seasonality on a trend to find an acceptable normal range. If the actual fell out of range then it was an anomaly. Great write up on the technique by Kim Larsen here: https://multithreaded.stitchfix.com/blog/2016/04/21/forget-a...
A z-score is just the number of standard deviations away from the mean a data point lies. It doesn't require any assumption about the distribution of the data.
If you wanted to make some inference, like maybe about the likelihood of observing some z-score given a hypothesis, then you would need to assume a distribution.
But, nothing the author does assumes a distribution, normal or otherwise. It doesn't mention probability at all as far as I can see.
Non-parametric means more than just avoiding talk of distributions.
In my previous post I said "doesn't assume any distribution" I meant it doesn't depend on any particular distribution function.
But in the post above that I was sloppy and said "no assumptions about the distribution at all". Good catch.
Instead, they determined N by looking at the past data.
(Contrast with the parametric approach of assuming normality and concluding that any point lying >3 sd from the mean is an anamoly due to properties of the gaussian distribution.)
Any tool can be used without regard for the consequences, but knowing a tool’s statistical properties yields know-how about the consequences of its application. It’s often a matter of the costs of error / stakes of the most problem at hand. Cheap solutions are often the best for cheap problems.
You then are not assuming anything about the distribution.
I'm curious if others think this is a bad/good approach.
There are at least two tools that support directly querying log files with SQL:
* logparser -- https://en.wikipedia.org/wiki/Logparser
* lnav -- https://lnav.org
lnav uses SQLite and provides quite a few extensions, so quite a lot is possible. The logs are exposed via virtual tables, so there is no separate import step.
In any case, do you have a write-up somewhere of how you use a columnar DBMS to slurp your server logs?
I like to store Apache & nginx logs as JSON so they can be parsed with tools like jq, and adding/removing fields doesn't break parsing.
From JSON it is also easy to pull them into PostgreSQL (at least) in jsonb format, or parse out key elements as their own regular table fields.
The strategies within could be applied to virtually any time-series data.
For larger data-sets you can group them, such as finding the min, max, and average for each given month, and then graph them using different bar symbols: M=min, X=max, A=average. Once you spot an odd month, you use the original detail version to study that month further. (Make sure you display the bars using a fixed-width font, such as Courier.)
2020-01 MMMMMMMM
2020-01 AAAAAAAAAAAAAAA
2020-01 XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
2020-02 MM
2020-02 AAAAAAAAAAA
2020-02 XXXXXXXXXXXXXXXXXXXXXXXXXXX
2020-03 MMMMM
2020-03 AAAAAAAAAAAAAAAAAAA
2020-03 XXXXXXXXXXXXXXXXXXXXXXXXXXX
Etc... SELECT * FROM series WHERE is_anomaly = f;
The subarray that compresses the most has the anomaly removed.
It's not very cheap, but it's much more general than median or average or string comparisons. It relies on the fact that compressed size is estimate of Kolmogorov complexity.
Nice article. My only comment would be that performing anomaly detection on the same data that you use to "train" your detector in the first place, is a classic example of a "data-leak" in data science, and very likely to lead to errors in itself.
I agree with your comment in general that using test data in your training set is a clear example of an error that will lead to overfitting and a model that generalizes poorly, but am having a hard time seeing where the author commits this mistake.
Then he proceeds to include 12 in the calculation deriving these bounds. This is not really the way to do it. In fact, if he had excluded the anomalous measurement from the training data, the '5' values would have been excluded as outliers as well, given his criteria for defining the bounds.
I agree that this is a trivial point made on a trivial example though, and that it is more a matter of 'sensible definitions' of what counts as anomalous or as training set in the first place. But it's still worth thinking about explicitly though, so I thought I'd mention it.
But thats the whole point of the very well established concept of standard deviation - to look at a dataset in isolation and analyse it.
This may leave you with some false positives (like any system of this nature would). Of course, you could go the route of actually defining an anomaly yourself and building a more sophisticated model (i.e. one not using z-scores as the measure of anomaly), but that's obviously a different scope.