Execution Status Overview with Starter Name and Execution server Name

[ratkals]

 Hi,

Can anyone help me out to have 'Starter Name' and 'Execution server Name' to 'Execution Status Overview' report.

Regards,

Shruti

[ratkals]

Hi,

I Looked into query of the report,
SELECT parent.NodeID_pk PARENTID, parent.NodeName FOLDERORSUITENAME, etn.NodeID_pk EXECDEFID,
etn.NodeName EXECDEFNAME, tdCount.TestDefNumber TESTDEFNUMBER, ed.PassedCount PASSEDCOUNT,
ed.FailedCount FAILEDCOUNT, ed.NotExecutedCount NOTEXECUTEDCOUNT,
p.ProductCode PRODUCTCODE, v.VersionName VERSIONNAME, b.BuildName BUILDNAME, etn.LastRunAt LASTRUNAT, etn.LastDuration LASTDURATION
FROM TM_ExecTreeNodes etn
JOIN TM_ExecTreeNodes parent ON etn.ParentFolderID_fk = parent.NodeID_pk
JOIN TM_ExecutionDefinitions ed ON ed.ExecDefID_pk_fk = etn.NodeID_pk
JOIN (
SELECT entd.ExecNodeID, COUNT(*) TestDefNumber
FROM TM_V_ExecNodes_TestDefs entd
WHERE entd.PositionNumber >= 0
GROUP BY entd.ExecNodeID
) tdCount ON tdCount.ExecNodeID = ed.ExecDefID_pk_fk
FULL OUTER JOIN SCC_Builds b ON b.BuildID_pk = etn.BuildID_fk
FULL OUTER JOIN SCC_Versions v ON v.VersionID_pk = b.VersionID_fk
JOIN TM_TestContainers tc ON tc.TestContainerID_pk_fk = ed.TestContainerID_fk
JOIN SCC_Products p ON p.ProductID_pk = tc.ProductID_pk_fk
JOIN TM_ExecTreePaths etp ON etp.NodeID_pk_fk = etn.NodeID_pk
WHERE etp.ParentNodeID_pk_fk = 127841 AND etn.IsArchived = 0 AND etn.ProjectID_fk = 8531
ORDER BY FOLDERORSUITENAME, EXECDEFNAME

===============================================================================================

I used TM_Execdefinitionruns edr on edr.EXECDEFID_FK = ed.EXECDEFID_PK_FK but its providing more data in result.

ex: Existing report is providing 167 rows. when i joined about table it is displaying more than 200 rows.

Kindly let me know what am i missing.

Regards,
Shruti

[Hubert Inreiter]

Hi Shruti,

You are on the right track, the table TM_ExecDefinitionRuns contains the information you are searching for. Please see the columns Starter and ExecServerName. The reason why joining with EXECDEFID_FK does not work as expected is that in TM_ExecDefinitionRuns all runs for the execution definitions are contained.

The query you posted does not contain the relevant run tables. Starting from the original query in "Execution Status Overview" I've added starter and exec server information below:

SELECT

 parent.NodeID_pk PARENTID,

 parent.NodeName FOLDERORSUITENAME,

 tdeds.ExecTreeNodeID_pk_fk EXECDEFID,

 etn.NodeName EXECDEFNAME,

 COUNT (tdeds.TestPlanNodeID_pk_fk) TESTDEFNUMBER,

 SUM(tdeds.PassedCount) PASSEDCOUNT,

 SUM(tdeds.FailedCount) FAILEDCOUNT,

 SUM(tdeds.NotExecutedCount) NOTEXECUTEDCOUNT,

 p.ProductCode PRODUCTCODE,

 edr.VersionName VERSIONNAME,

 b.BuildName BUILDNAME,

 etn.LastRunAt LASTRUNAT,

 SUM(CASE WHEN tde.Duration > 0 THEN tde.Duration ELSE 0 END) LASTDURATION

 , edr.Starter, edr.ExecServerName

FROM TM_TestDefExecDefStatuses tdeds

  INNER JOIN TM_TestPlanNodes tpn on tpn.NodeID_pk = tdeds.TestPlanNodeID_pk_fk

 INNER JOIN TM_TestDefinitions td on td.TestPlanNodeID_pk_fk = tpn.NodeID_pk

 INNER JOIN TM_V_ExecNodes_TestDefs entd ON entd.ExecNodeID = tdeds.ExecTreeNodeID_pk_fk

   AND (entd.TestDefID_pk_fk = tdeds.TestPlanNodeID_pk_fk OR entd.TestDefID_pk_fk = tpn.ParentNodeID_fk and td.IsDataDrivenInstance = 1)

 INNER JOIN TM_ExecTreePaths etp ON etp.NodeID_pk_fk = tdeds.ExecTreeNodeID_pk_fk

 INNER JOIN TM_V_ExecTreeNodes etn ON etn.NodeID_pk = tdeds.ExecTreeNodeID_pk_fk

 INNER JOIN TM_ExecTreeNodes parent ON parent.NodeID_pk = etn.ParentFolderID_fk

 INNER JOIN TM_TestDefExecutions tde ON tde.TestDefExecID_pk_fk = tdeds.TestDefExecID_fk

 INNER JOIN TM_ExecDefinitionRuns edr ON edr.ExecDefRunID_pk_fk = tde.ExecDefRunID_fk

 INNER JOIN TM_TestContainers tc ON tc.TestContainerID_pk_fk = etn.TestContainerID_fk

 INNER JOIN SCC_Products p ON p.ProductID_pk = tc.ProductID_pk_fk

 LEFT JOIN SCC_Builds b ON b.BuildID_pk = edr.BuildID_fk

WHERE etp.ParentNodeID_pk_fk = ?

 AND tdeds.TestDefExecID_fk IS NOT NULL

 AND etn.ProjectID_fk = ?

 AND etn.IsArchived = 0

 AND etn.TestContainerID_fk IS NOT NULL

 AND entd.PositionNumber >= 0

GROUP BY parent.NodeID_pk, parent.NodeName,

        tdeds.ExecTreeNodeID_pk_fk, etn.NodeName,

        p.ProductCode, edr.VersionName, b.BuildName,

        etn.LastRunAt, etn.LastDuration, edr.Starter, edr.ExecServerName

ORDER BY FOLDERORSUITENAME, EXECDEFNAME

Regards,

Hubert