BizTalk Health Statistics – Scripts

Sometimes you want to know how many messages have passed through BizTalk. Often this question pops up from a business perspective where they want to know how the use of the environment is evolving, in either positive or negative directions. It’s also useful to set out guidelines on how much data your environment can handle and to create upgrade plans when emergency levels are being reached.There are four kinds of statistics that we always try to gather. These are: 

  • Messages passing through per day (In & Out)
  • Instances (orchestrations & pipelines) executed per day
  • Usage of host instances
  • Largest message sizes

The statistics are gathered by executing SQL scripts on the Tracking Database.

Messages per day
 
SELECT datepart(dd, [dtInsertionTimeStamp]) as dateMsg, datepart(MM, [dtInsertionTimeStamp]) as monthMsg, count(Convert(char(10), [dtInsertionTimeStamp], 108)) as ant
FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK)
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime, ‘2014-02-10 00:00:00’, 120) AND convert(datetime, ‘2014-02-16 23:59:59’,  120)
GROUP BY datepart(dd, [dtInsertionTimeStamp]),datepart(MM, [dtInsertionTimeStamp]) ORDER BY [dateMsg]ASC, [monthMsg]
 
The “BETWEEN” part can be filled in with a timeframe of several days (in case your tracking database keeps all data on these days. A result as the following will be received:
 
dateMsg
monthMsg
ant
10
2
277262
11
2
381427
12
2
427297
13
2
291388
14
2
285235
15
2
3290
16
2
672
 

Instances per day

 
SELECT  datepart(dd, [dtInsertionTimeStamp]) as dateMsg, datepart(MM, [dtInsertionTimeStamp]) as monthMsg, count(Convert(char(10), [dtInsertionTimeStamp], 108)) as ant
FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] WITH (NOLOCK)
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime ,’2014-02-10 00:00:00′, 120) AND convert(datetime, ‘2014-02-16 23:59:59’, 120)
GROUP BY datepart(dd, [dtInsertionTimeStamp]), datepart(MM, [dtInsertionTimeStamp] )ORDER BY [dateMsg] ASC, [monthMsg]
 
Also in this script, the “BETWEEN” part can be filled in with a timeframe. Following result is received:
 
dateMsg
monthMsg
ant
10
2
127369
11
2
183848
12
2
201597
13
2
138431
14
2
132828
15
2
1536
16
2
314
 

Usage of host instances

 
SELECT count(Convert(char(10), [dta_ServiceInstances].[dtInsertionTimeStamp], 108)) as ant, [dta_Host].[strHostName]
FROM [BizTalkDTADb].[dbo].[dta_ServiceInstances] WITH (NOLOCK)
INNER JOIN [BizTalkDTADb].[dbo].[dta_Host] WITH (NOLOCK) ON
[dta_ServiceInstances].[nHostId]=[dta_Host].[nHostId]
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime ,’2014-02-10 00:00:00′, 120) AND convert(datetime, ‘2014-02-10 23:59:59’, 120)
GROUP BY [dta_Host].[strHostName] ORDER BY [dta_Host].[strHostName] ASC
 
In this script, a choice can be made in terms of grouping. When you want to have just the numbers of a timeframe (for example: between feb 01 and feb 15, there were 23,765 calls of MQHostReceive), just add the timeframe in the “BETWEEN” field. If you want the data grouped by day, the script needs to be executed once for every day you want the statistics. Set the “BETWEEN” to 2014-02-12 00:00:00 and 2014-02-12 23:59:59 for example, and you will get the following results:
 
ant
strHostName
303
NWND_BAM
301
NWND_BizTalkServerAdministation
23217
NWND_BizTalkServerApplication
15
NWND_ClusteredHost_RCV
18
NWND_ClusteredHost_SND
48481
NWND_MQConnectionHost_RCV
18127
NWND_MQConnectionHost_SND
10836
NWND_SAPProcessingHost
164
NWND_SAPLendingHost
2653
NWND_SIEBELProcessingHost
16746
NWND_TECHNICOMProcessingHost
6508
NWND_WCFHost
 

Largest message(s)

 
SELECT TOP 5 [dta_MessageInOutEvents].[nMessageSize], [dta_SchemaName].[strSchemaName]
FROM [BizTalkDTADb].[dbo].[dta_MessageInOutEvents] WITH (NOLOCK)
INNER JOIN [BizTalkDTADb].[dbo].[dta_SchemaName] WITH (NOLOCK) ON
[dta_MessageInOutEvents].[nSchemaId]=[dta_SchemaName].[nSchemaId]
WHERE [dtInsertionTimeStamp]
BETWEEN convert(datetime ,’2014-01-01 00:00:00′, 120) AND convert(datetime, ‘2014-01-01 23:59:59’, 120)
ORDER BY [nMessageSize] DESC
 
This script will get a top 5 of all the largest messages that passed through BizTalk in the given timeframe. A result like following will pop up:
 
nMessageSize
strSchemaName
28272537
http://NWD.DataSchema.Issuer.Schemas#Issuers
28272537
28272537
28255369
28255369
 
If you got any remarks, please leave them in the comment section!
 
 
Andrew De Bruyne (@draitnn)
blogger

blogger

Curious to know more about this topic?

Working at i8c

i8c is a system integrator that strives for an informal atmosphere between its employees, who have an average age of approx 30 years old. We invest a lot of effort in the professional development of each individual, through a direct connection between the consultants and the management (no multiple layers of middle management). We are based in Kontich, near Antwerp, but our customers are mainly located in the triangle Ghent-Antwerp-Brussels and belong to the top 500 companies in Belgium (Securex, Electrabel, UCB, etc…).

Quality Assurance

i8c is committed to delivering quality services and providing customer satisfaction. That’s why we invested in the introduction of a Quality Management System, which resulted in our ISO9001:2000 certification. This guarantees that we will meet your expectations, as a reliable, efficient and mature partner for your SOA & integration projects.

i8c - ISO9001-2015

Also worth reading

Apigee Scope Validation using OpenAPI Specification

In API security and management, we often use a lot of different security mechanisms to protect the requested resource behind the API Gateway. One of these mechanisms is the validation of scopes to authorize a client on a specific sub-resource of the API. Most of

Read More »

Integrating with TIBCO CLOUD

Our experts Glenn, Jason, Jurgen, and Kevin dedicated an i8c FastTrack Day to examining the TIBCO iPaaS offering. Check out their Research & Development day report to learn what they uncovered. 👇  TIBCO CLOUD™ The TIBCO Cloud™ Integration enterprise integration platform-as-a-service (iPaaS) provides self-service integration

Read More »