Posts Tagged ‘Monitoring’

I just updated three of my useful BizTalk PowerShell scripts in some cases fixing some small bugs, improving performance and in other cases adding also new capabilities.

PowerShell to configure Default Dynamic Send Port Handlers for each Adapter: This is a simple script that allows you to configure the default send handlers associated with all the existing Dynamic Send ports in your environment

  • In this new release I fixed some small bugs in the code that were not allowing the script to work as expected.

Check more about the script here: BizTalk DevOps: How to configure Default Dynamic Send Port Handlers with PowerShell

The full script can be found and download on Microsoft TechNet Gallery:
PowerShell to configure Default Dynamic Send Port Handlers for each Adapter (3.0 KB)
Microsoft TechNet Gallery

 

PowerShell to Configure the Default Send Handler for each static Send Port: This is a simple script that allows you to configure the Send handler associated with all the existing Static Send Ports in your environment independently of the adapter that is configured.

  • In this new release I fixed some small inconsistencies in the code, nevertheless in this case the previous script was working fine.

Check more about the script here: BizTalk DevOps: Configure the Default Send Handler as the Send Handler for each existing static Send Ports with PowerShell

The full script can be found and download on Microsoft TechNet Gallery:
PowerShell to Configure the Default Send Handler for each static Send Port (3.0 KB)
Microsoft TechNet Gallery

 

Monitoring BizTalk Server Ports with Auto-Healing capabilities with PowerShell: This is a simple script that allows you to monitor your BizTalk environment for disabled receive locations and stopped or unenlisted send ports, and automatically resume then according to certain conditions, using PowerShell.

In this new release:

  • I improved the general performance of the script
  • And add a new feature to the script: the ability to save a copy of the monitor report in the hard drive or a shared folder.

Check more about the script here: BizTalk DevOps: Monitor your BizTalk environment using PowerShell –Monitoring Ports (Stopped/disabled/unelisted) with Auto-Healing capabilities

The script can be found and download on Microsoft TechNet Gallery:
Monitoring BizTalk Server Ports with Auto-Healing capabilities with PowerShell (14.0 KB)
Microsoft TechNet Gallery

 

So, why these script are important?

All of these task can be easily made manually but they are time consuming tasks, special the monitor task if you don’t have the proper monitor tools like BizTalk360 or other tools available in the market. These PowerShell scripts allows you to automate and streamline these tasks saving us a considerable amount of time.

THESE POWERSHELL ARE PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

Quite a long time that Message Box Viewer in fully integrated with BizTalk360, which is awesome, because MBV is, or was, the perfect tool to analyze and identifying potential issues in the BizTalk environment.

MBV had over 400 rules that were able to verify different configurations/settings on your BizTalk Server environment, gathering the results in order to provide information about the general health of the environment to the administrations teams, like:

  • Whether backup jobs, or any BizTalk Job, are configured and running properly
  • Whether default throttling settings are changed or custom adapters are added to your environment
  • Whether the BizTalk databases are at their optimum levels in terms of size
  • Performance issues
  • And so on…

BizTalk 360 took advantage of the power of MBV and integrated it, in past versions of the product, n its architecture to the present day. By integrating MBV, BizTalk360 was capable to offer the following additional values to the users:

  • The ability to perform scheduled execution of MBV
  • Show reports categorized in a nicer way
  • Have a single platform to monitor and analyze BizTalk Server environments

BizTalk360 V8.0 has the same capabilities, however, Message Box Viewer (aka MBV) is currently deprecated, and it was replaced by BizTalk Health Monitor (aka BHM) and it is no longer available for download. Of course if you are using BizTalk Server 2013 or higher you may know that MBV was included in BizTalk Server 2013 and you will find it in your BizTalk installation directory:

  • Normally at “C:\Program Files (x86)\Microsoft BizTalk Server 2013\SDK\Utilities\Support Tools\MsgBoxViewer”
  • Or “…\Microsoft BizTalk Server 2013 R2\SDK\Utilities\Support Tools\MsgBoxViewer” folder

I know that BizTalk360 team is already plan and working to fix it and integrate BizTalk Health Monitor (aka BHM) with BizTalk360 tool replacing the currently MBV.

However, BHM is based on the same engine as MBV, what happen was that the MBV project team, after releasing MBV as a standalone tool for several years, decided to integrate it more closely with the BizTalk Administration Console providing this way a quick and complete dashboard of a BizTalk group that will help BizTalk administrators to monitor the health of their BizTalk platform more effectively.

Previous MBV had an execution file call “MBVConsole.exe” (Console MBV Client tool) that allowed users to automatically schedule (via PowerShell or windows scheduler Task) and generate reports, same tool that BizTalk360 uses.

BHM no longer has this console tool (“MBVConsole.exe”). The tool was renamed to “BHMCollect.exe” but it is “exactly the same” as “MBVConsole.exe” in MBV… so I decided to give it a shot and see if I was able to use the last version of the tool inside BizTalk360.

I when to “Config and Schedule Message Box Viewer Integration” page inside BizTalk360:

  • Click the “Settings” icon at the top of the page and then selecting “Message Box Viewer” option from the left menu bar.

On “Config and Schedule Message Box Viewer Integration” panel, instead of configure the path to the MBV directory in the “Message Box Viewer Download Directory” property, I set the path to the Health Monitor BizTalk, in my case:

  • C:\Program Files (x86)\BizTalk Support Tools\BHMv3.1

MessageBox-Viewer-BizTalk360-integration-configurations

Of course this just is not enough, because as I told before, BizTalk360 is trying to use “MBVConsole.exe”. So, as a workaround, if you want to use the BHM integrated with BizTalk360 you need to:

  • Go to the BHM installation directory;
  • Make a copy of “BHMCollect.exe” and rename it to “MBVConsole.exe”
    • Important: do not rename the original file! You still need it as it is!
    • You will then have to files: “BHMCollect.exe” and “MBVConsole.exe” witch is exactly the same file!

BHM-integrated-BizTalk360-installation-folder

Now if you try to run it manually inside BizTalk360, you will see that it was able to execute and provide the reports in the same nice and beautiful way than using MBV.

BizTalk-Health-Monitor-BizTalk360-integration

Again, this solution is a workaround that you can use for now, because, BizTalk360 team is already plan and working to fix it and integrate BizTalk Health Monitor (aka BHM), replacing the currently MBV, in next versions of the product.

This is not really a critical problem because BHM is practically equal to MBV, but BHM may have some new improvements/hotfixes that you can use that MBV doesn’t have it.

