Monitoring suspended orchestrations using System Center Operations Manager

When using SCOM as a monitoring tool in a BizTalk environment you will find some shortages from time to time in the BizTalk Server management pack.

We already solved many of these shortages in our own Codit BizTalk management pack (containing custom rules, overrides,…).

An example of such a shortage is the default monitoring of suspended orchestrations. The out of the box management pack contains a monitor called “Orchestration Suspended Instances Availability Monitor”.

Assuming you know the difference between alerts and monitors, the biggest disadvantage of this monitor is its nature: being a monitor. When an orchestration gets suspended for some reason, you will receive an alert. However when other orchestrations of the same type are getting suspended, and the previous instance has not been terminated/resumed yet, no new alert(s) will be fired.

It can be fatal in a production environment if the support team doesn’t receive an alert. Also in a scenario where an orchestration gets suspended by code for some seconds (waiting for another orchestration to finish first, sequencing,…) a false alert could be triggered.

We created an alert that will notify us every time an orchestration gets suspended and stays suspended longer than a certain amount of time.

This is how to create such an alert:

Create a new rule to execute a script every 15 minutes:

Script for this rule:

‘ ———————————————————
‘ SQL Database Query Check
‘ ———————————————————
‘ Param 0: The SQL connection string for the server
‘ Param 1: The Database to use
‘ Param 2: SQL Query
‘ Author: Brecht Vancauwenberghe
‘ Date: 05-02-2014
‘ ———————————————————
Option Explicit

Sub Main()

Dim oAPI, strServer, strDatabase, iThresholdHours, objBag, strErrDescription, objArgs, I, Param

Const EVENT_TYPE_ERROR = 1
Const EVENT_TYPE_WARNING = 2
Const EVENT_TYPE_INFORMATION = 4

‘ Initialize SCOM Script object
Set oAPI = CreateObject(“MOM.ScriptAPI”)

‘ Write Parameters to eventlog
‘ Enable for debugging.
Set objArgs = Wscript.Arguments
For I = 0 to objArgs.Count -1
Param = objArgs(I)
‘strErrDescription = strErrDescription & “, ” & Param
Next
‘call oAPI.LogScriptEvent(“SQL Database Query Check.vbs”, 1313, EVENT_TYPE_INFORMATION, strErrDescription)

If WScript.Arguments.Count = 3 then

‘ Retrieve parameters
strServer = CStr(WScript.Arguments(0))
strDatabase = CStr(WScript.Arguments(1))

‘Connect to the database
Dim cnADOConnection
Set cnADOConnection = CreateObject(“ADODB.Connection”)
cnADOConnection.Provider = “sqloledb”
cnADOConnection.ConnectionTimeout = 60
Dim ConnString
ConnString = “Server=” & strServer & “;Database=” & strDatabase & “;Integrated Security=SSPI”
cnADOConnection.Open ConnString

‘Connection established, now run the code
Dim rst
Set rst = cnADOConnection.Execute(CStr(WScript.Arguments(2)))

‘ should be just one record
‘ oResults.MoveFirst

‘Set objBag = oAPI.CreateTypedPropertyBag(1)
‘Call objBag.AddValue(“Count”, CInt(oResults(0)))

‘oAPI.AddItem(objBag)

Do While Not rst.EOF
Call oAPI.LogScriptEvent(“check_suspended_orchestrations.vbs”,12223, EVENT_TYPE_Error, “An orchestration is suspended with following error :” & rst.fields.item(0) & ” and is active for longer than 5 minutes. Check the BizTalk environment!”)
rst.MoveNext
Loop
cnADOConnection.Close

‘return the property bag objects
‘Call oAPI.ReturnItems

End If

End Sub

Call Main()

Parameters for this rule:

$Target/Property[Type=”Microsoft.BizTalk.Server.2010.BizTalkGroup”]/MgmtDbServerName$ BizTalkDTADb “DECLARE @msgbox nvarchar (500)Set @msgbox = (select top(1) DBserverName from [BizTalkMgmtDb].[dbo].[adm_MessageBox] with (nolock))exec(‘SELECT * FROM [BizTalkDTADb].[dbo].[dtav_ServiceFacts] with (nolock) INNER JOIN [‘ + @msgbox + ‘].BizTalkMsgBoxDb.dbo.InstancesSuspended as msgboxdbinstancesSuspended ON [BizTalkDTADb].[dbo].[dtav_ServiceFacts].[ServiceInstance/InstanceId] = msgboxdbinstancesSuspended.uidInstanceID where [Service/Type] = ”Orchestration” and datediff(minute,[ServiceInstance/StartTime],getutcdate()) between 5 and 15’)”

