Source

hbwebui / hbwebui / views.py

from django.http import HttpResponse
from django.template import Context, RequestContext, loader

from django.contrib.auth.decorators import login_required
from django.dispatch import receiver
from django.contrib.auth.signals import user_logged_in

from django.db import connections
from django.core import serializers
import json, csv, calendar
from django.contrib import auth
from models import Ident, IdentToGroupMapping

from decorators import login_required_simple, ident_required, ident_required_simple
import util

import queries

login_url = '/hbwebui/login'

@login_required(login_url=login_url)
@ident_required
def dashboard(request):
    idents = request.session['idents']
    
    t = loader.get_template('dashboard.html')
    c = RequestContext(request, {
            'page': 'dashboard',
            'idents': json.dumps(idents),
    })
    return HttpResponse(t.render(c))

@login_required(login_url=login_url)
@ident_required
def stats_binaries(request):
    idents = request.session['idents']
    
    q = queries.Stats_Binaries()
    q.filter_ident(idents)
    q.order_n_count(desc=True)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = []
    for d in qr:
        o = {}
        o['md5'] = d['md5']
        o['n_count'] = d['n_count']
        o['n_ident'] = d['n_ident']
        o['n_source_ip'] = d['n_source_ip']
        o['n_target_port'] = d['n_target_port']
        o['ts_last'] = calendar.timegm(d['ts_last'].utctimetuple())
        o['ts_first'] = calendar.timegm(d['ts_first'].utctimetuple())
        r.append(o)
    
    t = loader.get_template('stats_binaries.html')
    c = RequestContext(request, {
            'page': 'stats_binaries',
            'idents': json.dumps(idents),
            'r': json.dumps(r)
    })
    return HttpResponse(t.render(c))

@login_required_simple
@ident_required_simple
def r_stats_binaries(request):
    idents = request.session['idents']
    
    q = queries.Stats_Binaries()
    q.filter_ident(idents)
    q.order_n_count(desc=True)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = []
    for d in qr:
        o = {}
        o['md5'] = d['md5']
        o['n_count'] = d['n_count']
        o['n_ident'] = d['n_ident']
        o['n_source_ip'] = d['n_source_ip']
        o['n_target_port'] = d['n_target_port']
        o['ts_last'] = calendar.timegm(d['ts_last'].utctimetuple())
        o['ts_first'] = calendar.timegm(d['ts_first'].utctimetuple())
        r.append(o)
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

@login_required_simple
@ident_required_simple
def r_stats_binaries_summary(request):
    idents = request.session['idents']
    
    q = queries.Stats_Binaries_Summary()
    q.filter_ident(idents)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = {}
    r['n_count'] = qr[0]['n_count']
    r['n_ident'] = qr[0]['n_ident']
    r['n_source_ip'] = qr[0]['n_source_ip']
    r['n_target_port'] = qr[0]['n_target_port']
    r['ts_last'] = calendar.timegm(qr[0]['ts_last'].utctimetuple())
    r['ts_first'] = calendar.timegm(qr[0]['ts_first'].utctimetuple())
    r['n_total'] = qr[0]['n_total']
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

@login_required(login_url=login_url)
@ident_required
def stats_source_ips(request):
    idents = request.session['idents']
        
    t = loader.get_template('stats_source_ips.html')
    c = RequestContext(request, {
            'page': 'stats_source_ips',
            'idents': json.dumps(idents),
    })
    return HttpResponse(t.render(c))

@login_required_simple
@ident_required_simple
def r_stats_source_ips(request):
    idents = request.session['idents']
    
    q = queries.Stats_Source_IPs()
    q.filter_ident(idents)
    q.order_n_count(desc=True)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = []
    for d in qr:
        o = {}
        o['source_ip'] = d['source_ip']
        o['cc'] = d['cc']
        o['asn'] = d['asn']
        o['n_count'] = d['n_count']
        o['n_ident'] = d['n_ident']
        o['n_binary'] = d['n_binary']
        o['n_target_port'] = d['n_target_port']
        o['ts_last'] = calendar.timegm(d['ts_last'].utctimetuple())
        o['ts_first'] = calendar.timegm(d['ts_first'].utctimetuple())
        r.append(o)
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

@login_required_simple
@ident_required_simple
def r_stats_source_ips_summary(request):
    idents = request.session['idents']
    
    q = queries.Stats_Source_IPs_Summary()
    q.filter_ident(idents)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = {}
    r['n_count'] = qr[0]['n_count']
    r['n_cc'] = qr[0]['n_cc']
    r['n_asn'] = qr[0]['n_asn']
    r['n_ident'] = qr[0]['n_ident']
    r['n_binary'] = qr[0]['n_binary']
    r['n_target_port'] = qr[0]['n_target_port']
    r['ts_last'] = calendar.timegm(qr[0]['ts_last'].utctimetuple())
    r['ts_first'] = calendar.timegm(qr[0]['ts_first'].utctimetuple())
    r['n_total'] = qr[0]['n_total']
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

@login_required(login_url=login_url)
@ident_required
def stats_asns(request):
    idents = request.session['idents']
        
    t = loader.get_template('stats_asns.html')
    c = RequestContext(request, {
            'page': 'stats_asns',
            'idents': json.dumps(idents),
    })
    return HttpResponse(t.render(c))

