This script is very interesting tracking down specific users causing errors in your IIS environment. Could be a physical person or a tool configured to run under a domain account. One of my customers had a tool for checking site availability and it was pinging several pages constantly (SLA check). What they didn't know was that the tool was configured in the wrong way and was causing masses of 500 errors. Resolving them freed up server resources. Another benefit of doing some error cleaning is that you easily spot more severe problems or new error trends in the future if the log file is not cluttered. In my specific customers doing this operation removed about 100.000 of the 500 errors (about 1400/day). We first run this script to count the 500 error per ASPX page and Domain User SELECT cs-username, cs-uri-stem, count(*) as Times INTO 500PagesByUserAndPage.txt FROM logs\iis\ex*.log WHERE sc-status=500 GROUP BY cs-username, cs-uri-stem ORDER BY Times DESC
This above script revealed that the bulk of all 500 errors was within 5 pages and from one specific user. To get the percentage of errors originated from this user we run the below script and it was near 97% SELECT cs-username, count(*) as Times, propcount(*) as Percent INTO 500ErrorsByUser.csv FROM logs\iis\ex*.log WHERE sc-status=500 GROUP BY cs-username ORDER BY Times DESC
Customer now had plenty of data to back the decision to remove the tool or reconfigure. This is what it looked like after we removed the site pinging tool. You can also run this script to get the all the 500 sc-substatus codes. SELECT TO_STRING(To_timestamp(date, time), 'MMdd') AS Day, SUM(c0) AS 5000, SUM(c1) AS 50012, SUM(c2) AS 50013, SUM(c3) AS 50015, SUM(c4) AS 50016, SUM(c5) AS 50018, SUM(c6) AS 50019, SUM(c7) AS 500100 USING CASE sc-substatus WHEN 0 THEN 1 ELSE 0 END AS C0, CASE sc-substatus WHEN 12 THEN 1 ELSE 0 END AS c1, CASE sc-substatus WHEN 13 THEN 1 ELSE 0 END AS c2, CASE sc-substatus WHEN 15 THEN 1 ELSE 0 END AS c3, CASE sc-substatus WHEN 16 THEN 1 ELSE 0 END AS c4, CASE sc-substatus WHEN 18 THEN 1 ELSE 0 END AS c5, CASE sc-substatus WHEN 19 THEN 1 ELSE 0 END AS c6, CASE sc-substatus WHEN 100 THEN 1 ELSE 0 END AS c7 INTO 500subcodesperday.txt FROM logs\iis\ex*.log WHERE sc-status=500 GROUP BY Day ORDER BY Day
5xx - Server Error The server cannot complete the request because it encounters an error.
//Anders