!ossim-account-change-report.md

This is usefull Python script to generate csv file with report of user account and group membership change based on ossec agent data stored in AlienVault OSSIM. This is for Russians :) cp1251 used for Russian Windows and Excel

accrep.py

#! /usr/bin/python
# -*- coding: cp1251 -*-
# author Eugene Sokolov esguardian@outlook.com
# usage: accrep.py number
# where number - integer number of days from today for reporting. If omited then will be 1 day.
# I use this script with cron and start at 9:00 Moscow time (6:00 UTC)
# cp1251 charset used 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_userpassword>'
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='AC-' + today.strftime('%Y-%m-%d') + '.csv'
outfullpath='/usr/local/ossim_reports/' + outfilename

mytz="'+03:00'"
mycharset='cp1251'
colheader='Действие;Время;Оператор;Объект;Компьютер;Данные\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 + "'"

# Account change
tabheader='\n\n\nИзменение учетных записей за период ' + startdate + ' - ' + enddate + '\n\n'
what="userdata3 as action, convert_tz(timestamp,'+00:00'," + mytz +") as time, userdata8 as operator, username as object, src_hostname as source, substring_index(substring_index(data_payload,'.inbank.msk: ',-1),' Subject:',1) as info from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
where="acid_event.plugin_id=7043 and (acid_event.plugin_sid=18110 or acid_event.plugin_sid=18112 or acid_event.plugin_sid=18142)"
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()
# ---
# global and universal group change
tabheader='\n\n\nИзменение глобальных групп за период ' + startdate + ' - ' + enddate + '\n\n'

with codecs.open(outfullpath, 'a', encoding=mycharset) as out:
     out.write(codecs.decode(tabheader + colheader, mycharset)) 
     # global group create
     what ="userdata3 as action, convert_tz(timestamp,'+00:00'," + mytz +") as time, username as operator, substring_index(substring_index(data_payload,'Group Name: ',-1),' Group',1) as object, src_hostname as source, substring_index(substring_index(data_payload,'.inbank.msk: ',-1),' Subject:',1) as info from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
     where = "acid_event.plugin_id=7099 and acid_event.plugin_sid=18202"
     select="select " + what + " where " + where + " and " + when  + " order by time"
     cursor.execute(select)
     row = cursor.fetchone() 
     while row:
         out.write(codecs.decode(';'.join([str(c).replace(';',',').strip() for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
     # global group member add or remove and universal group member remove
     what = "userdata3 as action, convert_tz(timestamp,'+00:00'," + mytz +") as time, username as operator, substring_index(substring_index(data_payload,'Group Name: ',-1),' Group',1) as object, src_hostname as source, substring_index(substring_index(data_payload,'CN=',-1),',OU=',1) as info from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
     where = "acid_event.plugin_id=7107 and (acid_event.plugin_sid=18203 or acid_event.plugin_sid=18204 or acid_event.plugin_sid=18215)"
     select="select " + what + " where " + where + " and " + when  + " order by time"
     cursor.execute(select)
     row = cursor.fetchone() 
     while row:
         out.write(codecs.decode(';'.join([str(c).replace(';',',').strip() for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
     # Universal group member add 
     # Ooops, ossim save this record in other format than "member removed" I think this is ossec agent bug
     # so I need to do additional select
     what = "userdata3 as action, convert_tz(timestamp,'+00:00'," + mytz +") as time, substring_index(substring_index(substring_index(data_payload,'Group: Security ID:',-1),' Account Domain:',1),'Account Name: ',-1) as operator, substring_index(substring_index(data_payload,'Account Name: ',-1),' Account',1) as object, src_hostname as source, substring_index(substring_index(data_payload,'CN=',-1),',OU=',1) as info from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
     where = "acid_event.plugin_id=7107 and acid_event.plugin_sid=18214"
     select="select " + what + " where " + where + " and " + when  + " order by time"
     cursor.execute(select)
     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()
# ---
# Local group change
tabheader='\n\n\nИзменение локальных групп за период ' + startdate + ' - ' + enddate + '\n\n'

with codecs.open(outfullpath, 'a', encoding=mycharset) as out:
     out.write(codecs.decode(tabheader + colheader, mycharset)) 
     # in this cause ossec agent don't recognize account name by SID, so I use SID as "member"
     what = "userdata3 as action, convert_tz(timestamp,'+00:00'," + mytz +") as time, username as operator, substring_index(substring_index(data_payload,'Group Name: ',-1),' Group',1) as object, src_hostname as source, substring_index(substring_index(data_payload,'Member: ',-1),' Account',1) as info from acid_event join extra_data on (acid_event.id=extra_data.event_id)"
     where = "acid_event.plugin_id=7107 and (acid_event.plugin_sid=18207 or acid_event.plugin_sid=18208)"
     select="select " + what + " where " + where + " and " + when  + " order by time"
     cursor.execute(select)
     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()