# HG changeset patch
# User Peter Gervai <grin@grin.hu>
# Date 1642756219 -3600
# Node ID c3f49b58aff6f41110a1f374015bd1c8783168dc
# Parent  ba8922061637bc4d8f8aecf700df4d74e14cef04
Add synapse/cmd.sh: query db for various user statistics
- last registrations
- user events
- user event contents
- active daily users

diff -r ba8922061637 -r c3f49b58aff6 synapse/cmd.sh
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/synapse/cmd.sh	Fri Jan 21 10:10:19 2022 +0100
@@ -0,0 +1,106 @@
+#!/bin/bash
+#$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $
+#
+# (c)Peter Gervai, 2020-21
+# Licensed under GPLv3+ and CC_By-Sa-4.0-int
+#
+
+#CMD='sudo -u postgres psql synapse -tAc'
+CMD='psql -h 10.1.1.257 -U synapse synapse -c'
+
+MODE="$1"
+ARG="$2"
+
+if [ "x$MODE" = "x" ]; then
+	echo "Usage: $0 <mode> <args> ...."
+	echo " modes: user @someuser:grin.hu       - user stats"
+	echo "        userevents @someuser:grin.hu - user events"
+	echo "        active1                      - last 1 day active users"
+	echo "        lastreg [<n>]	           - last <n> (25) registrations"
+	exit
+fi
+
+
+
+egy_arg() {
+	local s=$1
+	if [ "x$ARG" = "x" ]; then
+		echo "Missing arg ${s}!"
+		echo "$0 $MODE $s"
+		exit
+	fi
+}
+
+
+
+if [ "$MODE" = "user" ]; then
+	egy_arg 'user'
+
+	echo "=== User data ==="
+	$CMD "SELECT display_name FROM user_directory WHERE user_id='$ARG'"
+	$CMD "SELECT medium, address, unts(validated_at) AS validated_at, unts(added_at) AS added_at FROM user_threepids WHERE user_id='$ARG'"
+	$CMD "SELECT joined_rooms FROM user_stats_current WHERE user_id='$ARG'"
+	
+ 	echo "=== Logins ==="
+ 	$CMD "SELECT user_id,device_id,ip,unts(last_seen) AS last_seen,user_agent FROM user_ips WHERE user_id='$ARG'"
+ 	$CMD "SELECT device_id, user_agent, unts(timestamp) FROM user_daily_visits WHERE user_id='$ARG' ORDER BY 1 ASC, 3 DESC LIMIT 10"
+ 	
+ 	echo "=== Shared private rooms ==="
+ 	$CMD "select * from users_who_share_private_rooms where user_id='$ARG'"
+ 	
+ 	echo "=== Rooms the user has been ==="
+ 	$CMD "SELECT rm.room_id, rm.event_id, rm.sender, rm.membership, 
+       rss.canonical_alias, rss.join_rules, rss.encryption, 
+       unts(e.origin_server_ts), rss.name
+ FROM room_memberships  rm
+ JOIN room_stats_state rss USING(room_id) 
+ JOIN events e USING(event_id)
+ WHERE user_id='$ARG'
+ ORDER BY e.origin_server_ts" | more
+	
+ 	exit
+fi
+
+
+if [ "$MODE" = "userevents" ]; then
+        egy_arg 'user'
+        $CMD "SELECT * FROM events WHERE sender='@sztg:grin.hu';
+SELECT stream_ordering,type,e.room_id,content,sender,unts(origin_server_ts) AS orig_s,json 
+ FROM events e LEFT JOIN event_json j USING(event_id) 
+ WHERE sender='$ARG'"
+
+	exit
+fi
+
+
+if [ "$MODE" = "active1" ]; then
+	$CMD "SELECT user_id,device_id,ip,unts(MAX(last_seen)) AS last_seen,user_agent FROM user_ips 
+		 WHERE unts(last_seen)> NOW()-'1 day'::interval 
+		 GROUP BY 1,2,3,5
+		 ORDER BY last_seen DESC"
+ 	exit
+fi
+
+if [ "$MODE" = "lastreg" ]; then
+	if [ "x$ARG" = "x" ]; then
+		ARG=25
+	fi
+	
+	$CMD "SELECT name,unts(creation_ts*1000) AS created,is_guest,deactivated,shadow_banned,
+		device_id,ip,unts(last_seen) AS last_seen, user_agent
+		FROM users LEFT JOIN user_ips ON(name=user_id)
+		ORDER BY 2 DESC 
+		LIMIT $ARG"
+fi
+
+if [ "$MODE" = "active1u" ]; then
+	$CMD "SELECT DISTINCT user_id FROM user_ips 
+		 WHERE unts(last_seen)> NOW()-'1 day'::interval 
+		 ORDER BY 1"
+
+	$CMD "SELECT DISTINCT user_id,device_id,ip FROM user_ips 
+		 WHERE unts(last_seen)> NOW()-'1 day'::interval 
+		 ORDER BY 1"
+ 	exit
+fi
+