Query BizTalk Tracking database

Ever wonder if you can just pass your orchestration name and get count of number of transaction / messages (received and send) for that orchestration throughout the day or a week.

As we all know by using HAT we can pretty much track all the messages, but sometimes its a little confusing since you get all the entries (send, recieve, orchestration etc). So what if you just want to know that for a particular orchestration how many messages have been received today or in past 3 days etc.

You will query "dtav_FindMessageFacts" a view in BizTalkDTADb, which contains all the tracking info. lets get to the sql,

select  count(*)
from     dbo.dtav_FindMessageFacts as dv
where
   dv.[ServiceInstance/ServiceName] = 'OrderProcess.OrderService' -- orchestration name (namespace included)
AND dv.[Event/Direction] = 'Receive'
AND dv.[Event/TimeStamp] between '2010-5-25 00:00:00:000' AND '2010-5-26 18:00:00:000'

I have specified the [Event/Direction] for my query, this will return all the messages that are recieved by my orchestration in the specified date range.

Hope this helps :)