What is the SQL query equivalent to the BizTalk Admin Console "Completed instances"?
I want to get various summaries of the completed instances, e.g. grouped by Service Name, but the "Completed instances" tab doesn't have a "Group by" option
Can I query the SQL tables directly?
And if so, what tables should I be looking at?
Yes you can. The BizTalk DDBB required is [BizTalkDTADb] and the table with all the executions is [dta_ServiceInstances]. To get the services' names and make an useful report the table [dta_Services] is needed too. I use this query for the same purpose that you are asking but obtaining all both OK and wrong instances:
SELECT [nServiceId] OrchID, [strServiceName] OrchName,
CASE WHEN HRESULT = 0 THEN 'OK' ELSE 'ERROR' END AS [Status],
COUNT([nServiceInstanceId]) NumberOfInstances
FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] SI WITH (NOLOCK)
INNER JOIN [BizTalkDTADb].[dbo].[dta_Services] S
ON SI.[uidServiceId] = S.[uidServiceId]
WHERE S.[strServiceType] = 'Orchestration' AND SI.[dtStartTime] > '20161101'
GROUP BY [strServiceName],[nServiceId],
CASE WHEN HRESULT = 0 THEN 'OK' ELSE 'ERROR' END
ORDER BY 2, 3 DESC
If you want only the successful ones, you should add in the 'where' clause "HRESULT = 0" and remove the CASE sentence in the 'select' and 'group by'. And I have a date in the where clause too, usually I only need to see last days.
EDIT: The query is working in BizTalk 2013, I have not a 2010 installed now but I guess it's the same.