There are several ways that you can integrate and schedule Message Box Viewer or BizTalk Health Monitor, since Message Box Viewer (aka MBV) is deprecated, and it is now replaced by BizTalk Health Monitor (aka BHM).

The first option is to use BHM itself, for that, if you have BHM integrated with BizTalk Administration Console (see how to here):

  • Open BizTalk Administration Console, expand BizTalk Health Monitor
  • And then right-click on “Default” profile (or your custom profile) and select “Settings…”
  • On the Monitoring Profile Settings – [Profile name] windows
    • Select “Schedule” panel and define your scheduler

BizTalk-Healt-Monitor-Scheduler-options

    • And then select “Notifications” panel to “customize” the desired notifications and email settings. For example:
      • Mail notification or/and Eventlog Notifications
      • Send a mail only when critical warnings are raised
      • And the option to attach the complete report (compressed)

BizTalk-Healt-Monitor-Notification-options

Of course, by using the BHM, you can customize also the types of queries that you want to run and so on.

The second option is, for example, to use BizTalk360 by:

  • Click the “Settings” icon at the top of the page and then selecting “Message Box Viewer” option from the left menu bar.
  • On “Config and Schedule Message Box Viewer Integration” panel you need to:
    • In the “Message Box Viewer Download Directory” property: Enter the path to the MBV directory in the
    • In the “Schedule Execution” panel:
      • In the “Environment” property: leave the default or select your BTS environment for which the MBV execution has to be scheduled
      • In the “Days” and “Time” properties – Select the day and time when the MBV should run automatically.
      • And finally, click “Save” to save the configuration settings.

MessageBox-Viewer-BizTalk360-integration

However, I was trying to full customize my notification warnings! And I want to receive an email notification if Warnings and Critical Warnings are raised but in some environments, special in small or test environments, I know that BizTalk machines are not fully configured as High Availability or according to all the best practices… sometimes these are limitations that you need to live with it. For example:

  • SSO DB: “Not Clustered (this DB is critical as it keep encrypted ports properties)!” – for small environments sometimes I only have one BizTalk Machine so in this case I don’t need, and I can’t, cluster the SSO. So, for me sometimes this is a false warning.
  • “MDF Db Growth for BizTalkMgmtDb” or “LOG Db Growth for BizTalkMgmtDb”: “Current =102400 KB (Def=1024 KB) – Recommended for this Db=1024 KB?!” – I have the MDF and LOG grow setting defined correctly but not according to the recommended setting of this tool, again, for me sometimes this is a false warning.
  • “LDF and MDF files Location for …”: “Same Drive (Can cause disk contention)!” – Again, sometimes the tool raises these warnings even if the location is different, so in certain cases, if I’m sure of the configurations I can consider this a false warning.
  • And so on…

Don’t get me wrong I love this tool but each time the report is generated, we need to make a review to the report and analyze what is real and what is “false” warnings, special the non-critical warnings. I want to be smarter and automate this task, so for fun I decide to give it a try with PowerShell script.

So how can PowerShell help us?

With this script you can be able to easily monitor the health of your BizTalk environment (engine and architecture) by scheduling Message Box Viewer(MBV) or BizTalk Health Monitor (BHM) and customize alerts using PowerShell.

Only if the script finds any critical warning (there are no conditions in critical warnings) or any non-critical warning that is not expected (what I may consider a “false” or expected warning) a mail notification will be sent.

Additional, if a notification mail is sent, I will also want to send the completed report (compressed) attached to the email.

This script allows you to set:

  • Set your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "mySMTPServer" #SMTP Server.
[STRING]$SubjectPrefix = "MBV Notification Report -  "
[STRING]$From = "biztalksupport@mail.pt"
[array]$EmailTo = ("sandro.pereira@devscope.net") 
  • Set location settings – the location of the tool and where the reports are saved can be different
#### Option to execute MBV
$mbvPath = "C:\Users\Administrator\Desktop\powerShell\MBV\"
#### Option to execute BHM
#$mbvPath = "C:\Program Files (x86)\BizTalk Support Tools\BHMv3.1\" 
$mbvReportSaveLocation = "C:\Users\Administrator\Desktop\powerShell\MBV\"
  • Critical errors we want to be notified (in this case all of them)
# Critical errors we want to be notified (in this case all of them)
if($xml.MSGBOXVIEWER.WARNINGSREPORT_RED.Count -gt 0)
{
    $mailBodyMsb += "<h3>Critical Warnings</h3> `n`n"

    Foreach ($criticalReport in $xml.MSGBOXVIEWER.WARNINGSREPORT_RED)
    {
        $countCriticalAlerts++;

        #Add mail content
        $mailBodyMsb += "<th><b>Critical Warning: " + $countCriticalAlerts + "</font></b></th>"
        $mailBodyMsb += "<table style='boder:0px 0px 0px 0px;'>"

        $mailBodyMsb += "<TR style='background-color:rgb(245,245,245);';><TD>Category</TD>"
        $mailBodyMsb += "<TD><b><font color='red'>" + $warningReport.Category + "</font><b></TD></TR>"
        
        $mailBodyMsb += "<TR style='background-color:white;'><TD>Item Caption</TD>"
        $mailBodyMsb += "<TD><b><font color='red'>" + $warningReport.ItemCaption + "</font><b></TD></TR>"
        
        $mailBodyMsb += "<TR style='background-color:rgb(245,245,245);';><TD>Details</TD>"
        $mailBodyMsb += "<TD>" + $warningReport.ItemValue + "</TD></TR>"

        $mailBodyMsb += "</table>"
        $mailBodyMsb += "<BR><BR>"
    }
}
  • Non-Critical errors we want to be notified – REPORT FILTER EXCLUSIONS
