1571513 - How to see Report Names in Audit Crystal Reports.

Symptom

There is no Activity universe based on audit database in Crystal Report server.
Need to know Report Name and Duration Time from the Audit database.

Environment

Enterprise : CRystal Reports 2008 server
Designer : Crystal Reports 2008
Audit database : SQL SERVER 2005, Oracle

Cause

Audit database contains only report CUID.

Resolution

When we use SQL SERVER 2005 database create a report using this query in ADD command, you can get the report names and other fields in Crystal Reports.

SELECT

CONVERT(DERIVED_OBJECT_NAME.Detail_Text, CHAR) ,

TRIM(TRAILING '\0' FROM CONVERT(DETAIL_TYPE.Detail_Type_Description, CHAR)) ,

AUDIT_EVENT.Duration,

AUDIT_EVENT.Object_CUID

FROM

DETAIL_TYPE INNER JOIN AUDIT_DETAIL ON (DETAIL_TYPE.Detail_Type_ID=AUDIT_DETAIL.Detail_Type_ID)

INNER JOIN AUDIT_EVENT ON (AUDIT_DETAIL.Event_ID=AUDIT_EVENT.Event_ID and AUDIT_DETAIL.Server_CUID=AUDIT_EVENT.Server_CUID)

INNER JOIN ( select

AUDIT_EVENT.Server_CUID, AUDIT_EVENT.Event_ID, AUDIT_DETAIL.Detail_Text as Detail_Text

from

AUDIT_EVENT, AUDIT_DETAIL

where

(AUDIT_EVENT.Server_CUID = AUDIT_DETAIL.Server_CUID) and

(AUDIT_EVENT.Event_ID = AUDIT_DETAIL.Event_ID) and

(AUDIT_DETAIL.Detail_Type_ID = 3)

) DERIVED_OBJECT_NAME ON (AUDIT_EVENT.Event_ID=DERIVED_OBJECT_NAME.Event_ID and AUDIT_EVENT.Server_CUID=DERIVED_OBJECT_NAME.Server_CUID)

When we use ORACLE database create a report using this query in ADD command, you can get the report names and other fields in Crystal Reports.