We have a few users who have rolled off a couple of ALM projects. I have been tasked with finding out some information on them. Specifically I need to know what defects that a User X had any edits on as well as what information was edited. Tall order perhaps, but was looking into the Audit log table but not quite getting my bearings on what the columns match to for other tables.
Answer by MrWhite · Jul 13, 2016 at 01:25 PM
Audit_Log is the right start, and you will also want to involve the Audit_Properites table.
Audit_Log has a few columns to take note of:
AU_USER is a match for your user’s ID name.
AU_ENTITY_TYPE will tell you the type of item that was modified (bug/defect, requirement, test, cycle, etc).
AU_ENTITY_ID will be the ID of the item modified. So if the type above is bug, then you know the BUG_ID as it will be a match to the AU_ENTITY_ID.
Last to note is the AU_ACTION_ID which is the ID given to the modification done.
With the above you can query to narrow it down to your user X based his ID, and only pull any defects that the user modified. And you will have the ID for each bug then.
Next would need to join or cross reference the AUDIT_Properties table.
In this table the key columns to note:
AP_PROPERTY_NAME will be the name of the field changed.
AP_OLD_VALUE is just as it sounds; it is the original value before change.
AP_NEW_VALUE is changed new value after change.
AP_ACTION_ID is the ID given to the modification and is equal to the AU_ACTION_ID from the Audit_LOG table.
All you need to do then is compose your query to pull your requested information as needed, using “WHERE audit_log.AU_ACTION_ID = audit_properties.AP_ACTION_ID.”
Hope that helps.