#!/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=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')
@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_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