When bad things happen to either your website or your server you’re usually faced with a situation that either makes or breaks you. Much like having a good backup and restore plan, being able to filter and scan log files for what you need to help draw conclusions on how a situation occurred or by whom it was conducted, is an important part of your security plan. However if you have a heavily traffic’d website, network share or part of your file system and you’re doing a lot of logging, you probably have files the size of the moon to wade through, so making sense of them can be a nightmare.
This is part 1 of 2 in a series on how to wade through the information overload that can come when searching giant logs files.
Searching through Windows Auditing Logs
If you have Windows File System Auditing turned on, you should be able to see all sorts of valuable information about actions that have occurred on your file system.
- Files being created, saved, deleted (maybe by your IIS user?).
- has a hack occurred that has provided the IIS user with more access than wanted?
- has someone discovered a security hole in your site allowing the IIS user to do things unintended?
- Web.config files being changed (Either by the IIS user, or an admin user).
- Has a third party user gained access to your server and logged in by RDP? Has your webhost done so without your knowledge?
While IIS logs can help with shed light on some of these things, actual file level actions are best recorded using Windows File System Auditing as this can be setup to record not just logs of requests such as those recorded in IIS logs, but also file opens and edits inside an RDP session, an FTP session and generally any other point of interaction with the file system.
This can be an important record when it comes to retracing the steps of an attack against your server – if you just remember to turn auditing on in sensitive parts of your file system!
Once you do turn it on however the next problem arises when it comes to investigating an incident on your server: Oceans of data.
So much data, that the Event Viewer itself is unable to filter and search this haystack at anything but a glacial pace.
Finding a Paddle
This problem can get really bad. I’ve had to search through Windows Event Logs that were many gigabytes before, and the event viewer simply packs it in.
So what can you use to scan logs these big?
Microsoft has the answer in the form of an unsupported tool called Log Parser 2.2 that is just the ticket.
While I'm definitely not the first to talk about this tool (Jeff Atwood was writing about Log Parser in 2005!) it’s a utility that often goes unknown outside the server admin world.
Although it was released in 2005, Log Parser 2.2 is still a very powerful and an extremely fast command line tool used to query log files using SQL queries.
When it comes to taking on the monumental task of querying 100’s of megabyte or 100’s of gigabyte log files, it soon becomes your best friend.
logparser.exe "SELECT * from 'C:\LogFiles\example_windows_eventviewer_file.evtx'" –i:EVT
2012 – Log Parsing; Now With GUI!
Log Parser 2.2 was released in 2005. The log formats for a lot of different applications haven’t changed much in this time – although a number of things have occurred in the “Log Parser world” since.
- Lizard Labs has released Log Parser Lizard, a GUI wrapper for Log Parser.
- The Exchange team has released Log Parser Studio another GUI wrapper for Log Parser.
I still use the command line exe to query large files. I’m not sure if its simply instinct or placebo, but it feels faster (I haven’t tested it), but I almost always use Log Parser Lizard to build my queries before running my command line log searches.
The main reason I use this is that Log Parser Lizard has intellisense to help write your queries (this saves you a lot of time)
The Log Parser Lizard application itself does have a downfall, in that it is not being written in a very async manner, meaning that if you use it on extremely large files, Log Parser Lizard will block the UI thread and hang – another reason that I use the command line to scan large files.
The Exchange team’s tool is written to not block the UI while processing files, but is very feature poor compared to Log Parser Lizard.
So my recommendation:
- Build your queries using Log Parser Lizard on a smaller subset of your log file.
- Copy your query into the command line and run it against the command line tool.
Example queries for Windows Security Logs
If you have file system auditing turned on, and you export your Windows Security logs to external files (evtx), you can then use Log Parser to query them directly.
For security logs, i’ve created a few sample queries, to give you an idea on some searches that help provide intelligence on what has occurred on your server and to get you started.
All actions against a folder within a certain timeframe
“What’s been happening in the folder that my website/application stores data in between date X and date Y”
logparser.exe "SELECT TimeGenerated, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message FROM 'C:\LogFiles\example_windows_eventviewer_file.evtx' WHERE Strings LIKE ‘%C:\\MyWebsiteRootFolder%’ AND TimeGenerated BETWEEN timestamp(’04/04/2012', ‘dd/MM/yyyy’) and timestamp(’06/04/2012', ‘dd/MM/yyyy’) ORDER BY TimeGenerated DESC" -i:EVT
All successful file actions
“Who/what users have accessed my folders and files inside a certain directory”
logparser.exe "SELECT TimeGenerated, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message FROM 'C:\LogFiles\example_windows_eventviewer_file.evtx' WHERE Strings LIKE '%C:\\MyWebsiteRootFolder%' AND EventTypeName='Success Audit Event'" -i:EVT
All failed attempts to do something to a file/folder
“Who/what users have attempted to run an action against a file that they aren’t allowed to do” (maybe a hacker, testing your site)
logparser.exe "SELECT TimeGenerated, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message FROM 'C:\LogFiles\example_windows_eventviewer_file.evtx' WHERE Strings LIKE '%C:\\MyWebsiteRootFolder%' AND EventTypeName='Failure Audit Event'" -i:EVT
Next up -> Scanning IIS Logs to investigate security incidents
While File system auditing is not something you want to turn on your entire servers disk drive, for instances where few writes occur (such as your website directory), setting it up and then using it to “play back the tape” on what has occurred on your server’s file system is a powerful tool to have in your arsenal.
What’s more important is that when you go to inspect these logs files, that you have an easy and fast way to query them.
Next we’ll cover how to do the same thing against IIS logs.
Further Reading
Monitoring Event Logs with Log Parser
Unofficial Log Parser Fan Site
Exchange Team Log Parser Studio (GUI for log parser)