# Non-Critical errors we want to be notified - REPORT FILTER EXCLUSIONS
if($xml.MSGBOXVIEWER.WARNINGSREPORT_YELLOW.Count -gt 0)
{
    $mailBodyMsb += "<h3>Non Critical Warnings</h3> `n`n"

    Foreach ($warningReport in $xml.MSGBOXVIEWER.WARNINGSREPORT_YELLOW)
    {
        #####################################################################################
        # REPORT FILTER EXCLUSIONS
        #
        #Exclude from the report "false" warnings (like MDF Db Growth for) 
        #or warnings that you already know that you have and need to deal with it (like LDF and MDF files Location for)
        #or others that you want to exclude
        #####################################################################################
        if($warningReport.ItemCaption -eq "Errors during Collect")
        {
            continue;
        }

        if($warningReport.ItemCaption -eq "Class Settings Changed")
        {
            continue;
        }

        if(($warningReport.ItemCaption -Match "MDF Db Growth for") -or ($warningReport.ItemCaption -Match "LOG Db Growth for") -or ($warningReport.ItemCaption -Match "LDF and MDF files Location for"))
        {
            continue;
        }

        if(($warningReport.ItemCaption -Match "LDF files Location for BizTalkDTADb and BizTalkMsgBoxDb") -or ($warningReport.ItemCaption -Match "MDF files Location for BizTalkDTADb and BizTalkMsgBoxDb"))
        {
            continue;
        }

        if(($warningReport.ItemCaption -Match 'BizTalkServerApplication') -and ($warningReport.ItemValue -Match "Run Receive Location+Orchestration"))
        {
            continue;
        }

        if($warningReport.ItemCaption -Match 'SMS agent is running')
        {
            continue;
        }

        if($warningReport.ItemCaption -eq "Non WCF SQL adapter used in some Receive Locations")
        {
            continue;
        }

        if(($warningReport.ItemCaption -Match 'Server WH0') -and ($warningReport.ItemValue -Match "Running in VMware Virtual Platform "))
        {
            continue;
        }

        if($warningReport.ItemValue -eq "Custom or Third-party adapter !")
        {
            continue;
        }

        if($warningReport.ItemCaption -eq "'maxconnection' property")
        {
            continue;
        }
        #####################################################################################
        # Report Filter exclusions
        #####################################################################################

        $countWarningAlerts++;
        #Add mail content
        $mailBodyMsb += "<th><b>Warning: " + $countWarningAlerts + "</b></th>"
        $mailBodyMsb += "<table style='boder:0px 0px 0px 0px;'>"
        $mailBodyMsb += "<TR style='background-color:rgb(245,245,245);';><TD>Category</TD>"
        $mailBodyMsb += "<TD><b><font color='Orange'>" + $warningReport.Category + "</font><b></TD></TR>"
        
        $mailBodyMsb += "<TR style='background-color:white;'><TD>Item Caption</TD>"
        $mailBodyMsb += "<TD><b><font color='Orange'>" + $warningReport.ItemCaption + "</font><b></TD></TR>"
        
        $mailBodyMsb += "<TR style='background-color:rgb(245,245,245);';><TD>Details</TD>"
        $mailBodyMsb += "<TD>" + $warningReport.ItemValue + "</TD></TR>"

        $mailBodyMsb += "</table>"
        $mailBodyMsb += "<BR><BR>"
    }
}
  • Delete the reports history from “Save Report” folder (I can have the last day’s executions as history) and remove compressed files.
#remote the rip report file
Remove-Item $zipFile
#remote report folder older then X days
get-childitem $mbvReportSaveLocation |? {$_.psiscontainer -and $_.lastwritetime -le (get-date).adddays(-3)} |% {remove-item $_ -Force -Recurse}

Again, only if the script finds any Critical or non-critical warning (that is not expected) a mail notification will be sent.

Report sample:

MessageBox-Viewer-BHM-Notification-report-powershell

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

THIS POWERSHELL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

The script can be found and download on Microsoft TechNet Gallery:
How to schedule MBV or BHM and customize notification alerts with PowerShell (18.0 KB)
Microsoft TechNet Gallery

In the sequence of my last two posts, welcome back, once again (probably the last one for now), to this serial of articles about Monitor your BizTalk environment using PowerShell.

Today we will talk about a topic that a new topic, “How to monitor Host Instance in BizTalk Server using PowerShell”, and once again, we will add Auto Healing functionalities to the script.

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments and react promptly to possible problems, you can accomplish this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

There are bunch of things that can go wrong and the longer they have in an error/failure situation, more impact could have in your business! So, we don’t just want to be notified of failures (that will always lead to a manual human intervention) but instead, when possible, be also able to try to automatically fix it, bringing them to the desired state (running, resumed, enable – depending on the artifact)

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

So how can PowerShell help us?

With this script you can be able to monitoring your BizTalk environment for Host Instance that aren’t Started (Stopped, Start pending, Stop pending, Continue pending, Pause pending, Paused or Unknown), and automatically bring them to desired state (Started) according to certain conditions, using PowerShell.

Only if the script finds any suspended messages a mail notification will be sent.

This script allows you to set:

  • Set your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "mySMTPServer" #SMTP Server.
[STRING]$SubjectPrefix = "Host instances not running - "
[STRING]$From = "biztalksupport@mail.pt"
[array]$EmailTo = ("sandro.pereira@devscope.net")

The following Windows PowerShell script is a fragment that will help us demonstrate this capabilities:

# Get Host instances not running
[ARRAY]$hostInstances = get-wmiobject MSBTS_HostInstance  -namespace 'root\MicrosoftBizTalkServer' -filter '(HostType = 1 and ServiceState != 4)'

