OSSIM Netflow report for OTX matched host communication.

!ossim_NfOTX_report.md

This is the Python script for reporting NfOTX Match events wich collected in OSSIM database by my modification of NfOTX plugin (initialy created by @PacketInspector). The script generate csv file with list of events and list of corresponded Netwlow data, so you can see what happend.

nfotxrep.py

#! /usr/bin/python
# -*- coding: cp1251 -*-
# author Eugene Sokolov esguardian@outlook.com
# version 1.0.0 created at 16:58 08.08.2015
# usage: nfotxrep.py number
# where number - integer number of days from today
# This is the Python script for reporting NfOTX Match events wich collected in OSSIM database 
# by my modification of NfOTX plugin (initialy created by @PacketInspector). 
# The script generate csv file with list of events and list of corresponded Netwlow data, 
# so you can see what happend.
# You can obtain modified NfOTX from my gists collection https://gist.github.com/ESGuardian
#
# cp1251 encoding used for Russian windows and Excel
#
import os
import sys
import MySQLdb
import codecs
import subprocess
from datetime import date, timedelta


# Datababe connection config. Use your own data
dbuser='<db_username>'
dbpass='<db_password>'
dbhost='127.0.0.1'
dbschema='alienvault_siem'
# --- End of Database config

# ---- Init 


period=1
if len(sys.argv) > 1:
    period=int(sys.argv[1])

# set time interval for mySQL Select
today=date.today()
enddate=today.strftime('%Y:%m:%d')
endtime=enddate + ' 06:00:00' # UTC time
startdate=(today - timedelta(days=period)).strftime('%Y:%m:%d')
starttime=startdate + ' 06:00:00'

#set time interval for nfdump search
nfdump_end = today.strftime('%Y/%m/%d') + '.09:00:00'
nfdump_start = (today - timedelta(days=period)).strftime('%Y/%m/%d') + '.09:00:00'
#set path to flow cache
nfdir = '/var/cache/nfdump/flows/live'
# ----- end of nfdump setting

outfilename='OTX-' + today.strftime('%Y-%m-%d') + '.csv'
outfullpath='/usr/local/ossim_reports/' + outfilename

mytz="'+03:00'"
mycharset='cp1251'
colheader='Время;Источник;Внешний IP;Репутация хоста\n'
my_rep_data = {}
if os.path.isfile('/etc/my_ossim/my_reputation.data'):
    with codecs.open('/etc/my_ossim/my_reputation.data', 'r', encoding=mycharset) as f:
        for line in f:
            if '#' in line:
                (ip,rep) = line.strip().split('#')
                my_rep_data[ip] = rep.strip()
    f.close() 

conn = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpass, db=dbshema, charset='utf8') 
cursor = conn.cursor() 

# ---- End of Init
when = "timestamp between '" + starttime + "' and '" + endtime + "'"

# start
tabheader='\n\n\nКоммуникации с известными вредоносными хостами за период ' + startdate + ' - ' + enddate + '\n\n'
what="convert_tz(timestamp,'+00:00'," + mytz +") as time, src_hostname, substring_index(substring_index(data_payload,'-> ',-1),':',1) as dst_ip, rep_act_dst from acid_event join extra_data on acid_event.id=extra_data.event_id left join reputation_data on id=reputation_data.event_id"
where="acid_event.plugin_id=90011 and acid_event.plugin_sid=1"
select="select  " + what + " where " + where + " and " + when + " order by time"
cursor.execute(select)
list=[] # create list of returned data for later use
with codecs.open(outfullpath, 'a', encoding=mycharset) as out:
    out.write(codecs.decode(tabheader + colheader, mycharset)) 
    row = cursor.fetchone() 
    while row:
        dst = row[2].strip()       
        if row[3] is None:
            if dst in my_rep_data:
                rep = my_rep_data[dst]
            else:
                rep = 'None'
        else:
            rep = str(row[3]) 
        if rep.lower() != 'false':
            outstr = str(row[0]).replace(';',',').strip()
            outstr = outstr + ';' + str(row[1]).replace(';',',').strip() 
            outstr = outstr + ';' + dst
            outstr = outstr + ';' + rep            
            list.append(outstr)
            out.write(codecs.decode(outstr + '\n','utf8'))
        row = cursor.fetchone()
     # and now add to the file netflow data for each event
    for item in list:
        (time,src,dst,rep)=item.split(';')
        # prepare nfdump command
        nf_dump_cmd = "/usr/bin/nfdump -R " + nfdir + " -q -m -t "+ nfdump_start + "-" + nfdump_end + " -o line " + "'ip " + dst + "'"
        p = subprocess.Popen (nf_dump_cmd, stdout=subprocess.PIPE, shell=True)
        (output,err) = p.communicate()
        p_stutus = p.wait()
        tabheader = '\n\n\nИнформация Netflow для ' + dst + ' : ' + rep + '\n'
        colheader = 'Время;Период;Протокол;Источник;Получатель;Пакетов;Байт;Потоков\n'
        out.write(codecs.decode(tabheader + colheader, mycharset)) 
        for line in output.splitlines():
            fields = line.rstrip().split()
            stime = fields[0] + ' ' + fields[1]
            sduration = fields[2]
            sproto = fields[3]
            ssrc=fields[4]
            sdst = fields[6]
            spackets = fields[7]
            sbytes = fields[8]
            sflows = fields[9]
            outstr=stime + ';' + sduration + ';' + sproto + ';' + ssrc + ';' + sdst + ';' + spackets + ';' + sbytes + ';' + sflows
            out.write(codecs.decode(outstr + '\n','utf8'))

out.close()
# --- End of All
conn.close()