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])

BizTalk blog posts 02/2013 - 05/2013

The past time I haven't posted a lot on this blog, especially not much blog posts on BizTalk subjects. This doesn't mean that I haven't done any BizTalk related blog posts, I just haven't done it on my personal blog for a while... I've mostly posted on my companies blog, where I am the sole contributor and administrator.

But for all of you loyal readers of my personal blog, I thought it would be nice to give an overview of all of my latest posts. I'm thinking of doing this every once in a while to keep all of you updated on ALL of my posts.

So here goes for the period till May 2013...

• BizTalk error: ‘The message found multiple request response subscriptions’: solution to sending messages to multiple request-response send ports
• BizTalk file mask difference for windows 7 and windows server 2008 r2: A difference in file mask usage between windows 7 and windows server 2008 r2
• BizTalk360 v6.0: new features explained: a quick view on some of the improvements introduced in the new BizTalk360 version 6.0
• BizTalk Server 2013 is now RTM!: the announcement of the new BizTalk version release on MSDN, with the new features listed
• BTS2013 features explained: XslCompiledTransform: I started a new series on viewing some of the new features of BizTalk 2013 in more detail, the first one was about the XslCompiledTransform
• BTS2013 features explained: Improvements in dynamic send ports: follow up post on a new BizTalk 2013 feature on the Improvements in dynamic send ports
• BTS2013 features explained: Dependency tracking: an in depth view of the new dependency tracking feature of BizTalk 2013
• First Cumulative Update for BizTalk 2013… available through Microsoft Update!: the announcement that the very first cumulative update was released for BizTalk 2013, and that it even was available through Microsoft Update
• BAM error: Failed to list permissions for BAM view – System.Data.SqlTypes.SqlNullValueException: Data is Null: a solution to a BAM issue I encountered

Hope you enjoy these blog posts!