/**********************************************
Name: RSSBusSuspendedQuery.sql
Author: Richard Seroter, http://seroter.wordpress.com
Usage: Set @appName parameter to choose which application to view suspended processes for
***********************************************/

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
SET DEADLOCK_PRIORITY LOW

--create and set variables
declare @appName varchar(50)
set @appName = 'Company.Project' --application name (from Admin Console)

--create table variable
DECLARE @CurrentBizTalkMetrics TABLE
(svcName varchar(256),
svcType varchar(50),
svcState varchar(50),
instanceCount int)

--add orchestration fields to table variable
INSERT @CurrentBizTalkMetrics (svcName, instanceCount, svcType, svcState)

SELECT  o.nvcName AS Orchestration, COUNT(*) as Count, 'orchestration' as serviceType,
	CASE i.nState
		WHEN 4 THEN 'Suspended Resumable'
		WHEN 32 THEN 'Suspended Non-Resumable'
	END as State
FROM [BizTalkMsgboxDb]..[Instances] AS i WITH (READPAST) 
JOIN [BizTalkMgmtDb]..[bts_Orchestration] AS o WITH (READPAST) ON i.uidServiceID = o.uidGUID
WHERE
(
 SELECT app.[nvcname]
	 FROM BizTalkMgmtDb.dbo.bts_application app WITH (READPAST) 
	 JOIN [BizTalkMgmtDb]..[bts_Assembly] AS a WITH (READPAST) ON a.nApplicationID = app.nID
	 WHERE 
		o.[nAssemblyID] = a.[nID]
) = @appName AND
(i.nState = 4 OR i.nState = 32)
GROUP BY o.nvcName, i.nState
--end add orchestration fields to table variable

-- add receive port fields to table variable
INSERT @CurrentBizTalkMetrics (svcName, instanceCount, svcType, svcState)

SELECT r.nvcName, COUNT(*) as Count, 'receive port' as serviceType,
CASE i.nState
		WHEN 4 THEN 'Suspended Resumable'
		WHEN 32 THEN 'Suspended Non-Resumable'
	END as State
FROM [BizTalkMsgboxDb]..[Instances] AS i WITH (READPAST) 
JOIN [BizTalkMgmtDb]..[bts_receiveport] AS r WITH (READPAST) ON i.uidServiceID = r.uidGUID 
WHERE
(
 SELECT app.[nvcname]
	 FROM BizTalkMgmtDb.dbo.bts_application app WITH (READPAST) 
	 WHERE 
		r.nApplicationID = app.nID
) = @appName AND
(i.nState = 4 OR i.nState = 32)
GROUP BY r.nvcName, i.nState
--end add receive port fields to table variable

-- add send port fields to table variable
INSERT @CurrentBizTalkMetrics (svcName, instanceCount, svcType, svcState)

SELECT s.nvcName, COUNT(*) as Count, 'send port' as serviceType,
CASE i.nState
		WHEN 4 THEN 'Suspended Resumable'
		WHEN 32 THEN 'Suspended Non-Resumable'
	END as State
FROM [BizTalkMsgboxDb]..[Instances] AS i WITH (READPAST) 
JOIN [BizTalkMgmtDb]..[bts_sendport] AS s WITH (READPAST) ON i.uidServiceID = s.uidGUID 
WHERE
(
 SELECT app.[nvcname]
	 FROM BizTalkMgmtDb.dbo.bts_application app WITH (READPAST) 
	 WHERE 
		s.nApplicationID = app.nID
) = @appName AND
(i.nState = 4 OR i.nState = 32)
GROUP BY s.nvcName, i.nState
--end add send port fields to table variable

--return result set
SELECT * FROM @CurrentBizTalkMetrics