Move dashboard to subfolder

6 files changed, 1221 insertions, 0 deletions

diff --git a/dash/pages/dozmod.py b/dash/pages/dozmod.py
new file mode 100644
index 0000000..4d9fb24
--- /dev/null
+++ b/dash/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/dash/pages/locations.py b/dash/pages/locations.py
new file mode 100644
index 0000000..85adcfc
--- /dev/null
+++ b/dash/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/dash/pages/machines.py b/dash/pages/machines.py
new file mode 100644
index 0000000..b82df43
--- /dev/null
+++ b/dash/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/dash/pages/satellites.py b/dash/pages/satellites.py
new file mode 100644
index 0000000..232d8c7
--- /dev/null
+++ b/dash/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/dash/pages/total.py b/dash/pages/total.py
new file mode 100644
index 0000000..eb7a63c
--- /dev/null
+++ b/dash/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/dash/pages/vms.py b/dash/pages/vms.py
new file mode 100644
index 0000000..c9ca141
--- /dev/null
+++ b/dash/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']
+