We all have to work with event logs on a regular basis. There are a lot of ways to tackle event logs and today I want talk about using LogParser with Event logs.
LogParser isn’t new, it’s been around for years but I haven’t heard of many people using it in there investigations. This is a quick blog post about transforming the data to SQL Server. You can do a LOT more with LogParser but for simplicity I’m going to stick to posts with specific goals in mind.
I’m sure you are asking why LogParser? Well it’s simple,
- you can use SQL command syntax for filtering the log data on the front end. You can build commands that generate report type summaries.
- You can import logs directly into Microsoft SQL Server (only RDMS supported is MSSQL, sorry) or other flat file formats.
- You can use wizardly in SQLServer including .NET CLR assemblies to manipulate the data once it is imported. Think Regex, data decoding, data mapping, crazy algorithms, etc…
- It’s a fast command line tool.
You have your standard flat file output formats available as well like CSV, etc… but what this post is addressing is digging through large numbers of event logs fast. Use the power of SQL Server!
LogParser does something else really interesting when transforming eventlogs. When converting event logs it creates a column called ‘Strings‘, a concatenated string of values from the event details that you can then search against for keywords/values of interest.
Importing Event logs
Here’s a basic example for importing a folder full of eventlogs.
“c:\Program Files (x86)\Log Parser 2.2\LogParser.exe” -i:EVT “SELECT EventLog,RecordNumber,TimeGenerated,TimeWritten,EventID,EventType,EventTypeName,EventCategory,EventCategoryName,SourceName,Strings,ComputerName,SID,Message,Data INTO [dbo].[eventlogs] FROM eventlogs\*.evtx” -o:SQL -createTable:ON -server:localhost -database:mydatabase
So what is this command doing? It’s Importing an entire directory of event logs into a single sql table! Excited yet?
Filtering data
You can filter your data before it gets imported as well…
Lets say you only care about a certain EventID. In this example I’m going to filter for system time changes. You can add a Where clause for EventID in (‘4616’). Using the SQL command “where in” instead of = allows you to specify multiple values to match such as EventID in (1102,4616,1108). You will receive results for EventID 1102, 4616 and 1108. You could take this a lot further but I want to keep it simple’ish here.
“c:\Program Files (x86)\Log Parser 2.2\LogParser.exe” -i:EVT “SELECT EventLog,RecordNumber,TimeGenerated,TimeWritten,EventID,EventType,EventTypeName,EventCategory,EventCategoryName,SourceName,Strings,ComputerName,SID,Message,Data INTO [dbo].[eventlogs] FROM eventlogs\*.evtx WHERE EventID in (1102,4616,1108) ” -o:SQL -createTable:ON -server:localhost -database:mydatabase
Getting fancy
Say you need a primary key field so you can create indexes when you have a lot of data to get through. You can map a fake column just like you would in SQL so you can go back and make it a PK or reassign the values. I actually create the table ahead of time and make IDCOLUMN a primary key identity column so it will assign a unique integer value. The easiest way to get the data structure is to start importing and cancel it and then script your create statement from SQL Server Manager.
Here’s the syntax for primary key field. Remember, You need to have the table created ahead of time…
“c:\Program Files (x86)\Log Parser 2.2\LogParser.exe” -i:EVT “SELECT 0 As IDCOLUMN,EventLog,RecordNumber,TimeGenerated,TimeWritten,EventID,EventType,EventTypeName,EventCategory,EventCategoryName,SourceName,Strings,ComputerName,SID,Message,Data INTO [dbo].[eventlogs] FROM eventlogs\*.evtx WHERE EventID in (1102,4616,1108) ” -o:SQL -createTable:ON -server:localhost -database:mydatabase
So what I’m doing is saying the number 0 is the value and the name is IDCOLUMN. SQL Server will ignore that value and give it it’s own since it’s a Primary key ident column. Nice right?
Query data in SQL
Now that you have a table called [dbo].[eventlogs] in your database [mydatabase] you can start looking for indicators.
Select * from [dbo].[eventlogs] where Strings like ‘%badactor%’ and EventID in (EventIDNumber)
Wrap Up
As you can see LogParser is a quick and powerful tool for dealing with eventlogs. LogParser is free and very powerful! You have the power of SQL Syntax on the front end and the back end with SQL Server. Sure you could grep for rows of interest but the data still needs to be transformed.
Part 2 will have LogParser examples with IIS logs. I hope you find this post both informative and useful!