Query to report on what defects a user edited and what edit was made
| ♥ 0 |
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.
Marked as spam
|
Posted by |
Private answer
Hello, *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. Marked as spam
 Posted by mrwhite (Questions: 1, Answers: 116) Answered on July 13, 2016 1:25 pm
|