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!

Using Modsec2sguil for HTTP transaction logging

Modsec2sguil is currently configured to send alerts to Sguil. ModSecurity can be configured to log any event or transaction, including 200 OK, 302 Redirect, etc. Modsec2sguil distinguishes between alerts and other events by only processing HTTP codes of 400 and higher. Since 0.8-dev2 there is a configuration directive to prevent certain codes, such as 404, from being treated as an alert.

Now I have the following idea. Since ModSecurity can log all events with details of request headers, response headers and POST message body, it may be interesting to just send all these events to Sguil. They should not be appearing as alerts, but having them in the database can perhaps be interesting. I know using flow data and full packet captures the same data can be accessed, but having it in the database makes querying it a lot easier and longer available.

Possible problems are mostly the performance hit the webserver may take for sending all these events to Sguil and the storage requirements in Sguil’s database. I estimate the events are about 1kb in size on average, so on a busy site this may cause the database to grow very rapidly. Of course this behavior would be optional so it can be disabled.

Any thoughts on this idea?