Basically oracle audit entries stored as .aud files in my AIX system
/oracle/SBX/saptrace/audit/
The entries of these files looks something like this :
Tue Jul 2 08:41:53 2013 +02:00
LENGTH : '159'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'orasbx'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1854349635'
Tue Jul 2 08:41:53 2013 +02:00
LENGTH : '159'
ACTION :[6] 'COMMIT'
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'orasbx'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1854349635'
Tue Jul 2 08:42:16 2013 +02:00
LENGTH : '222'
ACTION :[68] 'update SAPPRD.USR02 set uflag=64 where BNAME='CANAS' and MANDT='000''
DATABASE USER:[1] '/'
PRIVILEGE :[6] 'SYSDBA'
CLIENT USER:[6] 'orasbx'
CLIENT TERMINAL:[5] 'pts/0'
STATUS:[1] '0'
DBID:[10] '1854349635'
Now i have schedule a shell script in crontab for it to run every three hours.
Script is something like this:
#/bin/sh
grep -i USR02 /oracle/SBX/saptrace/audit/*.aud > /EDB/log/check_audit_dest.log
grep -i USH02 /oracle/SBX/saptrace/audit/*.aud >> /EDB/log/check_audit_dest.log
grep -i TCURR /oracle/SBX/saptrace/audit/*.aud >> /EDB/log/check_audit_dest.log
grep -i REGUH /oracle/SBX/saptrace/audit/*.aud >> /EDB/log/check_audit_dest.log
grep -i LFB1 /oracle/SBX/saptrace/audit/*.aud >> /EDB/log/check_audit_dest.log
grep -i LFA1 /oracle/SBX/saptrace/audit/*.aud >> /EDB/log/check_audit_dest.logs
What this script does is if there is any operations these tables it log that line into /EDB/log/check_audit_dest.log
Like this:
# cat /EDB/log/check_audit_dest.log
/oracle/SBX/saptrace/audit/sbx_ora_13828348_1.aud:ACTION :[68] 'update SAPPRD.USR02 set uflag=64 where BNAME='CANAS' and MANDT='000''
/oracle/SBX/saptrace/audit/sbx_ora_8847374_1.aud:ACTION :[67] 'update SAPPRD.USR02 set uflag=0 where BNAME='CANAS' and MANDT='000''
Now what i want is apart from that line i also want first line of every entry to be log in that log file(for example:Tue Jul 2 08:42:16 2013 +02:00).
Thank you
Given the sample input you posted, all you need is:
$ awk -v RS= -F'\n' '/USR02|USH02|TCURR|REGUH|LFB1|LFA1/ {print FILENAME, $1, $3}' file
file Tue Jul 2 08:42:16 2013 +02:00 ACTION :[68] 'update SAPPRD.USR02 set uflag=64 where BNAME='CANAS' and MANDT='000''
If that doesn't do it, post some more representative input and expected output.
Explanation as requested below by fedorqui
RS= => records are separated by blank lines-F'\n' => fields within a record are separated by newlines/USR02|USH02|TCURR|REGUH|LFB1|LFA1/ => look for records that
contain any of the |-separated stringsprint FILENAME, $1, $3 => print the name of the current file, and the 1st and 3rd lines/fields of the current record, the 1st line being the date and the 3rd being the ACTION.
Ed Morton gave an awk solution with setting RS and FS.
Here is another solution with awk:
awk '!$0{delete a;next}{a[NR]=$0}/USR02|USH02|TCURR|REGUH|LFB1|LFA1/{print a[NR-2];print $0}' file
the first part !$0{delete a;next} could be removed if your file is not a huge monster:
awk '{a[NR]=$0}/USR02|USH02|TCURR|REGUH|LFB1|LFA1/{print a[NR-2];print $0}' file
output with your input:
kent$ awk '{a[NR]=$0}/USR02|USH02|TCURR|REGUH|LFB1|LFA1/{print a[NR-2];print $0}' f
Tue Jul 2 08:42:16 2013 +02:00
ACTION :[68] 'update SAPPRD.USR02 set uflag=64 where BNAME='CANAS' and MANDT='000''
Why don't you do it like this?
grep -Ei "USR02|USH02|TCURR|REGUH|LFB1|LFA1" -B 2 file
grep -e admits multiple parameters. So instead of grep -i ONE and then grep -i TWO, you can do grep -Ei "ONE|TWO".
With -B 2 what you do is to print the two previous lines of the matched line.
Test
All together will make the following:
$ grep -Ei "USR02|USH02|TCURR|REGUH|LFB1|LFA1" -B 2 file
Tue Jul 2 08:42:16 2013 +02:00
LENGTH : '222'
ACTION :[68] 'update SAPPRD.USR02 set uflag=64 where BNAME='CANAS' and MANDT='000''
and if you do not want the line in the middle,
$ grep -Ei "USR02|USH02|TCURR|REGUH|LFB1|LFA1" -B 2 file | grep -v LENGTH
Tue Jul 2 08:42:16 2013 +02:00
ACTION :[68] 'update SAPPRD.USR02 set uflag=64 where BNAME='CANAS' and MANDT='000'
