From 7249323ac965457fc85b805f982d6f2b5cb3a5fa Mon Sep 17 00:00:00 2001 From: Someone Date: Mon, 13 Nov 2023 13:09:02 +0100 Subject: [PATCH] [stats] user_activity --- stats/user_activity.py | 78 ++++++++++++++++++++++++++++++++++++++++++ 1 file changed, 78 insertions(+) create mode 100644 stats/user_activity.py diff --git a/stats/user_activity.py b/stats/user_activity.py new file mode 100644 index 0000000..08b0a01 --- /dev/null +++ b/stats/user_activity.py @@ -0,0 +1,78 @@ +#!/usr/bin/env python3 +# Someone's Mattermost scripts. +# Copyright (c) 2016-2022 by Someone (aka. Jan Vales ) +# published under MIT-License +# +# Users online +# + +import psycopg2 +import psycopg2.extras + +import config + + +def main(dbconn): + msg = "#user_activity #mmstats distinct user activity.\n\n|users ...|day|week|month|since "+config.cutoff_date+"|\n|---|---:|---:|---:|---:|\n" + + # online + cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) + cur.execute(""" + SELECT sum(CASE WHEN lastactivity > extract(epoch FROM (NOW() - INTERVAL '1 day'))*1000 THEN 1 ELSE 0 END) AS cnt_day, + sum(CASE WHEN lastactivity > extract(epoch FROM (NOW() - INTERVAL '1 week'))*1000 THEN 1 ELSE 0 END) AS cnt_week, + sum(CASE WHEN lastactivity > extract(epoch FROM (NOW() - INTERVAL '1 month'))*1000 THEN 1 ELSE 0 END) AS cnt_month, + sum(CASE WHEN lastactivity > extract(epoch FROM TIMESTAMP '"""+config.cutoff_date+"""')*1000 THEN 1 ELSE 0 END) AS cnt_cutoff + FROM (SELECT users.id, GREATEST(status.lastactivityat, MAX(sessions.lastactivityat), users.updateat) as lastactivity + FROM users LEFT JOIN status ON (users.id = status.userid) LEFT JOIN sessions ON (users.id = sessions.userid) + GROUP BY users.id, status.lastactivityat, users.updateat) AS a; + """) + record = cur.fetchall()[0] + msg += "|online|"+str(record["cnt_day"])+"|"+str(record["cnt_week"])+"|"+str(record["cnt_month"])+"|"+str(record["cnt_cutoff"])+"|\n" + + # posts + cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) + cur.execute(""" + SELECT sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 day'))*1000 THEN 1 ELSE 0 END) AS cnt_day, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 week'))*1000 THEN 1 ELSE 0 END) AS cnt_week, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 month'))*1000 THEN 1 ELSE 0 END) AS cnt_month, + sum(CASE WHEN createat > extract(epoch FROM TIMESTAMP '"""+config.cutoff_date+"""')*1000 THEN 1 ELSE 0 END) AS cnt_cutoff + FROM (select userid, max(createat) as createat FROM posts GROUP BY userid ORDER BY createat DESC) AS a limit 10 + """) + record = cur.fetchall()[0] + msg += "|posted|"+str(record["cnt_day"])+"|"+str(record["cnt_week"])+"|"+str(record["cnt_month"])+"|"+str(record["cnt_cutoff"])+"|\n" + + # pubchan posts + cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) + cur.execute(""" + SELECT sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 day'))*1000 THEN 1 ELSE 0 END) AS cnt_day, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 week'))*1000 THEN 1 ELSE 0 END) AS cnt_week, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 month'))*1000 THEN 1 ELSE 0 END) AS cnt_month, + sum(CASE WHEN createat > extract(epoch FROM TIMESTAMP '"""+config.cutoff_date+"""')*1000 THEN 1 ELSE 0 END) AS cnt_cutoff + FROM (select userid, max(posts.createat) as createat FROM posts JOIN channels ON (posts.channelid = channels.id) WHERE channels.type='O' GROUP BY userid ORDER BY createat DESC) AS a + """) + record = cur.fetchall()[0] + msg += "|posted in pubchan|"+str(record["cnt_day"])+"|"+str(record["cnt_week"])+"|"+str(record["cnt_month"])+"|"+str(record["cnt_cutoff"])+"|\n" + + # privchan posts + cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) + cur.execute(""" + SELECT sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 day'))*1000 THEN 1 ELSE 0 END) AS cnt_day, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 week'))*1000 THEN 1 ELSE 0 END) AS cnt_week, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 month'))*1000 THEN 1 ELSE 0 END) AS cnt_month, + sum(CASE WHEN createat > extract(epoch FROM TIMESTAMP '"""+config.cutoff_date+"""')*1000 THEN 1 ELSE 0 END) AS cnt_cutoff + FROM (select userid, max(posts.createat) as createat FROM posts JOIN channels ON (posts.channelid = channels.id) WHERE channels.type='P' GROUP BY userid ORDER BY createat DESC) AS a + """) + record = cur.fetchall()[0] + msg += "|posted in privchan|"+str(record["cnt_day"])+"|"+str(record["cnt_week"])+"|"+str(record["cnt_month"])+"|"+str(record["cnt_cutoff"])+"|\n" + + # privchan posts + cur = dbconn.cursor(cursor_factory=psycopg2.extras.DictCursor) + cur.execute(""" + SELECT sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 day'))*1000 THEN 1 ELSE 0 END) AS cnt_day, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 week'))*1000 THEN 1 ELSE 0 END) AS cnt_week, + sum(CASE WHEN createat > extract(epoch FROM (NOW() - INTERVAL '1 month'))*1000 THEN 1 ELSE 0 END) AS cnt_month, + sum(CASE WHEN createat > extract(epoch FROM TIMESTAMP '"""+config.cutoff_date+"""')*1000 THEN 1 ELSE 0 END) AS cnt_cutoff + FROM (select userid, max(posts.createat) as createat FROM posts JOIN channels ON (posts.channelid = channels.id) WHERE channels.type NOT IN ('O', 'P') GROUP BY userid ORDER BY createat DESC) AS a + """) + record = cur.fetchall()[0] + return msg + "|posted private|"+str(record["cnt_day"])+"|"+str(record["cnt_week"])+"|"+str(record["cnt_month"])+"|"+str(record["cnt_cutoff"])+"|\n" -- 2.43.0