CODEWITHSUNDEEP
logparseriis-logs
ted1001
ted1001

Reputation: 83

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.

Upvotes: 0

Views: 245

Answers (1)

Gabriele Giuseppini
Gabriele Giuseppini

Reputation: 1579

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
...

Upvotes: 1

Related Questions