How to use the auditlog table to find when a policy was changed, and by whom.


Article ID: 160262


Updated On:


Data Loss Prevention Enforce


Is there a way to determine WHEN a policy was modified and by WHOM?


The following script can be used to search the auditlog table for modified policy information.

All that you need is the exact policy name.  The query is case sensitive.  Run script from SQLPlus as user protect.

set long 10000
set pagesize 100
set linesize 88
column time format a20
column username format a20
column action format a20
column entity format a20

select to_char(time,'DD-MON-YYYY HH24:MI:SS') time, username,action, entity, detail
  from Auditlog
where dbms_lob.instr(detail,'Policy Name: &policyname')>0
and time>sysdate-1

For multiple days, change the line "and time>sysdate-1 to some greater number (like "-7" for the past 7 days), or eliminate the line altogether for all history on that policy.  Here is an example of the output:

Enter value for policyname: General Keyc
old   3: where dbms_lob.instr(detail,'Policy Name: &policyname')>0
new   3: where dbms_lob.instr(detail,'Policy Name: General Keyc')>0

TIME                 USERNAME           ACTION               ENTITY
-------------------- ------------------ -------------------- ------------------
12-SEP-2011 12:20:39 Administrator(1)   Modify               Policy
Policy Name: General Keyc
Last Modified: 7/26/11 12:16 PM by Administrator
Description: keyword
Policy Group:  Default Policy Group
Status: Active

Detection Rules
        Rule Name:key word.Type:Keyword Match.Match "tennis".Severity: High.Count all m
atches with at least 2 matches.Look in envelope, body, attachments. Case insensi
tive. Match on whole words only.
Detection Exceptions
        Compound Exception:tennistennis.
                Type:Keyword Match.Match "tennistennis".Count all matches.Look in envelope, su
bject, body, attachments. Case insensitive. Match on whole words only.
                Type:Keyword Match.Match "brundelfly".Count all matches.Look in envelope, subj
ect, body, attachments. Case insensitive. Match on whole words only.
Identity Rules
Identity Exceptions
Response Rules
Response Rule: Block SMTP Email
Network Prevent: Block SMTP Message, All: Set Status: Escalated when Severity  I
s Any Of High
 and  Protocol or Endpoint Monitoring Is Any Of SMTP