1 #!/bin/bash |
1 #!/bin/bash |
2 #$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $ |
2 #$Id: cmd.sh,v 728762bf2e91 2022/03/02 13:38:25 grin $ |
|
3 # |
3 # |
4 # |
4 # This program queries PostgreSQL directly for various user statistics. |
5 # This program queries PostgreSQL directly for various user statistics. |
5 # Set psql access below (CMD=...), ensure that .pgpass lets user to login |
6 # Set psql access below (CMD=...), ensure that .pgpass lets user to login |
6 # without password entry. |
7 # without password entry. |
7 # Start without arguments for help. |
8 # Start without arguments for help. |
8 # |
9 # |
9 # (c)Peter Gervai, 2020-21 |
10 # (c)Peter Gervai, 2020-22 |
10 # @grin:grin.hu |
11 # @grin:grin.hu |
11 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
12 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
12 # |
13 # |
13 |
14 |
14 #CMD='sudo -u postgres psql synapse -tAc' |
15 #CMD='sudo -u postgres psql synapse -tAc' |
15 CMD='psql -h 10.1.1.257 -U synapse synapse -c' |
16 #CMD='psql -h 1.2.3.4 -U synapse synapse -c' |
|
17 |
|
18 . ./_config.sh |
|
19 |
|
20 CMD="${PSQL_CMD}" |
|
21 CMDQ='-At' |
16 |
22 |
17 MODE="$1" |
23 MODE="$1" |
18 ARG="$2" |
24 ARG="$2" |
19 |
25 |
20 if [ "x$MODE" = "x" ]; then |
26 if [ "x$MODE" = "x" ]; then |
21 echo "Usage: $0 <mode> <args> ...." |
27 echo "Usage: $0 <mode> <args> ...." |
22 echo " modes: user @someuser:grin.hu - user stats" |
28 echo " modes: user @someuser:grin.hu - user stats" |
23 echo " userevents @someuser:grin.hu - user events" |
29 echo " userevents @someuser:grin.hu - user events" |
24 echo " active1 - last 1 day active users timeline" |
30 echo " active1 - last 1 day active users" |
25 echo " active1u - last 1 day users (unique results)" |
31 echo " active1u - last 1 day users (unique results)" |
26 echo " lastreg [<n>] - last <n> (25) registrations" |
32 echo " lastreg [<n>] - last <n> (25) registrations" |
|
33 echo " room <room_id without !> - room info" |
|
34 echo " evt <event_id w/o $> - event info" |
27 exit |
35 exit |
28 fi |
36 fi |
29 |
37 |
30 |
38 |
31 |
39 |
32 egy_arg() { |
40 egy_arg() { |
33 local s=$1 |
41 local s=$1 |
34 if [ "x$ARG" = "x" ]; then |
42 if [ "x$ARG" = "x" ]; then |
35 echo "Missing arg ${s}!" |
43 echo "MIssing arg ${s}!" |
36 echo "$0 $MODE $s" |
44 echo "$0 $MODE $s" |
37 exit |
45 exit |
38 fi |
46 fi |
39 } |
47 } |
40 |
48 |
109 WHERE unts(last_seen)> NOW()-'1 day'::interval |
117 WHERE unts(last_seen)> NOW()-'1 day'::interval |
110 ORDER BY 1" | cat |
118 ORDER BY 1" | cat |
111 exit |
119 exit |
112 fi |
120 fi |
113 |
121 |
|
122 |
|
123 if [ "$MODE" = "room" ]; then |
|
124 $CMD "SELECT rs.name, rs.canonical_alias,rs.join_rules,rs.topic FROM room_stats_state rs WHERE room_id='!${ARG}'" | cat |
|
125 $CMD "SELECT room_alias, creator FROM room_aliases WHERE room_id='!${ARG}'" | cat |
|
126 $CMD "SELECT * FROM rooms WHERE room_id='!${ARG}'" | cat |
|
127 $CMD "SELECT * FROM room_stats_current WHERE room_id='!${ARG}'" | cat |
|
128 # $CMD "SELECT COUNT(*), type FROM state_groups_state WHERE room_id='!${ARG}' GROUP BY 2 ORDER BY 1 DESC" | cat |
|
129 fi |
|
130 |
|
131 |
|
132 if [ "$MODE" = "evt" ]; then |
|
133 $CMD "SELECT event_id,type,room_id,content,processed,depth,sender,instance_name,state_key,unts(origin_server_ts) AS orig, unts(received_ts) AS rcv FROM events WHERE event_id='\$${ARG}'" | cat |
|
134 $CMD "SELECT * FROM event_json WHERE event_id='\$${ARG}'" | cat |
|
135 evt='$'"${ARG}" |
|
136 room=`$CMD "SELECT room_id FROM events WHERE event_id='${evt}'" $CMDQ` |
|
137 |
|
138 #echo "Room_id=$room!" |
|
139 $CMD "SELECT rs.name, rs.canonical_alias,rs.join_rules,rs.topic FROM room_stats_state rs WHERE room_id='$room'" | cat |
|
140 $CMD "SELECT room_alias, creator FROM room_aliases WHERE room_id='$room'" | cat |
|
141 |
|
142 fi |
|
143 |