foreach ($hostInstance in $hostInstances)
{
    #Add mail content
    $mailBodyHI += "<th><b><font color='blue'>" + $hostInstance.Name + "</font></b></th>"
    $mailBodyHI += "<table style='boder:0px 0px 0px 0px;'>"
    $mailBodyHI += "<TR style='background-color:white;'><TD>Host Name</TD>"
    $mailBodyHI += "<TD><b><font color='red'>" + $hostInstance.HostName + "</font></b></TD>
</TR>"

    $mailBodyHI += "<TR style='background-color:rgb(245,245,245);';><TD>Host Type</TD>"
    $mailBodyHI += "<TD>In-process</TD></TR>"

    $mailBodyHI += "<TR style='background-color:white;'><TD>Running Server</TD>"
    $mailBodyHI += "<TD><b><font color='red'>" + $hostInstance.RunningServer + "</font></b></TD></TR>"

    # Stopped: 1
    # Start pending: 2
    # Stop pending: 3
    # Running: 4
    # Continue pending: 5
    # Pause pending: 6
    # Paused: 7
    # Unknown: 8

    if ($hostInstance.ServiceState -eq 1)
    {
        $statusHI = "Stopped" 
    }
    if ($hostInstance.ServiceState -eq 2)
    {
        $statusHI = "Start pending" 
    }
    if ($hostInstance.ServiceState -eq 3)
    {
        $statusHI = "Stop pending" 
    }
    if ($hostInstance.ServiceState -eq 5)
    {
        $statusHI = "Continue pending" 
    }
    if ($hostInstance.ServiceState -eq 6)
    {
        $statusHI = "Pause pending" 
    }
    if ($hostInstance.ServiceState -eq 7)
    {
        $statusHI = "Paused" 
    }
    if ($hostInstance.ServiceState -eq 8)
    {
        $statusHI = "Unknown" 
    }

    $mailBodyHI += "<TR style='background-color:rgb(245,245,245);';><TD>Service State</TD>"
    $mailBodyHI += "<TD><b><font color='red'>" + $statusHI + "</font></b></TD></TR>"

    #0: UnClusteredInstance
    #1: ClusteredInstance
    #2: ClusteredVirtualInstance
    if ($hostInstance.ClusterInstanceType -eq 0)
    {
        $statusHI = "UnClustered Instance" 
    }
    if ($hostInstance.ClusterInstanceType -eq 1)
    {
        $statusHI = "Clustered Instance" 
    }
    if ($hostInstance.ClusterInstanceType -eq 2)
    {
        $statusHI = "Clustered Virtual Instance" 
    }

    $mailBodyHI += "<TR style='background-color:white;'><TD>Cluster Instance Type</TD>"
    $mailBodyHI += "<TD>" + $statusHI + "</TD></TR>"

    $mailBodyHI += "<TR style='background-color:rgb(245,245,245);';><TD>Is Disabled</TD>"
    $mailBodyHI += "<TD>" + $hostInstance.IsDisabled + "</TD></TR>"

    $mailBodyHI += "<TR style='background-color:white;'><TD>NT Group Name</TD>"
    $mailBodyHI += "<TD>" + $hostInstance.NTGroupName + "</TD></TR>"

    $mailBodyHI += "<TR style='background-color:rgb(245,245,245);';><TD>Logon</TD>"
    $mailBodyHI += "<TD>" + $hostInstance.Logon + "</TD></TR>"
    
    #Auto-Healing feature
    if($hostInstance.IsDisabled = "False")
    {
        $hostInstance.InvokeMethod("Start",$null)

        $mailBodyHI += "<TR style='background-color:white;'><TD><font color='green'>Note</font></TD>"
        $mailBodyHI += "<TD><font color='green'>The Host Instance was started automatically by this script! Please access the environment to validate if all host instances are running.</font></TD></TR>"
    }

    $mailBodyHI += "</table>"
    $mailBodyHI += "<BR><BR>"
}

Here is example expected report output from running the Windows PowerShell script sample, if any of the Host instance are in an unwanted state.

BizTalk-Host-Instances-Report

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them. The script should also be adjusted to your needs.

THIS POWERSHELL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

The script can be found and download on Microsoft TechNet Gallery:
Monitoring BizTalk Host Instances with Auto-Healing with PowerShell (14.0 KB)
Microsoft TechNet Gallery

In the sequence of my last post, welcome back, once again, to this serial of articles about Monitor your BizTalk environment using PowerShell.

Today we will talk about a topic that was also previously covered by Jeroen, ” Monitor your BizTalk environment using PowerShell – Port monitoring”, but this time, I will also add Auto Healing functionalities to the script.

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments and react promptly to possible problems, you can accomplish this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

There are bunch of things that can go wrong and the longer they have in an error/failure situation, more impact could have in your business! So, we don’t just want to be notified of failures (that will always lead to a manual human intervention) but instead, when possible, be also able to try to automatically fix it, bringing them to the desired state (running, resumed, enable – depending on the artifact)

So how can PowerShell help us?

With this script you can be able to monitoring your BizTalk environment for disabled receive locations and stopped or unenlisted send ports, and automatically resume then according to certain conditions, using PowerShell.

A mail notification will be sent only if this scripts finds any receive location or send port in a non-conform situation.

This sample will demonstrate how can you easily create a script with a set of conditions to be able to monitor and to automatically fix the receive locations and send ports to the desired state:

  • “Enable” for receive locations
  • “Started” for send ports

This script allows you to set:

  • Set your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "mySMTPServer" #SMTP Server.
[STRING]$SubjectPrefix = "Port status - "
[STRING]$From = "biztalksupport@mail.pt"
[array]$EmailTo = ("sandro.pereira@devscope.net")

The following Windows PowerShell script is a fragment that will help us demonstrate this capabilities:

#Get Counters: Check if there are any disable receive locations and stopped/unelisted send ports
if ($sendports.count -ne (get-wmiobject MSBTS_SendPort -namespace 'root\MicrosoftBizTalkServer' -filter '(Status = 3)').count)
{
    [BOOL]$script:Continuescript = $True
    [INT]$countSendPorts = $sendports.count - $(get-wmiobject MSBTS_SendPort -namespace 'root\MicrosoftBizTalkServer' -filter {Status = 3}).count
}
if ($ReceiveLocations.count -ne (get-wmiobject MSBTS_ReceiveLocation -namespace 'root\MicrosoftBizTalkServer' -filter '(IsDisabled = False)').count)
{
    [BOOL]$script:Continuescript = $True
    [INT]$countReceivePorts = $ReceiveLocations.count - $(get-wmiobject MSBTS_ReceiveLocation -namespace 'root\MicrosoftBizTalkServer' -filter '(IsDisabled = False)').count
}

if($countSendPorts -gt 0)
{
    $mailTextReportPT += "" + $countSendPorts + " send ports stopped; "
    $mailBodyPT += "<h3>Send ports Stopped</h3>"

    #Add mail content for stopped send ports
    Foreach ($SendPort in $SendPorts)
    {
        #only add stopped send ports to the mail
        if ($SendPort.status -eq 2 -OR $SendPort.status -eq 1)
        {
            $StoppedSendPort = $True
            #Set status to a user friendly name
            if ($SendPort.status -eq 2)
            {
                $SendPortStatus = "Stopped"
            }
            elseif ($SendPort.status -eq 1)
            {
                $SendPortStatus = "Unelisted"
            }
    
            #Add mail content
            $mailBodyPT += "<th><b><font color='red'>" + $SendPort.name + "</font></b></th>"
            $mailBodyPT += "<table style='boder:0px 0px 0px 0px;'>"   
     
            $mailBodyPT += "<TR style='background-color:white;'><TD>Status</TD>"
            $mailBodyPT += "<TD><b><font color='red'>" + $SendPortStatus + "</font><b></TD></TR>"
        
            $mailBodyPT += "<TR style='background-color:rgb(245,245,245);';><TD>URI</TD>"
            $mailBodyPT += "<TD>" + $SendPort.PTAddress + "</TD></TR>"
        
            $mailBodyPT += "<TR style='background-color:white;'><TD>Transport type</TD>"
            $mailBodyPT += "<TD>" + $SendPort.PTTransportType + "</TD></TR>"

            if ($SendPort.status -eq 2)
            {
                #Auto-Healing feature
                $SendPort.InvokeMethod("Start",$null)

                $mailBodyPT += "<TR style='background-color:rgb(245,245,245);';><TD><font color='green'>Note</font></TD>"
                $mailBodyPT += "<TD><font color='green'>The Stopped port was automatically Started by this script! Please access the environment to check if ports are running correctly.</font></TD></TR>"
            }

            $mailBodyPT += "</table>"
            $mailBodyPT += "<BR><BR>"
        }
    }
}

