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.
Mike, custom SQL query module can be used for variety of purposes like the one you mentioned. Many custom reports can be produced.
For your purpose, I guess the user can directly view the SQL Server Instances link which shows all the SQL jobs and their statuses
http://assist.biztalk360.com/support/solutions/articles/1000130264-viewing-the-sql-server-instance-information
in addition we also show other important information like data size, log size, auto growth property etc.