#!/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': name, 'value': ip} for ip, name 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([ dcc.Loading(html.Pre(id='machines-sum-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=7, children=[ dcc.Loading(dcc.Graph(id='machines-graph-systemmodel')), ]), dbc.Col(width=12, xl=7, children=[ dcc.Loading(dcc.Graph(id='machines-graph-basesystem')), ]), 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=7, 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'), Output('machines-sum-location', 'children')], [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', yaxis_title = 'Machines' ) totalStr = 'Total Locations: {}'.format(len(data)) return figure, totalStr @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
Click Bar to view Details', 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
Click Bar to view Details', typ='bar') #def make_graph_basesystem(days, date, satellites): # return make_graph(days, date, satellites, 'basesystem', 'BASE SYSTEMS
Click Bar to view Details', 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 @app.callback(Output('machines-graph-basesystem', 'figure'), [Input('machines-days', 'value'), Input('machines-date', 'date'), Input('machines-satellites', 'value')]) def make_graph_basesystem(days, date, satellites): dbcon = db.getConnection() cursor = dbcon.cursor() stmt = """ SELECT b.system, SUM(b.count) AS count FROM reports r JOIN basesystem b ON r.id = b.report WHERE r.date = (SELECT date FROM reports WHERE date >= %s ORDER BY date ASC LIMIT 1) AND b.days = %s """ stmt_end = """ GROUP BY b.system""" 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: print(stmt) cursor.execute(stmt + stmt_end, (date, days)) data = cursor.fetchall() data = natsorted(data, key = lambda x: x['count']) db.closeConnection(dbcon) figure = go.Figure() figure.add_trace(go.Bar( y=[item['system'] for item in data], x=[item['count'] for item in data], orientation='h' )) figure.update_layout( title_text = "BASE SYSTEMS", height = max(20 * len(data) + 200,300), yaxis_automargin=True ) return figure 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'] 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