if($countReceivePorts -gt 0)
{
    $mailTextReportPT += "" + $countReceivePorts + " receive locations disabled; "
    $mailBodyPT += "<h3>Receive locations Disabled</h3>"

    #Add mail content for stopped receive locations
    Foreach ($ReceiveLocation in $ReceiveLocations)
    {
        #only add stopped receive locations to the mail
        if ($ReceiveLocation.IsDisabled -eq "True")
        {
            $StoppedReceiveLocation = $True
            
            #Set status to a user friendly name
            $ReceiveLocationStatus = "Disabled"
        
            #Add mail content
            $mailBodyPT += "<th><b><font color='red'>" + $ReceiveLocation.name + "</font></b></th>"
            $mailBodyPT += "<table style='boder:0px 0px 0px 0px;'>"           
            $mailBodyPT += "<TR style='background-color:white;'><TD>Status</TD>"
            $mailBodyPT += "<TD><b><font color='red'>" + $ReceiveLocationStatus + "</font></b></TD></TR>"
            
            $mailBodyPT += "<TR style='background-color:rgb(245,245,245);';><TD>URI</TD>"
            $mailBodyPT += "<TD>" + $ReceiveLocation.InboundTransportURL + "</TD></TR>"
            
            $mailBodyPT += "<TR style='background-color:white;'><TD>Transport type</TD>"
            $mailBodyPT += "<TD>" + $ReceiveLocation.AdapterName + "</TD></TR>"

            #Auto-Healing feature
            $ReceiveLocation.InvokeMethod("Enable",$null)

            $mailBodyPT += "<TR style='background-color:rgb(245,245,245);';><TD><font color='green'>Note</font></TD>"
            $mailBodyPT += "<TD><font color='green'>The Disabled port was automatically Enable by this script! Please access the environment to check if ports are running correctly.</font></TD></TR>"

            $mailBodyPT += "</table>"
            $mailBodyPT += "<BR><BR>"
        }
    }
}

Note: The script should be adjusted to your needs. Maybe having a variable or a list of receive location and Send ports that should be excluded for the monitoring or have a different expected status.

Here is example expected report output from running the Windows PowerShell script sample, if any of the ports are in an unwanted state.

BizTalk-Ports-Status-Report

Again, this type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

THIS POWERSHELL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

The script can be found and download on Microsoft TechNet Gallery:
Monitoring BizTalk Server Ports with Auto-Healing capabilities with PowerShell (14.0 KB)
Microsoft TechNet Gallery

Welcome back to this serial of articles about Monitor your BizTalk environment using PowerShell that already have several previous editions:

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments and react promptly to possible problems, you can accomplish this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

Today we will talk about a topic that was previously covered by Jeroen: “How to monitor Suspended instance in BizTalk Server using PowerShell”, but this time, I will also add Auto Healing functionalities to the script.

There are bunch of things that can go wrong and the longer they have in an error/failure situation, more impact could have in your business! So, we don’t just want to be notified of failures (that will always lead to a manual human intervention) but instead, when possible, be also able to try to automatically fix it, bringing them to the desired state (running, resumed, enable – depending on the artifact)

So how can PowerShell help us?

With this script you can be able to monitoring your BizTalk environment for suspended service instance, and automatically resume then according to certain conditions, using PowerShell.

Only if the script finds any suspended messages a mail notification will be sent.

This script allows you to set:

  • Set your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "mySMTPServer" #SMTP Server.
[STRING]$SubjectPrefix = "Suspended Messages - "
[STRING]$From = "biztalksupport@mail.pt"
[array]$EmailTo = ("sandro.pereira@devscope.net") 
  • The Service Instances desired states to monitor
#Get suspended messages
[ARRAY]$suspendedMessages = get-wmiobject MSBTS_ServiceInstance -namespace 'root\MicrosoftBizTalkServer' -filter '(ServiceStatus = 4 or ServiceStatus = 16 or ServiceStatus = 32 or ServiceStatus = 64)' 
  • The amount of hours that a suspended message can be resumed automatically. Only if the (Current Date – Automatic Resume hours) is less than Instance start time, the instance will be automatically resumed.
#This variable sets the amount of hours a suspended message can be resumed automatically
[INT]$AutomaticResume = 8 

Main monitoring script:

