Microsoft’s brief description of LogParser is this: “Log parser is a powerful, versatile tool that provides universal query access to text-based data such as log files, XML files and CSV files, as well as key data sources on the Windows® operating system such as the Event Log, the Registry, the file system, and Active Directory®.”
The SQL is based on TSQL, and is difficult to master. The good news is that there are many websites that have information about Log Parser, especially with regard to forensic use. I am frequently asked to show when a user logged into a particular workstation. Because we have security logging enabled for our XP workstations, typically this means looking for event id 528.
I created a batch file and a sql file and placed them into the program file directory for Log Parser to make this task easier. The batch file:
=======
rem GetInteractiveLogons.cmd
rem Alan Kaplan 5/15/2009
rem the SQL code is based on code by Dave Kleiman
rem http://seclists.org/basics/2006/Jan/0310.html
@echo off
cd /d %~d0%~p0
setlocal
if %1z == z (
ECHO Get Interactive logons from security log of what computer name?
set /p PCName=[ENTER] for %COMPUTERNAME%: &goto main
) ELSE (
set PCName=%1
)
:main
if %PCName%z ==z set PCName=%COMPUTERNAME%
logparser file:WorkstationInteractiveLogons.sql?machine=%PCName%
start excel LocalLogons_%PCName%.csv
EndLocal
=======
The batch file has a few things you don’t see frequently.
cd /d %~d0%~p0 — change to the drive and directory of the batch file
setlocal and endlocal causes the environment changes to be limited to the batch file.
I am using the extended syntax for the IF command, and the set /p command to prompt for an environment variable.
The sql file called by the batch file is WorkstationInteractiveLogons.sql. I also put this in the LogParser Program file directory.
=========
SELECT
TimeGenerated AS TimeGenerated,
EXTRACT_TOKEN(Strings,0,’|’) AS User,
TO_UPPERCASE(EXTRACT_TOKEN(Strings,6,’|’)) AS WorkStation,
CASE TO_INT(EXTRACT_TOKEN(Strings,3,’|’))
WHEN 2 THEN ‘Interactive’
WHEN 3 THEN ‘Network’
WHEN 4 THEN ‘Batch’
WHEN 5 THEN ‘Service’
WHEN 6 THEN ‘Proxy’
WHEN 7 THEN ‘Unlock’
WHEN 8 THEN ‘Network Cleartext’
WHEN 9 THEN ‘New Credentials’
WHEN 10 THEN ‘Remote Interactive’
WHEN 11 THEN ‘Cached Interactive’
WHEN 13 THEN ‘Cached RemoteInteractive’
WHEN 14 THEN ‘Cached Unlock’
END AS Type
INTO LocalLogons_%machine%.csv
FROM \\%machine%\security
WHERE EventID IN (528) and Workstation = TO_UPPERCASE(‘%machine%’) and type <> ‘Cached Interactive’
GROUP BY User,WorkStation,TimeGenerated,Type
ORDER BY TimeGenerated ASC
========
Note that this does not work for Vista or Windows 2008, as the log event 528 in the operating systems is now 4624, and format has changed. For those operating systems we have a new obtuse tool, wevutil. A brief explanation of how to get this data for Vista/2008 can be found at the blog of the Windows Auditing Team: http://blogs.msdn.com/ericfitz/archive/2008/07/16/wevtutil-scripting.aspx.