Is there a way to tracking a bug's history in QC? What I am trying to get is how many bugs are being reopened over a release and how many times it got reopened. From the QC, I can only see the "status" as "closed" but it won't give me the information about have many times it changed from "closed" to "reopen" then "fixed" then "reopen" then "closed".
Thanks in advance!
Audit history table of QC has information stored for every change in the QC element(here the Bug). So you can write a SQL query that retrieve reopen defects. I call them as first time fix failures and they provide good evidence on why there is more testing happening when you got more of them
Below is the blog link which has this information and tells how to extract with an example of SQL query
The history table does not automatically track everything so make sure first that wherever possible to mark it in the customization field module to track history. If you are using QC via Saas and have no access to Sql Server, you can always do it via the OTA (QC Api) and use sql there. Other alternative, use the workflow and write in a file, maybe a xml one (using vbscript) on the onchanged event or rather onsaved event of the relevant QC module.
You could create an SQL query (directly query the db, or use QC's "great" dashboard and an Excel query there) that looks at the rows in the history table for that field and defect number.
The documentation should have enough examples for queries like these that you should be able to come up with a usable query that either works or can be discussed here if you post it.
The key here is that we have an explicit 'Reopen' state in the workflow and we can test for this. Not as a current state but how many times we move through the state. This is easily done as a custom Excel Report.
As Anthony Hilyard shows, it is essentially a join between the Bug table and the audit log/property table. To get the release, you also need to join with the Releases table. We use both the detected in release and the targeted release. The targeted release moves on to the current release for any open defect so we coalesce them together so if there is no target release, it uses the current release. Unlike Anthony's solution, we just want to count the reopens, rather than list them.
The SQL to look in Release, '@RelNam@' would look like this:
SELECT
BG.BG_BUG_ID As 'Defect ID',
BG.BG_STATUS As 'Status',
COUNT(AU.AU_ACTION_ID) As 'Reopens',
BG.BG_DETECTED_BY AS 'Detected By',
BG.BG_SUMMARY As 'Summary'
FROM BUG BG
LEFT JOIN AUDIT_LOG AU ON BG.BG_BUG_ID = AU.AU_ENTITY_ID
INNER JOIN AUDIT_PROPERTIES AP ON AU.AU_ACTION_ID = AP.AP_ACTION_ID
INNER JOIN RELEASES RLS ON RLS.REL_ID = COALESCE(BG.BG_TARGET_REL,BG.BG_DETECTED_IN_REL)
WHERE 1=1
AND RLS.REL_NAME = '@RelNam@'
AND (AU.AU_ENTITY_TYPE = 'BUG' OR AU.AU_ENTITY_TYPE IS NULL)
AND AU.AU_ACTION = 'UPDATE'
AND (AP.AP_PROPERTY_NAME = 'Status' AND AP.AP_NEW_VALUE = 'Reopen')
GROUP BY
BG.BG_BUG_ID,
BG.BG_STATUS,
BG.BG_DETECTED_BY,
BG.BG_SUMMARY
ORDER BY BG.BG_BUG_ID
If you want to use date instead, drop the join with releases and the associated WHERE clause and just use a test for BG_DETECTION_DATE instead.
If you don't have an explicit 'Reopen' state but your workflow has a 'Retest' and 'Fix', you can instead look for the number of transition events involving old state=Retest and new state='Fix'. This will exclude first time through, only where a defect is reopened.
Remember if you want to add columns in the output, make sure they are also in the 'Group By' clause at the bottom.
Here is an Excel SQL Extract I use:
SELECT
"defect"."BG_BUG_ID" AS "Defect",
"defect"."BG_STATUS" AS "Status",
"defect"."BG_SEVERITY" AS "Severity",
"defect"."BG_PRIORITY" AS "Priority",
"defect"."BG_USER_03" AS "Category",
"defect"."BG_USER_01" AS "Modules",
"defect"."BG_USER_08" AS "EFD",
"defect"."BG_USER_02" AS "Region",
"defect"."BG_SUMMARY" AS "Summary",
"defect"."BG_DETECTED_BY" AS "Detected By",
"audit_log"."AU_ACTION" AS "Action",
"audit_log"."AU_USER" AS "User",
"audit_property"."AP_OLD_VALUE" AS "Old Value",
"audit_property"."AP_NEW_VALUE" AS "New Value",
"audit_log"."AU_TIME" AS "Change Time",
"audit_property"."AP_PROPERTY_NAME" AS "Change Area"
FROM
BUG "defect"
INNER JOIN AUDIT_LOG "audit_log" ON "defect"."BG_BUG_ID" = "audit_log"."AU_ENTITY_ID"
INNER JOIN AUDIT_PROPERTIES "audit_property" ON "audit_log"."AU_ACTION_ID" = "audit_property"."AP_ACTION_ID"
WHERE
"audit_log"."AU_ENTITY_TYPE" = 'BUG'
