CODEWITHSUNDEEP
logparser
enigment
enigment

Reputation: 3646

Log Parser Studio: Counts of 500 errors and non-errors by month

Parsing w3c logs, I want to get the count of 500 errors and not for each month. This gives the count of 500s per month:

SELECT TO_STRING(date, 'yyyy-MM') AS yearMonth, COUNT(*) AS HowMany
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%pageIcareabout%'
AND sc-status = 500
GROUP BY yearMonth

Changing sc-status = 500 to sc-status <> 500 gives the count of not-500s. However, I don't know how to show both 500s and not-500s, as separate columns for each month.

Upvotes: 0

Views: 554

Answers (2)

Gabriele Giuseppini
Gabriele Giuseppini

Reputation: 1579

it looks like you want to use the CASE statement:

SELECT TO_STRING(date, 'yyyy-MM') AS yearMonth, MyStatus, COUNT(*) AS HowMany
USING CASE sc-status WHEN 500 THEN '500' ELSE 'Not500' END AS MyStatus
FROM ...
WHERE cs-uri-stem LIKE '%pageIcareabout%'
GROUP BY yearMonth, MyStatus

Upvotes: 0

enigment
enigment

Reputation: 3646

I ended up with this:

SELECT 
      TO_STRING(date, 'yyyy-MM') AS yearMonth 
    , SUM(500) AS 500s
    , SUM(Not500) AS Not500s
USING 
      CASE sc-status WHEN 500 THEN 1 ELSE 0 END AS 500
    , CASE sc-status WHEN 500 THEN 0 ELSE 1 END AS Not500
FROM '[LOGFILEPATH]'
WHERE cs-uri-stem LIKE '%pageIcareabout%'
GROUP BY yearMonth

Result is just what I wanted -- 3 columns, yearMonth, 500s, and Not500s, the last 2 being the count of their respective values for the month.

Upvotes: 0

Related Questions