Added names for sattelites

7 files changed, 86 insertions, 53 deletions

diff --git a/dash/pages/dozmod.py b/dash/pages/dozmod.py
index 4d9fb24..d7db6b5 100644
--- a/dash/pages/dozmod.py
+++ b/dash/pages/dozmod.py
@@ -40,7 +40,7 @@ def layout():
             dbc.Col(width=12, lg=6, children=[
                 dcc.Dropdown(
                     id='dozmod-satellites',
-                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
                     multi=True,
                     value=[],
                     placeholder='All Satellites',
@@ -193,9 +193,13 @@ def get_satellites():
     dbcon = db.getConnection()
     cursor = dbcon.cursor()
 
-    cursor.execute("""SELECT DISTINCT ip FROM reports""")
+    cursor.execute("""
+                SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
+                LEFT OUTER JOIN names nm ON r.ip = nm.ip
+                ORDER BY name ASC
+            """)
 
-    data = [item['ip'] for item in cursor.fetchall()]
+    data = [(item['ip'], item['name']) for item in cursor.fetchall()]
 
     db.closeConnection(dbcon)
     return data
diff --git a/dash/pages/locations.py b/dash/pages/locations.py
index 74e86b2..f62baf6 100644
--- a/dash/pages/locations.py
+++ b/dash/pages/locations.py
@@ -44,7 +44,7 @@ def layout():
             dbc.Col(width=12, lg=6, children=[
                 dcc.Dropdown(
                     id='locations-satellite',
-                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
                     value=None,
                     placeholder='All Satellites',
                     persistence=True,
@@ -82,10 +82,10 @@ def make_content_all(days, date):
 
     db.closeConnection(dbcon)
 
-    satellites = natsorted(get_satellites())
+    satellites = natsorted(get_satellites(), key = lambda x: x[0])
 
     figures = []
-    for sat in satellites:
+    for sat, name in satellites:
         figure = go.Figure()
         figure.add_trace(go.Pie(
             labels=[item['locationname'][0:9] for item in data if item['ip'] == sat],
@@ -98,7 +98,7 @@ def make_content_all(days, date):
         ))
         numElements = len([item['ip'] for item in data if item['ip'] == sat])
         figure.update_layout(
-            title_text = '<sub>Sessiontime per Location (Total: {})</sub><br>{}'.format(numElements, sat),
+            title_text = '<sub>Sessiontime per Location (Total: {})</sub><br>{}'.format(numElements, name),
             showlegend=False
         )
         if len([item for item in data if item['ip'] == sat and item['totalSessionTime'] > 0]) > 0:
@@ -213,17 +213,6 @@ def make_content_sat_sessions(days, date, satellite):
 
     return figure
 
-def get_satellites():
-    dbcon = db.getConnection()
-    cursor = dbcon.cursor()
-
-    cursor.execute("""SELECT DISTINCT ip FROM reports""")
-
-    data = [item['ip'] for item in cursor.fetchall()]
-
-    db.closeConnection(dbcon)
-    return data
-
 def get_newest_date():
     dbcon = db.getConnection()
     cursor = dbcon.cursor()
@@ -246,3 +235,17 @@ def get_oldest_date():
     db.closeConnection(dbcon)
     return data[0]['date']
 
+def get_satellites():
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    cursor.execute("""
+                SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
+                LEFT OUTER JOIN names nm ON r.ip = nm.ip
+                ORDER BY name ASC
+            """)
+
+    data = [(item['ip'], item['name']) for item in cursor.fetchall()]
+
+    db.closeConnection(dbcon)
+    return data
diff --git a/dash/pages/machines.py b/dash/pages/machines.py
index b6cb6f8..f9d69da 100644
--- a/dash/pages/machines.py
+++ b/dash/pages/machines.py
@@ -43,7 +43,7 @@ def layout():
             dbc.Col(width=12, lg=6, children=[
                 dcc.Dropdown(
                     id='machines-satellites',
-                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
                     multi=True,
                     value=[],
                     placeholder='All Satellites',
@@ -279,17 +279,6 @@ def make_graph(days, date, satellites, prop, title, unit='', typ='pie'):
     )
     return figure
 
-def get_satellites():
-    dbcon = db.getConnection()
-    cursor = dbcon.cursor()
-
-    cursor.execute("""SELECT DISTINCT ip FROM reports""")
-
-    data = [item['ip'] for item in cursor.fetchall()]
-
-    db.closeConnection(dbcon)
-    return data
-
 def get_newest_date():
     dbcon = db.getConnection()
     cursor = dbcon.cursor()
@@ -312,3 +301,17 @@ def get_oldest_date():
     db.closeConnection(dbcon)
     return data[0]['date']
 
+def get_satellites():
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    cursor.execute("""
+                SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
+                LEFT OUTER JOIN names nm ON r.ip = nm.ip
+                ORDER BY name ASC
+            """)
+
+    data = [(item['ip'], item['name']) for item in cursor.fetchall()]
+
+    db.closeConnection(dbcon)
+    return data
diff --git a/dash/pages/satellites.py b/dash/pages/satellites.py
index 37a1f61..681c70b 100644
--- a/dash/pages/satellites.py
+++ b/dash/pages/satellites.py
@@ -19,7 +19,7 @@ def layout():
                 html.Div(className='table-responsive-lg', children=[dtbl.DataTable(
                     id='satellites-table',
                     columns=[
-                        {'id': 'ip', 'name': 'IP', 'type': 'text'},
+                        {'id': 'name', 'name': 'Name', 'type': 'text'},
                         {'id': 'date', 'name': 'Last Report', 'type': 'text'},
                         {'id': 'version', 'name': 'Version', 'type': 'text'},
                         {'id': 'numReports', 'name': '#Reports'},
@@ -52,7 +52,7 @@ def load_table(sort):
     sortstr = "ORDER BY r.date DESC"
     if sort != None and len(sort) > 0:
         mappings = {
-                'ip': 'r.ip',
+                'name': 'name',
                 'date': 'r.date',
                 'version': 'r.version',
                 'numReports': 'n.numReports',
@@ -67,10 +67,12 @@ def load_table(sort):
 
     cursor.execute("""
         SELECT
-            r.ip, r.version, r.date, s.cpuCount, s.cpuModel, s.uptime, n.numReports,
+            COALESCE(nm.name, r.ip) AS name,
+            r.version, r.date, s.cpuCount, s.cpuModel, s.uptime, n.numReports,
             ROUND(100 - s.memFree / s.memTotal * 100, 1) as memPercent,
             ROUND(s.swapUsed / s.swapTotal * 100, 1) as swapPercent
         FROM reports_newest r
+        LEFT OUTER JOIN names nm ON r.ip = nm.ip
         LEFT OUTER JOIN server s ON r.id = s.report
         LEFT OUTER JOIN (SELECT ip, COUNT(date) AS numReports FROM reports GROUP BY ip) n ON r.ip = n.ip
     """ + sortstr)
diff --git a/dash/pages/total.py b/dash/pages/total.py
index 461c03a..91bcc87 100644
--- a/dash/pages/total.py
+++ b/dash/pages/total.py
@@ -41,7 +41,7 @@ def layout():
             dbc.Col(width=12, lg=6, children=[
                 dcc.Dropdown(
                     id='total-satellites',
-                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
                     multi=True,
                     value=[],
                     placeholder='All Satellites',
@@ -135,9 +135,14 @@ def make_graph_session_length(days, rangeStart, rangeEnd, satellites):
     cursor = dbcon.cursor()
 
     stmt = """
-            SELECT r.date, r.ip, t.medianSessionLength
+            SELECT 
+                r.date,
+                r.ip,
+                COALESCE(nm.name, r.ip) AS name,
+                t.medianSessionLength
             FROM reports r
             JOIN total t ON r.id = t.report
+            LEFT OUTER JOIN names nm ON r.ip = nm.ip
             WHERE t.days = %s AND r.date >= %s AND r.date <= %s
         """
 
@@ -152,12 +157,12 @@ def make_graph_session_length(days, rangeStart, rangeEnd, satellites):
     db.closeConnection(dbcon)
 
     figure = go.Figure()
-    for sat in set([item['ip'] for item in data]):
+    for sat, n in set([(item['ip'], item['name']) for item in data]):
         figure.add_trace(go.Scatter(
             x=[item['date'] for item in data if item['ip'] == sat],
             y=[int(item['medianSessionLength'] / 60) for item in data if item['ip'] == sat],
             mode='lines+markers',
-            name=sat
+            name=n
         ))
     figure.update_layout(
         yaxis_ticksuffix=' min',
@@ -303,9 +308,13 @@ def get_satellites():
     dbcon = db.getConnection()
     cursor = dbcon.cursor()
 
-    cursor.execute("""SELECT DISTINCT ip FROM reports""")
+    cursor.execute("""
+                SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
+                LEFT OUTER JOIN names nm ON r.ip = nm.ip
+                ORDER BY name ASC
+            """)
 
-    data = [item['ip'] for item in cursor.fetchall()]
+    data = [(item['ip'], item['name']) for item in cursor.fetchall()]
 
     db.closeConnection(dbcon)
     return data
diff --git a/dash/pages/vms.py b/dash/pages/vms.py
index fae6fb9..953ff3b 100644
--- a/dash/pages/vms.py
+++ b/dash/pages/vms.py
@@ -43,7 +43,7 @@ def layout():
             dbc.Col(width=12, lg=6, children=[
                 dcc.Dropdown(
                     id='vms-satellite',
-                    options=[{'label': s, 'value': s} for s in get_satellites()],
+                    options=[{'label': name, 'value': ip} for ip, name in get_satellites()],
                     value=None,
                     placeholder='All Satellites',
                     persistence=True,
@@ -69,8 +69,9 @@ def make_content_all(days, date):
     cursor = dbcon.cursor()
 
     stmt = """
-        SELECT r.ip, COUNT(DISTINCT v.vm) as count
+        SELECT COUNT(DISTINCT v.vm) as count, COALESCE(nm.name, r.ip) AS name
         FROM reports r
+        LEFT OUTER JOIN names nm ON r.ip = nm.ip
         JOIN perVM v ON r.id = v.report
         WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1)
             AND v.days = %s
@@ -85,7 +86,7 @@ def make_content_all(days, date):
 
     figure = go.Figure()
     figure.add_trace(go.Bar(
-        x=[item['ip'] for item in data],
+        x=[item['name'] for item in data],
         y=[item['count'] for item in data]
     ))
     figure.update_layout(
@@ -123,17 +124,6 @@ def make_content_sat(days, date, satellite):
 
     return figure
 
-def get_satellites():
-    dbcon = db.getConnection()
-    cursor = dbcon.cursor()
-
-    cursor.execute("""SELECT DISTINCT ip FROM reports""")
-
-    data = [item['ip'] for item in cursor.fetchall()]
-
-    db.closeConnection(dbcon)
-    return data
-
 def get_newest_date():
     dbcon = db.getConnection()
     cursor = dbcon.cursor()
@@ -156,3 +146,17 @@ def get_oldest_date():
     db.closeConnection(dbcon)
     return data[0]['date']
 
+def get_satellites():
+    dbcon = db.getConnection()
+    cursor = dbcon.cursor()
+
+    cursor.execute("""
+                SELECT DISTINCT r.ip, COALESCE(nm.name, r.ip) AS name FROM reports r
+                LEFT OUTER JOIN names nm ON r.ip = nm.ip
+                ORDER BY name ASC
+            """)
+
+    data = [(item['ip'], item['name']) for item in cursor.fetchall()]
+
+    db.closeConnection(dbcon)
+    return data
diff --git a/db_structure.sql b/db_structure.sql
index 7ad3d87..a47b5c1 100644
--- a/db_structure.sql
+++ b/db_structure.sql
@@ -49,6 +49,11 @@ CREATE TABLE reports (
   version varchar(200) COLLATE utf8_bin NOT NULL
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
 
+CREATE TABLE names (
+  ip varchar(20) COLLATE utf8_bin NOT NULL,
+  name varchar(200) COLLATE utf8_bin NOT NULL
+) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
+
 CREATE TABLE server (
   report bigint(20) NOT NULL,
   cpuCount bigint(20) NOT NULL,
@@ -112,6 +117,9 @@ ALTER TABLE reports
 ALTER TABLE server
   ADD PRIMARY KEY (report);
 
+ALTER TABLE names
+  ADD PRIMARY KEY (ip);
+
 ALTER TABLE timeframe
   ADD PRIMARY KEY (report,days);