foreach ($msgSuspended in $suspendedMessages)
{
    $msg = $bo.GetServiceInstance($msgSuspended.InstanceID)

    #Add mail content
    $mailBodySM += "<th>Message Instance ID: <b><font color='blue'>" + $msgSuspended.InstanceID + "</font></b></th>"
    $mailBodySM += "<table style='boder:0px 0px 0px 0px;'>"
    $mailBodySM += "<TR style='background-color:white;'><TD>Application</TD>"
    $mailBodySM += "<TD>" + $msg.Application + "</TD></TR>"

    $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD>Service Name</TD>"
    $mailBodySM += "<TD><b><font color='red'>" + $msgSuspended.ServiceName + "</font></b></TD></TR>"

    $mailBodySM += "<TR style='background-color:white;'><TD>Class</TD>"
    $mailBodySM += "<TD>" + $msg.Class + "</TD></TR>"

    $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD>Assembly Name</TD>"
    $mailBodySM += "<TD>" + $msgSuspended.AssemblyName + ", Version=" + $msgSuspended.AssemblyVersion + ", Culture=" + $msgSuspended.AssemblyCulture +", PublicKeyToken=" + $msgSuspended.AssemblyPublicKeyToken + "</TD></TR>"

    $spActivationTime= [datetime]::ParseExact($msgSuspended.ActivationTime.Substring(0,14),'yyyyMMddHHmmss',[Globalization.CultureInfo]::InvariantCulture )
    $mailBodySM += "<TR style='background-color:white;'><TD>Activation Time</TD>"
    $mailBodySM += "<TD>" + $spActivationTime.ToString("dd-MM-yyyy HH:mm:ss") + "</TD></TR>"

    $myDate= [datetime]::ParseExact($msgSuspended.SuspendTime.Substring(0,14),'yyyyMMddHHmmss',[Globalization.CultureInfo]::InvariantCulture )
    $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD>Suspend Time</TD>"
    $mailBodySM += "<TD>" + $myDate.ToString("dd-MM-yyyy HH:mm:ss") + "</TD></TR>"

    #ServiceStatus = 1 - Ready To Run
    #ServiceStatus = 2 - Active
    #ServiceStatus = 4 - Suspended (Resumable)
    #ServiceStatus = 8 - Dehydrated
    #ServiceStatus = 16 - Completed With Discarded Messages' in BizTalk Server 2004
    #ServiceStatus = 32 - Suspended (Not Resumable) 
    #ServiceStatus = 64 - In Breakpoint 
    
    if ($msgSuspended.ServiceStatus -eq 4)
    {
        $statusMsg = "Suspended (Resumable)" 
    }
    if ($msgSuspended.ServiceStatus -eq 16)
    {
        $statusMsg = "Completed With Discarded Messages" 
    }
    if ($msgSuspended.ServiceStatus -eq 32)
    {
        $statusMsg = "Suspended (Not Resumable)" 
    }
    if ($msgSuspended.ServiceStatus -eq 64)
    {
        $statusMsg = "In Breakpoint" 
    }

    $mailBodySM += "<TR style='background-color:white;'><TD>Service Name</TD>"
    $mailBodySM += "<TD><b><font color='red'>" + $statusMsg + "</font></b></TD></TR>"
        
    $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD>HostName</TD>"
    $mailBodySM += "<TD>" + $msgSuspended.HostName + " (" + $msgSuspended.PSComputerName + ")</TD></TR>"

    $mailBodySM += "<TR style='background-color:white;'><TD>Error Code</TD>"
    $mailBodySM += "<TD>" + $msgSuspended.ErrorId + "</TD></TR>"

    $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD>Error Description</TD>"
    $mailBodySM += "<TD>" + $msgSuspended.ErrorDescription + "</TD></TR>"

    $mailBodySM += "<TR style='background-color:white;'><TD>Error Category</TD>"
    $mailBodySM += "<TD>" + $msgSuspended.ErrorCategory + "</TD></TR>"

    $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD>In Breakpoint</TD>"
    $mailBodySM += "<TD>" + $msg.InBreakpoint + "</TD></TR>"

    $mailBodySM += "<TR style='background-color:white;'><TD>Pending Operation</TD>"
    $mailBodySM += "<TD>" + $msg.PendingOperation + "</TD></TR>"


    If (($spActivationTime -ge $currentdateSI.AddHours(-$AutomaticResume)) -and ($msg.Class -like "Orchestration") -and ($msgSuspended.ServiceStatus -eq 4))
    {
        $msgSuspended.InvokeMethod("Resume",$null)

        $mailBodySM += "<TR style='background-color:rgb(245,245,245);';><TD><font color='green'>Note</font></TD>"
        $mailBodySM += "<TD><font color='green'>The suspended message was resumed automatically by this script! Please access the environment to check if the processes are running or completed.</font></TD></TR>"
    }

    $mailBodySM += "</table>"
    $mailBodySM += "<BR><BR>"
} 

Again, only if the script finds any suspended messages a mail notification will be sent.

Report sample:

BizTalk-Suspended-Instances-Report

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

THIS POWERSHELL IS PROVIDED “AS IS”, WITHOUT WARRANTY OF ANY KIND.

The script can be found and download on Microsoft TechNet Gallery:
Monitoring BizTalk Suspended Service Instances with Auto-Healing with PowerShell (8.0 KB)
Microsoft TechNet Gallery

Is very common in integration scenarios to see messages being archived locally into the hard drive – either by using a pipeline component like BizTalk Archiving – SQL and File or by simple using the default functionalities in BizTalk like filters:

This can happen for several reasons, for example:

  • The messages needs to be archived for legal reasons (normally (recommended) we use an “external” repository – not a local hard drive in the server)
  • or because it is practical and useful to have the file so it can easily be reprocessed in case of failure

The common problem with some of these approach is that normally we tend to implement the archive mechanism and we forget to implement a cleaning mechanism and we end up with several problems after a while:

  • The most critical –> disk full, causing processes to fail or other problems even more critical
  • Many files in a folder, causing performance problems when we are trying to perform operations on it à According to some articles, the lookup time of a directory increases proportional to the square of the number of entries and performance seems to drop between 1000 and 3000 files (the important is not the exact number itself but knowing that a high number may cause problems).

And if you have an integration process that processes hundreds of messages, you can quickly get this kind of problems.

In my demo scenario we have a common Message Archive folder:

  • D:\BizTalkApplications\Archive

This folder is organized by sub-folders describing the processes and the communication, for example:

  • System A
    • Inbound
    • Outbound
  • System B
    • Inbound
    • Errors
  • Canonical Messages
    • Inbounded
    • Archived

And so on… that I need to monitor and constant clean it.

So how can PowerShell help us?

With this script you can be able to automatically monitoring these folders on the servers using PowerShell.

This script allows you to set:

  • The numbers of hours, minutes or days that you want to archive the files, leaving only the recent ones – if the creation time of the file is older than this parameter then it will be archived and deleted from the folder
$Hours = "12"
$LastWrite = $Now.AddHours(-$Days)
  • The directory you want to monitor
$Directories = Get-ChildItem "D:\BizTalkApplications\Archive" -Recurse | ?{ $_.PSIsContainer } | Select-Object FullName
  • And for each subdirectory it will check if exist files to be archived
    • if so, it will create a zip file, using the 7-zip tool, in a different location “BackupFileWarehouse”
    • Otherwise will not create any file and move to the next directory
