how to capture history in defects module ?

  • Questions
  • how to capture history in defects module ?
Tags:
Question ID: 104374
1
0

How can i capture history for a particular defect in the defects module ? can i generate a report ?

Marked as spam
Posted by (Questions: 1, Answers: 0)
Asked on June 7, 2011 11:43 am
9 views
Answers (3)
0
Private answer

Here are some report definitions that can be used in QC11 Analysis to create excel reports of what changed Today, This Week, and Yesterday. It will query the project db and create a pivot table of the revisions. This is compatible with Office 2010 and QC 11, i haven't tested it anywhere else. However, there are some features used that are not compatible with anything less that Microsoft Office Excel 2010.

You can import these definitions into your project and try them out in the Dashboard Analysis Module of QC 11.

The heart of the report is this query:

SELECT ('Bug: ' + CONVERT(varchar(5),td.BUG.BG_BUG_ID) + ' - ' + CONVERT(varchar(256),td.BUG.BG_SUMMARY,101)) As calcBUGNAME ,
td.AUDIT_LOG.AU_ACTION_ID,
td.AUDIT_LOG.AU_USER,
td.AUDIT_LOG.AU_SESSION_ID,
td.AUDIT_LOG.AU_TIME,
td.AUDIT_LOG.AU_ACTION,
td.AUDIT_LOG.AU_ENTITY_TYPE,
td.AUDIT_LOG.AU_ENTITY_ID,
td.AUDIT_PROPERTIES.AP_PROPERTY_ID,
td.AUDIT_PROPERTIES.AP_ACTION_ID,
td.AUDIT_PROPERTIES.AP_TABLE_NAME,
td.AUDIT_PROPERTIES.AP_FIELD_NAME,
td.AUDIT_PROPERTIES.AP_PROPERTY_NAME,
td.AUDIT_PROPERTIES.AP_OLD_VALUE,
td.AUDIT_PROPERTIES.AP_NEW_VALUE,
td.BUG.BG_BUG_ID,
td.BUG.BG_STATUS,
td.BUG.BG_RESPONSIBLE,
td.BUG.BG_PROJECT,
td.BUG.BG_SUBJECT,
td.BUG.BG_SUMMARY,
td.BUG.BG_REPRODUCIBLE,
td.BUG.BG_SEVERITY,
td.BUG.BG_PRIORITY,
td.BUG.BG_DETECTED_BY,
td.BUG.BG_DETECTION_DATE,
td.BUG.BG_USER_01,
td.BUG.BG_USER_02,
td.BUG.BG_USER_03,
td.BUG.BG_USER_04,
td.BUG.BG_USER_05

FROM td.AUDIT_LOG
INNER JOIN
td.AUDIT_PROPERTIES ON td.AUDIT_LOG.AU_ACTION_ID = td.AUDIT_PROPERTIES.AP_ACTION_ID
INNER JOIN
td.BUG ON td.AUDIT_LOG.AU_ENTITY_ID = td.BUG.BG_BUG_ID
WHERE (td.AUDIT_LOG.AU_ENTITY_TYPE = 'BUG')

The report definitions can be found here as well as a short video with audio that demonstrates importing them into Quality Center 11 and generating the report:
[XML Report Definitions (CLICK HERE TO DOWNLOAD)][1]

[1]: http://ftporasi.com/EyeOnTesting/QC/Excel%20Reports/BugHistoryReport/BugsChangedReportDefinitions.zip

Marked as spam
Posted by (Questions: 6, Answers: 167)
Answered on December 30, 2011 11:13 am
@Captain Mercury, I was wondering if you have a query that will retrieve historical data related to status of defects from a project. I am trying to pull out all defects that had a ''Defect Status'' of ''Retest Passed'' at any point in the defect life cycle where ''Retest Passed'' is a lookup list item of that user defined field. Thank you.
( at March 14, 2017 5:46 pm)
0
Private answer

There are 2 tables called Audit_Log and Audit_Properties that have a relation in the QC database for a particular project. Take a look particularly at the Audit_Properties table which lists all of the fields, type of entity (Bug, test etc...) and old and new values.

You can run an excel report out of QC that queries this table. Based on your skills in SQL, you can export it out to a spreadsheet and either write some VBA to parse or organize it into whatever you want or you can utilize Excel formulas like pivot tables, formulas etc... to organize your data.

If you join it with the Audit_Log table, you can sort it by userID, date, time, action. If you had some specifics on what your report needs to be, I can narrow it down for you and maybe even write the SQL for you.

Dennis Stagliano
Senior Engineer, Orasi Software

Marked as spam
Posted by (Questions: 0, Answers: 4)
Answered on September 28, 2011 11:26 am
0
Private answer

@TheKing you can modify the where clause of the query above like this:

WHERE (td.AUDIT_LOG.AU_ENTITY_TYPE = 'BUG')
AND (td.AUDIT_PROPERTIES.AP_PROPERTY_NAME='Defect Status')
AND (td.AUDIT_PROPERTIES.AP_NEW_VALUE='Retest Passed')

Tip: Make sure in the project customization, the 'Defect Status' user-defined field is set to store history.

Marked as spam
Posted by (Questions: 6, Answers: 167)
Answered on April 5, 2017 6:12 pm