Using SQL queries in the BAM database to measure the performance and health of ESB Toolkit itineraries

Last year I created a Wiki about Using BAM in the ESB Toolkit that described what you get out of the box and how you can use it. I also created a custom BAM Dashboard sample in ASP.NET with several Charts that queries the BAM data in BAMPrimaryImport database. You can use these Charts and modify them to support your own needs if you want to create a Portal for example.
A Portal with Charts on top of the ESB Toolkit is ideal for administrators to measure the performance and health of the itineraries. But what if you don’t have time or resources to create a custom Portal? Or for example the Production environment is already running and you are not allowed to deploy the sample on it?
In that case you can also query the BAM data in BAMPrimaryImport database yourself. It isn’t that fancy as the Charts but it also does the trick!

SQL queries about Itineraries

The following SQL queries collect BAM data about Itineraries:

  • Amount of processed Itineraries for a specific date
  • The maximum, minimum and average times in seconds of all itineraries for a specific date
  • The maximum, minimum and average times in milliseconds of all itineraries for a specific date
  • Amount of running itineraries for a specific date
  • Amount of running itinerary services for a specific date
  • The maximum, minimum and average itinerary service execution times in milliseconds for a specific date
  • Overview of all the itinerary services execution times in milliseconds for a specific date
-- Amount of processed Itineraries for a specific date
SELECT ic1.ItineraryName
,      COUNT(*) AS Amount 
FROM   bam_ItineraryServiceActivity_CompletedInstances ic1
WHERE  ItineraryState = 'Complete' 
AND    ic1.ItineraryBeginTime > '2015-05-16 00:00:00.000'
AND    ic1.ItineraryBeginTime < '2015-05-17 23:00:00.000'
GROUP BY ic1.ItineraryName
Amount of processed Itineraries for a specific date
 
-- The maximum, minimum and average times in seconds of all itineraries for a specific date 
SELECT ic1.ItineraryName
,      COUNT(*) as Amount
,      MAX(DATEDIFF(SECOND,ItineraryBeginTime,ItineraryEndTime)) AS 'Max ExecutionTime (s)'
,      MIN(DATEDIFF(SECOND,ItineraryBeginTime,ItineraryEndTime)) AS 'Min ExecutionTime (s)'
,      AVG(DATEDIFF(SECOND,ItineraryBeginTime,ItineraryEndTime)) AS 'Avg ExecutionTime (s)'
FROM   bam_ItineraryServiceActivity_CompletedInstances ic1
WHERE  ItineraryState = 'Complete' 
AND    ic1.ItineraryBeginTime > '2015-05-16 00:00:00.000'
AND    ic1.ItineraryBeginTime < '2015-05-17 23:00:00.000'
GROUP BY ic1.ItineraryName
The maximum minimum and average times in seconds of all itineraries for a specific date
 
-- The maximum, minimum and average times in milliseconds of all itineraries for a specific date 
SELECT ic1.ItineraryName
,      COUNT(*) as Amount
,      MAX(DATEDIFF(MILLISECOND,ItineraryBeginTime,ItineraryEndTime)) AS 'Max ExecutionTime (ms)'
,      MIN(DATEDIFF(MILLISECOND,ItineraryBeginTime,ItineraryEndTime)) AS 'Min ExecutionTime (ms)'
,      AVG(DATEDIFF(MILLISECOND,ItineraryBeginTime,ItineraryEndTime)) AS 'Avg ExecutionTime (ms)'
FROM   bam_ItineraryServiceActivity_CompletedInstances ic1
WHERE  ItineraryState = 'Complete' 
AND    ic1.ItineraryBeginTime > '2015-05-16 00:00:00.000'
AND    ic1.ItineraryBeginTime < '2015-05-17 23:00:00.000'
GROUP BY ic1.ItineraryName
The maximum minimum and average times in milliseconds of all itineraries for a specific date
 
-- Amount of running itineraries for a specific date
SELECT ic1.ItineraryName
,      COUNT(*) as Amount  
FROM   bam_ItineraryServiceActivity_CompletedInstances ic1
WHERE  ServicePosition = 0 
AND    InterchangeId NOT IN (SELECT InterchangeId 
                             FROM   bam_ItineraryServiceActivity_CompletedInstances 
                             WHERE  ItineraryState = 'Complete') 
AND    ic1.ItineraryBeginTime > '2015-05-16 00:00:00.000'
AND    ic1.ItineraryBeginTime < '2015-05-17 23:00:00.000'
GROUP BY ic1.ItineraryName
Amount of running itineraries for a specific date
 
-- Amount of running itinerary services for a specific date
SELECT ic1.ServiceBusinessName
,      ic1.ESBServiceName
,      ic1.ServiceType
,      COUNT(*) as Amount 
FROM   bam_ItineraryServiceActivity_CompletedInstances ic1
WHERE  EXISTS (SELECT ic2.InterchangeId
               FROM   bam_ItineraryServiceActivity_CompletedInstances ic2
               WHERE  ic2.InterchangeId NOT IN (SELECT ic3.InterchangeId
                                                FROM   bam_ItineraryServiceActivity_CompletedInstances ic3
                                                WHERE  ic3.ItineraryState = 'Complete')
               AND ic2.InterchangeId = ic1.InterchangeId
               GROUP BY ic2.InterchangeId
               HAVING max(ServicePosition) = ic1.ServicePosition)
