Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring

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 much more… However, unfortunately, many times, some of these tools are not available for us but we still need to accomplish this task.

In my last post, I demonstrated how we could use PowerShell to monitor disk space. In this post, I will explain how you can be able to monitor SQL Agent Jobs in your BizTalk environment using PowerShell.

So how can PowerShell help us?

Windows PowerShell is a Windows command-line shell designed especially for system administrators. It includes an interactive prompt and a scripting environment that can be used independently or in combination. PowerShell can be used by BizTalk administrators to help them in automating tasks and monitor certain resources or operations.

With this script, you can be able to monitor SQL Agent Jobs in your BizTalk environment using PowerShell. BizTalk is shipped out with a total of 12 SQL Agent jobs. 2 of these jobs must be configured. The two jobs that need configurations are the two most important jobs. The “Backup BizTalk Server” and the “DTA Purge and Archive”.

This script allows you to set:

• A range of machines you need to monitor

#########################################################
# List of SQL Servers to be monitored
#########################################################
$sqlservers = Get-Content ".\SQL.txt";

The SQL.txt is a simple text file with the list of all machine names you want to monitor:
servername1
servername2
servername3

• Configure list of SQL Job that will not be subject to monitoring

#########################################################
# List of SQL Job that will not be subject to monitoring
#########################################################
$jobsToIgnore=@{
	"DTA Purge and Archive (BizTalkDTADb)" = "IGNORE"
}

• And configure your email notification settings

#########################################################
# Preparation of sending the email
#########################################################
$msg = New-Object Net.Mail.MailMessage
if($tmp -ne $null) {
    $smtpServer="mySMTPServer"
    $smtp = New-Object Net.Mail.SmtpClient -arg $smtpServer
    $fromemail = "suport@mail.net"
    $msg.From = $fromemail
    $users = "mail1@mail.net, mail2@mail.net"
    $msg.To.Add($users)
    $msg.Subject = "SQL Job Report "
    $msg.IsBodyHTML = $true
    $msg.Body = $HTMLmessage
    $smtp.Send($msg)
}

The script will monitor the JQL Agent Jobs in all machines and you will receive an email with a list of all jobs listed that have problems and need your attention. SQL Jobs that are functioning properly will not be listed.

#########################################################
# Monitoring Process
#########################################################
$report = foreach($sqlserver in $sqlservers) {
    # Create an SMO Server object
    $srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;  # srv vai ficar com o nome do servidor na lista sqlservers
    # total jobs and start the counters to 0.
    $totalJobCount = $srv.JobServer.Jobs.Count;
    $failedCount = 0;
    $successCount = 0;
    $Day = Get-Date ;

	$tmp = foreach($job in $srv.JobServer.Jobs)
	{
    	$colour = "Green";
        $jobName = $job.Name;
        $jobEnabled = $job.IsEnabled;
        $jobLastRunOutcome = $job.LastRunOutcome;
        $jobLastRunDate= $job.LastRunDate;
        $detail = $job.EnumHistory();
        $timerows = $job.EnumHistory().Rows

        [string]$runDetails=""
        if($job.EnumHistory().Rows[0] -ne $null){
            [string]$runDetails=""+ $job.EnumHistory().Rows[0].Message
            if($job.EnumHistory().Rows[1] -ne $null){
                [string]$runDetails+="<br><br>"+ $job.EnumHistory().Rows[1].Message
            }
        }
        else{
			[string]$runDetails="-"
		}

        $executions = foreach ($row in $timerows) {
            if(-not $row.HasErrors){
                New-Object PSObject -Property @{Duration = $row.RunDuratio}
            }
        }

	    $media =  $executions | Measure-Object -Property Duration -Average | select -expand Average
	    if($timerows -ne $null) {
	        $lastRun =  $timerows[0]['RunDuration']
	    }
		else
	    {
	        $lastRun = -1
	    }

        if($jobLastRunOutcome -eq "Failed")
        {
            #$colour = "Red";
            $failedCount += 1;
        }
        elseif ($jobLastRunOutcome -eq "Succeeded")
        {
            $successCount += 1;
        }
        New-Object PSObject -Property @{
           	Server = $sqlserver
           	JobName = $jobName
           	Enabled = $jobEnabled
           	OK = $successCount
           	Failed = $failedCount
           	LastRun = $jobLastRunOutcome
           	StartTime = $jobLastRunDate
           	Duration = $lastRun
           	ExpectedDuration = $media
           	RunDetails=$runDetails
        }

		##############################################################
		# Convert Seconds to Hours:Minutes:Seconds Table Duration    #
		##############################################################
		if ($lastRun -gt 9999) {
          	$hh = [int][Math]::Truncate($lastRun/10000);
          	$lastRun = $lastRun - ($hh*10000)
		}
		else {
		    $hh = 0
		}
	    if ($lastRun -gt 99) {
	    	$mm = [int][Math]::Truncate($lastRun/100);
	     	$lastRun = $lastRun - ($mm*100)
	    }
	    else {
	        $mm = 0
	    }
		$dur = ("{0:D2}" -f $hh) + ':' + "{0:D2}" -f $mm + ':' + "{0:D2}" -f $lastRun

		######################################################################
		# Convert Seconds to Hours:Minutes:Seconds Table ExpectedDuration    #
		######################################################################
	    [system.Double] $media
	    [Int32] $imedia = [System.Convert]::ToInt32($media);

    	if ($imedia -gt 9999) {
          	$h = [System.Double][Math]::Truncate($imedia/10000);
          	$imedia = $imedia - ($h*10000)
        }
        else {
          	$h = 0
        }
	    if ($imedia -gt 99) {
	     	$m = [System.Double][Math]::Truncate($media/100);
	     	$imedia = $imedia - ($m*100)
	    }
        else {
          	$m = 0
        }
     	$expDura = ("{0:D2}" -f $h) + ':' + "{0:D2}" -f $m + ':' + "{0:D2}" -f $imedia
	}
}

Report sample:

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

Download

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

You can download Monitoring SQL Agent Jobs in your BizTalk environment with PowerShell from GitHub here:

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira