From d8cb7225d2902ae3dd7fbcd1b3b2ebd084d81ca4 Mon Sep 17 00:00:00 2001
From: Neil Johnson <neil@matrix.org>
Date: Tue, 22 May 2018 18:09:09 +0100
Subject: daily user type phone home stats

---
 synapse/app/homeserver.py       |  4 ++++
 synapse/storage/registration.py | 29 +++++++++++++++++++++++++++++
 2 files changed, 33 insertions(+)

diff --git a/synapse/app/homeserver.py b/synapse/app/homeserver.py
index caccbaa814..026422a023 100755
--- a/synapse/app/homeserver.py
+++ b/synapse/app/homeserver.py
@@ -434,6 +434,10 @@ def run(hs):
         total_nonbridged_users = yield hs.get_datastore().count_nonbridged_users()
         stats["total_nonbridged_users"] = total_nonbridged_users
 
+        daily_user_type_results = yield hs.get_datastore().count_daily_user_type()
+        for name, count in daily_user_type_results.iteritems():
+            stats["daily_user_type_" + name] = count
+
         room_count = yield hs.get_datastore().get_room_count()
         stats["total_room_count"] = room_count
 
diff --git a/synapse/storage/registration.py b/synapse/storage/registration.py
index a530e29f43..d8e60d2e87 100644
--- a/synapse/storage/registration.py
+++ b/synapse/storage/registration.py
@@ -485,6 +485,35 @@ class RegistrationStore(RegistrationWorkerStore,
         ret = yield self.runInteraction("count_users", _count_users)
         defer.returnValue(ret)
 
+    def count_daily_user_type(self):
+        """
+        Counts 1) native non guest users
+               2) native guests users
+               3) bridged users
+        who registered on the homeserver in the past 24 hours
+        """
+        def _count_daily_user_type(txn):
+            yesterday = int(self._clock.time()) - (60 * 60 * 24)
+
+            sql = """
+                SELECT user_type, COALESCE(count(*), 0) AS count FROM (
+                    SELECT
+                    CASE
+                        WHEN is_guest=0 AND appservice_id IS NULL THEN 'native'
+                        WHEN is_guest=1 AND appservice_id IS NULL THEN 'guest'
+                        WHEN is_guest=0 AND appservice_id IS NOT NULL THEN 'bridged'
+                    END AS user_type
+                    FROM users
+                    WHERE creation_ts > ?
+                ) AS t GROUP BY user_type
+            """
+            results = {'native': 0, 'guest': 0, 'bridged': 0}
+            txn.execute(sql, (yesterday,))
+            for row in txn:
+                results[row[0]] = row[1]
+            return results
+        return self.runInteraction("count_daily_user_type", _count_daily_user_type)
+
     @defer.inlineCallbacks
     def count_nonbridged_users(self):
         def _count_users(txn):
-- 
cgit 1.4.1