network_report_server/app/backend/views.py

from django.shortcuts import render, redirect
from backend.mongodb import db
from exfo.lib import Exfo, Mikrotik, SLA_MAP
from pprint import pprint

from ttp import ttp
from django.http import JsonResponse, HttpResponse
from datetime import datetime
from celery import shared_task
from django.contrib import messages

exfo = Exfo("administrator", "exf0w0rxC@t4dm!n")
exfo.login()

from datetime import datetime, timedelta
from dateutil import tz

mkt = Mikrotik()

def index(request):
    collection  = db['mascot']
    mascot_details = collection.find({})

    rapi = exfo.list_api()
    sla = exfo.call_api("sla")
   
    data_to_parse = """
    interface Loopback0
     description Router-id-loopback
     ip address 192.168.0.113/24
    !
    interface Vlan778
     description CPE_Acces_Vlan
     ip address 2002::fd37/124
     ip vrf CPE1
    !
    """

    ttp_template = """
    interface {{ interface }}
     ip address {{ ip }}/{{ mask }}
     description {{ description }}
     ip vrf {{ vrf }}
    """

    template = """
<input load="text">
interface Loopback0
 description Router-id-loopback
 ip address 192.168.0.113/24
!
interface Loopback1
 description Router-id-loopback
 ip address 192.168.0.1/24
!
interface Vlan778
 ip address 2002::fd37/124
 ip vrf CPE1
!
interface Vlan779
 ip address 2002::bbcd/124
 ip vrf CPE2
!
</input>

<group name="loopbacks_new**.{{ interface }}">
interface {{ interface | contains("Loop") }}
 ip address {{ ip }}/{{ mask }}
 description {{ description }}
 ip vrf {{ vrf }}
</group>

<group name="vlans*">
interface {{ interface | contains("Vlan") }}
 ip address {{ ip }}/{{ mask }}
 description {{ description }}
 ip vrf {{ vrf }}
</group>
    """

    parser = ttp(template=template)
    parser.parse()
    # form excel table and save in file
    parser.result(
        format="excel",
        filename="excel_out_test_excel_formatter_update.xlsx",
        returner="file",
        update=True,
        url="./Output/",
        table=[
            {
                "headers": ["interface", "ip", "mask", "vrf", "description"],
                "path": "loopbacks_new",
                "key": "interface",
                "tab_name": "loopbacks_new",
            },
            {"path": "vlans"},
        ],
    )
    # create parser object and parse data using template:
    # parser = ttp(data=data_to_parse, template=ttp_template)
    # parser.parse()

    # # print result in JSON format
    # results = parser.result(format='xlsx')[0]
    # pprint(results)
    try:
        mk_ips = mkt.call_remote("ip/route") 
        mk_address = mkt.call_remote("ip/address") 
    except:
        mk_ips = []
        mk_address = []

    return render(request, 'backend/index.html', {'objs': mascot_details, 'output': rapi.json(),\
            'sla': sla.json(), 'mk_ips': mk_ips, 'mk_address': mk_address})


#Define Collection
def remote(request):
    cmd = request.GET.get('cmd', None)
    # exfo = Exfo("administrator", "exf0w0rxC@t4dm!n")
    # exfo.login()
    r = exfo.call_remote_api(cmd)
    pprint(r.json())
    # return JsonResponse(r.json())
    return render(request, 'backend/remote_render.html', {'res': r})

def service_status(request):
    cmd = request.GET.get('cmd', None)
    import urllib.parse
    section = request.GET.get('section', 'all')
    # exfo = Exfo("administrator", "exf0w0rxC@t4dm!n")
    # exfo.login()
    cmd = urllib.parse.unquote(cmd)
    pprint(f" cmd = {cmd}")
    r = exfo.call_remote_api(cmd)
    pprint(r.json())
    # return JsonResponse(r.json())
    return render(request, 'backend/service_status.html', {'res': r, 'section': section})

def reports(request):
    days = range(1,31)
    sla = exfo.call_api("sla")
    sla_json = sla.json()
    sla_results = {}
    test_status = {}
    service_results = {}
    for r in sla_json['result']:
        sla_uri = r['ids']['sla_uri']
        t = exfo.call_remote_api(sla_uri).json()
        sla_results[r['ids']['sla_name']] = t
        t = exfo.call_remote_api(sla_uri).json()
        service_results[r['ids']['sla_name']] = []
        for si in t['result']['service_instances']:
            service_results[r['ids']['sla_name']].append(exfo.call_remote_api(si['service_uri']).json()['result'])
        try:
            t2 = exfo.call_remote_api(t['result']['tests'][-1]['test_status_uri']).json()
            test_status[r['ids']['sla_name']] = t2
        except:
            test_status[r['ids']['sla_name']] = {}
    return render(request, 'backend/reports2.html', {'days': days, 'sla': sla.json(), 'sla_results': sla_results, 'test_status': test_status, 'service_results': service_results })

def dump_api(request):
    from bson.json_util import dumps
    from bson.json_util import loads
    col  = db['exfo_api']
    # col.delete_many({})
    sla = exfo.call_api("sla")
    sla_json = sla.json()
    temp = sla_json['result']
    for t in temp:
        sla_uri = t['ids']['sla_uri']
        sla_name = t['ids']['sla_name']
        r = exfo.call_remote_api(sla_uri).json()
        t['sla_uri_result'] = r['result']
        service_result = []
        for si in r['result']['service_instances']:
            c = exfo.call_remote_api(si['service_uri']).json()['result']
            service_result.append(c)

        test_instance_class_result = []
        test_status_result = []

        for si in r['result']['tests']:
            c = exfo.call_remote_api(si['test_instance_class_uri']).json()['result']
            service = c['service']
            target = None
            if 'target' in c:
                target = c['target']
            test_instance_class_result.append(c)
            c = exfo.call_remote_api(si['test_status_uri']).json()['result']
            test_status_result.append({'sla_name':sla_name, 'test_instance_id': si['test_instance_id'],\
                                    'service': service, 'target': target, \
                                    'type_type_name':  si['test_type_name'],\
                                    'test_instance_class_id': si['test_instance_class_id']  , 'status': c, })

        t['test_instance_class_result'] = test_instance_class_result
        t['test_status_result'] = test_status_result
        t['created'] = datetime.utcnow()
        col.insert_one(t)

    # pprint(temp)
    #col.insert_many(temp)
    results = col.find({})
    data = dumps(list(results), indent=4)
    return HttpResponse(data, content_type='application/json')


@shared_task
def dump_api_task():
    from bson.json_util import dumps
    from bson.json_util import loads
    col  = db['exfo_api']
    # col.delete_many({})
    sla = exfo.call_api("sla")
    sla_json = sla.json()
    temp = sla_json['result']
    for t in temp:
        sla_uri = t['ids']['sla_uri']
        sla_name = t['ids']['sla_name']
        r = exfo.call_remote_api(sla_uri).json()
        t['sla_uri_result'] = r['result']
        service_result = []
        for si in r['result']['service_instances']:
            c = exfo.call_remote_api(si['service_uri']).json()['result']
            service_result.append(c)

        test_instance_class_result = []
        test_status_result = []

        for si in r['result']['tests']:
            c = exfo.call_remote_api(si['test_instance_class_uri']).json()['result']
            service = c['service']
            target = None
            if 'target' in c:
                target = c['target']
            test_instance_class_result.append(c)
            c = exfo.call_remote_api(si['test_status_uri']).json()['result']
            test_status_result.append({'sla_name':sla_name, 'test_instance_id': si['test_instance_id'],\
                                    'service': service, 'target': target, \
                                    'type_type_name':  si['test_type_name'],\
                                    'test_instance_class_id': si['test_instance_class_id']  , 'status': c, })

        t['test_instance_class_result'] = test_instance_class_result
        t['test_status_result'] = test_status_result
        t['created'] = datetime.utcnow()
        col.insert_one(t)

    pprint("Dump API ... Finished")

def print_table(request):
    return render(request, 'backend/print_table.html')


    
