Brrrrrr....... Colorado is cold!

Using Microsoft Log Parser to search event viewer for critical system errors

I'm an internet scour-er... All day long I scour Google to do my job, I see the work others have done, I research problems, I pile lots of fragments of thoughts of many people together to come up with my own solutions.

So for once I thought I'd give something back in the hope that it will be useful for others... For this you will need Microsoft Log Parser and blat. Log Parser is a powerful little utility that allows you to use SQL statements to search many different types of log files and then export them in to a variety of formats. For my example we will search the Windows Event Viewer for Warning and Error messages, we will then export them to an HTML file via a template, and finally we will email the message to ourselves.

First the code and what it does.

It's really broken in to 3 segments that do the same thing, just in different logs. The 3 things that it does are 1) Search the log for Warning, Error, or Failures, 2) If anything is found, email the results to me, 3) Delete the results to keep from getting any duplicate stuff.

rem @echo off

REM Using Microsoft Log Parser to stay on top of problems in Event Viewer
REM By Scotty D
REM February 2008


REM You need to download both "blat" and "Microsoft Log Parser"
REM Blat is at http://www.blat.net/
REM Microsoft Log Parser is at http://www.microsoft.com/technet/scriptcenter/tools/logparser/default.mspx
REM These only need to be installed on the machine where the script is run


REM Here you set all of your variables as it's much easier to do it here than in the details of the script

set evtlogshome=c:\tools\evtlogs\
set serverlist=%evtlogshome%servers.txt
set logparser="C:\Program Files\Log Parser 2.2\LogParser.exe"
set blat=C:\tools\blat262\full\blat.exe
set adminemail=bobkratchet@superniftyserver.com
set emaildomain=@superniftyserver.com
set SMTP=relay.superniftyserver.com

REM Parse the System Event log using Microsoft Log Parser
REM We are looking only for error events and Warning events
REM Only the new events since the last time this script was run will be sent via email
REM You will get one email per server where a new event is found
REM If no new Error or Warning event is found, you won't get an email

@for /f "tokens=1" %%a in (%serverlist%) do %logparser% -i:EVT -o:TPL -tpl:%evtlogshome%systemerror.tpl "SELECT TimeGenerated, EventTypeName, SourceName, Message INTO %evtlogshome%reports\%%asystem.htm FROM \\%%a\System WHERE EventTypeName = 'Error event' OR EventTypeName = 'Warning event'" -iCheckpoint:%evtlogshome%tail\%%asystemcheckpoint.lpc

REM Now email the details from each server out if anything is found

@for /f "tokens=1" %%a in (%serverlist%) do if exist %evtlogshome%reports\%%asystem.htm %blat% %evtlogshome%reports\%%asystem.htm -serverSMTP %SMTP% -f %%a%emaildomain% -to %adminemail% -s "New System Event log event on %%a"

REM Delete those suckers so we don't get junk

@for /f "tokens=1" %%a in (%serverlist%) do if exist %evtlogshome%reports\%%asystem.htm del %evtlogshome%reports\%%asystem.htm

REM Parse the Application Event log using Microsoft Log Parser
REM We are looking only for error events and Warning events
REM Only the new events since the last time this script was run will be sent via email
REM You will get one email per server where a new event is found
REM If no new Error or Warning event is found, you won't get an email

@for /f "tokens=1" %%a in (%serverlist%) do %logparser% -i:EVT -o:TPL -tpl:%evtlogshome%Applicationerror.tpl "SELECT TimeGenerated, EventTypeName, SourceName, Message INTO %evtlogshome%reports\%%aapplication.htm FROM \\%%a\Application WHERE EventTypeName = 'Error event' OR EventTypeName = 'Warning event'" -iCheckpoint:%evtlogshome%tail\%%aapplicationcheckpoint.lpc

REM Now email the details from each server out if anything is found

@for /f "tokens=1" %%a in (%serverlist%) do if exist %evtlogshome%reports\%%aapplication.htm %blat% %evtlogshome%reports\%%aapplication.htm -serverSMTP %SMTP% -f %%a%emaildomain% -to %adminemail% -s "New Application Event log event on %%a"

REM Delete those suckers so we don't get junk

@for /f "tokens=1" %%a in (%serverlist%) do if exist %evtlogshome%reports\%%aapplication.htm del %evtlogshome%reports\%%aapplication.htm

REM Parse the Security using Microsoft Log Parser
REM We are looking only for Failure Audit events
REM Only the new events since the last time this script was run will be sent via email
REM You will get one email per server where a new event is found
REM If no new Error or Warning event is found, you won't get an email

@for /f "tokens=1" %%a in (%serverlist%) do %logparser% -i:EVT -o:TPL -tpl:%evtlogshome%Securityerror.tpl "SELECT TimeGenerated, EventTypeName, SourceName, ComputerName, Message INTO %evtlogshome%reports\%%asecurity.htm FROM \\%%a\Security WHERE EventTypeName = 'Failure Audit event'" -iCheckpoint:%evtlogshome%tail\%%asecuritycheckpoint.lpc

REM Now email the details from each server out if anything is found

@for /f "tokens=1" %%a in (%serverlist%) do if exist %evtlogshome%reports\%%asecurity.htm %blat% %evtlogshome%reports\%%asecurity.htm -serverSMTP %SMTP% -f %%a%emaildomain% -to %adminemail% -s "New Security event log event on %%a"

REM Delete those suckers so we don't get junk

@for /f "tokens=1" %%a in (%serverlist%) do if exist %evtlogshome%reports\%%asecurity.htm del %evtlogshome%reports\%%asecurity.htm