The VBscript fires following SQL query defined as a parameter on the MSGBOX and DTA:

DECLARE @msgbox nvarchar (500)Set @msgbox = (select top(1) DBserverName from [BizTalkMgmtDb].[dbo].[adm_MessageBox] with(nolock))exec(‘SELECT * FROM [BizTalkDTADb].[dbo].[dtav_ServiceFacts] with(nolock) INNER JOIN [‘ + @msgbox + ‘].BizTalkMsgBoxDb.dbo.InstancesSuspended as msgboxdbinstancesSuspended  ON [BizTalkDTADb].[dbo].[dtav_ServiceFacts].[ServiceInstance/InstanceId] = msgboxdbinstancesSuspended.uidInstanceID  where [Service/Type] = ”Orchestration” and datediff(minute,[ServiceInstance/StartTime],getutcdate()) between 5 and 15’)

This query will return the type of the suspended orchestration,…

Another example is a query only using the MSGBOX:

SELECT nvcErrorDescription
FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended] with (nolock)
JOIN [BizTalkMsgBoxDb].[dbo].[ServiceClasses] as serviceclasses
ON uidClassID = serviceclasses.uidServiceClassID
where serviceclasses.nvcName = ‘Orchestration’ and datediff(minute,dtSuspendTimeStamp,getutcdate()) between 5 and 15

This will return the error description why the orchestration got suspended. You can choose the query you prefer, or do much more with it.

Remark, for the MSGBOX example will need to use following parameters (query is executed on the MSGBOX):

$Target/Property[Type=”Microsoft.BizTalk.Server.2010.BizTalkRuntimeRole”]/MsgBoxDbServerName$ BizTalkMsgBoxDb “SELECT nvcErrorDescription FROM [BizTalkMsgBoxDb].[dbo].[InstancesSuspended] with(nolock) JOIN [BizTalkMsgBoxDb].[dbo].[ServiceClasses] as serviceclasses ON uidClassID = serviceclasses.uidServiceClassID where serviceclasses.nvcName = ‘Orchestration’ and datediff(minute,dtSuspendTimeStamp,getutcdate()) between 5 and 15”

Also, when using the MSGBOX example don’t forget to change your Alert target to BizTalk Run-Time Role:

Now you need to create a second rule to check for this eventID in the eventlog so an alert is triggered per suspended orchestration. We have one rule that retrieves all custom alerts:

You can find lots of examples to do this on the internet: http://technet.microsoft.com/en-us/library/bb309568.aspx

This is the line in the script that writes to the eventlog, you will need to check for the eventid you write in the script. You can also write some result information of your query in the alert:

Call oAPI.LogScriptEvent(“check_suspended_orchestrations.vbs”,12223, EVENT_TYPE_Error, “An orchestration is suspended with following error :” & rst.fields.item(0) & ” and is active for longer than 5 minutes. Check the BizTalk environment!”)

This is the result, an emailed alert per suspended orchestration containing the error:

Alert: Check Eventlog for Custom SCOM Rules [Check alert content for more details]

Source: BizTalkMgmtDb.BELANSQLBTSPRDSBTS

Path: BELANBTSPRD1.Ghent.corp.mycompany.com

Last modified by: System

Last modified time: 06/02/2014 15:48:52

Alert description: Event Description: check_suspended_orchestrations.vbs : An orchestration is suspended with following error :Uncaught exception (see the ‘inner exception’ below) has suspended an instance of service ‘Mycompany.MyApplication.Ghent.Processes.CreateTimeOutAlerts(8b2addb3-0531-9078-786b-a96d77224319)’.

The service instance will remain suspended until administratively resumed or terminated.

If resumed the instance will continue from its last persisted state and may re-throw the same unexpected exception.

InstanceId: ee15bbad-e4a8-40fd-ad16-46e3cc6f1f81

Shape name: Save Alert

ShapeId: 2652d27b-61fc-47b9-84ad-eca188d776bf

Exception thrown from: segment 1, progress 62 Inner exception: Exception in SaveAlert

Exception type: ApplicationException

Source: MyCompany.MyApplication.Helpers

Target Site: Void Add(Int32, System.String, System.String) The following is a stack trace that identifies the location where the exception occured

at MyCompany.MyApplication.Helpers.AlertHelper.Add(Int32 stateId, String type, String status)

at MyCompany.MyApplication.Ghent.Processes.CreateTimeOutAlerts.segment1(StopCond and is active for longer than 5 minutes. Check the BizTalk environment!

This was tested on a SCOM 2012 SP1 environment using BizTalk Server 2010. We expect this to work on older/newer versions of SCOM and BizTalk, but at this moment it hasn’t been validated by Codit yet. Also note that you should be using an unsealed management pack.