@login_required_simple
@ident_required_simple
def r_stats_asns(request):
    idents = request.session['idents']
    
    q = queries.Stats_ASNs()
    q.filter_ident(idents)
    q.order_n_count(desc=True)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = []
    for d in qr:
        o = {}
        o['as_name'] = d['as_name']
        o['asn'] = d['asn']
        o['cc'] = d['cc']
        o['n_count'] = d['n_count']
        o['n_ident'] = d['n_ident']
        o['n_binary'] = d['n_binary']
        o['n_source_ip'] = d['n_source_ip']
        o['n_target_port'] = d['n_target_port']
        o['ts_last'] = calendar.timegm(d['ts_last'].utctimetuple())
        o['ts_first'] = calendar.timegm(d['ts_first'].utctimetuple())
        r.append(o)
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

@login_required_simple
@ident_required_simple
def r_stats_asns_summary(request):
    idents = request.session['idents']
    
    q = queries.Stats_ASNs_Summary()
    q.filter_ident(idents)
    cursor = q.execute()
    qr = util.dictfetchall(cursor)
    
    r = {}
    r['n_count'] = qr[0]['n_count']
    r['n_cc'] = qr[0]['n_cc']
    r['n_source_ip'] = qr[0]['n_source_ip']
    r['n_ident'] = qr[0]['n_ident']
    r['n_binary'] = qr[0]['n_binary']
    r['n_target_port'] = qr[0]['n_target_port']
    r['ts_last'] = calendar.timegm(qr[0]['ts_last'].utctimetuple())
    r['ts_first'] = calendar.timegm(qr[0]['ts_first'].utctimetuple())
    r['n_total'] = qr[0]['n_total']
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

# example
@login_required_simple
@ident_required_simple
def myidents(request):
    idents = request.session['idents']
    
    response = HttpResponse(mimetype='application/json')
    json.dump(idents, response)
        
    return response

#example
@login_required_simple
@ident_required_simple
def sensoractivity(request, hours):
    idents = request.session['idents']
    
    cursor = connections['hbstats'].cursor()
    cursor.execute(
         """WITH tr AS
            (SELECT max(ts_min) AS tr_from, max(ts_min) - interval '%s hour' AS tr_until from ts_main_min)
            
            SELECT
                ident, sum(t.n_count)::bigint AS n_count,
                max(ts_min) as ts_last, min(ts_min) as ts_first,
                now()
            FROM
                (SELECT
                     ts_min, agg_id, n_count
                 FROM ts_main_min
                 WHERE
                     ts_min <= (SELECT tr_from FROM tr) AND ts_min > (SELECT tr_until FROM tr) AND
                     agg_id IN (SELECT agg_id from agg_main WHERE ident_id IN (SELECT id FROM dim_ident WHERE ident IN %s))
                ) as t
            JOIN
                agg_main ON t.agg_id = agg_main.agg_id
            JOIN
                dim_ident ON ident_id = dim_ident.id
                
            GROUP BY ident ORDER BY ident, n_count desc""",
            [hours, tuple(idents)]
    )
    qr = util.dictfetchall(cursor)
    r = []
    for d in qr:
        o = {}
        o['ident'] = d['ident']
        o['n_count'] = d['n_count']
        o['ts_last'] = d['ts_last'].isoformat()
        o['ts_first'] = d['ts_first'].isoformat()
        r.append(o)
    
    response = HttpResponse(mimetype='application/json')
    json.dump(r, response)
    
    return response

# example
@login_required_simple
@ident_required_simple
def ts_sensoractivity_x(request, hours):
    idents = request.session['idents']
    
    cursor = connections['hbstats'].cursor()
    cursor.execute(
        """WITH tr AS
            (SELECT max(ts_min) AS tr_from, max(ts_min) - interval '%s hour' AS tr_until from ts_main_min)
            
            SELECT
                extract(epoch from ts_min)::bigint AS ts_min,
                ident, md5, source_ip,
                sum(t.n_count) AS n_count       
            FROM
                (SELECT
                     ts_min, agg_id, n_count
                 FROM ts_main_min
                 WHERE
                     ts_min <= (SELECT tr_from FROM tr) AND ts_min > (SELECT tr_until FROM tr) AND
                     agg_id IN (SELECT agg_id from agg_main WHERE ident_id IN (SELECT id FROM dim_ident WHERE ident IN %s))
                ) as t
            JOIN
                agg_main ON t.agg_id = agg_main.agg_id
            JOIN
                dim_ident ON ident_id = dim_ident.id
            JOIN
                dim_binary ON binary_id = dim_binary.id
                
            GROUP BY ts_min, ident, md5, source_ip ORDER BY ts_min""",
            [hours, tuple(idents)]
    )
    
    response = HttpResponse(mimetype='text/plain')
    
    csvwriter = csv.writer(response)
    csvwriter.writerow(['ts_min', 'ident', 'md5', 'source_ip', 'n_count'])
    
    while True:
        rows = cursor.fetchmany()
        if not rows: break
        for row in rows:
            csvwriter.writerow([row[0], row[1], row[2], row[3], row[4]])
    
    return HttpResponse(response);


@receiver(user_logged_in)
def session_set_idents(sender, **kwargs):
    request = kwargs['request']
    idents = util.get_idents(request.user)
    if idents:
        request.session['idents'] = idents