Search code examples
logparseriis-logs

Hit count per sc-status 500 and 200 grouped by cs-uri-stem in same query?

I would like to use Logparser to get this output from an IIS log file:

cs-uri-stem, # of cs-status=200, # of cs-status=500
/myapp/start,453,12
/myapp/send,67,8
/myapp/save,112,10

The above output tells me that "send" request got 67 hits that went OK (200) and 8 failed (500).

I have trouble understanding how I can manage to get this output with just one query.

I can easily produce this output by simply grouping by both cs-uri-stem and cs-status. But that will put the result into two separate rows:

cs-uri-stem, cs-status, count
/myapp/send,200,67
/myapp/send,500,8

I would like to have the result on the same row. Is that even possible with LogParser?

Any help would be appreciated.

Solution

  • It's quite simple, use the CASE statement to produce two synthetic fields, one that is 1 when cs-status is 200 and 0 otherwise, and another field that is 1 when cs-status is 500 and 0 otherwise, and then SUM them:

    SELECT 
   SUM(Status200), SUM(Status500)
USING 
   CASE cs-status WHEN 200 THEN 1 ELSE 0 END AS Status200,   
   CASE cs-status WHEN 500 THEN 1 ELSE 0 END AS Status500
...