Querying and Syndicating BizTalk Traffic Metrics By Application

Have you ever wanted a clean query of traffic through BizTalk on a per application basis? And, how about exposing that information to your internal users in a very Web 2.0 fashion?

Our chief architect asked me if it was feasible to syndicate BizTalk metrics using a product like RSSBus. Given that BizTalk’s messaging metrics are stored in a database, I figured it would be fairly straightforward. However, my goal was to not only present overall BizTalk traffic information, but ALSO do it on a per application basis so that project teams could keep track of their BizTalk components.

So, the first step was to write the SQL queries that would extract the data from the BizTalk databases. I wanted two queries: one for all messaging metrics per application, and, one for all suspended messages per application. I figured that it’d be useful for folks to be able to see, real-time, how many messages had failed for their application.

My “traffic” query returns a result set like this:

Based on the interval you provide (past day, 2 weeks, 6 months, etc), all services for the given application are shown. Getting the “application” information required joining with the BizTalkMgmtDb database. Also, I had to take into account that BizTalk database timestamps are based on UTC. So, I subtracted 7 hours from the service completion time to get accurate counts.

Next is the “suspended messages by application” query. For this one, I got inspiration from Scott Woodgate’s old Advanced MessageBox Queries paper. Once again, I had to join on the BizTalkMgmtDb database in order to filter by application. The result of this query looks like this:

For each service, you see the type and the count by status.

The next step was taking this from “randomly executed SQL query” to syndicated feed. RSSBus is a pretty cool product that we’ve been looking for an excuse to use for quite some time. RSSBus comes with all sorts of connectors that can be used to generate RSS feeds. Naturally, you can write your own as well. This shot below is of a Yahoo! connector that’s included …

I took my SQL scripts and turned those into stored procedures (in a new database, avoiding any changes to the BizTalk databases). I then used the “SQL Connector” provided by RSSBus to call the stored procedure. Since we don’t have an enterprise RSS reader yet at my company, I used the RSSBus option of exposing a “template” instead. I added some HTML so that a browser-user could get a formatted look at the traffic statistics …

To prevent unnecessary load on the MessageBox and other BizTalk databases, I set the cache interval, so that the query will be executed no more than once per hour.

Pretty cool, eh? “Traffic by Application” was something I wanted to see for a while, so hopefully that helps somebody out.

Technorati Tags: BizTalk