News for hackers. Stuff that matters.
Re:Simple Anomaly Detection Using Plain SQL by kccqzy on Monday January 11, 2021 @05:16PM (#25738217)
While this is all good, very few places AFAIK actually process their HTTP server logs into a form that can be queried by SQL. Google does it, certainly, but many smaller shops don't. Has this been your experience as well?
Re:Simple Anomaly Detection Using Plain SQL by tstack on Monday January 11, 2021 @06:31PM (#25739687)
> very few places AFAIK actually process their HTTP server logs into a form that can be queried by SQL.

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.

Re:Simple Anomaly Detection Using Plain SQL by ironchef on Monday January 11, 2021 @05:51PM (#25738873)
Opposite experience here. Most places of size I know of shove weblogs into SQL, S3, or some Hadoop infra.
Re:Simple Anomaly Detection Using Plain SQL by simonw on Monday January 11, 2021 @05:59PM (#25739072)
Yeah SQL tends to be the lingua-franca for "big data" tools - so while logs usually won't be loaded into a regular MySQL/PostgreSQL it's pretty common for them to be made available in something like Hive that allows them to be analyzed using SQL.
Re:Simple Anomaly Detection Using Plain SQL by ilovefood on Monday January 11, 2021 @11:56PM (#25742481)
I use simple bash scripts that tail the logs and insert them into an SQLite database that I query through Metabase. It's been working great for my projects and no issues with scale or downtime. There's a writeup on my blog if you want :)
Re:Simple Anomaly Detection Using Plain SQL by jetter on Tuesday January 12, 2021 @08:57AM (#25746908)
I am doing exactly this in my current project - I put server logs of API services to column-based database with extensive SQL support, and it's feeling great. SQL allows me to build great analytics dashboard in a simple dialect that I know, and in terms of performance it runs circles around ELK stack (short animated GIF preview of UI is available on https://apiroad.net/ )
Re:Simple Anomaly Detection Using Plain SQL by ayewo on Tuesday January 12, 2021 @10:59AM (#25748578)
ApiRoad is an interesting project, are you running this all by yourself?

In any case, do you have a write-up somewhere of how you use a columnar DBMS to slurp your server logs?

Re:Simple Anomaly Detection Using Plain SQL by jetter on Tuesday January 12, 2021 @11:42AM (#25749333)
Thank you! Yes this is a big solo project now. Didn't have time to do a write up yet. I am a big fan of Clickhouse, so I use it for log storage. To collect server logs, it is not exactly a simple file parsing setup - I have a gateway daemon that stays in front of the API server. The daemon receives all the requests from subscribers, authorizes that this specific connection has access to specific API, rate limits, and proxies all the connections to upstream API, dumping HTTP logs to Clickhouse once in a while. Then Vue.js & Laravel powered dashboard queries Clickhouse to generate various stats, which is later used for analytics and usage-based billing.
Re:Simple Anomaly Detection Using Plain SQL by blimpy on Tuesday January 12, 2021 @07:22PM (#25756145)
I haven't seen it that often either.

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.

Re:Simple Anomaly Detection Using Plain SQL by jimsparkman on Monday January 11, 2021 @10:58PM (#25742089)
At least in the AWS ecosystem, Athena makes this very simple to implement. Stream load balancer logs to S3 and query directly.
Re:Simple Anomaly Detection Using Plain SQL by ithrow on Monday January 11, 2021 @05:41PM (#25738668)
Don't see any reason why even a solo developer couldn't/wouldn't do this? it's just one method to use.
Re:Simple Anomaly Detection Using Plain SQL by meritt on Monday January 11, 2021 @06:04PM (#25739183)
That's what you took from that? They gave an example of using SQL to do anomaly detection that happened to use httpd logs as an example.

The strategies within could be applied to virtually any time-series data.