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.
Below history provides an overview on changes and bug fixes applied on the SQL statements.
|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.
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.
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.
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.
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.
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.
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.
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.