summaryrefslogtreecommitdiffstats
diff options
context:
space:
mode:
Diffstat
-rw-r--r--dash/pages/dozmod.py10
-rw-r--r--dash/pages/locations.py33
-rw-r--r--dash/pages/machines.py27
-rw-r--r--dash/pages/satellites.py8
-rw-r--r--dash/pages/total.py21
-rw-r--r--dash/pages/vms.py32
-rw-r--r--db_structure.sql8
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);
generated by cgit v1.2.3-55-g7522 (git 2.39.0) at 2024-11-21 13:41:07 +0100