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:
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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" |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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() |
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.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
$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 | |
} |
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. ;)