First time fix failures or Reopen defect from QC

In quality center, for your project analysis, it would be useful to find how many defects reopened. As you might be aware that , due to defects and not so good work from developers, your testing progress gets hampered. But how will you be able to quantify like the defects most of them were reopened and users had to do more of regression and testing? For this, metrics of Reopen defect is quite good and will justify that there was more of testing required than expected.



In my previous blog  How to retrieve Quality center bug details ? , 

I have explained the information of Audit tables in QC. In case,if you are not aware of Audit tables, it would be worth going through it first.


So how to find reopen defects? The audit_properties table as said before holds the properties of the QC entity for any action that happened. So when a defect gets a new status, for this action there is entry made in audit_log table saying an action is performed for the bug.  Next for this action, audit_properties table stores the properties that have been changed.
This means audit_entity column of audit log table will have value saying it is for BUG ( the table name of defects). and ap_field_name column of audit_properties will have the value of  BG_status ( the column in the defect table that stores Defect status). Hence the value for the column ap_new_value of audit properties table will have has Reopen( Check the exact text for your project for Reopen status. It may vary)

Thus we have three conditions
1. audit_log.AU_ENTITY_TYPE = 'BUG'
2. audit_properties.AP_FIELD_NAME = 'BG_STATUS'
3. audit_properties.AP_NEW_VALUE = 'Reopen'

As per relationship between Audit_log and audit_properties , we have following condition . You can check the Relationship Diagram in my previous blog How to retrieve Quality center bug details ? 

audit_log.AU_ENTITY_ID = audit_properties.BG_BUG_ID
audit_properties.AP_ACTION_ID = audit_log.AU_ACTION_ID

Thus from above conditions you can find all the defects that have been reopened.


SELECT
Bug.bg_project,bug.BG_BUG_ID as Defect_ID
FROM BUG Bug, AUDIT_LOG , AUDIT_PROPERTIES 
WHERE audit_log.AU_ENTITY_ID = Bug.BG_BUG_ID 
and audit_log.AU_ENTITY_TYPE = 'BUG' and 
audit_properties.AP_ACTION_ID = audit_log.AU_ACTION_ID 
and audit_properties.AP_FIELD_NAME = 'BG_STATUS'
and audit_properties.AP_NEW_VALUE = 'Reopen'


* 'BG_STATUS' is default column in BUG table where the status of the defect is stored. But in many cases, or in your QC project , the status might have defined to be stored in some other column. You can try locate that field in BUG table when you have glance at it in QC excel reports, because user need to give appropiate name while setting up this.