Automating Log Analysis With LogParser, Log Parser Lizard And SendEmail
It has to be the most annoying and difficult task any admin (or otherwise) needs to perform. The task of viewing and gleaning information from log files. I’ll look at the two more common types of log files that Windows users often have to look into: IIS and Event Viewer logs. How to get the data you need out of them, and how to analyze the information in them with tools that are freely available. Once you have the data, how to get it out (via email). You may choose to use commercial utilities such as Sawmill – but you might be surprised at how much can be done for free!
logparser “select c-ip, count(*) as visits into output.txt from *.log group by c-ip having count(*) >= 50 order by visits” -i:iisw3c -rtp:-1
This will place the IP address and count (or hits) into a file named output.txt – this command uses the as “string” to name a column and sort it. You’ll get something like the image at left (using data that was provided here)
logparser file:cmd.sql -i:iisw3c -rtp:-1
SELECT STRCAT( cs-uri-stem, REPLACE_IF_NOT_NULL(cs-uri-query,
STRCAT(‘?’,cs-uri-query))) AS Request,
STRCAT( ‘.’,COALESCE(TO_STRING(sc-substatus), ‘?’ ))) AS Status,
COUNT(*) AS Total FROM #IISW3C#
WHERE (sc-status >= 400)
GROUP BY Request, Status
ORDER BY Total DESC
I create my output with Log Parser using this command:
LogParser -i:EVT “SELECT * into c:dataoutput.txt FROM System WHERE EventType=1 AND TimeWritten >= TO_LOCALTIME(SUB( SYSTEM_TIMESTAMP(), TIMESTAMP(‘6’, ‘d’) ) )”
To really make this useful, you will want to send output.txt by email. You can do this with the small free utility named sendEmail. II’m going to, in this example, send this email out once a week. If you manage servers this is one great, free, way – to keep track of errors that occur. If you manage multiple servers, try naming the output file the same as your server name.
I’ll give you an example of how to send the file C:DATAOUTPUT.TXT by email to addresses. You can add more email addresses by using commas:
sendEmail.exe -f email@example.com -t “firstname.lastname@example.org, email@example.com” -u “Log Report for Computer: XXXXX” -m “Please see attached file.” -a c:dataoutput.txt -s smtp.server.com -v
In the above example much of it is self-explanatory, however, be sure to know and test your SMTP server beforehand. I have not tested some of the advanced SMTP authentication features, but you do have the ability to pass that information. For help on the sendEmail command type sendemail /? from a command prompt that can find the application.
That’s the process – with all the tools mentioned above you have the ability to gather log information from places such as IIS and the Event Viewer and then take that output and send it to a configured email address. You can even automate this process using simple batch files and the Windows Task Scheduler. The power of being able to send this information externally can save you quite a bit of time when you need to understand a problem on a computer but don’t have immediate physical or remote access.
As always, your thoughts, suggestions and interesting anecdotes are always welcome.