Is it possible to monitor BizTalk artifacts with one single query?

Question

Is there anyone maybe who has written a query to monitor all BizTalk artifacts at once.

My query won't work and I can't seem to get it done:

Here is mine:

select
    RL.Name AS rlName
    , ('Url: ' + RL.InboundTransportURL + ' | Receiveport: ' + RP.nvcName) AS rlDescription
    , RL.Disabled AS rlStatus
    , RL.uidCustomCfgID as uidGuid
from BizTalkMgmtDb.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK)
    left join BizTalkMgmtDb.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK)
        ON RL.ReceivePortId = RP.nID
--Readpast and Rowlock are needed to avoid lock escalation.

Answer 1

I developed and designed a monitoring query for at least 3 BizTalk artifacts and it is covering yours as well. I try to explain my idea first before I show the actual query.

The idea is to use as many BizTalk artifacts as possible as one resulting table with both the port status and the messaging status. This way it is easy to monitor if something is actually wrong or not. The port status is rather simple because it is just a select. The messaging status is on an instance of a port. First I show you an ERD of the query.

In the above ERD you can see all the tables and the fields used in my query, in the image below is explained how the tables are used together:

Now here it is, the query which is monitoring Sendports, Receive Locations and Orchestrations:

--sendports, receive locations and orchestrations combined into one query
Declare @PortStatus as bigint  = null
Declare @MessagingStatus as bigint  = null
Declare @Name as Varchar(500) = null
Declare @Type as Varchar(500) = null
Declare @UniqueID as Varchar(500) = null 

;with combined as 
(
     (
        select s.uidGUID as uidGuid, s.nvcName AS Name, nPortStatus as PortStatus, 'SENDPORT' as [Type], nvcDescription as Description
        from dbo.[bts_sendport] AS s
     )
     union all
     (  
        select o.uidGUID as uidGuid, o.nvcName AS Name, nOrchestrationStatus as PortStatus, 'ORCHESTRATION' as [Type], nvcDescription as Description
        from dbo.[bts_Orchestration] AS o
     )
     union all
     (
        select
        RL.uidCustomCfgID as UniqueKey, RL.Name AS Name,
        CASE WHEN RL.Disabled = 0 THEN 4 ELSE 5 END as [PortStatus],
        'RECEIVELOCATION' as [Type]
        , ('Url: ' + RL.InboundTransportURL + ' | Receiveport: ' + RP.nvcName) as Description
        from BizTalkMgmtDb.dbo.adm_ReceiveLocation AS RL WITH(READPAST, ROWLOCK)
        left join BizTalkMgmtDb.dbo.bts_receiveport AS RP WITH(READPAST, ROWLOCK)
        ON RL.ReceivePortId = RP.nID 
     )
)

select uidGuid as UniqueKey, Name, Description,
CASE WHEN i.nState is NULL THEN 0 ELSE COUNT(*) END as [MessageCount],
[Type], i.nState as MessagingStatus, c.PortStatus
from [BizTalkMsgboxDb].dbo.[Instances] AS i WITH (NOLOCK)
    right join combined c ON i.uidServiceID = c.uidGuid
    WHERE 
    (@Type is null OR [Type] like '%' + @Type + '%') 
    AND uidGuid = COALESCE(@UniqueID, uidGuid) 
group by uidGUID, Name, i.nState, [Type], c.PortStatus, Description
having  c.PortStatus = COALESCE(@PortStatus, c.PortStatus) 
    AND (@MessagingStatus is NULL OR i.nState = @MessagingStatus) 
order by [Type], c.PortStatus, i.nState

In the above query I map the states to number, for messaging states I use the same as BizTalk does, for port states I map Enabled and Disabled to 4 and 5, so I can see receive location status in the same column

Possible messaging states:

• 0 : None
• 1 : Started
• 2 : Completed
• 3 : Terminated
• 4 : Suspended
• 5 : Ready To Run
• 6 : Active
• 8 : Dehydrated
• 16: Completed With Discarded Messages
• 32: Suspended Non Resumable
• 64: In Breakpoint

Possible Port states:

• 0 : None
• 1 : Unenlisted
• 2 : Stopped
• 3 : Started
• 4 : Enabled
• 5 : Disabled