nginxのログから連合メッセージ受信数を調べる

grep  /_matrix/federation/v1/send /var/log/nginx/matrix.juggler.jp.access.log |  \
  perl -pne 'my @a=split " ";$_="$a[0]\n"' |sort|uniq -c |sort -nr |head -n 10

送信サーバのIPアドレス別に受信イベント数を列挙する。 多いのは毎秒2回を超えていたので、nginxでURL指定してdenyした

過去数ヶ月のメッセージ保持数をサーバ別に列挙

select sum(c) as s, server 
  from (
    SELECT COUNT(*) as c, sender, substring(sender, '[^:]+$') as server
    FROM events
    WHERE origin_server_ts >= DATE_PART('epoch', NOW() - INTERVAL '3 month') * 1000
    GROUP BY sender
  ) as t
  GROUP BY server
  ORDER BY s desc
  LIMIT 20;

   s    |         server
--------+------------------------
 123420 | matrix.org
  62284 | cutefunny.art
  31401 | matrix.juggler.jp
  28961 | smith.gdgd.jp.net
  23328 | cuteworld.space
  14661 | midov.pl
  12030 | nitro.chat
   9723 | lolisho.chat
   6782 | matrix.fedibird.com
   5284 | sibnsk.net
   5029 | sakura.ci
   4529 | matrix.u-svc.dynv6.net
   4465 | koneko.chat
   4345 | the-apothecary.club
   3614 | matrix.im
   3610 | plan9.rocks
   3249 | t2bot.io
   2722 | nope.chat
   2557 | converser.eu
   2460 | 0wnz.at
(20 rows)

• History eventsは定期的に掃除してる
• ただし matrix.org の一部ルームはDB整合性のエラーでデータ整理が行えていない。つらい。

過去数ヶ月のメッセージ保持数を送信ユーザ別に列挙

SELECT COUNT(*), sender
  FROM events
  WHERE origin_server_ts >= DATE_PART('epoch', NOW() - INTERVAL '1 month') * 1000
  GROUP BY sender
  ORDER BY COUNT(*) DESC
  LIMIT 20;