Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

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

#! /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()
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.