!ossim_report_apps_change.md

This is usefull python script for make csv file with the report of the integrity change, application install/uninstall regestered by ossec agent. This is for Russians :) cp1251 encoding used for working with Russian Windows and Excel

apprep.py

#! /usr/bin/python
# -*- coding: cp1251 -*-
# author Eugene Sokolov esguardian@outlook.com
# usage: apprep.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)
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'
when = "timestamp between '" + starttime + "' and '" + endtime + "'"
outfilename='APP-' + 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

# Windows app monitor
tabheader='\n\nМониторинг программ Windows за период ' + startdate + ' - ' + enddate + '\n\n'
what = "convert_tz(timestamp,'+00:00'," + mytz +") as time, src_hostname, substring_index(substring_index(data_payload,'[INIT]',-1),'[END]',1) from acid_event join extra_data on (id=event_id)"
where = "acid_event.plugin_id=7093 and acid_event.plugin_sid=514"
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('Windows application monitor event;' + ';'.join([str(c).replace(';',',') for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
out.close()
# ---
# Windows APP install/uninstall
tabheader = '\n\nУстановка программ Windows за период ' + startdate + ' - ' + enddate + '\n\n'
what = "userdata3 as action, convert_tz(timestamp,'+00:00'," + mytz +") as time, src_hostname as source, concat('MsiInstaller: ',substring_index(substring_index(substring_index(data_payload,'MsiInstaller: ',-1),'[END]',1),' (NULL)',1)) as info from acid_event join extra_data on id=event_id"
where = "acid_event.plugin_id=7006 and (acid_event.plugin_sid=18147 or acid_event.plugin_sid=18146)"
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(';',',') for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
out.close()
# ---
# Linux package install
tabheader='\n\nУстановка пакетов Linux за период ' + startdate + ' - ' + enddate + '\n\n'
what = "convert_tz(timestamp,'+00:00'," + mytz +") as time, src_hostname, substring_index(substring_index(data_payload,'[INIT]',-1),'[END]',1) from acid_event join extra_data on id=event_id"
where = "acid_event.plugin_id=7042 and acid_event.plugin_sid=2902"
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('Linux Package installed;' + ';'.join([str(c).replace(';',',') for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
out.close()
# ---
#  Integrity checksum changed.
tabheader='\n\nИзменения контрольных сумм файлов за период ' + startdate + ' - ' + enddate + '\n\n'
what = "convert_tz(timestamp,'+00:00'," + mytz +") as time, src_hostname, substring_index(substring_index(data_payload,'[INIT]Integrity checksum changed for: ''',-1),'''',1) from acid_event join extra_data on id=event_id"
where = "acid_event.plugin_id=7094 and (acid_event.plugin_sid=550 or acid_event.plugin_sid=551 or acid_event.plugin_sid=552)"
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('Integrity checksum changed.;' + ';'.join([str(c).replace(';',',') for c in row]) + '\n','utf8'))
         row = cursor.fetchone()
out.close()
# ---
# --- End of All
conn.close()