From 538d843b76ccf935e805c034013509c1e3577925 Mon Sep 17 00:00:00 2001 From: Lukas Metzger Date: Sat, 23 May 2020 14:48:57 +0200 Subject: Added machine information and some minor fixes --- assets/20-style.css | 1 + index.py | 6 +- pages/machines.py | 209 ++++++++++++++++++++++++++++++++++++++++++++++++++++ pages/total.py | 4 - 4 files changed, 214 insertions(+), 6 deletions(-) create mode 100644 pages/machines.py diff --git a/assets/20-style.css b/assets/20-style.css index b909f70..85adf70 100644 --- a/assets/20-style.css +++ b/assets/20-style.css @@ -5,6 +5,7 @@ ul.navbar-nav { .table-responsive-lg { padding-left: 15px; + padding-right: 15px; } .DateInput_input { diff --git a/index.py b/index.py index 14316fa..ad9c77f 100644 --- a/index.py +++ b/index.py @@ -9,11 +9,12 @@ import plotly.graph_objects as go import plotly.express as px from app import app -from pages import satellites, total +from pages import satellites, total, machines pages = [ {'name': 'Satellites', 'link': '/', 'id': 'satellites', 'layout': satellites.layout}, {'name': 'Total', 'link': '/total', 'id': 'total', 'layout': total.layout}, + {'name': 'Machines', 'link': '/machines', 'id': 'machines', 'layout': machines.layout}, ] pio.templates['custom'] = dict( @@ -24,7 +25,8 @@ pio.templates['custom'] = dict( yanchor = 'top', font = dict(size = 25) ), - separators=',.' + separators=',.', + yaxis_tickformat = ',0f', ) ) pio.templates.default = 'custom' diff --git a/pages/machines.py b/pages/machines.py new file mode 100644 index 0000000..b750ddc --- /dev/null +++ b/pages/machines.py @@ -0,0 +1,209 @@ +#!/usr/bin/env python3 + +import dash_core_components as dcc +import dash_html_components as html +import dash_bootstrap_components as dbc +from dash.dependencies import Input, Output + +import plotly.graph_objects as go + +import datetime as dt +from natsort import natsorted + +import db +from app import app + +def layout(): + return dbc.Container(fluid=True, children=[ + dbc.Row([ + dbc.Col(width=12, lg=1, children=[ + dcc.Dropdown( + id='machines-days', + options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]], + value=7, + clearable=False + ) + ]), + dbc.Col(width=12, lg=3, children=[ + dcc.DatePickerSingle( + id='machines-date', + date=get_newest_date(), + display_format='DD-MM-YYYY', + max_date_allowed=get_newest_date(), + initial_visible_month=get_newest_date(), + first_day_of_week=1 + ), + ]), + dbc.Col(width=12, lg=6, children=[ + dcc.Dropdown( + id='machines-satellites', + options=[{'label': s, 'value': s} for s in get_satellites()], + multi=True, + value=[], + placeholder='All Satellites' + ) + ]) + ]), + dbc.Row([dbc.Col([ + dcc.Graph(id='machines-graph-location') + ])]), + dbc.Row([ + dbc.Col(width=12, md=6, xl=4, children=[ + dcc.Graph(id='machines-graph-ram') + ]), + dbc.Col(width=12, md=6, xl=4, children=[ + dcc.Graph(id='machines-graph-realcores') + ]), + dbc.Col(width=12, md=6, xl=4, children=[ + dcc.Graph(id='machines-graph-kvmstate') + ]), + dbc.Col(width=12, xl=6, children=[ + dcc.Graph(id='machines-graph-cpumodel') + ]), + dbc.Col(width=12, xl=6, children=[ + dcc.Graph(id='machines-graph-systemmodel') + ]) + ]) + ]) + +@app.callback(Output('machines-graph-location', 'figure'), + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-satellites', 'value')]) +def make_graph_location(days, date, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT m.value, m.count + FROM reports r + JOIN machine m ON r.id = m.report + WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) + AND m.days = %s AND m.property = 'location' + """ + stmt_end = """ ORDER BY m.count DESC""" + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days] + satellites)) + else: + cursor.execute(stmt + stmt_end, (date, days)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['value'][0:9] for item in data], + y=[item['count'] for item in data] + )) + figure.update_layout( + title_text = 'Locations' + ) + return figure + +@app.callback(Output('machines-graph-cpumodel', 'figure'), + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-satellites', 'value')]) +def make_graph_cpumodel(days, date, satellites): + return make_graph(days, date, satellites, 'cpumodel', 'CPU', typ='bar', height=600) + +@app.callback(Output('machines-graph-realcores', 'figure'), + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-satellites', 'value')]) +def make_graph_realcores(days, date, satellites): + return make_graph(days, date, satellites, 'realcores', 'Cores', unit=' Cores') + +@app.callback(Output('machines-graph-ram', 'figure'), + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-satellites', 'value')]) +def make_graph_ram(days, date, satellites): + return make_graph(days, date, satellites, 'ram', 'RAM', unit=' GB') + +@app.callback(Output('machines-graph-systemmodel', 'figure'), + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-satellites', 'value')]) +def make_graph_systemmodel(days, date, satellites): + return make_graph(days, date, satellites, 'systemmodel', 'System Model', typ='bar', height=600) + +@app.callback(Output('machines-graph-kvmstate', 'figure'), + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-satellites', 'value')]) +def make_graph_kvmstate(days, date, satellites): + return make_graph(days, date, satellites, 'kvmstate', 'KVM State') + + +def make_graph(days, date, satellites, prop, title, unit='', typ='pie', height=450): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT m.value, SUM(m.count) AS count + FROM reports r + JOIN machine m ON r.id = m.report + WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) + AND m.days = %s AND m.property = %s + """ + stmt_end = """ GROUP BY m.value""" + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings) + stmt_end, tuple([date, days, prop] + satellites)) + else: + cursor.execute(stmt + stmt_end, (date, days, prop)) + + data = cursor.fetchall() + data = natsorted(data, key = lambda x: x['value' if typ=='pie' else 'count']) + + db.closeConnection(dbcon) + + figure = go.Figure() + if typ == 'pie': + figure.add_trace(go.Pie( + labels=[item['value'] + unit for item in data], + values=[item['count'] for item in data], + hole=0.3, + sort=False, + textinfo='label', + direction='clockwise' + )) + elif typ == 'bar': + figure.add_trace(go.Bar( + y=[item['value'] + unit for item in data], + x=[item['count'] for item in data], + orientation='h' + )) + figure.update_layout( + title_text = title, + height = height, + yaxis_automargin=True + ) + 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() + + cursor.execute("""SELECT date FROM reports ORDER BY date DESC LIMIT 1""") + + data = cursor.fetchall() + + db.closeConnection(dbcon) + return data[0]['date'] diff --git a/pages/total.py b/pages/total.py index 843d581..f4d6c90 100644 --- a/pages/total.py +++ b/pages/total.py @@ -104,7 +104,6 @@ def make_graph_times(days, rangeStart, rangeEnd, satellites): )) figure.update_layout( yaxis_ticksuffix=' h', - yaxis_tickformat = ',0f', title_text = 'Usage Times' ) return figure @@ -145,7 +144,6 @@ def make_graph_session_length(days, rangeStart, rangeEnd, satellites): )) figure.update_layout( yaxis_ticksuffix=' min', - yaxis_tickformat = ',0f', title_text = 'Median Session Length' ) return figure @@ -190,7 +188,6 @@ def make_graph_sessions(days, rangeStart, rangeEnd, satellites): width=2*86400e3 )) figure.update_layout( - yaxis_tickformat = ',0f', barmode='stack', title_text = 'Sessions', ) @@ -230,7 +227,6 @@ def make_graph_users(days, rangeStart, rangeEnd, satellites): name='Unique Users' )) figure.update_layout( - yaxis_tickformat = ',0f', showlegend=True, title_text = 'Unique Users', ) -- cgit v1.2.3-55-g7522