For IRC, we use superseriousstats with a daily cron job. The database stores aggregated data. Its schema is a little funky, but effective enough. As before lastmonth is a parameter containing a date one month ago.
Contents |
select sum(`l_00`) as `l_00`, sum(`l_01`) as `l_01`, sum(`l_02`) as `l_02`,
sum(`l_03`) as `l_03`, sum(`l_04`) as `l_04`, sum(`l_05`) as `l_05`,
sum(`l_06`) as `l_06`, sum(`l_07`) as `l_07`, sum(`l_08`) as `l_08`,
sum(`l_09`) as `l_09`, sum(`l_10`) as `l_10`, sum(`l_11`) as `l_11`,
sum(`l_12`) as `l_12`, sum(`l_13`) as `l_13`, sum(`l_14`) as `l_14`,
sum(`l_15`) as `l_15`, sum(`l_16`) as `l_16`, sum(`l_17`) as `l_17`,
sum(`l_18`) as `l_18`, sum(`l_19`) as `l_19`, sum(`l_20`) as `l_20`,
sum(`l_21`) as `l_21`, sum(`l_22`) as `l_22`, sum(`l_23`) as `l_23`
from `channel`
select `q_lines`.`ruid`,
`csnick` as nick,
`l_total` as total,
`l_night` as night,
`l_morning` as morning, `l_afternoon` as afternoon,
`l_evening` as evening,
`quote` from `q_lines`
join `user_details`
on `q_lines`.`ruid` = `user_details`.`uid`
join `user_status`
on `q_lines`.`ruid` = `user_status`.`uid`
where `status` != 3
order by `l_total` desc,
`q_lines`.`ruid` asc
limit 20
select 'Time of day (UTC)' as timeofday,
sum(`l_00`) as `l_00`, sum(`l_01`) as `l_01`, sum(`l_02`) as `l_02`,
sum(`l_03`) as `l_03`, sum(`l_04`) as `l_04`, sum(`l_05`) as `l_05`,
sum(`l_06`) as `l_06`, sum(`l_07`) as `l_07`, sum(`l_08`) as `l_08`,
sum(`l_09`) as `l_09`, sum(`l_10`) as `l_10`, sum(`l_11`) as `l_11`,
sum(`l_12`) as `l_12`, sum(`l_13`) as `l_13`, sum(`l_14`) as `l_14`,
sum(`l_15`) as `l_15`, sum(`l_16`) as `l_16`, sum(`l_17`) as `l_17`,
sum(`l_18`) as `l_18`, sum(`l_19`) as `l_19`, sum(`l_20`) as `l_20`,
sum(`l_21`) as `l_21`, sum(`l_22`) as `l_22`, sum(`l_23`) as `l_23`
from `channel`
where month(`date`) = month(DATE_SUB(CURDATE(),INTERVAL 1 MONTH)) and
year(`date`) = year(DATE_SUB(CURDATE(),INTERVAL 1 MONTH))
;
select date_format(`date`, '%Y-%m') as `date`,
date_format(date, '%b %y') as monthname,
month(date) as month,
year(date) as year,
sum(`l_total`) as total,
sum(`l_night`) as night,
sum(`l_morning`) as morning,
sum(`l_afternoon`) as afternoon,
sum(`l_evening`) as evening
from `channel`
where year(date) <= year(${last_month}) and
month(date) <= month(${last_month})
group by year(`date`), month(`date`);
select `q_lines`.`ruid`,
`csnick` as nick,
sum(`q_activity_by_month`.`l_total`) as total,
sum(`q_activity_by_month`.`l_night`) as night,
sum(`q_activity_by_month`.`l_morning`) as morning,
sum(`q_activity_by_month`.`l_afternoon`) as afternoon,
sum(`q_activity_by_month`.`l_evening`) as evening,
`quote` from `q_lines`
join `q_activity_by_month`
on `q_lines`.`ruid` = `q_activity_by_month`.`ruid`
join `user_status`
on `q_lines`.`ruid` = `user_status`.`uid`
join `user_details`
on `q_lines`.`ruid` = `user_details`.`uid`
where `status` != 3 and
`date` = date_format(${last_month}, '%Y-%m')
group by `q_lines`.`ruid`
order by `q_activity_by_month`.`l_total` desc,
`q_lines`.`ruid` asc
limit 20
select `total` as `v1`,
`word` as `v2`
from `words`
where length(`word`) > 4
order by `v1` desc,
`v2` asc
limit 20
select `date`,
`l_total` as total,
`l_night` as night,
`l_morning` as morning,
`l_afternoon` as afternoon,
`l_evening` as evening
from `channel`
where
year(date) = year(${last_month}) and
month(date) = month(${last_month});
The schema reflects the fact that there is already aggregation done by SSS when it reads logs. The database schema is documented in the SSS source code.