search cancel

How to generate a User activity Audit report

book

Article ID: 252746

calendar_today

Updated On:

Products

Data Loss Prevention Enforce

Issue/Introduction

During the DLP product lifetime you may need to generate a user activity report for auditing purposes. While the product does not have such report available out of the box it is possible to generate one using direct Database queries. This article describes the process step by step. The result of the action will be a report of user activity, such as login into the Console or policy modification, in HTML format.

 

The described method requires SQL*Plus tool. It should be already installed by default on the Oracle box where the Database is stored, or the Enforce Server in three tier configurations (Database is stored on a remote server). It can be executed on either. The command will run from any directory as long as system variables for Oracle are configured what should already be in place taking the former into account.

 

Please note that pulling information directly from the database, or preparation of SQL queries, are not in scope of support as-is. This article only presents a concept which may be used to achieve the stated goal.

Cause

Information is needed for auditing.

Resolution

There are 2 approaches the process can be followed. The first (a.) requires more manual work but the result will be the same.

 

a)

1. Login to protect user of the database using the SQL*Plus tool. Example syntax:

sqlplus protect/<password>@protect

Replace <password> with the valid password for the protect user.

2. If the connection was successful the tool should present query entry line as visible on the screenshot below.

3. Execute the commands below one by one in the tool. Replace the path marked in red to one of preference - in the example the report will be written into the default Documents folder of the user logged into the system. The bold section is the actual query to be executed on the Database.

 

SET LINESIZE 4000
SET PAGESIZE 10000
SET LONG 20000
SET ECHO ON
SET FEEDBACK OFF
SET TRIMSPOOL ON
SET SERVEROUTPUT ON
SET TIME ON
SET TIMING ON
ALTER SESSION SET NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss';
SET MARKUP HTML ON ENTMAP ON SPOOL ON PREFORMAT OFF
SPOOL %USERPROFILE%\Documents\AuditReport.html
SELECT * FROM AUDITLOG ORDER BY AUDITLOGID;
SET MARKUP HTML OFF ENTMAP OFF SPOOL OFF PREFORMAT ON
EXIT;

 

The SET commands configure the current session of SQL*Plus. Those do not affect the Database itself and will be cleared as the tool is exited at the last line.

The tool will start executing the query once the SELECT command is launched. It will be indicated by quickly scrolling lines. Based on the size of the database and amount of data stored in the AUDITLOG table it may take a while. Once the query is done the lines will stop scrolling and the query entry line will be displayed. It will now have a "&gt;" suffix caused by the HTML markup configured. 

The report file will be generated with 0 KB at first and will be populated after exiting the tool at the last line.

 

4. Collect the resulting HTML report from the specified location.

b)

1. Copy the commands from point 3 of method a. and save the file with SQL extension. Alternatively download the one attached to this article. Replace the SPOOL path to change the default location and/or modify the query as needed and save your changes. Notepad can be used for this purpose.

2. Open cmd as administrator and change the directory to the location where the SQL file containing the commands was saved.

3. Login to protect user of the database using the SQL*Plus tool. Example syntax:

sqlplus protect/<password>@protect

Replace <password> with the valid password for the protect user.

4. Once successfully logged in run the saved script by using the following syntax. Adjust the name of the script accordingly if you created the file manually. The script file will be fetched from the currently directory in cmd.

@AuditReportScript.SQL

Execution of the query will be indicated by scrolling lines. The tool will exit automatically when done.

5. Collect the resulting HTML report from the specified path.

Additional Information

The query can be adjusted with additional conditions. The example below shows how to pull only information from the last 30 days. 

SELECT * FROM AUDITLOG WHERE TIME > SYSDATE-30 ORDER BY AUDITLOGID;

Attachments

1666598149480__AuditReportScript.zip get_app