Is there a way to see the data from audit fields in a report or portlet?

Products

• Clarity PPM On Premise

Issue/Introduction

Is there any functionality or reports available out of the box to view the data in the audit fields or have the data displayed in a list format in a portlet?

Environment

Release: All Supported releases

Resolution

1. There is nothing out of the box as far as reporting or displaying in list portlets.
2.  A custom portlet could be created with all of the audit fields, and then that data can be exported to excel
   • See communities post: Configure and export "Global Audit Trail" for a possible NSQL query that may help in creating a portlet
3. The Global Audit Trail from the Administration menu could be used, but there is no option to export any of the data.
   • See the above referenced communities post for a possible NSQL query that may be used for a portlet (Referenced as "Audit Trail All V01" in the post)
   • On Premise customers: You can run a query such as the below then copy that data into excel:
     • select * from cmn_audits
     • See communities post: Audit Table Query

Additional Information

Sample of a query that can be used to build the Audit Trail portlet:

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:AUDIT:aud.id:aud_id@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.object_code:aud_object_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.column_name:aud_column_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.table_name:aud_table_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.operation_code:operation_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_before:aud_value_before@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_after:aud_value_after@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_date:aud_last_updated_date@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_by:aud_last_updated_by@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.unique_name:res_unique_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.full_name:res_full_name@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.code:inv_code@,
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.name:inv_name@

FROM
cmn_audits aud,
srm_resources res,
inv_investments inv

where aud.last_updated_by = res.user_id
and aud.object_id = inv.id

and @FILTER@