OSSIM report for Cisco AnyConnect

!ossim_remote_access_report.md

This is the Python script for reporting Cisco AnyConnect (ip to user assign) events from OSSIM cisco-asa plugin data as csv file.

2015-07-09. Added ActiveSync events from my activesync-monitor plugin and GeoIP data (geolite2)

Be careful!!! you MUST previosly install geoip2 python module becouse it not installed by default

 
wget https://bootstrap.pypa.io/get-pip.py --no-check-certificate
python get-pip.py
pip install geoip2

and you must download GeoLite2-City database from

http://geolite.maxmind.com/download/geoip/database/GeoLite2-City.mmdb.gz

unzip and place in

/usr/share/geoip/GeoLite2-City.mmdb

rarep.py

#! /usr/bin/python
# -*- coding: cp1251 -*-
# author Eugene Sokolov esguardian@outlook.com
# usage: rarep.py number
# where number - integer number of days from today
# create csv file with report Cisco AnyConnect (ip to user assign) events from OSSIM database 
# data must be collected by cisco-asa plugin
# use cp1251 encoding for Russian Windows and Excel
# 2015-07-09. Added ActiveSync events from my activesync-monitor plugin and GeoIP data (geolite2)
#
# Be carefull!!! you MUST previosly install geoip2 python module
# becouse it not installed by default
# 
# wget https://bootstrap.pypa.io/get-pip.py --no-check-certificate
# python get-pip.py
# pip install geoip2
#
# and you must download GeoLite2-City database from
# http://geolite.maxmind.com/download/geoip/database/GeoLite2-City.mmdb.gz
# unzip and place in
#/usr/share/geoip/GeoLite2-City.mmdb
# 
import sys
import MySQLdb
import codecs
from datetime import date, timedelta
# import GeoIP
import geoip2.database



# Datababe connection config. Use your own data
dbuser='<db_username>'
dbpass='<db_password>'
dbhost='127.0.0.1'
dbshema='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='RA-' + today.strftime('%Y-%m-%d') + '.csv'
outfullpath='/usr/local/ossim_reports/' + outfilename

mytz="'+03:00'"
mycharset='cp1251'


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

reader=geoip2.database.Reader("/usr/share/geoip/GeoLite2-City.mmdb")

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

# start
tabheader='\n\n\nУдаленный доступ через Cisco AnyConnect за период ' + startdate + ' - ' + enddate + '\n\n'
colheader='Время;Источник;Место;Пользователь;Назначенный адрес\n'

what="convert_tz(timestamp,'+00:00'," + mytz +") as time, substring_index(substring_index(userdata4,'IP <',-1),'>',1), username, substring_index(substring_index(userdata4,'IPv4 Address <',-1),'>',1) from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
where="acid_event.plugin_id=1636 and acid_event.plugin_sid=722051"
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: 
         stime = str(row[0]).strip()
         source = str(row[1]).strip()
         response = reader.city(source)
         place =  response.city.name
         if place is None:
             place = response.country.name
         splace = str(place).strip()
         username = str(row[2]).strip()
         local_ip = str(row[3]).strip()
         outstr = stime + ';' + source + ';' + splace + ';' + username + ';' + local_ip + '\n'
         out.write(codecs.decode(outstr,'utf8'))
         row = cursor.fetchone()
out.close()

# Now collect activesync-monitor data

tabheader='\n\n\nДоступ к Exchange ActiveSync за период ' + startdate + ' - ' + enddate + '\n\n'
colheader='Время;Пользователь;Устройство;ИД устройства;Адрес подключения;Место;Событие\n'

what="convert_tz(timestamp,'+00:00'," + mytz +") as time, username, userdata1, userdata2, userdata3, inet_ntoa(conv(HEX(ip_src), 16, 10)) from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
where="acid_event.plugin_id=9007"
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: 
         stime = str(row[0]).strip()
         source = str(row[5]).strip()
         response = reader.city(source)
         place =  response.city.name
         if place is None:
             place = response.country.name
         splace = str(place).strip()
         username = str(row[1]).strip()
         dev_type = str(row[2]).strip()
         dev_id = str(row[3]).strip()
         info = str(row[4]).strip()
         outstr = stime + ';' + username + ';' + dev_type + ';' + dev_id + ';' + source + ';' + place + ';' + info + '\n'
         out.write(codecs.decode(outstr,'utf8'))
         row = cursor.fetchone()
out.close()
# --- End of All
reader.close()
conn.close()