How to filter history events of defects in HP ALM?

Question

We are in discussion about the work of members for the QA in our project. For that our QA manager wants to get list of all the HP ALM defects our QA team has worked on in a specific time window. He wants to see which status these tickets have to reveal the project manager that the low number of successfully finished tickets and the growing number of unsolved tickets is not caused by "non / slow" working members of the QA but of the rubbish work of the developers because in his eyes many tickets are not worked on or go back to developers after they were "fixed" by the them, because they still contain issues.

He asked me to create a filter in HP ALM for that the he can easily go through the list, watch the defect details etc. I tried to filter by "changed at" or own labens like "planned for release x.y.z" but I always only getting the current values of the defects but I can't filter about history events of them.

My question is now: Is there a way to create a filter for defects that shows a list of tickets the QA team (let's say user1, user2 and user3) have worked on in a given period of time?

Answer 1

There is no directy way to filter tickets over history events as you can - as you already noticed - only filter about current attribute values. But you can search in the history of any objects by using the Dashboard. Here is an example how you can create a filter for your needs:

Go to Dashboard and there to Analysis View: Press the green cross and choose New Excle Report ( or press Ctrl + E. Enter a name and open the Configuration Tab. There you can see a query window and yes you can query the ALM database using SQL and not only filters like in other dashboard reports.

For first we need some information about that database. Simple click on Help > Documentation Library in the head menu and in the opening help window on HP ALM Project Database Reference inside the API References section. Then a "download" of the database reference start where you get information about the schema.

Let's start to build the query. For that we can use the earlier opened Query window. We could directly work inside this sheet but I prefer to work with the Query builder (opend by pressing the button with the pencil icon in the menu). The nice thing about this tool is that you got the database tables on the right sight which you can easily integrate in your query and you can "filter" them by their technical name and/or by their labels the users of ALM see in the "normal" views like the defect overview. Therefore you can test your query without fully creating the report (note that this will only show you a maximum of the first 10 rows in the report) or execute the query but not creating an excle file of it. As you can see in the database reference the defects are stored in the BUG table while the history of all objects is stored in the AUDIT_PROPERTIES and AUDIT_LOG tables. With this information in mind we can build the following query:

SELECT  BG_BUG_ID,
        BUG.BG_STATUS,
        BUG.BG_DETECTION_DATE,
        BUG.BG_PRIORITY,
        BUG.BG_SEVERITY,
        BUG.BG_SUMMARY

FROM BUG
WHERE  BG_BUG_ID in (
        SELECT  CAST(AU_ENTITY_ID AS INT)
        FROM AUDIT_PROPERTIES
        LEFT JOIN AUDIT_LOG ON AP_ACTION_ID = AU_ACTION_ID
        WHERE (AU_USER IN ('user1', 'user2', 'user3')) AND
        (AP_TABLE_NAME = 'BUG')    AND
        (AU_TIME between '25/06/2015' and '18/07/2015')
        GROUP BY AU_ENTITY_ID)

ORDER BY BG_BUG_ID

Some information about that query:

• No ; at the end is needed, normally it will create an error (at least in our ALM version)
• As you cann see we select some data from the BUG table (orderd by ID) where the ID is a list of history events stored in the AUDIT_PROPERTIES
• We select all those audits which were done by the users user1, user2 and user3
• We also only want only audits that where done in a specific time window (here June 26 to July 18). NOTE: As you can see these are german dates as in our ALM instance they are stored as german dates. Change them to the layout in your instance (If you don't know the layout, just use the normal changed at filter of the defect list, select a date from the calender and copy the automatically created value from the filter list (and cut out the hours,minutes etc). Also remember about how between function works! It excludes the given borders - so the query above will only select audits really done from June 25 to July 17.
• And we only want to recieve AU_ENTITY_IDs of BUG entities, but no requirements or tests.

Now you are almost done. We know GENERATE the report and open the creating excle file. As you can see you got a nice list with the defects that your users were worked on in the given period of time.

After that - as you want to create a ALM filter of your report - we need some help of an text editior to create the ID list for the filter. I would have liked to use group_concatto create a nice list of all IDs which you can just copy paste inside your filter but this function is not standard SQL but only MySQL. So I'll use Notepad++ for this.

1. Mark and copy the first coloum (BG_BUG_ID) out of your excle sheet and paste it into an empty notepad sheet.
2. Remove the header line so that you only have a long list of numbers, one number in each line
3. Then open the Finde and Replace window
4. Enter [\r\n]+ into the FIND field and OR (notice the spaces around the OR!) inside the REPLACE WITH field.
5. Check the radio button for using regular expressions in the left down corner which activated checkbox finds \r and \n.
6. Press the Replace All button to get a single line where all numbers are sperated by OR.
7. Remove the last OR from the end of this list.
8. Copy the generated line from the notepad sheet into the ID filter field of your ALM and activate the filter - voila
9. To handle them over to your QA or the other project members you should save the filter as a public favorite using the Favorites menu option.