diff options
Diffstat (limited to 'pages')
| -rw-r--r-- | pages/dozmod.py | 201 | ||||
| -rw-r--r-- | pages/locations.py | 241 | ||||
| -rw-r--r-- | pages/machines.py | 306 | ||||
| -rw-r--r-- | pages/satellites.py | 61 | ||||
| -rw-r--r-- | pages/total.py | 254 | ||||
| -rw-r--r-- | pages/vms.py | 158 |
6 files changed, 1221 insertions, 0 deletions
diff --git a/pages/dozmod.py b/pages/dozmod.py new file mode 100644 index 0000000..4d9fb24 --- /dev/null +++ b/pages/dozmod.py @@ -0,0 +1,201 @@ +#!/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 + +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='dozmod-days', + options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]], + value=7, + clearable=False, + persistence=True, + persistence_type='memory' + ) + ]), + dbc.Col(width=12, lg=3, children=[ + dcc.DatePickerRange( + id='dozmod-range', + start_date=(dt.datetime.now() - dt.timedelta(days=365)).date(), + end_date=dt.datetime.now().date(), + display_format='DD-MM-YYYY', + initial_visible_month=dt.datetime.now(), + first_day_of_week=1, + persistence=True, + persistence_type='memory' + ), + ]), + dbc.Col(width=12, lg=6, children=[ + dcc.Dropdown( + id='dozmod-satellites', + options=[{'label': s, 'value': s} for s in get_satellites()], + multi=True, + value=[], + placeholder='All Satellites', + persistence=True, + persistence_type='memory' + ) + ]) + ]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='dozmod-graph-vms')) + ])]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='dozmod-graph-lectures')) + ])]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='dozmod-graph-users')) + ])]) + ]) + +@app.callback(Output('dozmod-graph-vms', 'figure'), + [Input('dozmod-days', 'value'), + Input('dozmod-range', 'start_date'), + Input('dozmod-range', 'end_date'), + Input('dozmod-satellites', 'value')]) +def make_graph_vms(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, SUM(d.vms_total) AS vms_total, SUM(d.vms_new) AS vms_new, SUM(d.vms_updated) AS vms_updated, SUM(d.vms_valid) AS vms_valid + FROM reports r + JOIN dozmod d ON r.id = d.report + WHERE d.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + for field, title in [('vms_total', 'VMs Total'), ('vms_new', 'VMs New'), ('vms_updated', 'VMs Updated'), ('vms_valid', 'VMs Valid')]: + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[item[field] for item in data], + mode='lines+markers', + name=title + )) + figure.update_layout( + title_text = 'VM Statistics', + uirevision=42 + ) + return figure + +@app.callback(Output('dozmod-graph-lectures', 'figure'), + [Input('dozmod-days', 'value'), + Input('dozmod-range', 'start_date'), + Input('dozmod-range', 'end_date'), + Input('dozmod-satellites', 'value')]) +def make_graph_lectures(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, SUM(d.lectures_total) AS lectures_total, SUM(d.lectures_new) AS lectures_new, SUM(d.lectures_updated) AS lectures_updated, SUM(d.lectures_valid) AS lectures_valid + FROM reports r + JOIN dozmod d ON r.id = d.report + WHERE d.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + for field, title in [('lectures_total', 'Lectures Total'), ('lectures_new', 'Lectures New'), ('lectures_updated', 'Lectures Updated'), ('lectures_valid', 'Lectures Valid')]: + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[item[field] for item in data], + mode='lines+markers', + name=title + )) + figure.update_layout( + title_text = 'Lecture Statistics', + uirevision=42 + ) + return figure + +@app.callback(Output('dozmod-graph-users', 'figure'), + [Input('dozmod-days', 'value'), + Input('dozmod-range', 'start_date'), + Input('dozmod-range', 'end_date'), + Input('dozmod-satellites', 'value')]) +def make_graph_users(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, SUM(d.users_total) AS users_total, SUM(d.users_organizations) AS users_organizations + FROM reports r + JOIN dozmod d ON r.id = d.report + WHERE d.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[item['users_total'] for item in data], + mode='lines+markers', + name='Users Total', + yaxis='y' + )) + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[item['users_organizations'] for item in data], + mode='lines+markers', + name='Users Organizations', + yaxis='y2' + )) + figure.update_layout( + title_text = 'Users Statistics', + yaxis = dict(title='Users Total', side='left'), + yaxis2 = dict(title = 'Users Organizations', side='right', overlaying='y'), + uirevision=42 + ) + 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 diff --git a/pages/locations.py b/pages/locations.py new file mode 100644 index 0000000..85adcfc --- /dev/null +++ b/pages/locations.py @@ -0,0 +1,241 @@ +#!/usr/bin/env python3 + +import dash_core_components as dcc +import dash_html_components as html +import dash_bootstrap_components as dbc +from plotly.subplots import make_subplots +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='locations-days', + options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]], + value=7, + clearable=False, + persistence=True, + persistence_type='memory' + ) + ]), + dbc.Col(width=12, lg=3, children=[ + dcc.DatePickerSingle( + id='locations-date', + date=get_newest_date(), + display_format='DD-MM-YYYY', + max_date_allowed=get_newest_date(), + min_date_allowed=get_oldest_date(), + initial_visible_month=get_newest_date(), + first_day_of_week=1, + persistence=True, + persistence_type='memory' + ), + ]), + dbc.Col(width=12, lg=6, children=[ + dcc.Dropdown( + id='locations-satellite', + options=[{'label': s, 'value': s} for s in get_satellites()], + value=None, + placeholder='All Satellites', + persistence=True, + persistence_type='memory' + ) + ]) + ]), + dcc.Loading(style={'marginTop': '200px'}, children=dbc.Row(id='locations-content')) + ]) + +@app.callback(Output('locations-content', 'children'), + [Input('locations-days', 'value'), + Input('locations-date', 'date'), + Input('locations-satellite', 'value')]) +def make_content(days, date, satellite): + if satellite == None: + return make_content_all(days, date) + else: + return make_content_sat(days, date, satellite) + +def make_content_all(days, date): + dbcon= db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.ip, l.locationname, l.totalSessionTime + FROM reports r + JOIN perLocation l ON r.id = l.report + WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) + AND l.days = %s + """ + cursor.execute(stmt, (date, days)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + satellites = natsorted(get_satellites()) + + figures = [] + for sat in satellites: + figure = go.Figure() + figure.add_trace(go.Pie( + labels=[item['locationname'][0:9] for item in data if item['ip'] == sat], + values=[int(item['totalSessionTime'] / 3600) for item in data if item['ip'] == sat], + text=[str(int(item['totalSessionTime'] / 3600)) + ' h' for item in data if item['ip'] == sat], + hole=0.3, + textinfo='none', + hoverinfo='label+text+percent', + direction='clockwise' + )) + figure.update_layout( + title_text = '<sub>Sessiontime per Location</sub><br>' + sat, + showlegend=False + ) + figures.append(dbc.Col(width=12, md=6, xl=4, children=dcc.Graph(figure=figure))) + + return figures + +def make_content_sat(days, date, satellite): + return [ + dbc.Col(width=12, children=dcc.Graph(figure=item(days, date, satellite))) + for item in [make_content_sat_sessiontime, make_content_sat_median, make_content_sat_sessions] + ] + +def make_content_sat_sessiontime(days, date, satellite): + dbcon= db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT l.locationname, l.totalSessionTime + FROM reports r + JOIN perLocation l ON r.id = l.report + WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) + AND l.days = %s AND r.ip = %s + ORDER BY l.locationname ASC + """ + cursor.execute(stmt, (date, days, satellite)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['locationname'][0:9] for item in data], + y=[int(item['totalSessionTime'] / 3600) for item in data] + )) + figure.update_layout( + title_text = 'Total Session Time', + yaxis_ticksuffix=' h', + ) + + return figure + +def make_content_sat_median(days, date, satellite): + dbcon= db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT l.locationname, l.medianSessionLength + FROM reports r + JOIN perLocation l ON r.id = l.report + WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) + AND l.days = %s AND r.ip = %s + ORDER BY l.locationname ASC + """ + cursor.execute(stmt, (date, days, satellite)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['locationname'][0:9] for item in data], + y=[int(item['medianSessionLength'] / 60) for item in data] + )) + figure.update_layout( + title_text = 'Median Session Time', + yaxis_ticksuffix=' min', + ) + + return figure + +def make_content_sat_sessions(days, date, satellite): + dbcon= db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT l.locationname, l.shortSessions, l.longSessions + FROM reports r + JOIN perLocation l ON r.id = l.report + WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) + AND l.days = %s AND r.ip = %s + ORDER BY l.locationname ASC + """ + cursor.execute(stmt, (date, days, satellite)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['locationname'][0:9] for item in data], + y=[item['shortSessions'] for item in data], + name='Short Sessions' + )) + figure.add_trace(go.Bar( + x=[item['locationname'][0:9] for item in data], + y=[item['longSessions'] for item in data], + name='Long Sessions' + )) + figure.update_layout( + title_text = 'Sessions', + barmode='stack', + legend=dict(x=0.9, y=1.2) + ) + + 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'] + +def get_oldest_date(): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + cursor.execute("""SELECT date FROM reports ORDER BY date ASC LIMIT 1""") + + data = cursor.fetchall() + + db.closeConnection(dbcon) + return data[0]['date'] + diff --git a/pages/machines.py b/pages/machines.py new file mode 100644 index 0000000..b82df43 --- /dev/null +++ b/pages/machines.py @@ -0,0 +1,306 @@ +#!/usr/bin/env python3 + +import dash_core_components as dcc +import dash_html_components as html +import dash_bootstrap_components as dbc +import dash_table as dtbl +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, + persistence=True, + persistence_type='memory' + ) + ]), + 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(), + min_date_allowed=get_oldest_date(), + initial_visible_month=get_newest_date(), + first_day_of_week=1, + persistence=True, + persistence_type='memory' + ), + ]), + 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', + persistence=True, + persistence_type='memory' + ) + ]) + ]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='machines-graph-location')) + ])]), + dbc.Row([ + dbc.Col(width=12, md=6, xl=4, children=[ + dcc.Loading(dcc.Graph(id='machines-graph-ram')) + ]), + dbc.Col(width=12, md=6, xl=4, children=[ + dcc.Loading(dcc.Graph(id='machines-graph-realcores')) + ]), + dbc.Col(width=12, md=6, xl=4, children=[ + dcc.Loading(dcc.Graph(id='machines-graph-kvmstate')) + ]), + dbc.Col(width=12, xl=8, children=[ + dcc.Loading(dcc.Graph(id='machines-graph-systemmodel')), + ]), + dbc.Col(width=12, xl=4, id='machines-graph-systemmodel-locations-container', children=[ + html.H3(id='machines-graph-systemmodel-locations-head', style=dict(fontWeight='normal', fontSize='25px', fontFamily='Open Sans', marginTop='20px')), + dcc.Loading(dtbl.DataTable( + id='machines-graph-systemmodel-locations', + columns=[ + {'id': 'ip', 'name': 'Location', 'type': 'text'}, + {'id': 'count', 'name': 'Count', 'type': 'numeric'} + ], + style_cell_conditional=[ + { + 'if': {'column_type': 'text'}, + 'textAlign': 'left' + } + ] + )) + ]), + dbc.Col(width=12, xl=8, children=[ + dcc.Loading(dcc.Graph(id='machines-graph-cpumodel')), + ]), + dbc.Col(width=12, xl=4, id='machines-graph-cpumodel-locations-container', children=[ + html.H3(id='machines-graph-cpumodel-locations-head', style=dict(fontWeight='normal', fontSize='25px', fontFamily='Open Sans', marginTop='20px')), + dcc.Loading(dtbl.DataTable( + id='machines-graph-cpumodel-locations', + columns=[ + {'id': 'ip', 'name': 'Location', 'type': 'text'}, + {'id': 'count', 'name': 'Count', 'type': 'numeric'} + ], + style_cell_conditional=[ + { + 'if': {'column_type': 'text'}, + 'textAlign': 'left' + } + ] + )) + ]) + ]) + ]) + +@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') + +@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') + +@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') + +@app.callback([Output('machines-graph-systemmodel-locations-container', 'style'), + Output('machines-graph-systemmodel-locations', 'data'), + Output('machines-graph-systemmodel-locations-head', 'children')], + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-graph-systemmodel', 'clickData')]) +def make_graph_systemmodel_locations(days, date, clickData): + return make_graph_locations(days, date, clickData, 'systemmodel') + +@app.callback([Output('machines-graph-cpumodel-locations-container', 'style'), + Output('machines-graph-cpumodel-locations', 'data'), + Output('machines-graph-cpumodel-locations-head', 'children')], + [Input('machines-days', 'value'), + Input('machines-date', 'date'), + Input('machines-graph-cpumodel', 'clickData')]) +def make_graph_cpumodel_locations(days, date, clickData): + return make_graph_locations(days, date, clickData, 'cpumodel') + +def make_graph_locations(days, date, clickData, prop): + if clickData == None: + return dict(display='none'), [], '' + + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.ip, 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 = %s + AND m.value = %s + ORDER BY m.count DESC + """ + item = clickData['points'][0]['y'] + + cursor.execute(stmt, (date, days, prop, item)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + return dict(display='block'), data, item + + +def make_graph(days, date, satellites, prop, title, unit='', typ='pie'): + 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 = 450 if typ == 'pie' else max(20 * len(data) + 200,300), + 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'] + +def get_oldest_date(): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + cursor.execute("""SELECT date FROM reports ORDER BY date ASC LIMIT 1""") + + data = cursor.fetchall() + + db.closeConnection(dbcon) + return data[0]['date'] + diff --git a/pages/satellites.py b/pages/satellites.py new file mode 100644 index 0000000..232d8c7 --- /dev/null +++ b/pages/satellites.py @@ -0,0 +1,61 @@ +#!/usr/bin/env python3 + +import dash_core_components as dcc +import dash_html_components as html +import dash_table as dtbl +import dash_bootstrap_components as dbc +from dash.dependencies import Input, Output + +import humanize +import datetime as dt + +import db +from app import app + +def layout(): + return dbc.Container(fluid=True, children=[ + dbc.Row([ + dbc.Col([ + html.Div(className='table-responsive-lg', children=[dtbl.DataTable( + id='satellites-table', + columns=[ + {'id': 'ip', 'name': 'IP', 'type': 'text'}, + {'id': 'version', 'name': 'Version', 'type': 'text'}, + {'id': 'cpuCount', 'name': '#CPUs'}, + {'id': 'cpuModel', 'name': 'CPU Model'}, + {'id': 'uptime', 'name': 'Uptime'}, + {'id': 'memPercent', 'name': 'Mem Usage'}, + {'id': 'swapPercent', 'name': 'Swap Usage'} + ], + style_cell_conditional=[ + { + 'if': {'column_type': 'text'}, + 'textAlign': 'left' + } + ], + data=load_table() + )]) + ]) + ]) + ]) + +def load_table(): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + cursor.execute(""" + SELECT + r.ip, r.version, s.cpuCount, s.cpuModel, s.uptime, + 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 server s ON r.id = s.report + """) + + data = cursor.fetchall() + + for record in data: + record['uptime'] = humanize.naturaldelta(dt.timedelta(seconds=record['uptime'])) + + db.closeConnection(dbcon) + return data diff --git a/pages/total.py b/pages/total.py new file mode 100644 index 0000000..eb7a63c --- /dev/null +++ b/pages/total.py @@ -0,0 +1,254 @@ +#!/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 + +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='total-days', + options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]], + value=7, + clearable=False, + persistence=True, + persistence_type='memory' + ) + ]), + dbc.Col(width=12, lg=3, children=[ + dcc.DatePickerRange( + id='total-range', + start_date=(dt.datetime.now() - dt.timedelta(days=365)).date(), + end_date=dt.datetime.now().date(), + display_format='DD-MM-YYYY', + initial_visible_month=dt.datetime.now(), + first_day_of_week=1, + persistence=True, + persistence_type='memory' + ), + ]), + dbc.Col(width=12, lg=6, children=[ + dcc.Dropdown( + id='total-satellites', + options=[{'label': s, 'value': s} for s in get_satellites()], + multi=True, + value=[], + placeholder='All Satellites', + persistence=True, + persistence_type='memory' + ) + ]) + ]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='total-graph-times')) + ])]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='total-graph-session-length')) + ])]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='total-graph-sessions')) + ])]), + dbc.Row([dbc.Col([ + dcc.Loading(dcc.Graph(id='total-graph-users')) + ])]) + ]) + +@app.callback(Output('total-graph-times', 'figure'), + [Input('total-days', 'value'), + Input('total-range', 'start_date'), + Input('total-range', 'end_date'), + Input('total-satellites', 'value')]) +def make_graph_times(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, SUM(t.totalTime) as totalTime, SUM(t.totalSessionTime) as totalSessionTime, SUM(t.totalOffTime) as totalOffTime + FROM reports r + JOIN total t ON r.id = t.report + WHERE t.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[int(item['totalTime'] / 3600) for item in data], + mode='lines+markers', + name='Total Time' + )) + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[int(item['totalSessionTime'] / 3600) for item in data], + mode='lines+markers', + name='Total Session Time' + )) + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[int(item['totalOffTime'] / 3600) for item in data], + mode='lines+markers', + name='Total Off Time' + )) + figure.update_layout( + yaxis_ticksuffix=' h', + title_text = 'Usage Times', + uirevision=42 + ) + return figure + +@app.callback(Output('total-graph-session-length', 'figure'), + [Input('total-days', 'value'), + Input('total-range', 'start_date'), + Input('total-range', 'end_date'), + Input('total-satellites', 'value')]) +def make_graph_session_length(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, r.ip, t.medianSessionLength + FROM reports r + JOIN total t ON r.id = t.report + WHERE t.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({})'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt, (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + for sat in set([item['ip'] 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 + )) + figure.update_layout( + yaxis_ticksuffix=' min', + title_text = 'Median Session Length', + uirevision=42 + ) + return figure + +@app.callback(Output('total-graph-sessions', 'figure'), + [Input('total-days', 'value'), + Input('total-range', 'start_date'), + Input('total-range', 'end_date'), + Input('total-satellites', 'value')]) +def make_graph_sessions(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, SUM(t.shortSessions) as shortSessions, SUM(t.longSessions) as longSessions + FROM reports r + JOIN total t ON r.id = t.report + WHERE t.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['date'] for item in data], + y=[item['shortSessions'] for item in data], + name='Short Sessions', + width=2*86400e3 + )) + figure.add_trace(go.Bar( + x=[item['date'] for item in data], + y=[item['longSessions'] for item in data], + name='Long Sessions', + width=2*86400e3 + )) + figure.update_layout( + barmode='stack', + title_text = 'Sessions', + uirevision=42 + ) + return figure + +@app.callback(Output('total-graph-users', 'figure'), + [Input('total-days', 'value'), + Input('total-range', 'start_date'), + Input('total-range', 'end_date'), + Input('total-satellites', 'value')]) +def make_graph_users(days, rangeStart, rangeEnd, satellites): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.date, SUM(t.uniqueUsers) as uniqueUsers + FROM reports r + JOIN total t ON r.id = t.report + WHERE t.days = %s AND r.date >= %s AND r.date <= %s + """ + + if len(satellites) > 0: + formatStrings = ','.join(['%s'] * len(satellites)) + cursor.execute(stmt + ' AND r.ip IN ({}) GROUP BY r.date'.format(formatStrings), tuple([days, rangeStart, rangeEnd] + satellites)) + else: + cursor.execute(stmt + ' GROUP BY r.date', (days, rangeStart, rangeEnd)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Scatter( + x=[item['date'] for item in data], + y=[item['uniqueUsers'] for item in data], + mode='lines+markers', + name='Unique Users' + )) + figure.update_layout( + showlegend=True, + title_text = 'Unique Users', + uirevision=42 + ) + 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 diff --git a/pages/vms.py b/pages/vms.py new file mode 100644 index 0000000..c9ca141 --- /dev/null +++ b/pages/vms.py @@ -0,0 +1,158 @@ +#!/usr/bin/env python3 + +import dash_core_components as dcc +import dash_html_components as html +import dash_bootstrap_components as dbc +from plotly.subplots import make_subplots +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='vms-days', + options=[{'label': '{} days'.format(d), 'value': d} for d in [7, 30, 90]], + value=7, + clearable=False, + persistence=True, + persistence_type='memory' + ) + ]), + dbc.Col(width=12, lg=3, children=[ + dcc.DatePickerSingle( + id='vms-date', + date=get_newest_date(), + display_format='DD-MM-YYYY', + max_date_allowed=get_newest_date(), + min_date_allowed=get_oldest_date(), + initial_visible_month=get_newest_date(), + first_day_of_week=1, + persistence=True, + persistence_type='memory' + ), + ]), + dbc.Col(width=12, lg=6, children=[ + dcc.Dropdown( + id='vms-satellite', + options=[{'label': s, 'value': s} for s in get_satellites()], + value=None, + placeholder='All Satellites', + persistence=True, + persistence_type='memory' + ) + ]) + ]), + dcc.Loading(dcc.Graph(id='vms-figure')) + ]) + +@app.callback(Output('vms-figure', 'figure'), + [Input('vms-days', 'value'), + Input('vms-date', 'date'), + Input('vms-satellite', 'value')]) +def make_content(days, date, satellite): + if satellite == None: + return make_content_all(days, date) + else: + return make_content_sat(days, date, satellite) + +def make_content_all(days, date): + dbcon= db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.ip, COUNT(DISTINCT v.vm) as count + FROM reports r + 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 + GROUP BY r.ip + ORDER BY count DESC + """ + cursor.execute(stmt, (date, days)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['ip'] for item in data], + y=[item['count'] for item in data] + )) + figure.update_layout( + title_text = 'VMs per Location' + ) + + return figure + +def make_content_sat(days, date, satellite): + dbcon= db.getConnection() + cursor = dbcon.cursor() + + stmt = """ + SELECT r.ip, v.vm, v.sessions + FROM reports r + 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 AND r.ip = %s + ORDER BY v.sessions DESC + """ + cursor.execute(stmt, (date, days, satellite)) + + data = cursor.fetchall() + + db.closeConnection(dbcon) + + figure = go.Figure() + figure.add_trace(go.Bar( + x=[item['vm'][0:9] for item in data], + y=[item['sessions'] for item in data] + )) + figure.update_layout( + title_text = 'Sessions per VM', + ) + + 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'] + +def get_oldest_date(): + dbcon = db.getConnection() + cursor = dbcon.cursor() + + cursor.execute("""SELECT date FROM reports ORDER BY date ASC LIMIT 1""") + + data = cursor.fetchall() + + db.closeConnection(dbcon) + return data[0]['date'] + |