Apache Log Database

Ovid on 2008-09-03T09:59:38

My Apache log database is working out fine. I was quite pleased with my query to generate a report of requests per day of week, including a summary line, but restricting it to those which produced internal server errors.

SELECT   HOUR(request_date) AS Hour,
         SUM(IF(DAYNAME(request_date) = 'Monday',    1, 0)) AS 'Monday',
         SUM(IF(DAYNAME(request_date) = 'Tuesday',   1, 0)) AS 'Tuesday',
         SUM(IF(DAYNAME(request_date) = 'Wednesday', 1, 0)) AS 'Wednesday',
         SUM(IF(DAYNAME(request_date) = 'Thursday',  1, 0)) AS 'Thursday',
         SUM(IF(DAYNAME(request_date) = 'Friday',    1, 0)) AS 'Friday',
         SUM(IF(DAYNAME(request_date) = 'Saturday',  1, 0)) AS 'Saturday',
         SUM(IF(DAYNAME(request_date) = 'Sunday',    1, 0)) AS 'Sunday',
         FORMAT(count(*)/7,0) AS Average,
         COUNT(*)             AS Total
FROM     request r, status s
WHERE    r.status_id = s.id
  AND    s.code = 500
GROUP BY Hour
UNION
SELECT 'Total',
       SUM(IF(DAYNAME(request_date) = 'Monday',    1, 0)),
       SUM(IF(DAYNAME(request_date) = 'Tuesday',   1, 0)),
       SUM(IF(DAYNAME(request_date) = 'Wednesday', 1, 0)),
       SUM(IF(DAYNAME(request_date) = 'Thursday',  1, 0)),
       SUM(IF(DAYNAME(request_date) = 'Friday',    1, 0)),
       SUM(IF(DAYNAME(request_date) = 'Saturday',  1, 0)),
       SUM(IF(DAYNAME(request_date) = 'Sunday',    1, 0)),
       FORMAT(COUNT(*)/7,0) AS Average,
       COUNT(*)             AS Total
FROM   request r, status s
WHERE  r.status_id = s.id
  AND  s.code = 500

Yeah, I know, stuff out there already does this for me, and better, but it's fun to write. Are we still allowed to have fun at our jobs? I can't tell any more.

If you're curious about the results of the above query (empty columns trimmed for legibility):

+-------+.+-----------+.+--------+.+---------+-------+
| Hour  |.| Wednesday |.| Friday |.| Average | Total |
+-------+.+-----------+.+--------+.+---------+-------+
| 0     |.|         2 |.|      0 |.| 0       |     2 |
| 14    |.|         0 |.|      3 |.| 0       |     3 |
| Total |.|         2 |.|      3 |.| 1       |     5 |
+-------+.+-----------+.+--------+.+---------+-------+

In other words, five internal server errors. That's on an integration server after handling 441,000 requests.

Fnu!

Aristotle on 2008-09-04T18:40:54

I was quite pleased with me query […] Am we still allowed to have fun at our jobs?

I don’t know, but apparently you did hit the bottle at some point in there. :-)

Re:Fnu!

Ovid on 2008-09-05T09:45:44

Ah, sheesh. Unbottled, thanks :)