donderdag 3 oktober 2013

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.

Here's the SQL query I used:
use BizTalkDTAdb

SELECT top 200
      a.[Event/Direction],
      a.[Event/Port],
      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
WHERE a.[Event/Port] <> 'PortSqlMonitor'
AND a.[Event/Port] = 'BizTalkPort'
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])