Tracking Data Query – Number of messages received in last day

More for later reference that anything.  The following query can of course be run via the HAT query window or SQL Management Studio on BizTalkDTADb and will return the number of messages received via a supplied receive port in the last day.
— Number of messages received in the last day
declare @Timestamp as datetime
set @Timestamp = GETUTCDATE() – 1  — local time converted to UTC
declare @PortName as varchar(50)
set @PortName = ‘<PortNameGoesHere>’
declare @Receive as nvarchar (100), @Send as nvarchar(100)
SELECT @Receive = strStatus FROM [dbo].[dta_MessageStatus] WHERE nMessageStatusId=0 — Receive=0
SELECT @Send = strStatus FROM [dbo].[dta_MessageStatus] WHERE nMessageStatusId=1 — Send=1
FROM dbo.dtav_MessageFacts mf WITH (READPAST)
WHERE [Event/Direction] = @Receive
AND [Event/Port] = @PortName
AND [Event/Timestamp] > @Timestamp
AND [MessageInstance/InstanceID] in
 SELECT mioe.uidMessageInstanceId
 FROM dbo.dta_MessageInOutEvents mioe WITH (READPAST)
 GROUP BY mioe.uidMessageInstanceId
 HAVING count (distinct cast (mioe.uidActivityId as varchar(36))) = 1 — message participated in single activity only
  AND min((mioe.nStatus-1)*(mioe.nStatus-1)) = 0 — at least one send (nStatus=1)
  AND min(mioe.dtTimestamp) > @Timestamp
This entry was posted in BizTalk Server. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s