Monitor your BizTalk environment using PowerShell – SQL Agent Jobs Monitoring

Posted: October 13, 2012 in BizTalk, PowerShell
Tags: , , , ,

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 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.

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 monitoring 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 monitoring 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 needs configuration 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 atention. 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.

The script can be found and download on Microsoft TechNet Gallery:
Monitoring SQL Agent Jobs in your BizTalk environment with PowerShell (7.4 KB)
Microsoft TechNet Gallery

About these ads
Comments
  1. Pedro Castro says:

    Conheço isto de algum lado…

    • Of course you know.

      Pedro Castro is my coworker and system administrator at DevScope. He along with Rui Machado, two young promises talents, are key elements in my PowerShell script projects, they are the executors behind my ideas :).

  2. HelderSoares says:

    Great Job!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s