Using the DATAGRID output option in Microsoft LogParser to see error 500 IIS issues


I love Logparser but sometimes I don’t want large amounts of data in a command prompt. And you may not want to drop it into a csv file either, you just want a nice readable output in front of your face immediately. So I like to use the DATAGRID output option which gives me a nice neat GUI output for these situations. Here is an example I used this morning to pull all the IIS error 500 messages in an IIS W3C formated log file:

logparser "SELECT date, time, cs-method, s-ip, s-port, cs-username, c-ip, cs-host, time-taken, cs-uri-stem, cs-uri-query FROM C:\Logs\ex100917.log WHERE sc-status = 500" -rtp:-1 -i:iisw3c -o:datagrid

and here’s one to see the average time taken on every page on your site. This is great to pinpoint those pages with high load times that you can possible optimize. In this example, I want to include every page in my stats except those that threw an error 500:

logparser -i:IISW3C "SELECT cs-uri-stem, AVG(time-taken) As AvgTimeTaken FROM C:\Logs\ex100917.log WHERE sc-status <> 500 GROUP BY cs-uri-stem ORDER BY AvgTimeTaken DESC" -q:ON -o:datagrid

Now we know how pages normally perform. Now let’s compare that to the pages that did have error 500s. I also want to add a column for the total number of error 500s on each page. So a slight modification in the query:

logparser -i:IISW3C "SELECT cs-uri-stem, Count(*) AS Error500Hits, AVG(time-taken) As AvgTimeTaken FROM C:\Logs\ex100917.log WHERE sc-status = 500 GROUP BY cs-uri-stem ORDER BY AvgTimeTaken DESC" -q:ON -o:datagrid

All the data that is output using datagrid exports to an Excel spreadsheet nicely. Just right click, Select All and then copy and paste into your .xlsx spreadsheet.

About Jason Samuel

Jason Samuel lives in Houston, TX with a primary focus on strategic advisory and architecture of end-user computing, security, enterprise mobility, virtualization, and cloud technologies from Citrix, Microsoft, & VMware. He also has an extensive background in web architecture and networking over his 20+ year career in IT. He is an Author, Speaker, and Local User Group Community Leader. He is certified in several technologies and is 1 of 63 people globally that is a recipient of the prestigious Citrix Technology Professional (CTP) award. He is 1 of 42 people in the world that has been awarded as a VMware EUC Champion and VMware vExpert. He is a featured author on DABCC which provides the latest IT Community News on Cloud, Data Center, Desktop, Mobility, Security, Storage, & Virtualization. In his spare time Jason enjoys writing how-to articles and evangelizing the technologies he works with. Disclaimer: The content and opinions expressed in articles and posts are his own and are by no means associated with his employer.

Recommended for you

Leave a Reply

Your email address will not be published. Required fields are marked *