Using Modsec2sguil for HTTP transaction logging revisited
Recently I wrote about the idea to log all HTTP transactions into Sguil using my Modsec2sguil agent. I’ve implemented this in the current 0.8-dev5 release and it works very well. All events go into Sguil smoothly and I’ve not experienced slowdowns on the webserver. I’ve been running it for almost a week now, like to share the first experiences here.
I find it to be quite useful. When receiving an alert, it is perhaps more interesting to see what else was done from that ipaddress than to see what was blocked (unless you are suspecting a false positive of course). One area I find to be useful is when I’m creating rules against comment spam on this blog. By seeing all properties of a spam message I can create better rules. For example on broken user-agents or weird codes inserted into the comment field of Wordpress.
It’s easy to search and filter on HTTP response codes because the code is a part of the RT message. For example, when searching for all HTTP 500 error codes, add the following ‘WHERE’ clause to a query:
WHERE event.signature like “%MSc 500%”
This works quite fast although you best limit the query on properties like date and port as well. To get all the HTTP code 500 alerts from the last days do something like:
WHERE event.timestamp > ‘2007-08-18’ AND (event.dst_port = 80 OR event.dst_port = 443) AND event.signature like “%MSc 500%”
One thing that is disappointing is the inabillity to search in the event payloads stored in the database. Technically it’s possible to create mysql queries that search for certain strings, but this process is so slow that it’s hardly usable in practice. The problem here is that the database field containing the payload is not indexed. I’ll show the query I used here (ripped from David Bianco’s blog)
WHERE event.timestamp >= ‘2007-08-18’ AND (event.dst_port = 80 OR event.dst_port = 443) AND data.data_payload like CONCAT("%", HEX(“Mozilla/5.0”), “%”)
If you know a more efficient query, please let me know!