Configure the audit report generating with help of Oracle Scheduler

Generate the weekly or monthly report of audit in text file and save it in hard disk for future references.

Audit_weekly_report or Audit_Monthly_report both are Procedure to create the file of audit records weekly or monthly base then you can scheduled it in Oracle Scheduler for generating report weekly or monthly.

Below are the following steps to configure the Audit reports:

1. Create procedure as weekly or monthly as you want. Choose according to your  need. login with sysdba user and create the following procedure:

---For Weekly Report

Create Procedure Audit_weekly_report
as
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 db_user in ('SYSTEM','SYS','SYSMAN','DBWATCH','NETBACKUP')
and  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;
/

OR

------For Monthly Report

Create Procedure Audit_Monthly_report
as
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 db_user in ('SYSTEM','IFSCONS1','IFSCONS2','IFSCONS3','IFSAPP','DBAHISP','SYS','SYSMAN','DBWATCH','NETBACKUP')
and  EXTENDED_TIMESTAMP between trunc(trunc(systimestamp, 'MM') - 1, 'MM')
and trunc(systimestamp, 'MM') - 1;
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;
/


2. Schedule the procedure for execution with Oracle Scheduler

--For Weekly Scheduled

BEGIN
dbms_scheduler.create_job(
job_name => 'AUDIT_WEEKLY'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN  Audit_weekly_report;  END;'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=WEEKLY; BYDAY=FRI; BYHOUR=5; BYMINUTE=0; BYSECOND=0'
,enabled => TRUE
,comments => 'Weekly Audit report generation process');
END;
/

OR

--For Monthly Scheduled Report
BEGIN
dbms_scheduler.create_job(
job_name => 'AUDIT_MONTHLY'
,job_type => 'PLSQL_BLOCK'
,job_action => 'BEGIN  Audit_Monthly_report;  END;'
,start_date => SYSDATE +1/24/59
,repeat_interval => 'FREQ=MONTHLY; BYMONTHDAY=1; BYHOUR=6; BYMINUTE=0; BYSECOND=0'
,enabled => TRUE
,comments => 'Monthly Audit report generation process');
END;
/

3. It will generate report as follows:
Weekly: The report generate at 5AM on every Friday
Monthly: the report at 6 am morning of every first of the month.

4. If you want to drop the jobs, so with following code you can drop the jobs:

EXEC dbms_scheduler.drop_job(job_name => 'AUDIT_MONTHLY');
EXEC dbms_scheduler.drop_job(job_name => 'AUDIT_SENDMAIL');