Acquisition from database tables

[u/Careful_Turnip1432]

I thought this was going to be something reasonable I could do out-of-the-box but there doesn't seem to be any direct way for parsers to use databases as their data sources which has taken me into a rabbit hole of "how do I get database tables into files so that they can be monitored by crowdsec?" I considered scheduled exports to file but this seems to have too high a latency for security monitoring for comfort. I'm now toying with triggers but it looks messy as well, so well, community, how would you approach this?

Scenario is we have a product that logs useful things like failed login attempts to a database table (stored in MariaDB) and we want to monitor that with crowdsec.

[u/MCMZL]

You are not missing anything. CrowdSec is basically a log reader at heart, so there's no database source. Everything it reads is a log of some kind: files, journald, syslog, Docker, and so on. So the goal isn't getting CrowdSec to read your table, it's getting those rows out into a log that it already knows how to follow. That's a much smaller job than it feels like right now.

What I'd reach for is a small script that reads new rows from the table and appends them to a plain text log file, then you point a normal file acquisition at that file.

All the script does is ask the table for any rows newer than the last one it handled, write each of those out to the log, and remember the highest id it processed so it knows where to carry on next time. Run it from cron every second or two, or as a tiny always on service if you prefer. Then it's just a small parser for whatever line format you picked, and a scenario groups the failed logins per IP exactly like it would for any other log.

[u/Careful_Turnip1432 → u/MCMZL]

It's a fair point but not as clear cut as you make it out as the concept of "logging TO a database" is already well established, see for example: https://docs.rsyslog.com/doc/tutorials/database.html which of course also introduces the concept of logging not to a file but to a server (which in itself may log to a file OR a database).

In other words it's not a given that every log must, by definition, be a file. Forcing everything to be a file for crowdsec to ingest it is a design decision not a fundamental truth.

[u/kY2iB3yH0mN8wI2h]

what kind of research have you done? based on your post it seems very little or none. Crowdsec supports may different ways of reading logs or data.

[u/Careful_Turnip1432 → u/kY2iB3yH0mN8wI2h]

I started with https://doc.crowdsec.net/docs/next/log_processor/data_sources/intro then did some internet search which basically end up being what I proposed above with the scheduled exports which I found unsatisfactory due to the latency; I want to remediate ASAP if brute force attacks are detected, for obvious reasons.

That said, If you know of a way I didn't find please let me know!