chart stacked grouped by ip address

1 files changed, 17 insertions, 1 deletions

diff --git a/src/db_handler.py b/src/db_handler.py
index d607aee..033b11a 100644
--- a/src/db_handler.py
+++ b/src/db_handler.py
@@ -56,7 +56,23 @@ LIMIT 1;
             return password
 
 
-def get_histogram() -> str:
+def get_histogram_detailed() -> str:
+    with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
+        with conn.cursor() as cur:
+            cur.execute("""
+SELECT count(la.id) as total_count, date_trunc('hour', la.timestamp) as date, cn.ip
+FROM login_attempt la
+JOIN connection cn on cn.id = la.connection
+GROUP BY date_trunc('hour', la.timestamp), cn.ip
+ORDER BY date_trunc('hour', la.timestamp) DESC
+LIMIT 48;
+;
+                """)
+            histogram = cur.fetchall()
+            return histogram
+
+
+def get_histogram_simple() -> str:
     with psycopg.connect(**db_con_params, row_factory=psycopg.rows.dict_row) as conn:
         with conn.cursor() as cur:
             cur.execute("""