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