From b70a9b7ed2f5ebea9d35d9bdf4a666b8cfdce29b Mon Sep 17 00:00:00 2001 From: user Date: Sat, 23 Mar 2024 22:24:06 +0100 Subject: chart stacked grouped by ip address --- src/db_handler.py | 18 +++++++++++++++++- 1 file changed, 17 insertions(+), 1 deletion(-) (limited to 'src/db_handler.py') 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(""" -- cgit v1.2.3