foreach ($directory in $Directories)
{
    $zipFile = "E:\BackupFileWarehouse\" + $directory.FullName.Substring($directory.FullName.LastIndexOf(":")+2).Replace("\","_") + "_" +  (get-date -f yyyyMMdd_hhss).ToString() + ".zip"

    $filelist = get-childitem $directory.FullName |
                                where-object {$_.LastWriteTime -le (get-date).AddHours(-$Hours)} |
                                where-object {-not $_.PSIsContainer}

    if($filelist.Count -gt 0)
    {
        $filelist | format-table -hideTableHeaders FullName | out-file -encoding utf8 -filepath lastmonthsfiles.txt
                    & 'C:\Program Files (x86)\7-Zip\7z.exe' a $zipFile `@lastmonthsfiles.txt

        $filelist | %{Remove-Item $_.FullName }
        rm lastmonthsfiles.txt
    }
}

Of course if this script will not move/create the archived files in a shared location or something similar, then you will need an additional mechanism to maintain and delete this zip files

Requirements:

The script can be found and download on Microsoft TechNet Gallery:
BizTalk DevOps: Manage messages being archived locally into the hard drive (1.0 KB)
Microsoft TechNet Gallery

Probably this was one of the most talked and funny tips and I completely forgot to publish in my blog despite the resources despite the resources are already available for download and referenced in the session slides that you can found here.

If you are familiarly with the BizTalk Innovation Day or BizTalk Summit’s, you will all remember that at some point my dear friend Tord Glad Nordahl complaining in his session about BizTalk Developers writing unnecessary information in the Application Event Log and for they do not use the Event Viewer. You can also check his post and his point of view about this topic: BizTalk + Event log = angry admins

My goal here is not to criticize those who use the event viewer or if there is better way to accomplish this task (monitor/logging BizTalk applications errors)… but I partially have to agree with Tord and say that… you shouldn’t write custom application errors, warnings or information in the Application Event Log.

Many times BizTalk developers like to write information’s that will help them tracking and debugging there orchestrations like:

  • Message received
  • Message successfully Transformed
  • Message sent to external system

custom-source-logs-logged-application-event-log

And for that they normally use the following code:

System.Diagnostics.EventLog.WriteEntry("AnnoyingTord",
               "Just an update Tord! Message successfully Transformed");

The problem using this code is that by default these information is being logged in the Application Event Log. You need to realize that Application Event Log holds almost all the important information related to different aspects in BizTalk – SQL, IIS, BizTalk infrastructure and runtime problems – it is one of the main places that admins used to monitor “applications” installed in your server/machine. And these is the information that is extremely important for BizTalk Administrator in order to monitor the wellbeing of the platform and to diagnose problems and you don’t want to infest this event log with irrelevant and unnecessary information at the point to be almost impossible to find real problems – instead you, or in this case the admins, should keep it clean.

So I told – “I partially have to agree…” – because I think that this are unnecessary information that are being logged in the Application Event Log that doesn’t provide any additional information to BizTalk Administrators but…

But I told – “I partially have to agree…” – because, instead, you can use a custom event log for logging that unnecessary information that doesn’t provide any additional information to BizTalk Administrators and in this case I really don’t care if you are using the Event Viewer to log BizTalk application errors or tracking or debugging information (despite I don’t agree this last part).

So you can use the Event viewer as long as you do not use the Application Event Log to write custom errors.

Building the Sample

In this sample you will find a simple orchestration that will receive any XML message and will log some traditional tracking information that developers normally do in their orchestrations… I call this trying to annoying Tord Glad Nordahl (my friend and one of the best BizTalk Administrator that I know):

Trying-to-Annoying-Tord

The source code can be found and download on MSDN Code Gallery:
BizTalk Server: Moving Event Source To Different Event Log (Administration) (152.2 KB)
MSDN Code Gallery

 

What the Admin does normally?

When facing this type of development, BizTalk Administrators normally ask the developer’s to change their code, to not write in the application log or to disable this type of logging/tracking. Code that already is deployed in all the environments.

However change is hard – Getting others to change can be impossible or a big challenge – Developers will try to find a thousand excuses for explaining why such information is important!

What the Admin should do?

My advice:

• Let the developer by happy by writing in the Event Viewer

But take back the control of your environment by easily creating or using PowerShell

With this script you can easily move an Event Source to a different or to a Custom Windows Event Log:

foreach ($LogSource in $LogSources) {
    Remove-EventLog -Source $LogSource
} 

$logFileExists = Get-EventLog -list | Where-Object {$_.logdisplayname -eq $LogName}
if (! $logFileExists) {
    $LogSources | %{
        New-EventLog -LogName $LogName -Source $_
    } 

    # Compose Key:
    $LogPath = 'HKLM:\SYSTEM\CurrentControlSet\services\eventlog\'+$LogName;
    if(Test-Path $LogPath)
    {
        $acl = Get-Acl $LogPath
        $GpOrUsers | %{
            $ace = New-Object System.Security.AccessControl.RegistryAccessRule $_,'WriteKey, ReadKey','allow'
            $acl.AddAccessRule($ace)
            #Set-Acl $LogPath $acl
        }
    }else{Write-Error "Cannot acesss log $LogName"}
}
else {
    $LogSources | %{
        New-EventLog -LogName $LogName -Source $_
    }
}

moving-source-logs-to-another-event-log

This way, you as an admin can take back the control of your environment and fix the blunders (or foolishness) of the developers – if you are a BizTalk developer, don’t be offended please, I’m also a BizTalk Developer.

The script can be found and download on Microsoft TechNet Gallery:
BizTalk DevOps: Moving an Event Source To a Different/Custom Windows Event Log (4.2 KB)
Microsoft TechNet Gallery

 

Again If you are a developer and you for some reason want to write “tracking” or error information in the Event Viewer, then you should start to optimize your code to write by default in a custom Event log. You can use for example a similar code:

string logName = “MyCustomEventLog”;
string logName = “MyProjectLogSource”;

if (!System.Diagnostics.EventLog.SourceExists(logName))
{
   System.Diagnostics.EventLog.CreateEventSource(projectName, logName);
}
System.Diagnostics.EventLog.WriteEntry(projectName, genericString.ToString(), logType);

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments on a regular basis and react promptly to solve any possible issues that may appear in order to keep your BizTalk Server applications accessible to your users/organization.

You can accomplished this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

However sometimes we don’t have access to some of these tools and in this case PowerShell is a good way to address and implement some of the basic monetization tasks or even complement and extend the existing tools.

Now I’m playing a little with PowerShell… following my last post and the series “Monitor your BizTalk environment using PowerShell”.

So how can PowerShell help us?

BizTalk Server writes all errors that occur in the environment in the Event Viewer Application Log.

So is important that you maintain this log clean of noise (custom application errors, warnings of information that you can use inside your code)

With this script you can be able to monitoring the Event Viewer for BizTalk Server related errors

This script allows you to set:

  • The timeframe that you want to monitor, the Entry Type and the Sources
$getEventLog = Get-Eventlog -log application -after ((get-date).AddHours(-4)) -EntryType Error | Where-Object {($_.Source -eq 'BizTalk Server')} 
  • And configure your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "smtp"
[STRING]$Subject = "BizTalk Job Monitor"
[STRING]$From = "biztalk@monitor.com"
[array]$EmailTo = ("support@biztalk.com")

if($count -gt 0)
{
    #Send mail
    foreach ($to in $EmailTo)
    {
        $Body = $HTMLmessage
        $SMTPClient = New-Object Net.Mail.SmtpClient($PSEmailServer)
        $message = New-Object Net.Mail.MailMessage($from, $to, $Subject, $Body)
        $message.IsBodyHtml = $true;
        $SMTPClient.Send($message)
    }
}

Report sample:

BizTalk-Server-Event-Viewer-monitor

Note: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

The script can be found and download on Microsoft TechNet Gallery:
Monitoring Event Viewer Errors in your BizTalk environment with PowerShell (2.4 KB)
Microsoft TechNet Gallery

One of the principal needs for BizTalk Administrators is the ability to monitor the health of BizTalk environments on a regular basis and react promptly to solve any possible issues that may appear in order to keep your BizTalk Server applications accessible to your users/organization.

When monitoring BizTalk Server, keep these points in mind:

  • Your infrastructure could be healthy, but your applications might not be (for example, they are receiving invalid messages and are unable to process them).
  • Your infrastructure could be unhealthy, but your applications might be running fine (for example, if a server is down, but there are enough servers assigned to the host to take over the load).
  • An infrastructure problem could surface as an application problem (for example, messages are not being processed fast enough because a server is down).

Source: Monitoring BizTalk Server

You can accomplished this by using certain tools such as: BizTalk Administration Console; BizTalk360; SCOM and many more… However, unfortunately many times, some of these tools are not available for us but we still need to accomplish this task.

Since BizTalk Server 2010 the product brings a job (Monitor BizTalk Server) that monitors the health of your environment identifying any known issues:

  • Messages without any references
  • Messages without reference counts
  • Messages with reference count less than 0
  • Message references without spool rows
  • Message references without instances
  • Instance state without instances
  • Instance subscriptions without corresponding instances
  • Orphaned DTA service instances
  • Orphaned DTA service instance exceptions
  • TDDS is not running on any host instance when global tracking option is enabled.

However this is not enough…

So how can PowerShell help us?

In some of my previous post:

I demonstrated how we could use PowerShell to monitor some aspects or features of your BizTalk environment including a script to monitor BizTalk Jobs, however this script will only monitor if the last execution of the job was successfully or not and if not send a notification.

Although this is a useful, at least in my opinion, in the last two month I found three additional situations that are important to monitor:

  • Some of the jobs were disable
  • MessageBox_Message_Cleanup_BizTalkMsgBoxDb was enable
  • SQL Server Agent was running, BizTalk Jobs were correctly enable… but despite all that no job was running (the last execution has been 7 days back – coinciding with an intervention in the system)

So I decided to create another PowerShell script. With this script you can be able to monitoring SQL Agent Jobs in your BizTalk environment using PowerShell, checking:

  • If the jobs are being executed according to the schedulers that are configured
  • If all jobs (with the exception of MessageBox_Message_Cleanup_BizTalkMsgBoxDb) are enable
  • If MessageBox_Message_Cleanup_BizTalkMsgBoxDb is disable

This script allows you to set:

  • The Jobs execution timeframe and configurations
WITH MostRecentSched AS
 (
 -- For each job get the most recent scheduled run date (this will be the one where Rnk=1)
 SELECT job_id,
		last_executed_step_date,
		RANK() OVER (PARTITION BY job_id ORDER BY last_executed_step_date DESC) AS Rnk
 FROM sysjobactivity
 )
 select name [Job Name],
	last_executed_step_date [Last Scheduled Run Date],
        DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) AS [Minutes Delayed],
		CASE WHEN enabled=1 THEN 'Enabled'
          ELSE 'Disabled'
        END [Job Status]
from MostRecentSched MRS
JOIN   sysjobs SJ
ON     MRS.job_id=SJ.job_id
where Rnk=1
and ((
		((name = 'Backup BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 17))
		OR (name = 'CleanupBTFExpiredEntriesJob_BizTalkMgmtDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 722)
		OR (name = 'Monitor BizTalk Server (BizTalkMgmtDb)' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 11520)
		OR (name = 'Rules_Database_Cleanup_BizTalkRuleEngineDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 62)
		OR (name = 'MessageBox_UpdateStats_BizTalkMsgBoxDb' and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 7)
		OR (name IN ('DTA Purge and Archive (BizTalkDTADb)','MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb', 'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb', 'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'TrackedMessages_Copy_BizTalkMsgBoxDb') and DATEDIFF(minute, last_executed_step_date, SYSDATETIME()) > 1)
	) OR (name IN ('Backup BizTalk Server (BizTalkMgmtDb)','CleanupBTFExpiredEntriesJob_BizTalkMgmtDb',
	               'DTA Purge and Archive (BizTalkDTADb)', 'MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb', 'MessageBox_Parts_Cleanup_BizTalkMsgBoxDb',
				   'MessageBox_UpdateStats_BizTalkMsgBoxDb', 'Monitor BizTalk Server (BizTalkMgmtDb)',
				   'Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb',
				   'PurgeSubscriptionsJob_BizTalkMsgBoxDb', 'Rules_Database_Cleanup_BizTalkRuleEngineDb',
				   'TrackedMessages_Copy_BizTalkMsgBoxDb') AND SJ.enabled = 0)
	  OR (name = 'MessageBox_Message_Cleanup_BizTalkMsgBoxDb' AND SJ.enabled = 1)
	)
order by name, last_executed_step_date desc;
  • And configure your email notification settings
#Set mail variables
[STRING]$PSEmailServer = "smtp"
[STRING]$Subject = "BizTalk Job Monitor"
[STRING]$From = "biztalk@monitor.com"
[array]$EmailTo = ("support@biztalk.com")

if($count -gt 0)
{
    #Send mail
    foreach ($to in $EmailTo)
    {
        $Body = $HTMLmessage
        $SMTPClient = New-Object Net.Mail.SmtpClient($PSEmailServer)
        $message = New-Object Net.Mail.MailMessage($from, $to, $Subject, $Body)
        $message.IsBodyHtml = $true;
        $SMTPClient.Send($message)
    }
}

Report sample:

BizTalk-SQL-Jobs-another-way-monitorNote: This type of script must be viewed as a complement to the tools mentioned above or used in the absence of them.

Credits: Special thanks to José Dias who helped me developing this SQL Script.

The script can be found and download on Microsoft TechNet Gallery:
Another way to monitor BizTalk SQL Agent Jobs with PowerShell (2.8 KB)
Microsoft TechNet Gallery