path: root/dash/pages/vms.py

#!/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': name, 'value': ip} for ip, name 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 COUNT(DISTINCT v.vm) as count, COALESCE(nm.name, r.ip) AS name
        FROM reports r
        LEFT OUTER JOIN names nm ON r.ip = nm.ip
        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['name'] for item in data],
        y=[item['count'] for item in data]
    ))
    figure.update_layout(
        title_text = 'VMs per Satellite'
    )

    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_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