What is the SQL query equivalent to the BizTalk Admin Console "Completed Instances"?

Question

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?

Answer 1

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.