Check SQL Server Jobs with Powershell

Standard

I like to automate as much as possible using scripts with SQL Server. So one of the things I want to know is if any job has failed in a list of servers. I’m a fan of Powershell because it’s so powerfull and is easy to develop.

This script uses Excel to write the status of the different jobs on a server.

The script can be run using two arguments:

  1. ServerList
  2. Output

ServerList

The server list parameter is a mandatory list that holds all the different instances that need to be checked. The file is a text file with on every new line a instance. Make sure the full instance is put in when a named instance is used like: SQL01\SQLEXPRESS or SQL01\name.

In case of a default instance only the name of the server needs to be put in.

Output

The ouput parameter is used when you don’t want to see the Excel sheets. Instead the scripts writes the file directly to the given folder.

Usage

The script can be used like this:

powershell CheckSQLJobs.ps1 -Serverlist 'c:\temp\servers.txt' -Output 'c:\temp\sqljobscheck'

The code can be seen below:

# PowerShell script to return all the database disk recovery points
# from the DPM database
#
# Author: Sander Stad
# Version: 1.1 November 2010 tested on Powershell v2.0
#
# Changes:
# Version 1.0: 
# 	Initial version worked as it should
# Version 1.1:
# 	Implementation of parameters
# 	Implementation of constants
# 	Error check on parameters
# 	Partially loaded classes
# 	Adjusted query to give percentage with precission of 2
# 	Adjusted query to order the datasource names from a-z
#	Implemented output to Excell file on disk
#	Color implemented for several values
#
# Format of ServerList file
#	Named instance: 	SERVERNAME\INSTANCENAME 
#	Default instance: 	SERVERNAME
#	Every server must be on a new row.

Param(
	[string] $ServerList,				# List of servers that needs to be checked. Has to a text file!
	[string] $Output					# Output directory for the Excell file. If no value is given an Excell sheet will open.
)

# Loading partial classes
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

# Set constant variables
Set-Variable -Name _WAITTIME	-Option Constant -Value 2

# Set global error check
# Value 1: High error program will stop
# Value 2: Warning program will continue
# Value 3: Information about execution
$ErrorStatus = 0;

#Set Excel activity
# Value 1: Excell sheet will open on execute (Default value)
# Value 2: Excell sheet will be written to given path
$ExcelAction = 0;


If($Output -eq '')
{
	Write-Host "INFO: Path for output not given. Excell sheet will be opened." -ForegroundColor Red -BackgroundColor Green
	$ExcelAction = 1
	$ErrorStatus = 3
}
ElseIf(Test-Path $Output)
{
	$Output = $Output + '\SQLJobReport_' + (Get-Date).ToString("yyyyMMddHHmmss") + '.xls'
	Write-Host "INFO: Excell sheet will be opened written to $Output." -ForegroundColor Red -BackgroundColor Green
	$ExcelAction = 2
	$ErrorStatus = 3
}
Else
{
	Write-Host "INFO: Path for output is set. Data will be written to $Output." -ForegroundColor Red -BackgroundColor Green
	$ExcelAction = 2
	$ErrorStatus = 3
}
# Check all the parameters
If($ServerList -eq '')
{
	Write-Host "ERROR: Server list not given" -ForegroundColor Red -BackgroundColor Yellow
	$ErrorStatus = 1
}
ElseIf(!(Test-Path $ServerList))
{
	Write-Host "ERROR: Path to serverlist doesn't exist" -ForegroundColor Red -BackgroundColor Yellow
	$ErrorStatus = 1
}




# Check if any error occured in the parameters
If($ErrorStatus -eq 0)
{
	Write-Host ""
	Write-Host "No errors found in the parameters. Continuing....." -ForegroundColor Green
	Start-Sleep -Seconds $_WAITTIME
}
ElseIf($ErrorStatus -eq 1)
{
	Write-Host ""
	Write-Host "Critical errors found. Exiting..." -ForegroundColor Red 
	Start-Sleep -Seconds $_WAITTIME
	Exit
}
ElseIf($ErrorStatus -eq 2)
{
	Write-Host ""
	Write-Host "Warnings found in the parameters. Continuing....." -ForegroundColor Blue
	Start-Sleep -Seconds $_WAITTIME
}
ElseIf($ErrorStatus -eq 3)
{
	Write-Host ""
	Write-Host "Informational messages found. Continuing....." -ForegroundColor Green
	Start-Sleep -Seconds $_WAITTIME
}

# Set the settings for the Excel sheet
If($ExcelAction -eq 1)
{
	#Create a new Excel object using COM 
	$Excel = New-Object -ComObject Excel.Application
	$Excel.visible = $True
	$Excel = $Excel.Workbooks.Add()
	$Sheet = $Excel.Worksheets.Item(1)
}
ElseIf($ExcelAction -eq 2)
{
	#Create a new Excel object using COM 
	$Excel = New-Object -ComObject Excel.Application
	$Excel.visible = $False
	#$Excel = $Excel.Workbooks.Open()
	$Excel = $Excel.Workbooks.Add()
	$Sheet = $Excel.Worksheets.Item(1)
	
}

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
Foreach ($instance in Get-Content $ServerList)
{
	$instance
    #Create column headers
    $Sheet.Cells.Item($intRow,1) = "INSTANCE NAME: " + $S
    #$Sheet.Cells.Item($intRow,2) = $instance
    $Sheet.Cells.Item($intRow,1).Font.Bold = $True
    $Sheet.Cells.Item($intRow,2).Font.Bold = $True

    $intRow++

    $Sheet.Cells.Item($intRow,1) = "JOB NAME"
    $Sheet.Cells.Item($intRow,2) = "LAST RUN OUTCOME"
    $Sheet.Cells.Item($intRow,3) = "LAST RUN DATE"

    #Format the column headers
    For ($col = 1; $col –le 3; $col++)
    {
        $Sheet.Cells.Item($intRow,$col).Font.Bold = $True
        $Sheet.Cells.Item($intRow,$col).Interior.ColorIndex = 48
        $Sheet.Cells.Item($intRow,$col).Font.ColorIndex = 34
    }

    $intRow++     

    # Create an SMO connection to the instance
    $srv = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $instance
	
	# Get the jobs from the instance
    $jobs = $srv.JobServer.Jobs

	# Loop through the jobs
	ForEach ($job in $jobs)  
	{ 
		# Formatting for the failed jobs 
		if ($job.LastRunOutcome -eq 0) { $fgColor = 3 } 
		else {$fgColor = 0 } 

		# Insert the values
		$Sheet.Cells.Item($intRow, 1) =  $job.Name 
		$Sheet.Cells.Item($intRow, 2) = $job.LastRunOutcome.ToString() 
		$Sheet.Cells.item($intRow, 2).Interior.ColorIndex = $fgColor 
		$Sheet.Cells.Item($intRow, 3) =  $job.LastRunDate
		
		# Increment the row number
		$intRow ++ 	   
	} 
	
	# Increment the row number
   	$intRow ++ 

}

# Automatically size the field in the excel sheet
$Sheet.UsedRange.EntireColumn.AutoFit()

# Close the document if it's automatically written to disk
If($ExcelAction -eq 2)
{
	$Excel.SaveAs($Output, 56)
	$Excel.Close($False)
}