Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

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

#! /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()
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.