AND    ic1.ItineraryBeginTime > '2015-05-16 00:00:00.000'
AND    ic1.ItineraryBeginTime < '2015-05-17 23:00:00.000'
GROUP BY ic1.ServiceBusinessName
,        ic1.ESBServiceName
,        ic1.ServiceType
Amount of running itinerary services for a specific date
 
-- The maximum, minimum and average itinerary service execution times in milliseconds for a specific date
-- NOTES: 
--        - There is no BeginTime and EndTime for a service in BAM. 
--        - The TimeStamp field is used to estimate the EndTime. 
--        - The first service is not in the results. 
SELECT ic1.ESBServiceName
,      ic1.ServiceType
,      COUNT(*) as Amount
,      MAX(DATEDIFF(MILLISECOND,ic2.[TimeStamp],ic1.[TimeStamp])) AS 'Max ExecutionTime (ms)'
,      MIN(DATEDIFF(MILLISECOND,ic2.[TimeStamp],ic1.[TimeStamp])) AS 'Min ExecutionTime (ms)'
,      AVG(DATEDIFF(MILLISECOND,ic2.[TimeStamp],ic1.[TimeStamp])) AS 'Avg ExecutionTime (ms)'
FROM   bam_ItineraryServiceActivity_CompletedInstances ic1
INNER JOIN bam_ItineraryServiceActivity_CompletedInstances ic2
ON     ic1.InterchangeId=ic2.InterchangeId
WHERE  ic2.ServicePosition = (ic1.ServicePosition-1)
AND    ic1.ItineraryBeginTime > '2015-05-16 00:00:00.000'
AND    ic1.ItineraryBeginTime < '2015-05-17 23:00:00.000'
GROUP BY ic1.ESBServiceName
,        ic1.ServiceType
The maximum minimum and average itinerary service execution times in milliseconds for a specific date
 

 

SQL queries about Exceptions

The following SQL queries collect BAM data about Exceptions:

  • Number of exceptions per BizTalk Application for a specific date
  • Number of exceptions per FaultGenerator for a specific date
  • Number of exceptions per FaultGenerator and Service for a specific date
  • Number of exceptions per ErrorType for a specific date
-- Number of exceptions per BizTalk Application for a specific date
SELECT [Application]
,      COUNT(*) as Amount
FROM   bam_EsbExceptions_CompletedInstances
WHERE  [ExcDatetime] > '2015-01-01 00:00:00.000'
AND    [ExcDatetime] < '2015-05-17 23:00:00.000'
GROUP BY [Application]
Number of exceptions per BizTalk Application for a specific date
 
-- Number of exceptions per FaultGenerator for a specific date
SELECT [FaultGenerator]
,      COUNT(*) as Amount
FROM   bam_EsbExceptions_CompletedInstances
WHERE  [ExcDatetime] > '2015-01-01 00:00:00.000'
AND    [ExcDatetime] < '2015-05-17 23:00:00.000'
GROUP BY [FaultGenerator]
Number of exceptions per FaultGenerator for a specific date
 
-- Number of exceptions per FaultGenerator and Service for a specific date
SELECT [FaultGenerator]
,      [SvcName]
,      COUNT(*) as Amount
FROM   bam_EsbExceptions_CompletedInstances
WHERE  [ExcDatetime] > '2015-01-01 00:00:00.000'
AND    [ExcDatetime] < '2015-05-17 23:00:00.000'
GROUP BY [FaultGenerator]
,        [SvcName]
Number of exceptions per FaultGenerator and Service for a specific date
 
-- Number of exceptions per ErrorType for a specific date
SELECT [ErrorType]
,      COUNT(*) as Amount
FROM   bam_EsbExceptions_CompletedInstances
WHERE  [ExcDatetime] > '2015-01-01 00:00:00.000'
AND    [ExcDatetime] < '2015-05-17 23:00:00.000'
GROUP BY [ErrorType]
Number of exceptions per ErrorType for a specific date

 

Note that the ItineraryBeginTime field is used to filter the query results in all the SQL queries about Itineraries to only show the data from a specific date and time. In the SQL queries about Exceptions is the ExcDatetime field used to filter the data.

You can download the .zip file with all the SQL queries here: BAM_ESB Toolkit_queries.zip

About the author

Tomasso Groenendijk lives in Netherlands and is a senior integration consultant at Motion10. He has over 15 years’ experience in software development and software design. Tomasso is specialized in application integration with a strong interest in the Windows Azure cloud platform. In 2014, 2015 and 2016 Tomasso was awarded with the Microsoft Azure MVP award. He is an active contributor to the integration community through his work on his blog, MSDN samples, TechNet Wiki and also speaking on events.

 
 
 
MVP profile Tomasso Groenendijk
 
 
Sentinet Product Specialist
 
 
BizTalk360 Product Specialist

Month List