Sending ModSecurity Logs to MySQL

Previous Work

As part of our positions at SpiderLabs Research we each get time to undertake various research tasks. Typically on the Web Server Security team we spend this time improving ModSecurity and Trustwave WAF, analyzing the latest web threats, or coming up with new and interesting attack vectors. Occasionally though, we just want to make something cool. A few weeks ago while staring at a blank space on my office wall I decided I wanted to have something fun for my office. I wanted a quick and easy way to collect and visualize all my honeypot data. By doing this, I could quickly show off the power of ModSecurity while also filling up that pesky open wall space.

The first order of business was collecting logs into a central location. While Ryan Barnett has detailed how to forward logs to a central syslog server (which can be consumed by Splunk and the like) in his book The Web Application Defender's Cookbook, we wanted something a bit more versatile. While looking for a solution, we found that way back in 2004 we had linked to a post about parsing audit logs and placing them into MySQL (https://www.trustwave.com/en-us/resources/blogs/spiderlabs-blog/modsecurity-audit-log-to-mysql-parser/). While the actual code is no longer available (and written for an older version of ModSecurity) this general concept seemed promising for what we wanted to accomplish. This was also interesting as there are no formally documented ways to migrate ModSecurity logging information to databases like MySQL or Elasticsearch.

For our needs we wanted our logs in a format that could be easily accessed by a web application. To do this we used the ModSecurity Audit Log Collector (mlogc). The Mlogc project is described as a connector between a Modsecurity instance and a ModSecurity sensor. Originally this project was used to connect to ModSecurity to consoles like JWall's AuditConsole. While it isn't required to compile the base code itself is shipped with and supported as part of the greater ModSecurity project. For those that are curious, the code is available directly in the ModSecurity Git Repository (https://github.com/SpiderLabs/ModSecurity/tree/master/mlogc). In fact, if you have compiled ModSecurity from source you likely already have mlogc installed. If you didn't compile ModSecurity from source, don't fret, it is available via many package managers (i.e yum install mlogc).

Getting mlogc to work

Configuring mlogc is simple, although fairly undocumented. First ensure that it has been installed correctly. Typically the binary will appear in /usr/local/bin/ named mlogc. Second ensure you have a configuration file. The configuration file typically resides in /etc/mlogc.conf but can reside anywhere, as we'll see. You can grab an example configuration file from (https://github.com/SpiderLabs/ModSecurity/blob/master/mlogc/mlogc-default.conf). Inside the configuration file you must specify where the consoleURI is. We have slightly modified the base path to point it to a script that accepts our requests on the local server. Additionally, we specified our SensorUsername and SensorPassword, we will see how these are used later.

ConsoleURI "http://127.0.0.1:8888/rpc/auditLogReceiver.php"

SensorUsername "admin"

SensorPassword "admin"

The last configuration step is to setup the audit log feeds in the ModSecurity configuration file. Our configuration file looks as follows:

SecAuditEngine RelevantOnly

SecAuditLogParts ABIJDEFHZ

SecAuditLogType Concurrent

SecAuditLogStorageDir /var/log/mlogc/data

SecAuditLog "|/usr/local/bin/mlogc /etc/mlogc.conf"

Notice that we pipe our audit log to /usr/local/bin/mlogc and specify our configuration file as an argument. In the background whenever an auditable event is triggered ModSecurity will forward the event to Mlogc. Mlogc will in turn send a PUT request using basic authentication to the ConsoleURI specified in the Mlogc configuration with the username and password specified (Note: it is recommended to use SSL in this case due to the lack of transport communication provided by basic authentication). For our demo we configured Apache to listen locally on port 8888 and only serve our RPC page. If we were to use a non-local address it should be easy to see how we could allow multiple honeypots to communicate to our RPC page.

Parsing and Displaying Our Logs

Receiving this PUT request is a simple matter of generating a server side program that is expecting it. While any language capable of this feat will work, we used PHP for ease and compatibility. We have attached a sample of such a parsing program (auditLogReceiver.php). Essentially, this code expects an audit log entry, and when it receives one it splits it by audit log part and adds it to a database. The database schema is available here (mlogc.sql). A similar technique can be used to add information to other databases or push the data to an Elasticsearch instance (although you will have to convert the data to JSON). A brief note on this last scenario, ModSecurity v3 (AKA libmodsecurity) already supports native JSON based logging.

What we can do with this information is limitless. For our uses we threw together a real-time Google Maps representation of users attacking our honeypot. The code comes in two parts. Part one takes the data from the database and pulls out the parts we will use for in mapping (Attack message, IP, and headers). The code will then run the IP addresses through the MaxMind GeoIP PHP module and output an XML document Google's mapping API can understand. The first file is available here (http://www.modsecurity.org/MySQLScripts/geo.php). The second part is the JavaScript/HTML to display the maps and markers. That code is available here (http://www.modsecurity.org/MySQLScripts/map.html).

To see a demo of this in action, check our AWS instance http://54.172.243.151/maps/map.html

In this posting we have demonstrated how ModSecurity's Mlogc application can be used to push logs to remote (or local) webservers for processing. This can allow users to store logs in a central database or parse logs into a format needed for commercial or custom log parsing applications. This functionality makes ModSecurity 2.x more extensile and allows for strong interaction with other existing security technologies. Within ModSecurity 3.x the auditlog format has been changes to JSON to even further support this interoperability and we hope to implement an abstraction layer in order to allow build in interaction in a modular manner directly into the core product. Keep an eye out for these changes and more!