10
$\begingroup$

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).

$\endgroup$
  • $\begingroup$ Not really, but SORTing by each column and looking at the minimum and maximum values, and setting alerts for ridiculous values might be helpful. $\endgroup$ – Barry Carter Feb 13 '16 at 3:41
  • $\begingroup$ In case it doesn't have to be super sophisticated you could use mlinreg moving window linear regression and thus identify large deviations. $\endgroup$ – Diego Feb 13 '16 at 10:36
3
$\begingroup$

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.

| improve this answer | |
$\endgroup$
  • $\begingroup$ I should have noted that this is technically Intersystems Caché. It exposes all classes as SQL tables. That's how I do all my reporting, for sanity's sake. $\endgroup$ – Devil's Advocate Feb 2 '17 at 14:06
3
$\begingroup$

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.

| improve this answer | |
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.