Thursday, April 28, 2011

Parsing Logs - Part I

Microsoft does a ton of great logging in their OS and Applications.  The hardest part is sorting through it.

A while ago, I discovered Microsoft's LogParser tool.  It's great for older systems that don't leverage PowerShell natively.  Certainly, WMI is out there, but learning curve can feel pretty steep.  Instead, utilizing LogParser's native SQL query engine, I can leverage a syntax I know and love.

Without further adieu, here's how I do it:

SELECT EventLog, -- The Name of the Event Log
  
EventID, -- The number of the Event ID
  
SourceName, -- The Source of the error

   COUNT(*) AS Frequency, -- Aggregate distinct results
  
Message -- The body of text from the event

INTO Report.txt -- Return the results to txt, based on the execution path
FROM Application, -- The names of the default logs
   Security,
  
System,
  
'Directory Service', -- E
xplicitly specify extra logs
   'DNS Server',
  
'File Replication Service' 

WHERE EventType IN(1;2;3;5;16) -- Ignore Information or Success event types
  
AND TimeGenerated >= TO_LOCALTIME( SUB( SYSTEM_TIMESTAMP(),
      
TIMESTAMP('30', 'd') ) ) -- Subtract 30 days from exec time

GROUP BY EventLog, -- Group our results, for the aforementioned aggregate
   EventID,
  
SourceName,
  
Message
HAVING
COUNT(*) > 1 -- Only return events that have happened more than once

ORDER BY EventLog, -- Sort by log name, ascending
  
EventID -- Then event id number, ascending