def dump_fixed_results(request):
    from bson.json_util import dumps
    from bson.json_util import loads

    from_zone = tz.gettz('UTC')
    to_zone = tz.gettz('Asia/Bangkok')

    col  = db['fixed_results']
    col.delete_many({})
    tt = exfo.call_remote_api("/API/REST/Test/v1/TypeByName?list_all=false&size=0")
    pprint(tt)
    tt_json = tt.json()
    temp = tt_json['result']
    for t in temp:
        tn = t['ids']['test_type_name']
        r = exfo.call_remote_api('/API/REST/Test/v1/FixedResults/'+tn+'?sections=all&size=0&sort=verifier_id asc,time_stamp desc&time_range={"start":"1 days  ago","end":"now"}').json()
        #pprint(r)
        try:
            r0 = r['result']
            pprint(r0)
            if len(r0) > 0:
                col.insert_many(r0)
            else:
                pprint("Empty Result")
        except Exception as e:
            pprint(e)

    # pprint(temp)
    #col.insert_many(temp)
    results = col.find({})
    data = dumps(list(results), indent=4)
    data = {'msg': 'done'}
    redir = request.GET.get('redir', None)
    if redir:
        messages.success(request, 'Dump Fixed Results')
        return redirect(redir)
    # return HttpResponse(data, content_type='application/json')
    return JsonResponse(data)

import humanize

def con_human(r):
    try:
        '''
        if 'time' in r['header'].lower():
            return r['results']
        '''
        x = r['results']
        n = int(x)
        if n > 1000:
            return humanize.naturalsize(n,gnu=True)
        else:
            return n
    except:
        return r['results']

def gen_report_notebook():

    import pandas as pd

    from_zone = tz.gettz('UTC')
    to_zone = tz.gettz('Asia/Bangkok')
    
    c = db['fixed_results'].find()
    c2  = db['ip_address']

    data = []
    for i in c:
        #pprint(i['header'])
        res = dict(zip(i['header'], i['results']))
        utc = datetime.fromtimestamp(int(i['ids']['time_stamp'])/1000000000)
        utc = utc.replace(tzinfo=from_zone)
        yest = utc - timedelta(minutes=4)
        dt = utc.astimezone(to_zone)

        d = {'header': i['header'], 'results': i['results'], 'output': res, 'params': i['parameters'], 'ts': i['ids']['time_stamp'], 
             'dt': str(dt), 'test_type_name': i['ids']['fixed_results_url']}

                

        d.update(i['names'])
        if not d['sla_name']:
            continue 

        # sla_splt = d['sla_name'].split('-')
        sla_splt = d['verifier_name'].split('_')
        if len(sla_splt) > 1:
            # reg_cond = f'(?=^pppoe-out)(?=.*{sla_splt[0]})(?=.*{sla_splt[-1]})'
            # reg_cond = f'(?=^pppoe-out)(?=.*{sla_splt[-1]})'
            reg_cond = f'(?=.*pppoe-out)(?=.*{sla_splt[-1]})'

            pprint(reg_cond)
            temp_addr = list(c2.find({'ts': {'$lte': utc, '$gte': yest }, 'interface': {'$regex': reg_cond,  '$not': { '$regex': 'pppoe-out5'}, '$options': 'i' }, 'invalid': 'false'}).sort("ts", -1))
            # temp_addr = list(c2.find({'ts': {'$lte': utc, '$gte': yest }, 'interface': {'$regex': reg_cond, '$options': 'i' }, 'invalid': 'false'}).sort("ts", -1))
            # temp_addr = list(c2.find({'interface': {'$regex': reg_cond, '$options': 'i' }, 'invalid': 'false'}).sort("ts", -1))
            pprint("-------")
            pprint("temp addr")
            pprint(d['sla_name'])
            
            pprint(temp_addr)
            
            target_address = None
            if temp_addr:
                target_address = temp_addr[0]

            if target_address:
                pprint("Found Target")
                pprint(target_address)
                d.update(target_address)
 
        #res.update(i['ids'])
        #res.update(i[])
        #pprint(d)
        data.append(d)

    df0 = pd.DataFrame(data)
    df0 = df0.explode(["header", "results"])
    try:
        df1 = df0[["header", "results", "dt", "sla_name", "test_display_name", "verifier_name", "network", "address", "interface"]]
    except:
        df1 = df0[["header", "results", "dt", "sla_name", "test_display_name", "verifier_name",]]

    df1 = df1.query('sla_name == sla_name')
    #pprint(df1['sla_name'].unique())
    
    #df1['results_text'] = df1['results'].apply(con_human)
    df1['results_text'] = df1.apply(con_human, axis=1)
    try:
        df1['network_str'] = df1['network'] + " / " + df1['interface'] 
    except:
        df1['network_str'] = "N/A"

    table = df1.pivot(index=['sla_name', 'dt', 'verifier_name', 'network_str'],columns=['test_display_name','header'], values='results_text').sort_values(by=['sla_name', 'dt'], ascending=[True, False])
    table = table.dropna(how='all', axis=0)
    return (table,df1)

