This script is an example to aggregate data of Service Instances from the tracking database (BizTalkDTADb):
SELECT
dta_Svc.strServiceName + '_' + dta_SvcSta.strState AS 'Service Name'
, COUNT(*) AS 'Number of Hits'
, MAX(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Maximum Execution Time'
, AVG(CONVERT(BIGINT, DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime))) AS 'Average Execution Time'
, MIN(DATEDIFF(millisecond, [dta_SI].dtStartTime, [dta_SI].dtEndTime)) AS 'Minimum Execution Time'
FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] [dta_SI]
INNER JOIN
[BizTalkDTADb].[dbo].[dta_Services] dta_Svc ON [dta_SI].uidServiceId = dta_Svc.uidServiceId
INNER JOIN
[BizTalkMgmtDb].[dbo].[bts_assembly] bts_ass ON dta_Svc.strAssemblyName = bts_ass.nvcFullName
INNER JOIN
[BizTalkDTADb].[dbo].[dta_ServiceState] dta_SvcSta ON [dta_SI].nServiceStateId = dta_SvcSta.nServiceStateId
WHERE
[dta_SI].dtEndTime IS NOT NULL
GROUP BY dta_Svc.strServiceName, dta_SvcSta.strState
ORDER BY dta_Svc.strServiceName
The sample result of the script is:
In my case, this script is further enhanced by filtering to get only the last 5 minutes tracked service instances and wrapped as a stored procedure. The data from the stored procedure is used to feed the monitoring system.
