Quality center defect history – To track the defects that have undergone severity change

This blog is in response to comments from one of my previous blog. I was away from quality centre for long time and had not got the oppurtunity to reply to the comment. Now I feel I should blog about this if it could help someone else and I know how it can be done.

As mentioned in my previous blogs, history of any entity in the QC can be retrieved from AUDIT_PROPERTIES and AUDIT_LOG tables. In the previous blog QC History for closed defect – Status History, there is explanation on how to join these tables. I will quickly jump to main of query on how to retrieve defects that have undergone severity change



To find this, you need to basically pick defects that have changed from one status to another status. This is recorded in AUDIT_PROPERTIES table as AP_OLD_VALUE and AP_NEW_VALUE. So if defect severity is changes the AP_OLD_VALUE will have old value and AP_NEW_VALUE will have new value. If the severity is just set for first time then , AP_OLD_VALUE is null. Thus we need to find defects that do not have NULL value in  AP_OLD_VALUE

AP.AP_OLD_VALUE IS NOT NULL

Since we are interested in the defects that have undergone change for severity, we need to put a where condition that just filters out defects having severity change.

AP.AP_FIELD_NAME = ‘BG_SEVERITY’

Using these two above conditions, you can arrive at the query as shown below that will retrieve all defects which have undergone change in severity levels.

SELECT B.BG_BUG_ID,AL.AU_TIME ,AL.AU_USER ,AP.AP_OLD_VALUE  as OLD_STATUS ,AP.AP_NEW_VALUE AS NEW_STATUS FROM BUG B
INNER JOIN AUDIT_LOG AL
ON AL.AU_ENTITY_ID = B.BG_BUG_ID
INNER JOIN AUDIT_PROPERTIES AP
ON AL.AU_ACTION_ID = AP.AP_ACTION_ID
AND AL.AU_ENTITY_TYPE = 'BUG'
AND AL.AU_ACTION_ID= AP.AP_ACTION_ID
AND AP.AP_FIELD_NAME = 'BG_SEVERITY'
AND AP.AP_OLD_VALUE IS NOT NULL
ORDER BY AU_TIME DESC

Below is the snapshot showing the output





Another user of this query is , say if it is required to extract the defects that have undergone severity change in last three months, then all you need to do this add one more condition to where clause as shown in below and and you should retrieve the results as expected.

SELECT B.BG_BUG_ID,AL.AU_TIME ,AL.AU_USER ,AP.AP_OLD_VALUE  as OLD_STATUS ,AP.AP_NEW_VALUE AS NEW_STATUS FROM BUG B
INNER JOIN AUDIT_LOG AL
ON AL.AU_ENTITY_ID = B.BG_BUG_ID
INNER JOIN AUDIT_PROPERTIES AP
ON AL.AU_ACTION_ID = AP.AP_ACTION_ID
AND AL.AU_ENTITY_TYPE = 'BUG'
AND AL.AU_ACTION_ID= AP.AP_ACTION_ID
AND AP.AP_FIELD_NAME = 'BG_SEVERITY'
AND AP.AP_OLD_VALUE IS NOT NULL
AND AL.AU_TIME > '20130501'
ORDER BY AU_TIME DESC

The other useful blogs related to Audit tables and QC history can be found in following locations

1. First time Fix faliures or Reopen defects
2. How to retrieve Quality Center bug details and Audit tables information
3. How to retrieve QC status history for closed defect
4. How to retrieve test instances and test sets within particular folder
5. How to retrieve requirements and test coverage and linked test cases