Retrieving audit logs

You can query the database to retrieve audit logs for tasks such as, generating reports and building audit trails. (See User story for generating audit reports in Managed Review & Approval Solution Accelerator 9.5 Solution Guide .) You require access to the database to make SQL calls. It is presumed that you are familiar with working with databases and also have an understanding of the Review, Commenting, and Approval building block database structure. (See Review, Commenting, and Approval database .)

The following tables store audit information:

rca_audit_meta
rca_audit_attribute
rca_audit_module
rca_audit_level
rca_audit_action

Each review is identified by a review ID, which is a unique string. The attributeName of REVIEW_ID is used to pass the review ID to a SQL query.

Before you can make queries to the database, you must have the proper credentials and a tool to access the database. For example, for a LiveCycle Turnkey installation, you can access the MySQL database. The user ID is root and the password is configured as one of the steps when you install the Managed Review & Approval Solution Accelerator. (See Installing and Deploying Solution Accelerators .) For example, use the user name of root and a password of password .

The following examples are common SQL queries you create to get audit log information:

To retrieve all actions for a review:

select * from rca_audit_meta p, rca_audit_attribute c where c.attributeName 
='REVIEW_ID' and c.attributeValue='R-20100728-165828-244-4292' and 
c.auditMetaInfo=p.meta_info_id

To retrieve attributes for a particular action and review:

select * from rca_audit_meta p, rca_audit_attribute c where p.actionName 
='PURGE_REVIEW' and p.meta_info_id IN (select distinct c1.auditMetaInfo from 
rca_audit_attribute c1 where c1.attributeName ='REVIEW_ID' and 
c1.attributeValue='R-20100728-165828-244-4292') AND c.auditMetaInfo=P.meta_info_id

To retrieve the audit logs for a particular review ID:

select p.actionName, c.* from rca_audit_meta p, rca_audit_attribute c where 
p.meta_info_id IN (select distinct c1.auditMetaInfo from rca_audit_attribute c1 where 
c1.attributeName ='REVIEW_ID' and c1.attributeValue='R-20100728-165828-244-4292') 
AND c.auditMetaInfo=P.meta_info_id

To retrieve the audit logs for a specific action:

select p.actionName, c.* from rca_audit_meta p, rca_audit_attribute c where 
p.actionName ='PURGE_REVIEW' and c.auditMetaInfo=p.meta_info_id order by 
auditMetaInfo, attributeName