4 ms·
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 man
by kccqzy 6y ago
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?
- ithrow 6y agoDon't see any reason why even a solo developer couldn't/wouldn't do this? it's just one method to use.
- ironchef 6y agoOpposite experience here. Most places of size I know of shove weblogs into SQL, S3, or some Hadoop infra.
- simonw 6y agoYeah 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.
- meritt 6y agoThat'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.
- tstack 6y ago> 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 https://en.wikipedia.org/wiki/Logparser * lnav -- https://lnav.org 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.
- deleted 6y ago[deleted]
- jimsparkman 6y agoAt least in the AWS ecosystem, Athena makes this very simple to implement. Stream load balancer logs to S3 and query directly.
- ilovefood 6y agoI 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 :)
- jetter 6y agoI 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/ https://apiroad.net/ )
- ayewo 6y agoApiRoad 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?
- jetter 6y agoThank 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.
- blimpy 6y agoI 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.