SQL query: hourly processed messages for BizTalk ports

One of my clients wanted to know how many messages were processed on a certain BizTalk port.
To show some statistics, I created a SQL query that lists a message count of the messages processed per hour for a certain Port. The SQL query will run against the BizTalk tracking database BizTalkDTAdb.

Here’s the SQL query I used:

use BizTalkDTAdb

SELECT top 200
      CONVERT( VARCHAR(10), a.[Event/Timestamp], 111) as [date],
DATEPART(HOUR,a.[Event/Timestamp]) as [Hour],
COUNT(*) as [Count]
FROM [dbo].[dtav_MessageFacts] a
inner join dbo.btsv_Tracking_Parts b on a.[MessageInstance/InstanceID] = b.uidMessageID
a.[Event/Port] =‘RP_SAM.Messaging_SamData_MPM’
AND a.[Event/Direction] =‘Receive’
AND a.[Event/Timestamp] >DATEADD(day,3,GETDATE())
GROUP BY  a.[Event/Port], CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp]), a.[Event/Direction]
ORDER BY CONVERT(VARCHAR(10), a.[Event/Timestamp], 111), DATEPART(HOUR,a.[Event/Timestamp])