equal
deleted
inserted
replaced
1 #!/bin/bash |
1 #!/bin/bash |
2 #$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $ |
2 #$Id: cmd.sh,v 7c8632f2b6be 2022/01/21 09:05:10 grin $ |
3 # |
3 # |
|
4 # This program queries PostgreSQL directly for various user statistics. |
|
5 # Set psql access below (CMD=...), ensure that .pgpass lets user to login |
|
6 # without password entry. |
|
7 # Start without arguments for help. |
|
8 # |
4 # (c)Peter Gervai, 2020-21 |
9 # (c)Peter Gervai, 2020-21 |
|
10 # @grin:grin.hu |
5 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
11 # Licensed under GPLv3+ and CC_By-Sa-4.0-int |
6 # |
12 # |
7 |
13 |
8 #CMD='sudo -u postgres psql synapse -tAc' |
14 #CMD='sudo -u postgres psql synapse -tAc' |
9 CMD='psql -h 10.1.1.257 -U synapse synapse -c' |
15 CMD='psql -h 10.1.1.257 -U synapse synapse -c' |
13 |
19 |
14 if [ "x$MODE" = "x" ]; then |
20 if [ "x$MODE" = "x" ]; then |
15 echo "Usage: $0 <mode> <args> ...." |
21 echo "Usage: $0 <mode> <args> ...." |
16 echo " modes: user @someuser:grin.hu - user stats" |
22 echo " modes: user @someuser:grin.hu - user stats" |
17 echo " userevents @someuser:grin.hu - user events" |
23 echo " userevents @someuser:grin.hu - user events" |
18 echo " active1 - last 1 day active users" |
24 echo " active1 - last 1 day active users timeline" |
|
25 echo " active1u - last 1 day users (unique results)" |
19 echo " lastreg [<n>] - last <n> (25) registrations" |
26 echo " lastreg [<n>] - last <n> (25) registrations" |
20 exit |
27 exit |
21 fi |
28 fi |
22 |
29 |
23 |
30 |
94 fi |
101 fi |
95 |
102 |
96 if [ "$MODE" = "active1u" ]; then |
103 if [ "$MODE" = "active1u" ]; then |
97 $CMD "SELECT DISTINCT user_id FROM user_ips |
104 $CMD "SELECT DISTINCT user_id FROM user_ips |
98 WHERE unts(last_seen)> NOW()-'1 day'::interval |
105 WHERE unts(last_seen)> NOW()-'1 day'::interval |
99 ORDER BY 1" |
106 ORDER BY 1" | cat |
100 |
107 |
101 $CMD "SELECT DISTINCT user_id,device_id,ip FROM user_ips |
108 $CMD "SELECT DISTINCT user_id,device_id,ip FROM user_ips |
102 WHERE unts(last_seen)> NOW()-'1 day'::interval |
109 WHERE unts(last_seen)> NOW()-'1 day'::interval |
103 ORDER BY 1" |
110 ORDER BY 1" | cat |
104 exit |
111 exit |
105 fi |
112 fi |
106 |
113 |