Oracle PLSQL Script for generate csv file from table and mail of Audit Report

PLSQL Script for generate csv file from table and mail of Oracle Audit Report

Audit Report is need to track the database about users activities. Suppose database is used for Financial system then it mandatory to track the things of user which user is update,insert or delete on table for maintaining the security of database.
DBA_COMMON_AUDIT_TRAIL is the view to see the audit information. The following Query in Step 1 is fetching data from view and write it in the CSV format of last 7 days activities:

Step 1: Pl/sql script generate csv file from Oracle audit table. Save it as query.sql file

Declare
cursor c_direct_reports is
select session_id,statementid,extended_timestamp,LOGOFF_TIME,db_user,os_user,userhost,object_schema,
object_name,statement_type,scn,priv_used from dba_common_audit_trail
where EXTENDED_TIMESTAMP between trunc(systimestamp)-7 and trunc(systimestamp);

wfile_handle utl_file.file_type;
v_wstring varchar2(5000);
v_header varchar2(5000);
v_file varchar2(100);
v_date varchar2(20);
v_iname varchar2(20);
begin
select to_char(sysdate,'ddmmyyyy') into v_date from dual;
select instance_name into v_iname from v$instance;

v_header :='SESSION ID'||chr(9)||'STATEMENT ID'||chr(9)||'LOG ON'||chr(9)||'LOG OFF'||chr(9)||'DB USER'||chr(9)||'OS USER'||chr(9)||'USER HOST'||chr(9)||'SCHEMA'||chr(9)||'OBJECT NAME'||chr(9)||'STATEMENT TYPE'||chr(9)||'SCN'||chr(9)||'PRIV USED';
v_file := 'Audit_reports_'||v_iname||'_'||v_date||'.xls';
wfile_handle := utl_file.fopen('AUD_DIR',v_file, 'W');
utl_file.put_line(wfile_handle,v_header);
for Mnrec in c_direct_reports loop
v_wstring := to_char(Mnrec.session_id)||chr(9)||to_char(Mnrec.statementid)||chr(9)||to_char(Mnrec.extended_timestamp,'DD/MM/YYYY HH24:MI:SS')||chr(9)||to_char(Mnrec.LOGOFF_TIME,'DD/MM/YYYY HH24:MI:SS')||chr(9)||to_char(Mnrec.db_user)||chr(9)||to_char(Mnrec.os_user)||chr(9)||to_char(Mnrec.userhost)||chr(9)||to_char(Mnrec.object_schema)||chr(9)||to_char(Mnrec.object_name)||chr(9)||to_char(Mnrec.statement_type)||chr(9)||to_char(Mnrec.scn)||chr(9)||to_char(Mnrec.priv_used);
utl_file.put_line(wfile_handle,v_wstring);
end loop;
utl_file.fclose(wfile_handle);
end;
/

exit

Step 2: Save in bat file and sendmail utility is used for mail

@echo off
REM
set Stamp=%DATE:~-7,2%%DATE:~-10,2%%DATE:~-4%
cd E:\scripts\audit
del *.xls
del *.zip

xcopy /e /y /z "\\10.178.176.69\E$\scripts\audit\Audit_reports_sc7prod_%Stamp%.zip" E:\scripts\audit\*.*
xcopy /e /y /z "\\10.178.176.70\E$\scripts\audit\Audit_reports_sc7stg_%Stamp%.zip" E:\scripts\audit\*.*

E:\scripts\audit\sendEmail\sendEmail -f @gmail.com -t @gmail.com -u IFSDB Weekly Audit Report : %DATE%  -a E:\scripts\audit\Attached1.zip E:\scripts\audit\attached2.zip -s  -o message-content-type=html -o message-file=E:\scripts\audit\message_body.html

Step 3: Save the message body as message_body.html file used by sendemail utility for body of mail:

Hi

Please find the attached audit reports.

Regards
DBA Team

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s