QC history for closed defect – Status History

For some defects one would like to know the status history of defect. This can be easily found in the QC when you click on particular defect and then click history tab. But if you want to have this information for multiple defects on single sheet or page, then Excel query option in the QC is the best option.

The AUDIT_LOG table has all the events registered for a entity and AUDIT_PROPERTIES TABLE has the details of these events.  Both these tables can be used to retrieve the history of any defect

Let’s try simple query to extract details for a particular bug. The bug id of BUG table is linked to AUDIT_LOG table with field AU_ENTITY_ID and hence create below inner join to begin with
SELECT * FROM BUG B
INNER JOIN AUDIT_LOG AL
ON AL.AU_ENTITY_ID = B.BG_BUG_ID



The AUDIT_PROPERTIES table is linked to AUDIT_LOG by Action_id values. The new query will be
SELECT * 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

Next comes the where conditions so that we filter out appropriate records. The new query will be
SELECT * 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_FIELD_NAME='BG_STATUS'
AND B.BG_BUG_ID='26'
ORDER BY AU_TIME DESC

The above query would give values for all the columns of three tables.  For status history , typically interested columns are who changed the status of defect, from what status to what status, and when. To retrieve these values the query will be now
SELECT 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_FIELD_NAME='BG_STATUS'
AND B.BG_BUG_ID='26'
ORDER BY AU_TIME DESC

Below is the result value for defect that I have been querying

The above query can be then generalised by using B.BG_BUG_ID IN (list of defects)

SELECT 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_FIELD_NAME='BG_STATUS'
AND B.BG_BUG_ID IN (list of defects)
ORDER BY AU_TIME DESC