Combining BizTalk Monitoring Job Results & BizTalk 360

In BizTalk 2010 a new job was added to BizTalk in the database which would perform some of the checks you can do against BizTalk to ensure its healthy.

Steef-Jan does an excellent job of explaining this monitoring job in this article.

In the article Steef-Jan also explains how you can create a view to help you see if there are any issues picked up by the job.  This is cool but it I was thinking there might be a better way to present the data.  In BizTalk 360 we have a feature called Custom SQL Queries.  This lets us add some SQL statements and gives us a nice user interface to execute them when we need to.  This has the benefit that the query now drops into our existing admin and operator interface.

The steps to do this are:

1. Add permission to the BizTalk 360 account to query those tables

You may or may not need to do this depending up on how you have setup your BizTalk 360 environment, but the easiest way to do this is most likely to be to run the following SQL command like you would have done to give BizTalk 360 access to other tables.

 

GRANT SELECT ON dbo.btsmon_inconsistancies TO BTS_ADMIN_USERS
GRANT SELECT ON dbo.btsmon_issues TO BTS_ADMIN_USERS

 

2. Go to the Custom SQL Queries part of the BizTalk 360 interface and add a new query

3. Use the below SQL which is the same as Steef-Jan was going to use for a view

 

SELECT

Inconsistancy.DBServer AS [Server Name],
Inconsistancy.DBName AS [Database Name],
Inconsistancy.nProblemCode AS [Problem Code],
Issue.nvcProblemDescription AS [Description]
FROM btsmon_Inconsistancies Inconsistancy
INNER JOIN [dbo].[btsmon_Issues] Issue ON Inconsistancy.nProblemCode = Issue.nProblemCode
WHERE Inconsistancy.nCount > 0

 

4. Add the friendly name (I called it BizTalk Out of Box Monitoring Job so its easy to know what it is) , Server name for your database server and the database name which is likely to be BizTalkMgmtDb

5. Click to save it

 

You should now have a new query in your Custom SQL Queries list giving you an easy way to see the output of the BizTalk job.