BizTalk Message count per Application (DTADB data)

  • Article

 

You can download the code here at MSDN Code Gallery

 

it will create an ouput like this one:

 

image 

NO APPLICATION results are meesages sent/received without a port (direct bindings)

 

listed operations:

  • Receiving
  • Sending
  • Error (if the send or receive opration fail)

 

Thanks to Roger Monclus, we have an improved version now Sonrisa that shows also min and max timeframes to get an idea about when BizTalk processed thouse messages in between.

 

--number of days to look back:

 

DECLARE @DaysBack int

 

SET @DaysBack = -5

 

USE BIZTALKMGMTDB

 

 

 

--LIST OF ALL BUSINESS APPLICATIONS

SELECT nvcName as [Application Name] FROM dbo.bts_application WITH (READPAST)

WHERE nvcName <> 'BizTalk Application 1'

                                AND nvcName <> 'BizTalk EDI Application'

                                AND nvcName <> 'BizTalk.System'

                                AND nvcName <> 'SoapTester'

ORDER BY nvcName

 

 

--THE FOLLOWING TSQL GETS ALL MESSAGES RECEIVED, SENT AND ERRORS IN APPLICATION.

--IMPORTANT: TRACKING MUST BE ENABLED FOR ALL APPLICATIONS, ALLOWING THIS QUERY TO WORK PROPERLY WITH REAL DATA

 

USE BizTalkDTADb

 

--*********************************************************************************

 

--PROCESS FOR RECEIVING,SENDING AND NUMBER OF ERRORS

 

--*********************************************************************************

 

--check if the TEMP tables already exist in TempDb. If yes we delete them

 

IF OBJECT_ID ('TempDb..#temp') IS NOT NULL DROP TABLE #temp

IF OBJECT_ID ('TempDb..#temp2') IS NOT NULL DROP TABLE #temp2

 

 

--USING TEMP TABLES INSTEAD OF NESTED QUERIES

CREATE TABLE #temp(

 

Number int,

 

--PortName nvarchar(255), --WE COULD ADD THE SAME STATISTICS AT PORT LEVEL ALSO BY ADDING PORT NAME EVERYWHERE

 

RCV nvarchar(255),

 

SND nvarchar(255),

 

Operation int,

 

min_timestamp datetime ,

 

max_timestamp datetime

 

)

 

 

INSERT INTO #temp (Number, RCV,SND,Operation, min_timestamp, max_timestamp)

 

SELECT

 

COUNT(*) AS Number,

 

--[Event/Port] as PortName,

 

BizTalkMgmtDb.DBO.bts_application.[nvcName] AS RCV,

 

app2.[nvcName] AS SND,

 

nMessageStatusId AS Operation,

 

min([Event/Timestamp]) as min_timestamp,

 

max([Event/Timestamp]) as max_timestamp

 

FROM [dbo].[dtav_MessageFacts] mf

 

JOIN [dbo].[dta_MessageStatus] st ON st.strStatus = mf.[Event/Direction]

 

LEFT OUTER JOIN BizTalkMgmtDb.DBO.bts_receiveport ON BizTalkMgmtDb.DBO.bts_receiveport.nvcname = [Event/Port]

 

LEFT OUTER JOIN BizTalkMgmtDb.DBO.bts_application ON BizTalkMgmtDb.DBO.bts_application.nid = BizTalkMgmtDb.DBO.bts_receiveport.nApplicationID

 

LEFT OUTER JOIN BizTalkMgmtDb.DBO.bts_sENDport ON BizTalkMgmtDb.DBO.bts_sENDport.nvcname = [Event/Port]

 

LEFT OUTER JOIN BizTalkMgmtDb.DBO.bts_application AS app2 ON app2.nid = BizTalkMgmtDb.DBO.bts_sENDport.nApplicationID

 

LEFT OUTER JOIN BizTalkMgmtDb.dbo.adm_ReceiveLocation ON BizTalkMgmtDb.DBO.adm_receivelocation.name = [Event/Port]

 

 

WHERE

 

     (st.nMessageStatusId = 0 OR nMessageStatusId = 1 OR st.nMessageStatusId = 5) --0 is received, 1 sent, 5 with errors

 

    AND [Event/Timestamp] >= dateadd(day, @DaysBack, getdate())

 

 

GROUP BY

 

--[Event/Port],

 

BizTalkMgmtDb.DBO.bts_application.[nvcName],

 

app2.[nvcName] ,

 

nMessageStatusId

 

 

 

 

 

 

CREATE TABLE #temp2(

 

Number int,

 

ApplicationName nvarchar(255),

 

Operation nvarchar(10),

 

min_timestamp datetime ,

 

max_timestamp datetime

 

)

 

 

-- INSERT THE RECORDS IN #TEMP2 TO GROUP RESULTS BY APPLICATION

 

 

INSERT INTO #temp2(Number,ApplicationName,Operation, min_timestamp , max_timestamp )

 

SELECT

 

Number ,

 

--PortName, 

 

(CASE

 

       WHEN RCV IS NULL and SND IS NULL THEN 'NO APPLICATION' -- WHEN we cannot find the link between the port and de application name, It might be because the port/receive location does not exists anymore

 

       WHEN RCV IS NULL THEN SND -- IF RECEIVE PORT NAME IIS NULLTHEN IT HAS TO BE A SEND PORT (WE ARE RECEIVING FROM A REQUEST RESPONSE PORT)

 

                   WHEN SND IS NULL THEN RCV -- IF SEND PORT NAME IIS NULLTHEN IT HAS TO BE A RECEIVE PORT (WE ARE SENDING TO A SOLICIT RESPONSE PORT)

 

    ELSE SND END) AS ApplicationName

 

,(CASE WHEN Operation = 1 THEN 'Send' WHEN Operation = 5 THEN 'Error' ELSE 'Receive' END) AS operation, -- 0 is received, 1 sent, 5 with errors

min_timestamp,

max_timestamp

 

 

FROM #temp

 

 

--FINAL GROUPED QUERY TO GET THE NUMBERS PER APPLICATION, NOT PER PORT

 

SELECT ApplicationName as [Application Name]

, Operation

,sum(number) AS [Count],

min_timestamp ,

max_timestamp

 

FROM #temp2

 

WHERE ApplicationName <> 'BizTalk Application 1' --EXCLUDING NON BUSSINES APPLICATIONS

                                AND ApplicationName <> 'BizTalk EDI Application'

                                AND ApplicationName <> 'BizTalk.System'

                                AND ApplicationName <> 'SoapTester'

 

GROUP BY ApplicationName,Operation, min_timestamp ,

max_timestamp

 

ORDER BY ApplicationName,Operation

 

SELECT Operation

,sum(number) AS [Total Count]

 

FROM #temp2

 

WHERE ApplicationName <> 'BizTalk Application 1' --EXCLUDING NON BUSSINES APPLICATIONS

                                AND ApplicationName <> 'BizTalk EDI Application'

                                AND ApplicationName <> 'BizTalk.System'

                                AND ApplicationName <> 'SoapTester'

 

GROUP BY Operation

 

ORDER BY Operation

 

 

--DELETING TEMP TABLES

 

IF OBJECT_ID ('TempDb..#temp') IS NOT NULL DROP TABLE #temp

IF OBJECT_ID ('TempDb..#temp2') IS NOT NULL DROP TABLE #temp2

 

Enjoy! Sonrisa