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.
In the cmn_audits, check the operation = D (Delete)
Also, check this -
RE: Audit for delete task
... and maybe also this
Configure and export "Global Audit Trail"
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?
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.
If you see in the screenshot, I already set aud.operation_code = 'D'.
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?
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).
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
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
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
"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.
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.
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.
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.
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.
SELECTaud.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 audinner 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))
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
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=
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
(:param_hours=0 AND 1=1))
says that the correct audit trail value is there so it is not the same problem.
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.
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.