The block of set commands is simply for setting a bunch of variables. They are rather self-explanatory, just follow the format of the examples given. The checkpoint files are files Log Parser uses so that it doesn't look at the entire log again, it's a similar concept to using "tail".

Now let's break out the first set of commands.

@for /f "tokens=1" %%a in (%serverlist%) do %logparser% -i:EVT -o:TPL -tpl:%systemtemplate% "SELECT TimeGenerated, EventTypeName, SourceName, Message INTO %systemresults% FROM \\%%a\System WHERE EventTypeName = 'Error event' OR EventTypeName = 'Warning event'" -iCheckpoint:%systemcheckpoint%

The for command is simply a batch command which tells the script to open the txt file of servers and loop through the command until all servers are done. Starting at do is the log parser script.

%%a - anytime you see that it's current "token" pulled from txt file of servers

-i:EVT tells the script you are looking at Windows Event Viewer

-o:TPL tells the script to output the findings to a template format

-tpl: tells the script the location of your templates

SELECT - the SELECT statement is your SQL, in this instance we pick selected fields from Event Viewer. You could substitute * to see everything and pick which fields you want just as you would when doing a database query.

INTO tells which output file to put the results in to, in my case HTML

FROM is the server and log. In this case \\%%a\System means it will be the current server of the batch for loop and it's system log.

WHERE is simply conditional SQL to filter out all of the stuff I don't want to see.

-iCheckpoint spells out the file where the past parsed information is kept so that it doesn't send you duplicate messages.

The tough part is done. Next we simply see if a file exists, and if it does we sent it out via blat. After that, we check if the file exists and then delete it.

The last part of this is the template and it's ridiculously simple. Basically what I wanted to do was set up a simple HTML table that would get emailed to me via blat. You really just need to correspond the fields you selected in the query to the fields you want in the table.

<LPBODY>
<TABLE border=1>
<TR>
<TD width=15%>%TimeGenerated%</TD>
<TD width=5%><font color=red>%EventTypeName%</TD>
<TD width=10%>%SourceName%</TD>
<TD width=70%>%Message%</TD>

</TR>
</TABLE>
</LPBODY>

Once I had this set up, I just set one of my machines to check every server (via the list) every morning before I come in. Since it's only new events, the volume is rather low and I can skim through them quickly to see if there are problems needing addressing. The final result is simply an email like this. :)

Example System Event Log error message:

From: monitoredserver@superniftyserver [mailto:myserver@superniftyserver]
Sent: Wednesday, February 13, 2008 1:11 PM
To: Kratchet, Bob
Subject: New Server Event log event on monitoredserver

2008-02-13 12:45:55

Warning event

LSASRV

The Security System detected an authentication error for the server LDAP/powerfulserver.superniftyserver.com/superniftyserver.com@superniftyserver.com. The failure code from authentication protocol Kerberos was "The specified user does not exist. (0xc0000064)".

2008-02-13 12:46:41

Warning event

LSASRV

The Security System detected an authentication error for the server LDAP/nitcdc01.superniftyserver.com/superniftyserver.com@superniftyserver.com. The failure code from authentication protocol Kerberos was "The specified user does not exist. (0xc0000064)".

try this in the "real" world

This humorous picture is my local post office, Sullivan Station (8700 E. Jefferson Way). There are just as many people behind me as you see in front of me here. I came in at 12:50 with 3 people working the counters. Promptly at 1:00 with about 20 people still waiting in line, 2 people logged off leaving this one poor person.

Customers were throwing up their hands and leaving, new customers came in and turned right around. Counter workers walked through, but made no eye contact. The brave customers that stayed around were cracking jokes about government jobs.

Promptly at 1:15 two people came back, logged in, and helped the line of now 30+ customers.

A one time occurence is understandable, people could be sick, volume could be higher than normal, I get that... Unfortunately, it's happened over and over at that post office so I decided to go online to file a complaint, which you can do here. I wouldn't recommend it though, because all they do is forward your phone number to the manager of the post office you filed a complaint on. In my case, he called me and irritatedly justified that people need to take breaks no matter how many people are waiting. I tried to reason that it obviously was a management problem of staffing, at which point I was thanked for my concern and the conversation closed.

I suspect it's not entirely the manager's fault, he's probably surviving the best he can with the resources he's allowed.. The more plausible scenario is that someone who's never visited the branch has determined exactly how many people they need and budget cuts at different branches have probably been made to pay that salary. The workers who didn't make eye contact probably cared and worked their butts off at one time, but have totally given up when the same situation plays out day after day.

This sign hangs in every Post Office....

UPS Says:
"The Postal Service wouldn't last one day in the free and open market of competition."
Prove them wrong every day.

Long awaited dog sledding post

Ok, not really, but I thought the pic was funny.... I have to admit, dog sledding was one of the most fun things I've ever done... it wasn't just sitting on the sled as someone else drove, it was 3 minutes of instruction and then you were thrown in to driving! The dogs went fast, their trainer Alissa explained them as a car stuck in drive. When you released the brake they took off, and they took off fast! It wasn't just some easy trail, we were flying down hills, around tight corners, everyone somersaulted off at least 3-4 times. Luckily the snow was DEEP and it didn't hurt all that much. On the uphill they let Aysh drive by herself as the dogs were going slower and they were no scary turns, she had a blast too... This was shot with my little camera, not a video camera, so excuse the super bounciness if you can....

Godspeed Sheldon Brown 1944-2008

Cycling has lost one of the greatest minds and most passionate members we've been lucky enough to have. Rest in peace Sheldon Brown and thanks for everything you've given to us.



What a legacy Sheldon Brown leaves behind... one can only hope to get close in our own lifetime's to positively impacting all of the lives he's touched...