|
1 #!/bin/bash |
|
2 #$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $ |
|
3 # |
|
4 # (c)Peter Gervai, 2020-21 |
|
5 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
|
6 # |
|
7 |
|
8 #CMD='sudo -u postgres psql synapse -tAc' |
|
9 CMD='psql -h 10.1.1.257 -U synapse synapse -c' |
|
10 |
|
11 MODE="$1" |
|
12 ARG="$2" |
|
13 |
|
14 if [ "x$MODE" = "x" ]; then |
|
15 echo "Usage: $0 <mode> <args> ...." |
|
16 echo " modes: user @someuser:grin.hu - user stats" |
|
17 echo " userevents @someuser:grin.hu - user events" |
|
18 echo " active1 - last 1 day active users" |
|
19 echo " lastreg [<n>] - last <n> (25) registrations" |
|
20 exit |
|
21 fi |
|
22 |
|
23 |
|
24 |
|
25 egy_arg() { |
|
26 local s=$1 |
|
27 if [ "x$ARG" = "x" ]; then |
|
28 echo "Missing arg ${s}!" |
|
29 echo "$0 $MODE $s" |
|
30 exit |
|
31 fi |
|
32 } |
|
33 |
|
34 |
|
35 |
|
36 if [ "$MODE" = "user" ]; then |
|
37 egy_arg 'user' |
|
38 |
|
39 echo "=== User data ===" |
|
40 $CMD "SELECT display_name FROM user_directory WHERE user_id='$ARG'" |
|
41 $CMD "SELECT medium, address, unts(validated_at) AS validated_at, unts(added_at) AS added_at FROM user_threepids WHERE user_id='$ARG'" |
|
42 $CMD "SELECT joined_rooms FROM user_stats_current WHERE user_id='$ARG'" |
|
43 |
|
44 echo "=== Logins ===" |
|
45 $CMD "SELECT user_id,device_id,ip,unts(last_seen) AS last_seen,user_agent FROM user_ips WHERE user_id='$ARG'" |
|
46 $CMD "SELECT device_id, user_agent, unts(timestamp) FROM user_daily_visits WHERE user_id='$ARG' ORDER BY 1 ASC, 3 DESC LIMIT 10" |
|
47 |
|
48 echo "=== Shared private rooms ===" |
|
49 $CMD "select * from users_who_share_private_rooms where user_id='$ARG'" |
|
50 |
|
51 echo "=== Rooms the user has been ===" |
|
52 $CMD "SELECT rm.room_id, rm.event_id, rm.sender, rm.membership, |
|
53 rss.canonical_alias, rss.join_rules, rss.encryption, |
|
54 unts(e.origin_server_ts), rss.name |
|
55 FROM room_memberships rm |
|
56 JOIN room_stats_state rss USING(room_id) |
|
57 JOIN events e USING(event_id) |
|
58 WHERE user_id='$ARG' |
|
59 ORDER BY e.origin_server_ts" | more |
|
60 |
|
61 exit |
|
62 fi |
|
63 |
|
64 |
|
65 if [ "$MODE" = "userevents" ]; then |
|
66 egy_arg 'user' |
|
67 $CMD "SELECT * FROM events WHERE sender='@sztg:grin.hu'; |
|
68 SELECT stream_ordering,type,e.room_id,content,sender,unts(origin_server_ts) AS orig_s,json |
|
69 FROM events e LEFT JOIN event_json j USING(event_id) |
|
70 WHERE sender='$ARG'" |
|
71 |
|
72 exit |
|
73 fi |
|
74 |
|
75 |
|
76 if [ "$MODE" = "active1" ]; then |
|
77 $CMD "SELECT user_id,device_id,ip,unts(MAX(last_seen)) AS last_seen,user_agent FROM user_ips |
|
78 WHERE unts(last_seen)> NOW()-'1 day'::interval |
|
79 GROUP BY 1,2,3,5 |
|
80 ORDER BY last_seen DESC" |
|
81 exit |
|
82 fi |
|
83 |
|
84 if [ "$MODE" = "lastreg" ]; then |
|
85 if [ "x$ARG" = "x" ]; then |
|
86 ARG=25 |
|
87 fi |
|
88 |
|
89 $CMD "SELECT name,unts(creation_ts*1000) AS created,is_guest,deactivated,shadow_banned, |
|
90 device_id,ip,unts(last_seen) AS last_seen, user_agent |
|
91 FROM users LEFT JOIN user_ips ON(name=user_id) |
|
92 ORDER BY 2 DESC |
|
93 LIMIT $ARG" |
|
94 fi |
|
95 |
|
96 if [ "$MODE" = "active1u" ]; then |
|
97 $CMD "SELECT DISTINCT user_id FROM user_ips |
|
98 WHERE unts(last_seen)> NOW()-'1 day'::interval |
|
99 ORDER BY 1" |
|
100 |
|
101 $CMD "SELECT DISTINCT user_id,device_id,ip FROM user_ips |
|
102 WHERE unts(last_seen)> NOW()-'1 day'::interval |
|
103 ORDER BY 1" |
|
104 exit |
|
105 fi |
|
106 |