Effectively analysing sysmon logs

We previously covered setting up and using sysmon (System Monitor), which is part of the Sysinternals suite from Microsoft. In this article, we’ll walk through analysing the logs using Microsoft’s LogParser utility.
LogParser has been around for many years. It is a free console based tool that can parse a wide variety of log types typically found on Windows systems. Analysis is performed by running queries that are a dialect of the Structured Query Language (SQL). When developing queries, we like to use the LogParser Studio tool, also available as a free download from Microsoft Technet. This provides a clean, but moderately flexible GUI front end to LogParser. LogParser supports Windows Event Logs. Since sysmon writes its output in this format, LogParser is a useful tool to analyse that output.
You can either analyse exported sysmon event logs or view them on the host platform. However, if you wish to view them on the host platform, then you will first need to modify the registry to facilitate this. In regedit (or your favourite registry editor) you simply need to create a registry key named Microsoft-Windows-Sysmon/Operational under HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\eventlog

Sysmon registry key

A query with LogParser to view all the records in the sysmon event log would then look like this: LogParser.exe -i:EVT -o:NAT "SELECT *FROM Microsoft-Windows-Sysmon/Operational
You will likely find that the output is hard to understand. Within LogParser Studio, the output is presented in tabular format, but still doesn’t give us all of the information in an easily understandable way. This is largely due to the nature of Windows event logs on modern systems. You may be familiar with the XML format of Windows Event logs, where the metadata relating to the event is recorded in the System section of the log and the actual event is recorded in the EventData section that comprises field name and field value pairs. LogParser studio presents the meta-data nicely formatted but all of the EventData section is concatenated into a field called “strings”.

EventData concatenated into Strings

Constructing Queries

If you look carefully at the strings field, you will note that each field value is separated by a | (pipe) character. LogParser provides us with a function named EXTRACT_TOKEN, which allows us to use the pipe character as a field separator and extract each numbered field. We can use a SQL query to extract and name each one of the those fields. With this information we can create some SQL queries that will present the sysmon event data in a much more user friendly way.

Sample LogParser SQL query

Breaking down that query, applicable to a sysmon Net Connection event, you can see that it takes the form of a standard SQL select query: SELECT * FROM table WHERE condition exists
We use the EXTRACT_TOKEN function repeatedly to extract each field in the “Strings” section of the output sequentially, using the AS statement to name each field. We can nest functions, as in the line starting with the function REVERSEDNS. Here, we are extracting the field containing the Destination IP address and using the REVERSEDNS function to resolve the IP address to a host name. This is useful in helping establish if the Destination IP address is a dynamic IP address – threat actors sometimes use these in their infrastructure. You can quickly compare the destination host name field (created when the event log was written) to the destination host name when you are doing your analysis.
We have created a set of queries for the sysmon log files that will process each of the Event types that sysmon records – there are 15 different events at the time of writing. You can download these queries from Nettitude’s github account. LogParser studio will allow you to open each query in a separate tab; you can switch tabs according to your investigative needs.

Advanced Queries

Of course, the queries will only present you with the log data in a handy format. Log Parser allows you to output the data in a variety of different formats. You can also build on these queries to conduct threat hunting or more focused analysis of the sysmon event logs. You have to bear in mind that for any strings you need to search for, no matter which of the data fields it may be in, you have to search inside the field called Strings for that data. For instance, in our previous query example for Network Connections, if we wanted to search for any connections where the source port was 49186 we would modify the last line of the query to read WHERE EventID=3 AND Strings Like '%49186%'.
This applies to any event data that you are interested in. This limitation does provide some challenges, but with some careful thought they could be overcome. For instance, if you have configured sysmon to capture all command line activity, you can use the ProcessCreate query and search the Strings sections for dangerous commands, e.g. psexec. Alternatively, you could just export all the data into a comma separated values file and then “stack” the commands to identify the least frequently used commands. These are commands that may deviate from the normal patterns of behaviour in your environment and may therefore be worth of further investigation.

Conclusion

LogParser and LogParser Studio are free tools that can be utilised to analyse a wide variety of log types, including sysmon event logs. Learning how to build and execute SQL queries is a critical component of Incident Response.