/**********************************************
Name: RSSBusTrafficQuery.sql
Author: Richard Seroter, http://seroter.wordpress.com
Usage: Set @appName parameter to choose which application to view suspended processes for and set the
@dayInterval to choose how far back to grab statistics.
***********************************************/

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

--create variables
DECLARE @appName varchar(50), @dayInterval int, @dayWindow int, @currentDate datetime
--set variable values
SET @appName = 'Company.Project' --application name (from Admin Console)
SET @dayInterval = 1	-- how many days to go back, e.g. 1 day (yesterday)
SET @dayWindow = 0 - @dayInterval
SET @currentDate = convert(varchar, GETDATE(), 101) --format date to remove "time" component

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

--add orchestration fields to table variable
INSERT @CurrentBizTalkMetrics (svcName, instanceCount, svcType)
SELECT 
	DISTINCT sf.[service/name], 
	COUNT(sf.[service/name]) as instanceCount, 
	'orchestration' as serviceType
FROM 
	BizTalkDTADb..dtav_servicefacts sf WITH (READPAST)
	INNER JOIN
	BizTalkMgmtDb.dbo.bts_assembly a WITH (READPAST) on sf.[service/assemblyname] = a.[nvcfullname]
WHERE 
	sf.[service/type] = 'Orchestration'  AND
	(
	 SELECT app.[nvcname]
	 FROM BizTalkMgmtDb.dbo.bts_application app WITH (READPAST)
	 WHERE 
		app.[nid] = a.[napplicationid]
	 ) 
	 = @appName AND
	--get the items (adjust for UTC time), NOT including today's traffic
	(DATEADD(hh, -7 ,sf.[serviceinstance/endtime]) >= DATEADD(dd, @dayWindow, @currentDate) AND DATEADD(hh, -7 ,sf.[serviceinstance/endtime]) < @currentDate)
GROUP BY sf.[service/name]
--end orchestration fields

--add send port fields to table variable
INSERT @CurrentBizTalkMetrics (svcName, instanceCount, svcType)
SELECT 
	DISTINCT mf.[event/port], 
	COUNT(mf.[event/port]) as instanceCount, 
	'receive' as serviceType
FROM 
	BizTalkDTADb..dtav_messagefacts mf WITH (READPAST) 
	INNER JOIN
	BizTalkMgmtDb.dbo.bts_receiveport rp WITH (READPAST) on mf.[event/port] = rp.[nvcname]
	WHERE 
	mf.[event/direction] = 'Send' AND
	(
	 SELECT app.[nvcname]
	 FROM BizTalkMgmtDb.dbo.bts_application app WITH (READPAST) 
	 WHERE 
		app.[nid] = rp.[napplicationid]
	 ) 
	= @appName AND
	--get the items (adjust for UTC time), NOT including today's traffic
	(DATEADD(hh, -7 , mf.[event/timestamp]) >= DATEADD(dd, @dayWindow, @currentDate) AND DATEADD(hh, -7 , mf.[event/timestamp]) < @currentDate)
GROUP BY mf.[event/port]
--end send port fields

--add receive port fields to table variable
INSERT @CurrentBizTalkMetrics (svcName, instanceCount, svcType)
SELECT 
	DISTINCT mf.[event/port], 
	COUNT(mf.[event/port]) as instanceCount, 
	'send' as serviceType
FROM 
	BizTalkDTADb..dtav_messagefacts mf WITH (READPAST) 
	INNER JOIN
	BizTalkMgmtDb.dbo.bts_sendport sp WITH (READPAST) on mf.[event/port] = sp.[nvcname]
WHERE 
	mf.[event/direction] = 'Send' AND
	(
	 SELECT app.[nvcname]
	 FROM BizTalkMgmtDb.dbo.bts_application app WITH (READPAST) 
	 WHERE 
		app.[nid] = sp.[napplicationid]
	 )
	 = @appName AND
	--get the items (adjust for UTC time), NOT including today's traffic
	(DATEADD(hh, -7 , mf.[event/timestamp]) >= DATEADD(dd, @dayWindow, @currentDate) AND DATEADD(hh, -7 , mf.[event/timestamp]) < @currentDate)
GROUP BY mf.[event/port]

--end receive port fields

--return result set
SELECT * FROM @CurrentBizTalkMetrics