Whenever you are interested in ALM Quality Center audit history you have various choices of getting the information.

One way of extracting history information from the database is using the Excel reporting functionality of the HP ALM Quality Center Dashboard.

First create a new Excel Report in the Analysis View of the Dashboard (see documentation library of HP ALM on details how to create such a report).

In the tab Configuration you will find the Query Builder, which allows querying the underlying database server of ALM Quality Center using SQL.

This is a nice feature, if you have to apply more complex analysis. Especially analysis on history information will require accessing audit tables either through the HP ALM OTA API or through SQL or both.

Version history

Below history provides an overview on changes and bug fixes applied on the SQL statements.

Version Date Description
0.1 2014-10-21 Initial commit.
0.2 2015-07-05 Added documentation on a bug fix for joining tables audit_log and bug causing a type cast error only occuring for certain records.
0.3 2015-07-29 Added query templates for querying audit tables for more than one field.

SQL for querying history data

Below SQL provides a standard structure for querying audit tables in HP QC for one field of interest.

1
2
3
Select BG_BUG_ID, AU_TIME, AP_FIELD_NAME, AP_PROPERTY_NAME, AP_OLD_VALUE, AP_NEW_VALUE, AU_USER
FROM BUG, AUDIT_LOG, AUDIT_PROPERTIES
WHERE AU_ENTITY_ID=to_char(BG_BUG_ID) AND AU_ENTITY_TYPE='BUG' AND AU_ACTION_ID=AP_ACTION_ID AND AP_FIELD_NAME='field_of_interest' 

Using JOIN in the Query Builder is sometimes possible, but I have seen not logical error messages for more complex queries using JOINs.

Therefore referenced tables are listed in the FROM part and joined via the WHERE statement.

SQL for querying last history entry per record for one field

Below SQL provides a standard structure for querying audit tables to get details on the last change applied for a certain field.

1
2
3
4
5
6
7
8
9
Select BG_BUG_ID, AU_TIME, AP_FIELD_NAME, AP_PROPERTY_NAME, AP_OLD_VALUE, AP_NEW_VALUE, AU_USER
FROM BUG, AUDIT_LOG, AUDIT_PROPERTIES,
(
SELECT AU_ENTITY_ID bug_id, MAX(AU_TIME) max_time
FROM AUDIT_LOG, AUDIT_PROPERTIES
WHERE AU_ENTITY_TYPE='BUG' AND AU_ACTION_ID=AP_ACTION_ID AND AP_FIELD_NAME='field_of_interest'
GROUP BY AU_ENTITY_ID
) lau
WHERE AU_ENTITY_ID=to_char(BG_BUG_ID) 
AND AU_ENTITY_TYPE='BUG' 
AND AU_ACTION_ID=AP_ACTION_ID 
AND AP_FIELD_NAME='field_of_interest' 
AND AU_TIME=lau.max_time 
AND BG_BUG_ID=lau.bug_id

Important is the sub select formatted in bold characters. This provides the timestamp of the last change for a field of an entity of interest, in this case the entity "BUG" (Defect). You can adjust this query to your needs by changing the AU_ENTITY_TYPE as well as the field(s) of interest.

SQL for querying the history for a period in time

If you are interested in the history for a certain field within a period of time, the following SQL will do the trick for you assuming your ALM installation accesses an oracle database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT BG_BUG_ID,
BG_RESPONSIBLE,
BG_STATUS,
AUDIT_LOG.AU_TIME,
Audit_properties.AP_OLD_VALUE as Value_OLD,
Audit_properties.AP_NEW_VALUE as Value_NEW
FROM AUDIT_LOG, AUDIT_PROPERTIES, BUG
WHERE  ap_table_name = 'BUG'
AND  ap_field_name = 'field_of_interest'
AND  au_action_id = AP_ACTION_ID
AND  to_char(BUG.BG_BUG_ID) = AU_ENTITY_ID
AND AUDIT_LOG.AU_TIME >= to_date('01-05-2015' ,'dd-MM-yyyy')
AND AUDIT_LOG.AU_TIME <= to_date('21-05-2015' ,'dd-MM-yyyy')
ORDER BY AUDIT_LOG.AU_TIME

Important parts are the field_of_interest as well as the parameters for the oracle function to_date('', 'date format') in order to create a valid where condition.

Preventing errors and bug fixing

When querying audit tables in HP ALM you have to be careful with the design of your SQL Statements. I for example used the above queries with an invalid join condition to get details from the BUG table into the resultset.

The below statement will work in most cases and only fail depending on the data selected. For example I have seen the error ORA 01702 invlaid number for an SQL designed like this at a clients because of an implicit type cast working for most records. Therefore we did not encounter the bug in red text during testing, but only two weeks after the report went live.

1
2
3
4
SELECT BG_BUG_ID, AU_TIME, AP_FIELD_NAME, AP_PROPERTY_NAME, AP_OLD_VALUE, AP_NEW_VALUE, AU_USER
FROM AUDIT_LOG INNER JOIN AUDIT_PROPERTIES ON AU_ACTION_ID=AP_ACTION_ID 
INNER JOIN BUG AU_ENTITY_ID=BG_BUG_ID
WHERE AND AU_ENTITY_TYPE='BUG' AND AND AP_FIELD_NAME='field_of_interest'

