Global Audit Trail Attribute Name

[Anon Anon]

Hi CA PPM Experts,

 

I am trying to replicate the Global Audit Trail by writing my custom query. There i am facing a problem in getting the attribute name (label of the attribute) using the attribute code in the audit table.

I am using tables cmn_audits, odf_view_attributes and odf_audited_attributes. But i cannot get the desired attribute label with attribute code by joining these tables.

Please help me out to resolve the issue of getting the attribute name . Or, if any one have any idea of another table to resolve my issue then please let me know.

Also, if any one have worked on the Global Audit Trail and have NSQL for that, then that will also help.

Waiting for response.

[Deactivated User]

Try search

https://communities.ca.com/message/58954272#58954272

https://communities.ca.com/message/108754258#108754258

 

and mroe

[Anon Anon]

Hi Urmas,

thanks for your quick reply, but still i didn't get the correct answer of my query.

I want the table name through which i can fetch the attribute name using the attribute code in cmn_audits. If you know the table name then let me know.

I was using the odf_audited_attributes and odf_view_attributes tables to fetch the attribute name. But i was unable to join these table with cmn_audits.

So if you are able to fetch the attribute name with it, then please send me the sql query.

[David Morton]

I thought it was just the label attribute on odf_view_attributes

[Anon Anon]

Hi david,

I had already got this table. I know it is having the label of all attributes but i am unable to join this table with cmn_audits.

If you are able to join them, then kindly help me out.

[David Morton]

Surely its just on attribute_code (from cmn-audits) - think you need a view_id as well.

 

But if you SQL trace, or look at the Clarity source code itself (what others are trying to tell you to do) you should find the answer you are looking for.

[Nick Darlington]

If your objective is the replication of the existing page, then why not reuse the same query that the page uses?  You will likely overlook things otherwise as the query for getting all that information is not trivial (reference: $NIKU_HOME/META-INF/odf/pmd/audit.xml and statement set / ID within it called "odf.auditTrail").

 

If you perform an Action Trace ( http://www.ca.com/us/support/ca-support-online/product-content/knowledgebase-articles/tec573491.aspx ) then you can navigate to the page in the UI, apply any filter as you choose, and when you receive the results get the trace log file and look for the same PMD statement set / ID, "odf.auditTrail", and you will get a version of that query converted into as close to the native database query as possible.

 

It's possible for this query to change from one version of Clarity to the next, so you would also be best to check if it changes after any upgrade in order to see if your own query also needs changing, for consistency.