Clarity

Expand all | Collapse all

Configure and export "Global Audit Trail"

Jump to Best Answer
  • 1.  Configure and export "Global Audit Trail"

    Posted 11-23-2012 11:41 AM
    Hi all.

    Is there any possibility to configure and/or export to Excel the "Global Audit Trail"

    Thanks.


  • 2.  RE: Configure and export "Global Audit Trail"

    Posted 11-23-2012 12:12 PM
    What do you mean with configure?

    There is no export option for the standard audit trail as displayed in the GUI.
    If you create a query base portlet you can export the results.

    There is discussion in the thread
    Use of the Audit Trail in Clarity
    (but no solution)

    See also
    I am sorry - Another Newbie NSQL Question
    2302692

    Martti K.


  • 3.  Re: Configure and export "Global Audit Trail"

    Posted 08-26-2016 02:14 AM

    The link has been jived

    ON Aug 26 2016 the thread is
    https://communities.ca.com/message/2302692?commentID=2302692#comment-2302692 



  • 4.  RE: Configure and export "Global Audit Trail"
    Best Answer

    Posted 11-23-2012 03:03 PM
    Below are two NSQL Queries that I have used to look at Audit Trail information in a Portlet view. Once the information is in a portlet - you can export to an excel spreadsheet.

    The NSQL do not yet join with the Attribute Table to display the name of the attribute value ( that is a work in progress ) - but they may assist your efforts.

    Audit Trail All V01

    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:AUDIT:aud.id:aud_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.object_code:aud_object_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.column_name:aud_column_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.table_name:aud_table_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.operation_code:operation_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_before:aud_value_before@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_after:aud_value_after@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_date:aud_last_updated_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_by:aud_last_updated_by@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.unique_name:res_unique_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.full_name:res_full_name@

    FROM
    cmn_audits aud,
    srm_resources res

    WHERE
    aud.last_updated_by = res.user_id


    Audit Trail Investments V01
    SELECT
    @SELECT:DIM:USER_DEF:IMPLIED:AUDIT:aud.id:aud_id@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.object_code:aud_object_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.column_name:aud_column_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.table_name:aud_table_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.operation_code:operation_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_before:aud_value_before@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.value_after:aud_value_after@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_date:aud_last_updated_date@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:aud.last_updated_by:aud_last_updated_by@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.unique_name:res_unique_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:res.full_name:res_full_name@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.code:inv_code@,
    @SELECT:DIM_PROP:USER_DEF:IMPLIED:AUDIT:inv.name:inv_name@

    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 @FILTER@


  • 5.  RE: Configure and export "Global Audit Trail"

    Posted 11-26-2012 04:18 AM
      |   view attached
    For an worthy and useful audit portlet need to do some work in Nsql.The same like OOB but with some more benefits.

    To be more comprehensive In filter you can show the object code (resource,investment,task,hiearrachy etc) and also should show the audited object attributes.
    so users can filter and choose whatever they want. (Choose project object and then choose attribute status) and check who had modified it.
    In the list view also it wil be some what challenging to show the results based on choosen parameters.Lot of case or decode conditions are needed in nsql.

    You should also have an agreement with business that the audit records should be kept only for an certain period etc (purge the records ) as this will have millions of records.
    And also auditing an attribute means an trigger will be enforced so we must be careful that unless until an strong justification we should not audit attributes,
    otherwise it might impact system performance.

    1 big drawback we faced in current audit trail is mentioned in the below post

    98900392


    cheers,
    sundar


  • 6.  RE: Configure and export "Global Audit Trail"

    Posted 10-24-2013 10:09 AM
    Hello,

    Question related to audit trial. Are there any best practices for audit trail? I am wondering if it is OK to audit a large String attribute.

    Thank you,
    Rajani.


  • 7.  RE: Configure and export "Global Audit Trail"

    Posted 10-24-2013 10:20 AM

    Rajani wrote:

    I am wondering if it is OK to audit a large String attribute.
    A large "string" or a "large string"?

    I don't believe we can audit "large strings" (implemented as binary objects in the database).


  • 8.  RE: Configure and export "Global Audit Trail"

    Posted 10-24-2013 11:14 AM
    Thanks Dave!


  • 9.  RE: Configure and export "Global Audit Trail"

    Posted 10-30-2013 01:43 PM

    I don't think either that you can, but you can easily verify it by just looking at the attributes available for auditing.

     

    Martti K:



  • 10.  Re: RE: Configure and export "Global Audit Trail"

    Posted 11-30-2014 03:44 AM

    Hello Sundar,

                              can you please elaborate what do you mean by "1 big drawback we faced in current audit trail is mentioned in the below post"....

    I looked at the attached link and got just one image...

     

    Thank You!!

    Namita Mishra



  • 11.  Re: Configure and export "Global Audit Trail"

    Posted 08-26-2016 02:10 AM

    https://communities.ca.com/servlet/JiveServlet/download/99717888-1-46858/Audit%20Trail.jpg



  • 12.  RE: Configure and export "Global Audit Trail"

    Posted 01-17-2013 07:11 AM
    Hi All,

    I'm trying to display the audit data in portlet but opertion_code is displayed as ''I' or 'U'' or "D" and I need to display as "INSERT" or "UPDATE" or "DELETE" .
    This information could be stored in any of the table, Please let me know If there is any such table.
    In case the table don't exist, please throw some light to achieve this requirement.

    -Radha Vihari


  • 13.  RE: Configure and export "Global Audit Trail"

    Posted 01-17-2013 07:55 AM
    I'm trying to display the audit data in portlet but opertion_code is displayed as ''I' or 'U'' or "D" and I need to display as "INSERT" or "UPDATE" or "DELETE" .

    You can use decode

    decode(operation_code, "I","INSERT","U","UPDATE","D","DELETE")

    NJ


  • 14.  RE: Configure and export "Global Audit Trail"

    Posted 01-17-2013 03:21 PM
    or
    case
    when audits.operation_code='D' then 'DELETE'
    when audits.operation_code='I' then 'INSERT'
    when audits.operation_code='U' then 'UPDATE'
    else 'NO OPERATION'
    end o_code

    if you don't want Oracle PL/SQL

    Martti K.


  • 15.  RE: Configure and export "Global Audit Trail"

    Posted 01-17-2013 09:24 PM
    Thank you guys for your valuable suggestions....

    This sql query is working fine

    select case a.[operation_code] WHEN 'I' THEN 'INSERT' WHEN 'U' THEN 'UPDATE' WHEN 'D' THEN 'DELETE' END AS 'Operation_Code' from CMN_AUDITS a ;


    Thanks & Regards,
    Radha Vihari