Is it possible to control BizTalk artifacts with a single request?

Question

Is there anyone who wrote a request to simultaneously monitor all BizTalk artifacts.

My request will not work, and I cannot do this:

Here is my:

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 designed and developed a test request for at least 3 BizTalk artifacts, and it also covers yours. First I try to explain my idea before I show the actual request.

The idea is to use as many BizTalk artifacts as possible, as one result table with port status and messaging status. Thus, it is easy to control whether something is really wrong or not. The state of the port is pretty simple because it is just a choice. The messaging state is in the port instance. First I will show you an ERD request.

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

Now, the request that controls Sendports, Receive Locations, and Orchestration:

 --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 the number, for messaging states I use the same as BizTalk, for port states I display Enabled and Disabled for 4 and 5, so I can see the recipient’s location status in the same column

Possible messaging states:

• 0: no
• 1: Started
• 2: completed
• 3: completed
• 4: paused
• 5: ready to go
• 6: Active
• 8: dehydrated
• 16: Done with discarded messages
• 32: suspended without resuming
• 64: At the breakpoint

Possible port states:

• 0: no
• 1: Unnamed
• 2: stopped
• 3: started
• 4: Enabled
• 5: disabled

Answer 2

You can refer to the article below, which explains how to track all instances of the node and start them if they are stopped. The same methods can be applied to other BizTalk artifacts: http://social.technet.microsoft.com/wiki/contents/articles/17835.biztalk-monitoring-and-automatically-starting-host-instances-via-a- scheduled-task.aspx