Absent Member.
Absent Member.

changed tests that have been automated..

Hi, I am trying to put together a SQL report that will give me details on changed tests...what I would like is to have the report detail the test name, folder it is a part of (or test container), what has changed (possible to include step name?) and the other usual details of ChangedAt, ChangedBy, CreatedAt,CreatedBy.

I would like it to filter off of the attribute or property as well (eg test that have been automated), note we have been looking at using the 'show changes' button but it gives us all changes to tests but we want to look up just changes to automated tests (based on attribute or property).

this is what I got so far, very general and covers all tests...need detail on automated tests, 

SELECT Name as 'Test Case', Description, ChangedBy, ChangedAt, CreatedAt, NodeID_pk, ParentNodeID_fk
FROM TM_TestPlanNodes tpn
WHERE datediff(ww, tpn.ChangedAt, getDate()) <= 1


any suggestion to use another approach is appreciated and any help would be appreciated,



2 Replies
Micro Focus Expert
Micro Focus Expert

Hi James,

If you want to query all tests which have been automated recently you can use the table "TM_PlanHistory" there for some (not all) types of changes of tests history entries are written. NodeType = 121 is for tests automated from manual and NodeType = 123 is for tests converted from type automated to manual.

Generally, I'd suggest to use the LQM_Tests table for retrieving information on tests which covers test properties in a flat form and reduces complexity of SQL (less joins necessary). Additionally the Test Container is covered with this table too.

Please see the example below which queries all recent tests which have been automated. Use for example columns starting with a_junit_* to get the JUnit properties or a_nunit_* for NUnit properties:

SELECT tests.Name "Test Case", tests.ChangedBy, tests.ChangedAt, tests.CreatedAt, TestID_pk, ParentID_fk,

tpnTc.Name "Test Container", tests.Type  -- tests.a_junit_* tests.a_nunit_* ...

FROM LQM_Tests tests

INNER JOIN TM_TestPlanNodes tpnTc ON tests.ContainerID_fk = tpnTc.NodeID_pk

INNER JOIN TM_PlanHistory hist ON tests.TestID_pk = hist.PlanNodeID_fk

WHERE hist.NodeType = 121 -- CREATED_FROM_MANUAL

AND datediff(ww, hist.ChangedAt, getDate()) <= 1

ORDER BY hist.ChangedAt DESC




Absent Member.
Absent Member.

Thanks Hubert, I will try out the code...thanks for helping out!
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.