Tools for automatic anomaly detection on a SQL table?

Question

I have a large SQL table that is essentially a log. The data is pretty complex and I'm trying to find some way to identify anomalies without me understanding all the data. I've found lots of tools for Anomaly Detection but most of them require a "middle-man" of sorts, ie Elastic Search, Splunk, etc.

Does anyone know of a tool that can run against a SQL table which builds a baseline and alerts of anomalies automagically?

This may sound lazy but I've spent dozens of hours writing individual reporting scripts as I learn what each event type means and which other fields go with each event and I don't feel any closer to being able to alert on real problems in a meaningful way. The table has 41 columns and just hit 500 million rows (3 years of data).

Answer 1

If you need SQL code that runs various outlier detection methods against any arbitrary table, check out my series of articles and code samples geared towards SQL Server. I provide some preliminary code for Grubb's Tests, Z-Scores and Modified Z-Scores, Interquartile Range, Dixon's Q-Test, GESD, the Tietjen-Moore Test, Pierce's Criterion, Chauvenet's Criterion, the Modified Thompson Tau Test, Cook's Distance, Mahalanobis Distance, Benford's Law and various visual plots. Please note that I'm an amateur at this and that this is some of my older code, so I'm sure corrections will be needed; this at least provides a starting point so that you can run these tests against any database table you have permissions for. It's also in T-SQL, so you'll need to adjust the code for PL/SQL or whatever other brand of SQL you might be using, if you happen to be on some other platform like Oracle or MySql. This should get you started though. Just works out the kinks and set the stored procedures to run periodically on a schedule and you're good to go. Let me know if you have any feedback on the routines, I'm always looking to improve my SQL writing.

Answer 2

If you want to approach this from a SQL perspective, then broadly I would identify any classification variables that cause different behaviour. Then perform something like the following on a number of analysis variables.

SELECT ClassificationVar1, 
    ClassificationVar2, 
    MIN(AnalysisVar1) as Min_AnalysisVar1, 
    MAX(AnalysisVar1) as Max_AnalysisVar1, 
    MEAN(AnalysisVar1) as Mean_AnalysiVar1, 
    STDEV(AnalysisVar1) as Std_AnalysisVar1,
    MIN(AnalysisVar2) as Min_AnalysisVar2, 
    MAX(AnalysisVar2) as Max_AnalysisVar2, 
    MEAN(AnalysisVar2) as Mean_AnalysiVar2, 
    STDEV(AnalysisVar2) as Std_AnalysisVar2,
    etc.
    FROM YourDataFile
GROUP BY ClassificationVar1, ClassificationVar2
ORDER BY ClassificationVar1, ClassificationVar2

I would perform this as a one-off exercise on say the most recent year worth of data, then from a speed perspective, I would run this as regularly as you need to, to flag up exceptional data.

A better approach, perhaps, which means learning new technologies is a HDFS/Spark then PIG/Python/R solution. But HDFS/Spark have some solutions that come out of the box to do log analysis. 500 million records is probably getting up into the reaches of performance problems with SQL, even with table partitioning and column indexing.