From b70a9b7ed2f5ebea9d35d9bdf4a666b8cfdce29b Mon Sep 17 00:00:00 2001
From: user <user@node5.net>
Date: Sat, 23 Mar 2024 22:24:06 +0100
Subject: chart stacked grouped by ip address

---
 src/db_handler.py        | 18 +++++++++++++++++-
 src/static/histogram.js  | 35 +++++++++++++++++++++++++----------
 src/templates/chart.html | 32 ++++++++++++++++++++++++++++++++
 src/templates/index.html | 11 +++--------
 4 files changed, 77 insertions(+), 19 deletions(-)
 create mode 100644 src/templates/chart.html

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("""
diff --git a/src/static/histogram.js b/src/static/histogram.js
index ce84bea..c65efd2 100644
--- a/src/static/histogram.js
+++ b/src/static/histogram.js
@@ -3,15 +3,30 @@ var ctx = document.getElementById("histogram");
 var myChart = new Chart(ctx, {
   type: "line",
   options: {
-    aspectRatio: 5,
+    aspectRatio: 4,
     },
-  data: {
-    labels: labels,
-    datasets: [
-      {
-        label: "SSH Login attempts",
-        data: data,
-      },
-    ],
-  },
+            data: {
+                labels: labels,
+                datasets: datasets
+            },
+            options: {
+                scales: {
+                    y: {
+                        stacked: true,
+                        beginAtZero: true,
+                        title: {
+                            display: true,
+                            text: 'Page Views'
+                        }
+                    },
+                },
+                layout: {
+                    padding: {
+                        left: 20,
+                        right: 20,
+                        top: 20,
+                        bottom: 20
+                    }
+                }
+            }
 });
diff --git a/src/templates/chart.html b/src/templates/chart.html
new file mode 100644
index 0000000..4536784
--- /dev/null
+++ b/src/templates/chart.html
@@ -0,0 +1,32 @@
+<canvas id="histogram"></canvas>
+
+{{ histogram_data|safe }}
+
+{{ histogram_labels|safe }}
+
+<script defer>
+    var datasets = {{ histogram_data|safe }}
+
+    /*
+     [
+                {
+                    label: 'Page Views',
+                    data: [5000, 7500, 8000, 6000, 9000],
+                    backgroundColor: 'hsl(0, 100%, 50%)',
+                    borderColor: 'rgba(75, 192, 192, 1)',
+                    borderWidth: 2,
+                    fill: 'start'
+                },
+                {
+                    label: 'Page Views2',
+                    data: [0, 0, 1100, 400, 1000],
+                    backgroundColor: 'rgba(75, 192, 192, 0.5)',
+                    borderColor: 'rgba(75, 192, 192, 1)',
+                    borderWidth: 2,
+                    fill: 'start'
+                },
+
+       ]
+      */
+    var labels = {{ histogram_labels|safe }};
+</script>
diff --git a/src/templates/index.html b/src/templates/index.html
index e5f602b..8b2d0c0 100644
--- a/src/templates/index.html
+++ b/src/templates/index.html
@@ -4,6 +4,7 @@
     <meta charset="utf-8">
     <meta name="viewport" content="width=device-width, initial-scale=1">
     <title>SSH login attempts</title>
+    <meta name="viewport" content="width=device-width, initial-scale=1.0">
     <link rel="stylesheet" href="/bootstrap-5.3.3-dist/css/bootstrap.min.css">
     <script src="/chartjs/chart.umd.js" defer></script>
     <script type="module" src="/histogram.js" defer></script>
@@ -26,7 +27,7 @@
     </div>
     <div class="row">
         <div class="col">
-            <canvas id="histogram"></canvas>
+            {% include 'chart.html' %}
         </div>
     </div>
     <div class="row">
@@ -50,15 +51,9 @@
         </div>
     </div>
 
-    <p>Do you like living life dangerously? use <a href="/password_of_the_month">password of the month</a>
+    <p>Do you like living life dangerously? use <a href="/potm">password of the month</a>
         as your next password</p>
 </div>
 
-
-<script defer>
-    var data = {{ histogram_data|safe }};
-    var labels = {{ histogram_labels|safe }};
-</script>
-
 </body>
 </html>
\ No newline at end of file
-- 
cgit v1.2.3