Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

Ovid (2709)

Ovid
  (email not shown publicly)
http://publius-ovidius.livejournal.com/
AOL IM: ovidperl (Add Buddy, Send Message)

Stuff with the Perl Foundation. A couple of patches in the Perl core. A few CPAN modules. That about sums it up.

Journal of Ovid (2709)

Wednesday September 03, 2008
04:59 AM

Apache Log Database

[ #37335 ]

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • 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. :-)