How to retrieve Quality center bug details ?

The main table in quality center for defect related info is Bug table. This table stores all the recent /current bug details . There are few columns within this table which would of your interest. Below are columns for reference

BUG.BG_SUMMARY /*Defect.Title*/
BUG.BG_RESPONSIBLE /*Defect.Assigned To*/
BUG.BG_CLOSING_DATE /*Defect.Closing Date*/
BUG.BG_DEV_COMMENTS /*Defect.Comments*/
BUG.BG_DETECTED_BY /*Defect.Detected By*/
BUG.BG_DESCRIPTION /*Defect.Description*/
BUG.BG_BUG_ID /*Defect.ID*/
BUG.BG_USER_32 /*Defect.Defect Status*/
BUG.BG_PRIORITY /*Defect.Priority*/
BUG.BG_PROJECT /*Defect.Project*/
BUG.BG_SEVERITY /*Defect.Severity*/



*Note BUG.BG_USER_32 is defect status in our project which I am using. By default the status of bug is stored in BG_status. So in your project it may be BG_status
There is only one primary key in this table . That is Bg_bug _id which is defect number.
Another table that would be of interest are the audit tables. Audit tables are the ones that store the history of all entities and bug is one of them. So all the historical entries would be stored in audit tables.


So what are these audit tables?

They are two of them . One - AUDIT_LOG , second - AUDIT_PROPERTIES
AUDIT_LOG: This table stores all the actions that any users does on any of QC entities like requirement, bug, test cases or test sets. AU_action ID is the only primary key. Below I have listed the details of each column.

Column Name	Pk	Data Type	Use/Description
AU_ACTION_ID	1	NUMBER (10)	This is only primary key and is uniquely generated for every action on QC entities
AU_FATHER_ID		NUMBER (10)	You can ignore this
AU_USER		VARCHAR2 (2000 Char)	The user login who is doing the update
AU_SESSION_ID		NUMBER (10)	Session Id of user
AU_TIME		DATE	The time when the change was done. This one of important field when you are referring to time/date for reopened defect, aging etc
AU_ACTION		VARCHAR2 (200 Char)	This details the action whether it is delete or update
AU_ENTITY_TYPE		VARCHAR2 (200 Char)	This is important field. It will store the name of entity whether it is Bug or requirement etc
AU_ENTITY_ID		VARCHAR2 (200 Char)	This is important field . It will store the Bug id , test case id etc
AU_DESCRIPTION		CLOB	You can ignore this field for a while

AUDIT_PROPERTIES: This table stores information what is changed to what and from what.

Column Name	Pk	Data Type	Use/Description
AP_PROPERTY_ID	1	NUMBER (10)	This is only primary key and is uniquely generate for every action on QC entities
AP_ACTION_ID		NUMBER (10)	This is a foreign key from Audit Log table
AP_TABLE_NAME		VARCHAR2 (40 Char)	This field stores value of QC table like BUG etc, which is been affected by the action
AP_FIELD_NAME		VARCHAR2 (40 Char)	This field stores value of which field name that is been changed from the above table
AP_PROPERTY_NAME		VARCHAR2 (50 Char)	This field stores the property value that is been affected. Like title , if defect title has been changed etc
AP_OLD_VALUE		VARCHAR2 (2000 Char)	What was the old value before change
AP_NEW_VALUE		VARCHAR2 (2000 Char)	what is new value of the entity's property . Like if you change the title to Title 2 from Title 1. Then AP_NEW_VALUE would be Title 2 and AP_OLD_VALUE would be Title 1
AP_OLD_LONG_VALUE		CLOB	You can ignore this
AP_NEW_LONG_VALUE		CLOB	You can ignore this
AP_OLD_DATE_VALUE		DATE	You can ignore this
AP_NEW_DATE_VALUE		DATE	You can ignore this

So what is relationship between these tables?


Let try out first exercise.


To retrieve the list of people to whom all defect had been assigned.

First we will write simple query to retrieve the bug details from bug table. This just returns one row.

Select *
from bug B
where B.bg_bug_id = '1001'

Next lets join audit table AUDIT_LOG


Select Al.AU_ACTION_ID
from bug B , Audit_log AL
where B.Bg_bug_id = '1001'
And Al.AU_entity_id = B.BG_Bug_ID
And AL.AU_entity_type = 'BUG'

The above query would list all the actions that corresponding to Bug 1001.

Next lets join AUDIT_PROPERTIES to retrieve the to whom it all assigned to and when it was assigned

Select Al.AU_time as DateAssigned, AP.AP_NEW_VALUE as Assigned to
from bug B , Audit_log AL
where B.Bg_bug_id = '1001'
And Al.AU_entity_id = B.BG_Bug_ID
And AL.AU_entity_type = 'BUG'
And ap.AP_ACTION_ID = al.AU_ACTION_ID
And AP_FIELD_NAME = 'BG_RESPONSIBLE'

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. To Extract defects that have undergone severity change
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