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', -- Explicitly 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