def gen_report(request):
    pprint("report notebook ...")
    if request.GET.get('clear', None):
        return redirect("/backend/dump_fixed_results/?redir=/backend/gen_report/")
   
    try:
        table,df = gen_report_notebook()
    except Exception as e:
        messages.error(request, str(e))
        return render(request, 'backend/gen_report.html', {'dump_again': True})
    
    slas = list(df['sla_name'].unique())
    vrs = list(df['verifier_name'].unique())
    sla_name = request.GET.getlist('sla_name')
    vr_name = request.GET.getlist('vr_name')
    
    pprint("--- sla_name ---")
    pprint(sla_name)
    if len(sla_name) > 0:
        sla_filter = ", ".join(f"'{w}'" for w in sla_name)
        table = table.query(f"sla_name in ({sla_filter})")
    
    if len(vr_name) > 0:
        vr_filter = ", ".join(f"'{w}'" for w in vr_name)
        table = table.query(f"verifier_name in ({vr_filter})")
    
    table = table.dropna(how='all', axis=0)
    table = table.dropna(how='all', axis=1)

    is_gen_report = request.GET.get('genReport', None)
    
    report_link = None
    if is_gen_report:
        import time
        # ts = int(time.time())
        import pytz
        tz = pytz.timezone('Asia/Bangkok')
        ts = datetime.now(tz).strftime("%Y%m%d_%H%M%S")
        fn = f"report_{ts}.xlsx"

        table.to_excel(f'/code/media/{fn}')
        report_link = f'/media/{fn}'

    try:
        mk_ips = []
        mk_address = []
        for v in SLA_MAP.values():
            mkt = Mikrotik(v)
            mk_ips.append(mkt.call_remote("ip/route")) 
            mk_address.append(mkt.call_remote("ip/address"))
    except:
        mk_ips = []
        mk_address = []
    return render(request, 'backend/gen_report.html', {'tbl': table.head(50).to_html(\
        classes=["table", "table-striped", "table-bordered", "align-middle"],\
        table_id="report_tbl"), 'slas': slas, 'report_link': report_link, 'mk_ips': mk_ips, 'mk_address': mk_address, 'vrs': vrs, 'sla_name': sla_name, 'vr_name': vr_name})


@shared_task
def mikro_dump_task():
    #look at only pppoe only ( excloude pppoe5 )
    col  = db['ip_address']
    pprint(SLA_MAP.values())
    for v in SLA_MAP.values():
        for v0 in v.split(','):
            mkt = Mikrotik(v0)
            try:
                mk_address = mkt.call_remote("ip/address") 
                for i in mk_address:
                    temp = {'ts': datetime.utcnow()}
                    temp = temp | i
                    col.insert_one(temp)
                pprint("dump ip complete")
            except Exception as e:
                pprint(e)

@shared_task
def gen_report_task():
    import requests
    import socket
    ip = socket.gethostbyname(socket.gethostname())

    r = requests.get(f'http://web:8000/backend/dump_fixed_results/', timeout=None)
    if r.status_code == 200:
        pprint("dump OK")
    else:
        pprint(f"dump = {r.status_code}")

    # r = requests.get(f'http://web:8000/backend/gen_report/?sla_name=BNG-144-VLAN20-Public&sla_name=BNG-144-VLAN10-Private&sla_name=BRAS-88-VLAN20-Public&sla_name=BRAS-88-VLAN10-Private&genReport=Gen+Report', timeout=None)
    r = requests.get(f'http://web:8000/backend/gen_report/?genReport=Gen+Report', timeout=None)
    if r.status_code == 200:
        pprint("genreport OK")
    else:
        pprint(f"genreport = {r.status_code}")

def list_reports(request):
    import os
    from django.conf import settings
    
    mr = settings.MEDIA_ROOT
    file_list =os.listdir(mr)  

    def get_creation_time(item):
        item_path = os.path.join(mr, item)
        return os.path.getctime(item_path)
    file_list = sorted(file_list, key=get_creation_time, reverse=True)
    return render(request, 'backend/list_reports.html', {'file_list': file_list})