PowerShell for BizTalk Administration: Suspended Message Counts

Continuing on with using PowerShell to help with BizTalk administration, I'd like to focus on suspended messages.

Sometimes, one of our internal departments expects a message to come in at a particular time. There are times, however, that the message was never sent to BizTalk for processing. Either way, the "I don't see an expected message, can you see if it failed?" routine is communicated our way. Keep in mind that we already have put mechanisms in place to notify if something failed... :)

At any rate, I've developed a quick PowerShell script to see if anything indeed has suspended. This one uses SQL, and it hooks into the BizTalk MessageBox Database; make sure permissions are set accordingly. This post is rather code agnostic, but the approach is to use PowerShell to be consistent with the other scripts which our group has put in place.

The query is a common, read-only script that you may have seen elsewhere:

	$SqlQuery =`
	"SET NOCOUNT ON
	SET TRANSACTION ISOLATION LEVEL READ COMMITTED
	SET deadlock_priority LOW
	SELECT
	nvcName AS ApplicationName,
	uidInstanceID AS InstanceID,
	nErrorCategory,
	DATEADD(hh,-5,dtSuspendTimeStamp) AS DateSuspended, -- Subtract the appropriate hours for your timezone
	nvcAdapter AS Adapter,
	nvcURI AS URI,
	nvcErrorDescription AS ErrorDescription,
	CASE nState
	WHEN 1 THEN 'Ready To Run'
	WHEN 2 THEN 'Active'
	WHEN 4 THEN 'Suspended Resumable'
	WHEN 8 THEN 'Dehydrated'
	WHEN 16 THEN 'Completed With Discarded Messages'
	WHEN 32 THEN 'Suspended Non-Resumable'
	END AS State
	FROM InstancesSuspended WITH (NOLOCK)
	LEFT JOIN [Services] WITH (NOLOCK)
	ON InstancesSuspended.uidServiceID = [Services].uidServiceID
	LEFT JOIN Modules WITH (NOLOCK)
	ON Modules.nModuleID = [Services].nModuleID
	ORDER BY dtCreated DESC"

view raw gistfile1.ps1 hosted with ❤ by GitHub

The next step is to do the traditional SQL routine, create the SQL connection, open it, put it into a SQL adapter, and so on.

	$SQLServer = 'yourBizTalkSQLServer'
	$SQLDBName = 'BizTalkMsgBoxDb'
	$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
	$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
	$SqlConnection.Open()
	# Create SqlCommand object, define command text, and set the connection
	$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
	$SqlCmd.CommandText = $SqlQuery
	$SqlCmd.Connection = $SqlConnection
	$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
	$SqlAdapter.SelectCommand = $SqlCmd
	# Return/populate first iteration of DataSet
	$DataSet1 = New-Object System.Data.DataSet
	$SqlAdapter.Fill($DataSet1)
	# Housekeeping
	$SqlConnection.Close()

view raw gistfile1.ps1 hosted with ❤ by GitHub

Now you have a list of all suspended messages in a typical .NET DataSet. From there we can slice and dice the information. I like sorting and grouping everything out, and PowerShell does a fantastic job of that.

	$arrInstancesSuspended = @()
	foreach ($Row in $DataSet1.Tables[0].Rows)
	{
	$arrInstancesSuspended = $arrInstancesSuspended + (,($Row["ApplicationName"],$Row["InstanceID"]))
	# Use format: $arrInstancesSuspended [0][0]
	}
	# Sort Array
	$arrInstancesSuspended = $arrInstancesSuspended | Sort-Object @{Expression={$_[0]};Ascending=$true}
	# Get unique apps
	$unique_Application = $arrInstancesSuspended | foreach {$_[0]} | sort-object -unique
	# Ready to push out needed values
	foreach ($appName in $unique_Application)
	{
	$Temp = ($arrInstancesSuspended -match $appName) | where {$_[0].Length -eq $appName.Length}
	$Count = $Temp.count
	Write-Host $appName $Count
	}

view raw gistfile1.ps1 hosted with ❤ by GitHub

The result is a list of unique BizTalk applications in your environment with a suspended message count for each application. If there aren't any suspended messages, obviously you won't have any rows in your DataSet, and you can notify your customers that everything is operating as expected in BizTalk. ;)