Clarity PPM1

Expand all | Collapse all

Audit Portlet Fields

  • 1.  Audit Portlet Fields

    Posted 08-25-2016 03:22 AM

    Hi,

     

    I am creating an Audit portlet in Clarity 13.3, it is same like the Audit Trail portlet in Admin tab.

     

    I want some information about some fields that i am unable to locate in database.

     

    Below are the fields I am not able to find:

     

    •     Object Name (Object type field is in CMN_Audits)
    •      Instance ID
    •      Instance Code
    •      Attribute Name (Attribute Code is in CMN_Audits)
    •     User that performed the change

     

    Can you please tell me the database tables that have these fields??

     

     



  • 2.  Re: Audit Portlet Fields

    Posted 08-25-2016 05:13 AM

    Hope this will help Audit Table Query 

     

    Thanks,

    Adersh Ram



  • 3.  Re: Audit Portlet Fields

    Posted 08-25-2016 05:24 AM

    Thanks for reply.

     

    The fields that i want are not in the tables mentioned in the link.

     

    Can you please help me with these fields?

     

    Thanks

    Pragya



  • 4.  Re: Audit Portlet Fields

    Posted 08-25-2016 08:50 AM

    Use the values that are in the CMN_AUDITS table to get the "names" from the dictionary tables.

    For the object and attribute 'names' you can use the codes to look up the details in the CMN_CAPTIONS_NLS table (which contains all the translated 'names')

    The 'instance id' is just the object_id on the audit table (and the instance code will be the code from the relevant object instance)

    Use the created_by or updated_by from the audit table (they will be the same) to lookup the user details from the CMN_SEC_USERS table.



  • 5.  Re: Audit Portlet Fields

    Posted 08-25-2016 09:21 AM

    Hi David,

     

    How can we JOIN CMN_AUDITS and CMN_CAPTIONS_NLS tables to get the "Object_name" and "Attribute_Name"?



  • 6.  Re: Audit Portlet Fields

    Posted 08-25-2016 09:55 AM

    Have you looked at the data in CMN_CAPTIONS_NLS? Its not very hard to work out.

    (I'm deliberately not giving you the answer, I want you to find it out for yourself)



  • 7.  Re: Audit Portlet Fields



  • 8.  Re: Audit Portlet Fields

    Posted 08-26-2016 06:36 AM

    Hi David,

     

    I tried to JOIN both the tables but i am not able to do that.

     

    Can you please help me here, How can we JOIN CMN_AUDITS and CMN_CAPTIONS_NLS tables to get the "Object_name" and "Attribute_Name"?

     

    Regards

    Pragya



  • 9.  Re: Audit Portlet Fields

    Posted 08-26-2016 08:56 AM

    Object example;

    select name
    from CMN_CAPTIONS_NLS
    where TABLE_NAME = 'ODF_OBJECTS'
    and LANGUAGE_CODE = 'en'
    and pk_id = 5000000

    But you should provide the language_code ( not 'en' ) if its not English - pick that up from the @WHERE:PARAM:LANGUAGE@ if in a portlet

    and you need to JOIN via the pk_id (being the object id from the audit table)

     

    Attribute names are trickier ; I'd suggest just using the attribute code then (one reason that they are tricky is that an attribute can be labelled differently on the various views in the system so which label you would want to display is debatable)



  • 10.  Re: Audit Portlet Fields

    Posted 08-30-2016 07:29 PM

    Hi PragyaT - Did Dave_3.0's response help answer your question? If so please mark as Correct Answer. Thanks!