The explanation is simple. According to the HP ALM database schema documentation the field BG_BUG_ID is of type Integer (4) and the field AU_ENTITY_ID is of type VarChar (200).

I do not know exactly how Orackle databases behave regarding implicit type cassts (e.g. which field will be castet, left or right) but changing the query to the below design fixed the bug for me and this is also used in all queries in previous sections of this article.

1
2
3
4
5
6
7
SELECT BG_BUG_ID, AU_TIME, AP_FIELD_NAME, AP_PROPERTY_NAME, AP_OLD_VALUE, AP_NEW_VALUE, AU_USER
FROM
        AUDIT_LOG INNER JOIN AUDIT_PROPERTIES ON AU_ACTION_ID = AP_ACTION_ID,
        BUG
WHERE
        ap_field_name = 'field of interest' 
        AND AU_ENTITY_ID = to_char(bg_bug_id)

Solution is provided in green letters. The join can be applied on the tables audit_log and audit_properties, but the table BUG has to be included via the WHERE condition of the query. It might be possible to use join also for bug casting the bug id in the joins condition but I did not test this yet.

Querying the history for two dependent fields

If you have to query the audit tables for two fields instead of just one and the fields are not maintained at the same time you can work either with sub selects or create a second query and then use post processing in Excel VBA.

Since I do not like coding in VBA and this case in particular I choose to work with a sub select. This example like all other examples uses SQL for an Oracle database.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
SELECT BUG.BG_BUG_ID As Ticket_Id,         
	AUDIT_LOG.AU_TIME As Change_Time,
    AUDIT_PROPERTIES.AP_OLD_VALUE,
    AUDIT_PROPERTIES.AP_NEW_VALUE,
	t1.AP_OLD_VALUE As Other_Old_Value,
	t1.AP_NEW_VALUE As Other_New_Value
FROM AUDIT_LOG, AUDIT_PROPERTIES, BUG 
LEFT JOIN
  (
      SELECT BG_BUG_ID,
          AP_OLD_VALUE,
          AP_NEW_VALUE
      FROM AUDIT_LOG, AUDIT_PROPERTIES, BUG
      WHERE AP_TABLE_NAME = 'BUG'
          AND  AP_FIELD_NAME = 'other_field_of_interest'
          AND  AU_ACTION_ID = AP_ACTION_ID
          AND  to_char(BUG.BG_BUG_ID) = AU_ENTITY_ID
          AND AUDIT_LOG.AU_TIME >= to_date('01-05-2015' ,'dd-MM-yyyy')
          AND AUDIT_LOG.AU_TIME <= to_date('31-05-2015' ,'dd-MM-yyyy')
  ) t1 
ON (BUG.BG_BUG_ID = t1.BG_BUG_ID)
WHERE AUDIT_PROPERTIES.ap_table_name = 'BUG'
	AND AUDIT_PROPERTIES.ap_field_name = 'field_of_interest'        
    AND AUDIT_LOG.AU_ACTION_ID = AUDIT_PROPERTIES.AP_ACTION_ID
	AND to_char(BUG.BG_BUG_ID) = AUDIT_LOG.AU_ENTITY_ID
	AND AUDIT_LOG.AU_TIME >= to_date('01-05-2015' ,'dd-MM-yyyy')
	AND AUDIT_LOG.AU_TIME <= to_date('31-05-2015' ,'dd-MM-yyyy')
ORDER BY AUDIT_LOG.AU_TIME

The second field of interest 'other_field_of_interest' is queried in the sub select resulting in the table with alias 't1'.

Then we can join this result with the result of the outer query. Be careful with the results. I am for example interested in two fields which are not maintained at the same point in time. That is why I am joining the different results using the bug id and have to use a sub select.

If you just want to transpose / display the history of two fields next to each other the above sub select template can be used. Of course you then still have to decide what field you want to join on. Normally, if you want to display one action per row in a result set, you would join the sub and outer select using the AU_ACTION_ID and not the BG_BUG_ID.

If you are simply interested in two different fields, which are maintained at the same time and it does not matter if they are displayed next to each other or not you can use the below template.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Select BG_BUG_ID, AU_TIME, 
	AP_FIELD_NAME, 
    AP_PROPERTY_NAME, 
    AP_OLD_VALUE, 
    AP_NEW_VALUE, 
    AU_USER
FROM BUG, AUDIT_LOG, AUDIT_PROPERTIES
WHERE AU_ENTITY_ID=to_char(BG_BUG_ID) 
  AND AU_ENTITY_TYPE='BUG' 
  AND AU_ACTION_ID=AP_ACTION_ID 
  AND 
  (
    AP_FIELD_NAME='field_of_interest'
    OR AP_FIELD_NAME='field_of_interest'
    OR AP_FIELD_NAME='field_of_interest'
  )

Good to know

With version 12.0 of HP ALM the Dashboard appearently will be frozen, meaning that only read access will be possible. However the same reports can be implemented in business views in a projects customization. Since I will have to this for my current client I will report on any issues encountered during migration of the HP ALM dashboard reports into business views.

One thing annoys me already. The business views seem to be available for users only through an Excel Add-In. Why oh why? Probably because it cannot deliver what Excel does for you and many companies misuse Excel as a reporting tool throwing files on SharePoint or something crappy like that.

However business views have a big advantage compared to Dashboard reports. It supports data hiding.

Version 12.x of HP ALM will appearently not support Dashboard Excel Reports anymore.