Simple script for reporting tmg events

!ossim_tmg_data_leak_report.md

This is the simple Python script for generate the csv file from OSSIM database with "possible data leak" events collected by my own tmg-web plugin. "possible data leak" events have generated by plugin when large amount of data transfered to external host.

tmgrep.py

#! /usr/bin/python
# -*- coding: cp1251 -*-
# author Eugene Sokolov esguardian@outlook.com
# usage: tmgrep.py number
# where number - integer number of days from today
# this script generate csv list of events "possible data leek" collected by my own tmg-web plugin for OSSIM
# see my gist !ossim_tmg_plugin
# use cp1251 encoding for Russian Windows and Excel
#
import sys
import MySQLdb
import codecs
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])


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'

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

mytz="'+03:00'"
mycharset='cp1251'
colheader='Время;Источник;Пользователь;Внешний URL;Принято байт;Отдано байт наружу;Протокол;Данные WhoIs\n'


conn = MySQLdb.connect(host=dbhost, user=dbuser, passwd=dbpass, db=dbschema, 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, username, userdata1, userdata2, userdata3, userdata4 from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
where="acid_event.plugin_id=9004 and acid_event.plugin_sid=2000"
select="select  " + what + " where " + where + " and " + when + " order by time"
cursor.execute(select)
with codecs.open(outfullpath, 'a', encoding=mycharset) as out:
     out.write(codecs.decode(tabheader + colheader, mycharset)) 
     row = cursor.fetchone() 
     while row:
         out.write(codecs.decode(';'.join([str(c).replace(';',',').strip() for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
out.close()
# --- End of All
conn.close()