Running a Windows server can be tricky. When things go wrong, finding trends or patterns in a busy Windows Event Log with the native Event Viewer isn’t the easiest thing in the universe. Windows servers on the Internet are particularly vulnerable to Dictionary-based attacks, and one server I look after had a multitude of these messages:
I wanted to run a query in Event Viewer to show me all the failed logons, and see which usernames are commonly used in dictionary attacks. I also wanted to see which IP addresses these came from so that I can perhaps tweak firewall settings to block any rogue ISPs.
Windows Log Parser comes to the rescue. It’s a free command-line tool from Microsoft that can parse a multitude of log types using an SQL-like query language. It’s particularly good at parsing IIS logs, but we can save an .evtx event log file and begin to query items that we are interested in. We can use SQL commands like DISTINCT to only show unique rows.
Let’s dive in with an example:
LogParser.exe "SELECT * from SecurityLog.evtx" -i:EVT
This shows us all the columns available in our event log file called SecurityLog.evtx. I’ve specified that the input file is EVT with the -i:EVT switch.
We’re shown a lot of columns, such as EventID, EventType, SourceName. The details I want to dig into are account name and IP address. These are held in a long pipe delimited column called “Strings”. Let’s modify our query:
LogParser.exe "SELECT Strings from SecurityLog.evtx" -i:EVT
This gives us a single column containing the data we need:
We can see that the username is the 6th pipe delimited section along. We can modify the query to grab the 6th delimited text using:
SELECT distinct EXTRACT_TOKEN(strings,5,'|') as UserNames
Note we’re also using the DISTINCT keyword to get unique values only, and given the column a name of UserNames that we can use on a ORDER BY later. We now want to output to a file rather than to screen, and also only select items that have “winlogon” in the 19th column. This is so we only see failed windows logons. Our final commandline looks like this:
LogParser.exe "SELECT distinct EXTRACT_TOKEN(strings,5,'|') as UserNames into users.csv from SecurityLog.evtx where EXTRACT_TOKEN(strings,18,'|')='C:\Windows\System32\winlogon.exe' order by UserNames" -i:EVT -o:CSV
We’ve specified that the output file is a CSV file (-o:CSV) and we’ve told it to save to a file using the “into” command. Here are just a few usernames that have been tried:
Yet another reminder why you should change your Administrator username to something else!
We can see the IPs where these attacks came from (in column 20) using:
LogParser.exe "SELECT distinct EXTRACT_TOKEN(strings,19,'|') as IP into outIP.csv from SecurityLog.evtx where EXTRACT_TOKEN(strings,18,'|')='C:\Windows\System32\winlogon.exe' order by IP " -i:EVT -o:CSV
Hopefully this gives you an idea of how powerful Log Parser can be, and how you can use SQL syntax to search your log files.