LogParser Loves Security Logs

, 0 Comments

Just digging something up that I used to use regularly to look for logon events related to a certain username (samAccountName). Thought I’d regurgitate them here for “the next time..”

Three different SQL queries for three different use cases:

Case 1. I know that the logon event that I’m looking for occurred on DC01.oholics.net, I’m therefore going to interrogate the live DC log. The primary username I’m looking for is “jon”, a secondary name shown as “dave”. This could be replaced by a junk string if I’m only really looking for “jon”, or just trim the query (up to you.. ).

SELECT * INTO C:\TEMP\Output\output.csv
FROM \\DC01.OHOLICS.NET\security
WHERE TimeWritten > TIMESTAMP ( '2009-01-01 01:00:00', 'yyyy-MM-dd hh:mm:ss' ) AND SourceName = 'Microsoft-Windows-Security-Auditing' AND
( Strings LIKE '%jon%' OR strings LIKE '%dave%')
view raw LogParser-Server-User.sql hosted with ❤ by GitHub

Case 2. In my domain, there are three domain controllers, I’m not sure where the logon events happened, so as in Case 1 I search the live DC logs, but this time searching all DC’s logs.

SELECT * INTO C:\TEMP\Output\output.csv
FROM \\DC01.OHOLICS.NET\security;\\DC02.OHOLICS.NET\security;\\DC03.OHOLICS.NET\security
WHERE TimeWritten > TIMESTAMP ( '2009-01-01 01:00:00', 'yyyy-MM-dd hh:mm:ss' ) AND SourceName = 'Microsoft-Windows-Security-Auditing' AND
( Strings LIKE '%jon%' OR strings LIKE '%dave%')
view raw LogParser-Servers-User.sql hosted with ❤ by GitHub

Case 3. I have three months of backed up logs to search through (in C:\TEMP\Logs) for all logon events for samAccount name “jon” (and optionally “dave”, as above). I may splurge out the script that I used to use to backup and clear the event logs next, that could be useful again – I’ve got to clean it first.

SELECT * INTO C:\TEMP\Output\output.csv
FROM C:\TEMP\Logs\*
WHERE TimeWritten > TIMESTAMP ( '2009-01-01 01:00:00', 'yyyy-MM-dd hh:mm:ss' ) AND SourceName = 'Microsoft-Windows-Security-Auditing' AND
( Strings LIKE '%jon%' OR strings LIKE '%dave%')
view raw LogParser-Files-User.sql hosted with ❤ by GitHub

Usage: logparser -i:EVT file:<SQLFileName>.sql -o:CSV -resolveSIDs:ON 

Where:

  • The above SQL query is saved as LogParserRedaction.sql in the same location as the LogParser binary.
  • The collection of logs to be redacted are in C:\TEMP\Logs\
  • The output file will be written to C:\TEMP\Output\output.csv