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:
Can you please tell me the database tables that have these fields??
Hope this will help Audit Table Query
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?
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.
How can we JOIN CMN_AUDITS and CMN_CAPTIONS_NLS tables to get the "Object_name" and "Attribute_Name"?
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)
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"?
Hi PragyaT - Did Dave_3.0's response help answer your question? If so please mark as Correct Answer. Thanks!
select namefrom CMN_CAPTIONS_NLSwhere 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)