MastodonのデータをZabbixから監視するためのRubyスクリプト

**内容が古いので注意してください**
## やりたいこと
運用している```Mastodon```インスタンスのデータを```Zabbix```から監視できるような```UserParam```を設定したかった。
グラフを生成してインスタンスの詳細な状況を見たかった。

## やったこと
```Munin```用の```Mastodon```監視プラグイン https://github.com/cquest/mastodon-munin-plugins
を元にさせて頂きました。
私は```Docker```で```Mastodon```インスタンスを管理しているのでコマンドを```Docker```向けに変更しました。
また、```mastodon```のディレクトリ直下にスクリプトを配置しています。
```sudo``` コマンドを使用していますので```visudo```で```zabbix```ユーザーにコマンドの実効権限を与えてください。


以下にRubyスクリプトを示します。
最後のStreamはPostgreSQLとは全く関係ないですがついでに入ってます。
説明が間違っていれば指摘をお願いします（PostgreSQLわかんない）

```rb:MastodonQueryZabbix01.rb
# encoding: utf-8
## This is a script to query Mastodon data 
## Thanks to https://github.com/cquest/mastodon-munin-plugins
## How to use
## Add to /etc/zabbix/zabbix_agentd.conf
## UserParameter=mastodon.postgres[*],ruby /hogedir/MastodonQueryZabbix01.rb $1
if ARGV[0] == "accounts_valid"
	# 有効なアカウント数
	s = `echo "SELECT COUNT(*) FROM users WHERE confirmed_at is not null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "accounts_waiting"
	# 確認待ちのアカウント数
	s = `echo "SELECT COUNT(*) FROM users WHERE confirmed_at is null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "accounts_remote"
	# リモートのアカウント数
	s = `echo "SELECT COUNT(*) FROM accounts WHERE domain is not null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "accounts_remote_domains"
	# リモートのインスタンス数
	s = `echo "SELECT COUNT(distinct(domain)) FROM accounts WHERE domain is not null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "in_in"
	# ローカル内でのフォロー数
	s = `echo "select count(*) from follows f join accounts a on (a.id=account_id) join accounts t on (t.id=target_account_id) where a.domain is null and t.domain is null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "in_out"
	# ローカル>リモートのフォロー数
	s = `echo "select count(*) from follows f join accounts a on (a.id=account_id) join accounts t on (t.id=target_account_id) where a.domain is null and t.domain is not null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "out_in"
	# リモート>ローカルのフォロー数
	s = `echo "select count(*) from follows f join accounts a on (a.id=account_id) join accounts t on (t.id=target_account_id) where a.domain is not null and t.domain is null;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`	
	puts s.to_i
elsif ARGV[0] == "statuses_total"
	# 保存されている総Toot数
	s = `echo "SELECT COUNT(*) FROM statuses;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "statuses_local"
	# ローカルのToot数
	s = `echo "SELECT COUNT(*) FROM statuses AS s, accounts AS a WHERE a.id=s.account_id AND a.domain IS NULL;" | sudo docker exec -i mastodon_db_1 psql postgres -tqU postgres | head -n 1`
	puts s.to_i
elsif ARGV[0] == "statuses_visibility_local_public"
	# 直近一時間でのToot種類別の分類（％） 1:パブリック
	s = `echo "select format('statuses_%s.value %s',vis, coalesce(100*nb/total::numeric,0)) from (select generate_series(0,3) as vis) as v left join (select visibility, count(*) as nb from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour' group by 1) as s on (vis=visibility), (select count(*) as total from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour') as t;" | sudo docker exec -i mastodon_db_1 psql postgres -tAU postgres | grep 0.value`
	puts s.gsub("statuses_0.value ", "")
elsif ARGV[0] == "statuses_visibility_local_nonlisted"
	# 直近一時間でのToot種類別の分類（％） 2:非収載
	s = `echo "select format('statuses_%s.value %s',vis, coalesce(100*nb/total::numeric,0)) from (select generate_series(0,3) as vis) as v left join (select visibility, count(*) as nb from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour' group by 1) as s on (vis=visibility), (select count(*) as total from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour') as t;" | sudo docker exec -i mastodon_db_1 psql postgres -tAU postgres | grep 1.value`
	puts s.gsub("statuses_1.value ", "")
elsif ARGV[0] == "statuses_visibility_local_private"
	# 直近一時間でのToot種類別の分類（％） 3:非公開
	s = `echo "select format('statuses_%s.value %s',vis, coalesce(100*nb/total::numeric,0)) from (select generate_series(0,3) as vis) as v left join (select visibility, count(*) as nb from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour' group by 1) as s on (vis=visibility), (select count(*) as total from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour') as t;" | sudo docker exec -i mastodon_db_1 psql postgres -tAU postgres | grep 2.value`
	puts s.gsub("statuses_2.value ", "").to_i
elsif ARGV[0] == "statuses_visibility_local_direct"
	# 直近一時間でのToot種類別の分類（％） 4:ダイレクト
	s = `echo "select format('statuses_%s.value %s',vis, coalesce(100*nb/total::numeric,0)) from (select generate_series(0,3) as vis) as v left join (select visibility, count(*) as nb from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour' group by 1) as s on (vis=visibility), (select count(*) as total from statuses s join accounts a on (a.id=s.account_id and a.domain is null) where s.created_at > now() - INTERVAL '1 hour') as t;" | sudo docker exec -i mastodon_db_1 psql postgres -tAU postgres | grep 3.value`
	puts s.gsub("statuses_3.value ", "").to_i	
elsif ARGV[0] == "streams"
	# 確立されているStreamの総数
	s = `netstat -t | grep ':4000 .* ESTABLISHED' | wc -l`
	puts s.to_i
end
```

Zabbixにアイテムやグラフを登録する方法は別記事がたくさんあると思いますのでここでは省きます。


## おわりに
次はsidekiqの監視もやる予定