Clarity

Expand all | Collapse all

Audit Table Query

Jump to Best Answer
  • 1.  Audit Table Query

    Posted 07-28-2014 03:38 PM

    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:

    auditquery.png

     

    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



  • 2.  Re: Audit Table Query

    Posted 07-28-2014 04:32 PM

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

     

    NJ



  • 3.  Re: Audit Table Query

    Posted 07-28-2014 04:35 PM

    Also, check this -

     

    RE: Audit for delete task

     

    NJ



  • 4.  Re: Audit Table Query

    Posted 07-28-2014 05:09 PM


  • 5.  Re: Audit Table Query

    Posted 07-28-2014 05:17 PM

    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?



  • 6.  Re: Audit Table Query

    Posted 07-28-2014 05:29 PM

    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.



  • 7.  Re: Audit Table Query

    Posted 07-28-2014 05:13 PM

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



  • 8.  Re: Audit Table Query

    Posted 07-29-2014 04:20 AM

    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?



  • 9.  Re: Audit Table Query

    Posted 07-29-2014 09:51 AM

    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).



  • 10.  Re: Audit Table Query

    Posted 07-29-2014 12:56 PM

    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



  • 11.  Re: Audit Table Query

    Posted 07-29-2014 03:22 PM

    "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.



  • 12.  Re: Audit Table Query
    Best Answer

    Posted 07-30-2014 12:55 PM

    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.



  • 13.  Re: Audit Table Query

    Posted 07-30-2014 01:38 PM

    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.



  • 14.  Re: Audit Table Query

    Posted 07-30-2014 01:44 PM

    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.



  • 15.  Re: Audit Table Query

    Posted 07-30-2014 04:29 PM

    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))



  • 16.  Re: Audit Table Query

    Posted 07-31-2014 06:31 AM

    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))
    


  • 17.  Re: Audit Table Query

    Posted 07-30-2014 07:10 AM

    "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.



  • 18.  Re: Audit Table Query

    Posted 07-30-2014 08:00 AM

    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.



  • 19.  Re: Audit Table Query

    Posted 07-30-2014 01:22 AM

    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.