Audit Table Query

[Anon Anon]

I am trying to write a query that will produce a list of records deleted in the last 24 hours. I notice that the LAST_UPDATED_DATE field seems to be populated with when the object (let's use a Status Report for example) was last modified, not whe the Status itself was deleted. In Clarity 13.0.

 

Here is a screenshot:

 

I need to know when the record was deleted for this query to work correctly. I notice in the Audit trail on the Project that the date the delete took place is captured, so it has to be in there somewhere.

 

 

Ideas?

 

 

Thanks,

 

Ben

[navzjoshi00]

In the cmn_audits, check the operation = D (Delete)

 

NJ

[navzjoshi00]

Also, check this -

 

RE: Audit for delete task

 

NJ

[Deactivated User]

... and maybe also this

Configure and export "Global Audit Trail"

[Deactivated User]

So you need that in query results or export to Excel from a portlet and looking at the audit trail in the GUI is not enough?

[Anon Anon]

Correct, I am actually trying to export this data via Data Extractor. I know the date is stored somewhere (as it shows in the GUI) and am wondering if anyone knows the table/column in which to find it.

 

Thanks for your quick responses.

[Anon Anon]

If you see in the screenshot, I already set aud.operation_code = 'D'.

[Anon Anon]

Sorry I'm a little confused - you are after the modified date of the records not the delete date, i.e. the record has been deleted after being modified? Then that would just be earlier records in CMN_AUDITS (ones with operation_code = 'U' ) - or am I getting very confused about what you are looking for?

[Anon Anon]

Dave,

 

Sorry to confuse you. Here's further clarification as to what I'm looking for.

 

I need the query results to provide the date that the record was deleted. For example, I go in to Clarity and delete a Status Report. The Status Report name field is an audited attribute upon delete operation. In my mind, when I return the row for the delete operation (operation_code='D') for this Status Report, the last_updated_date should be the date the Status Report got deleted. Instead, last_updated_date is showing the date the Report was last modified (or the date it was created, in this case. See how created_date matches last_updated_date in the screenshot? I deleted the Status Report in the highlighted row on 29-JUL-14).

[Anon Anon]

I think this is related to a known issue. Something similar happens when you edit OBS on project. Refer to the below -

 

Defect No. CLRT-74259

 

Title: Audit fields Changed By and Date Timestamp for OBS incorrect

 

Steps to Reproduce:

1. Go to Admin Tool --> Resources

 

2. Create 2 new resources and add admin access rights to both

 

3. Go to Admin Tool -> Objects -> Project -> Audit Trail

 

4. Set up Attribute Audit for Department OBS

 

5. Login as Resource 1, navigate to a project and update the Department OBS

 

value

 

6. Check the Audit Trail tab for the Project. For the first time the

 

attribute is changed, the audit trail shows all the values (Attribute Name,

 

Operation, Changed By, Old Value, New Value and Date) correctly

 

7. Now logout as the Resource 1 and login as Resource 2

 

8. Navigate to the same project in Step 5 and update the Department OBS

 

value

 

9. Check the Audit Trail tab for the Project. The 'Changed By' shows

 

incorrectly as Resource 1 and 'Date' shows incorrectly as the Date and

 

Timestamp of Previous Update.

 

Expected Result:  The Changed By and Date Timestamp should show the values

 

from the second update

 

Actual Result:  The Changed By and Date Timestamp shows the values from the

 

first update

[Deactivated User]

"I notice in the Audit trail on the Project that the date the delete took place is captured, so it has to be in there somewhere."

 

Have you looked at the ER diagrams in the Tech Ref?

the CMN_AUD and ODF tables are also in the Tech ref.

[Anon Anon]

Urmas,

 

You were on the right track with the ODF tables.

 

Adding the below join to the query, and then selecting the odf table's LAST_UPDATED_DATE in the where clause worked as I needed.

         

          FROM cmn_auits aud

          inner join odf_aud_obj_inst_context oao on aud.object_id= oao.object_instance_id

Thanks for everyone's help! I will post the query if there is any interest.

[Deactivated User]

Thanks for the update.

Yes please definitely post the query.

 

Later on today I was thinking further....

Is auditing the deletion of the name gonna work properly as the cmn_audits.last_updated. stores the last updated but not when deleted?

Would auditing ID be any different?

 

Is this behaviour for subobjects only? because I was under the impression that the queries in the links worked for masterobjects also for delete.

 

Can hardly wait for your query.

[Deactivated User]

On the other hand the brute force way would be to put the audit on delete, do a delete and the look at all the possible audit tables and see if the correct timestamp is somewhere.

[Anon Anon]

Urmas,

 

The query is at the bottom here. If anyone knows how to inlay code or have the forum recognize code let me know. I'm new here

 

Your point is correct on the "brute force" method. I actually got some assistance from CA, and it was the odf_aud_obj_inst_context table that contained when the row was actually deleted. A simple join to that table allows me to use the odf table LAST_UPDATED_DATE in the parameter.

 

For context, this is used as part of Clarity Data Extractor PWP.

 

 

SELECT
aud.object_id Internal_ID,
CASE aud.object_code WHEN 'cop_prj_statusrpt' THEN 'status' ELSE aud.object_code END AS Table_Name

 

FROM cmn_audits aud
inner join odf_aud_obj_inst_context oao on aud.object_id= oao.object_instance_id

WHERE aud.operation_code='D' and (aud.attribute_code='name' or aud.attribute_code='prname') and (aud.object_code='risk' or aud.object_code='issue' or aud.object_code='change' or aud.object_code='project' or aud.object_code='cop_prj_statusrpt' or aud.object_code='task')
and
((:param_hours>0 AND
    (oao.LAST_UPDATED_DATE)>(SYSDATE)-(:param_hours/24))
    OR
    (:param_hours=0 AND 1=1))

[Deactivated User]

If you are looking for something like what it below for posting code you you click Andvaced editor and then in the tool bar beside theemoticon click the double arrow and select Syntax highlighting for SLQ

SELECT
aud.object_id Internal_ID,
CASE aud.object_code WHEN 'cop_prj_statusrpt' THEN 'status' ELSE 

aud.object_code END AS Table_Name



FROM cmn_audits aud
inner join odf_aud_obj_inst_context oao on aud.object_id= 

oao.object_instance_id


WHERE aud.operation_code='D' and (aud.attribute_code='name' or 

aud.attribute_code='prname') and (aud.object_code='risk' or 

aud.object_code='issue' or aud.object_code='change' or 

aud.object_code='project' or aud.object_code='cop_prj_statusrpt' or 

aud.object_code='task')
and
((:param_hours>0 AND
    (oao.LAST_UPDATED_DATE)>(SYSDATE)-(:param_hours/24))
    OR
    (:param_hours=0 AND 1=1))

[Deactivated User]

"I notice in the Audit trail on the Project that the date the delete took place is captured, so it has to be in there somewhere."

says that the correct audit trail value is there so it is not the same problem.

[Deactivated User]

1. Put the activity trace on and see what the query is.

2. If it the last updated column  in pre v13. version open a case with support.

3. Brig it up in the next Office hours session August 14.

[Aditya Angrish]

If you have a Audit set to capture data on delete (the last section on the audit screen), you can just refer to cmn_audits. If you don't have audits set for delete, the data is gone.