Moodleへのアクセス件数を曜日・時間帯毎に集計する

概要

Moodleの利用状況を調べるときに曜日や時間帯によってどの様なアクセス数の違いがあるか確認したいときに使うSQL。Logging 2以降の対応なのでたぶんMoodle 2.6以降でstandard_logを使っている場合に利用可能。
WHEREで適当に期間を区切らないと恐ろしいほど集計に時間が掛かるので、可視化ツール使えばいいのにと思う人は可視化ツール使ってください…

集計方法

アクセス数の合計を単純に集計

「時間帯、曜日、アクセス数」の列で取得。

  SELECT DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%k') AS hour,
         DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%w') AS dow,
         COUNT(*) AS accesscount
    FROM logstore_standard_log AS l
GROUP BY hour, dow

アクセス元に分けて集計

社内(学内)ネットワークとそれ以外に分けて集計する場合。あとMoodleシステムからのアクセス（IPがnull）も考慮。「時間帯、曜日、イントラアクセス数、インターネットアクセス数、システムアクセス数、総アクセス数」の列で取得。
ネットワークの判別の部分はこれを使っている。イントラのIPはダミーなので深く考えてはいけない。

  SELECT DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%k') AS hour,
         DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%w') AS dow,
         COUNT(
           CASE WHEN 
             (INET_ATON(l.ip) ^ INET_ATON(SUBSTRING_INDEX('192.168.1.0/24', '/', 1))) >> (32 - SUBSTRING_INDEX('192.168.1.0/24', '/', -1)) = 0
           THEN 1 ELSE null END
         ) AS intranet,
         COUNT(
           CASE WHEN 
             (INET_ATON(l.ip) ^ INET_ATON(SUBSTRING_INDEX('192.168.1.0/24', '/', 1))) >> (32 - SUBSTRING_INDEX('192.168.1.0/24', '/', -1)) != 0
           THEN 1 ELSE null END
         ) AS internet,
         COUNT(
           CASE WHEN 
             ISNULL(l.ip)
           THEN 1 ELSE null END
         ) AS system,
         COUNT(*) AS allaccess
    FROM logstore_standard_log AS l
GROUP BY hour, dow

アクセス元に分けて集計 その２

アクセス元のルールを後で追加しやすいように変更。「時間帯、曜日、ネットワーク種別、アクセス数」の列で取得。

  SELECT DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%k') AS hour,
         DATE_FORMAT(FROM_UNIXTIME(l.timecreated), '%w') AS dow,
         CASE WHEN ISNULL(l.ip) THEN 'system'
         ELSE
           CASE WHEN (INET_ATON(l.ip) ^ INET_ATON(SUBSTRING_INDEX('192.168.1.0/24', '/', 1))) >> (32 - SUBSTRING_INDEX('192.168.1.0/24', '/', -1)) = 0
             THEN 'intranet1'
           WHEN (INET_ATON(l.ip) ^ INET_ATON(SUBSTRING_INDEX('192.168.2.0/24', '/', 1))) >> (32 - SUBSTRING_INDEX('192.168.2.0/24', '/', -1)) = 0
             THEN 'intranet2'
           ELSE 'internet' END
         END AS network,
         COUNT(*) AS accesscount
    FROM logstore_standard_log AS l
GROUP BY hour, dow, network

メモ

userテーブルを結合させてユニークユーザ数のカウントとかコース別の集計とかもできるので、そのあたりも組み合わせると使えるデータになりそう。