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
Component: ODSTU

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

See KB 141061 - How to search for known Clarity PPM Issues using Self Service

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

SELECT
@SELECT:DIM:USER_DEF:IMPLIED:AUDIT:aud.id:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.object_code:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.column_name:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.table_name:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.operation_code:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_before:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_after:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_date:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_by:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.unique_name:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.full_name:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.code:[email protected],
@SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.name:[email protected]

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 @[email protected]