Monitoring BizTalk Server Without SCCM, using SQL query

For the environment that does not have SCCM to monitor BizTalk server health, the following SQL query can be incorporate into a SQL job and have BizTalk server at least on a "constant" check.

Note: be aware that the following was tested only against a single suspended instance with a send port.  Since BizTalk SQL server does not allow select against the ServiceClass table, I couldn't use a single table join.  With the data I currently have, I am not 100% sure the Receive Port, Pipeline, Orchestration parts work.

 
SELECT TOP 1000
      _M.nvcName AS ApplicationName
      , CASE
            WHEN _BSP.nvcName IS NOT NULL
                  THEN 'Send Port: ' + _BSP.nvcName
            WHEN _BSP.nvcName IS NOT NULL
                  THEN 'Receive Port: ' + _BSP.nvcName
            WHEN _BPL.Name IS NOT NULL
                  THEN 'Pipeline: ' + _BPL.Name
            WHEN _BO.nvcName IS NOT NULL
                  THEN 'Orchestration: ' + _BO.nvcName
            ELSE 'Unknown'
      END AS ServiceName
      , _IS.dtCreated
      , _IS.dtSuspendTimeStamp
      , _IS.nvcAdapter
      , _IS.nvcURI
      , _IS.nvcErrorDescription
      , _IS.nvcErrorProcessingServer
FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended] AS _IS WITH(READPAST)
      INNER JOIN [BizTalkMsgBoxDb].[dbo].[Services] AS _S WITH(READPAST)
            ON _IS.[uidServiceID] = _S.uidServiceID
      INNER JOIN [BizTalkMsgBoxDb].[dbo].[Modules] AS _M WITH(READPAST)
            ON _S.nModuleID = _M.nModuleID
      LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_sendport] AS _BSP WITH(READPAST)
            ON _IS.uidServiceID = _BSP.uidGUID
      LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_receiveport] AS _BRP WITH(READPAST)
            ON _IS.uidServiceID = _BRP.uidGUID
      LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_pipeline] AS _BPL WITH(READPAST)
            ON _IS.uidServiceID = _BPL.PipelineID
      LEFT OUTER JOIN [BizTalkMgmtDb].[dbo].[bts_orchestration] AS _BO WITH(READPAST)
            ON _IS.uidServiceID = _BO.uidGUID
WHERE
      _M.nvcName = 'Your application name' -- change application name here to monitor different applications
 

 

Print | posted on Wednesday, September 14, 2011 10:37 PM

Feedback

No comments posted yet.

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski