As a SQL DBA we need to know if SQL Agent jobs are running successfully on all the Servers which we are supporting, for this we have SCOM monitoring in place and which is linked to SNOW ticketing tool means If any SQL Agent Fails it triggers a SNOW ticket to our DBA Queue and we work on it.
But recently there was a SCOM upgrade happened and after that we our DBA team are not getting any sort of alerts for some servers which we are supporting this includes not getting SNOW tickets for SQL Agent Job Failures , this becomes a problematic for us to Login each and every server to check the jobs status (Servers which are Missed by SCOM to generate a ticket ).
Manual checking is creating a lot of work for us So, thought of Automating it to get the SQL Agent Jobs Status Report generated for every day ( in each Shift at least once ). In my career starting there is no monitoring tool setup in my environment to check the status of jobs ,databases e.tc., At that time our Team Leader had done some good automation to reduce our DBA’s work in that one of the Automation is to get Status Report of SQL Agent Jobs for all the Servers which are provided in TEXT file and get the report stored in an Excel file .
I have that script with me and saved in my Scripts repository , today that script is reQuired for me in my current organization So, I have did some testing in my Personal Laptop and it works well.
This Automation was done using a PowerShell where we need to provide the list of servers in a text file to check for SQL Agent jobs Status .Also we need an Excel already installed on your Remote VDI or Citrix where you are running this script and the Login account which you are using for running the PowerShell script needs to have Admin permission to all the SQL Instances mentioned in the text file.
I have created a text file with name ‘ Servers ‘ and provided list of Instances in the text file that you want to check .
I have opened the PowerShell ISE with run As Administrator and copied below script to it :
#############################################################################################
#
# NAME: SQL Agent Job Status Report to Excel.ps1
# Iterates through the sqlservers.txt file to populate
# Excel File with color coded status
## ————————————————————————
# Get List of sql servers to check
$sqlservers = Get-Content 'C:\temp\Servers.txt'; # Change the Path here where you stored the servers text file
# Create a .com object for Excel
$xl = new-object -comobject excel.application
$xl.Visible = $true # Set this to False when you run in production
$wb = $xl.Workbooks.Add()
$ws = $wb.Worksheets.Item(1)
$date = Get-Date -format f
$Filename = ( get-date ).ToString('ddMMMyyyHHmm')
$cells = $ws.Cells
# Create a description
$cells.item(1, 3).font.bold = $True
$cells.item(1, 3).font.size = 18
$cells.item(1, 3) = "Jobs Status Report $date"
$cells.item(5, 9) = "Last Job Run Older than 1 Day"
$cells.item(5, 8).Interior.ColorIndex = 43
$cells.item(4, 9) = "Last Job Run Older than 7 Days"
$cells.item(4, 8).Interior.ColorIndex = 53
$cells.item(7, 9) = "Successful Job"
$cells.item(7, 8).Interior.ColorIndex = 4
$cells.item(8, 9) = "Failed Job"
$cells.item(8, 8).Interior.ColorIndex = 3
$cells.item(9, 9) = "Job Status Unknown"
$cells.item(9, 8).Interior.ColorIndex = 15
#define some variables to control navigation
$row = 3
$col = 2
#insert column headings
$cells.item($row, $col) = "Server Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 10
$col++
$cells.item($row, $col) = "Job Name"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 40
$col++
$cells.item($row, $col) = "Enabled?"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 15
$col++
$cells.item($row, $col) = "Outcome"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 12
$col++
$cells.item($row, $col) = "Last Run Time"
$cells.item($row, $col).font.size = 16
$Cells.item($row, $col).Columnwidth = 15
$col++
# Load SMO extension
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null;
# Loop through each sql server from sqlservers.txt
foreach ($sqlserver in $sqlservers) {
# Create an SMO Server object
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver;
# For each jobs on the server
foreach ($job in $srv.JobServer.Jobs) {
$jobName = $job.Name;
$jobEnabled = $job.IsEnabled;
$jobLastRunOutcome = $job.LastRunOutcome;
$Time = $job.LastRunDate ;
# Set Fill Colour for Job Enabled
if ($jobEnabled -eq "FALSE")
{ $colourenabled = "2"}
else {$colourenabled = "48" }
# Set Fill Colour for Failed jobs
if ($jobLastRunOutcome -eq "Failed") {
$colour = "3" # RED
}
# Set Fill Colour for Uknown jobs
Elseif ($jobLastRunOutcome -eq "Unknown")
{ $colour = "15"} #GREY
else {$Colour = "4"} # Success is Green
$row++
$col = 2
$cells.item($Row, $col) = $sqlserver
$col++
$cells.item($Row, $col) = $jobName
$col++
$cells.item($Row, $col) = $jobEnabled
#Set colour of cells for Disabled Jobs to Grey
$cells.item($Row, $col).Interior.ColorIndex = $colourEnabled
if ($colourenabled -eq "48") {
$cells.item($Row , 1 ).Interior.ColorIndex = 48
$cells.item($Row , 2 ).Interior.ColorIndex = 48
$cells.item($Row , 3 ).Interior.ColorIndex = 48
$cells.item($Row , 4 ).Interior.ColorIndex = 48
$cells.item($Row , 5 ).Interior.ColorIndex = 48
$cells.item($Row , 6 ).Interior.ColorIndex = 48
$cells.item($Row , 7 ).Interior.ColorIndex = 48
}
$col++
$cells.item($Row, $col) = "$jobLastRunOutcome"
$cells.item($Row, $col).Interior.ColorIndex = $colour
#Reset Disabled Jobs Fill Colour
if ($colourenabled -eq "48")
{$cells.item($Row, $col).Interior.ColorIndex = 48}
$col++
$cells.item($Row, $col) = $Time
#Set teh Fill Colour for Time Cells
If ($Time -lt ($(Get-Date).AddDays(-1)))
{ $cells.item($Row, $col).Interior.ColorIndex = 43}
If ($Time -lt ($(Get-Date).AddDays(-7)))
{ $cells.item($Row, $col).Interior.ColorIndex = 53}
}
$row++
$row++
# Add two Yellow Rows
$ws.rows.item($Row).Interior.ColorIndex = 6
$row++
$ws.rows.item($Row).Interior.ColorIndex = 6
$row++
}
$wb.Saveas("C:\temp\SQLAgentJobstatusReport$filename.xlsx") #Change the Path where ever you want the Excel Report to Store
$xl.quit()
Stop-Process -Name EXCELI have executed the script , immediately an Excel is opened at the backend where the jobs information is getting populated in it , you can find that in below screenshot :
Once the Report collection is finished the excel file will get closed automatically and saved the populated jobs information in it, you can open the excel file from where you have given the path to Save it in above PowerShell Script.
After opening the Excel file you can see all the servers Jobs Information with Color coded for easy identification like below :
Also after each server it will Separate with Yellow Color coding like below :
From the Servers list I provided one of my SQL Instance is in Stopped State for that server While pulling the information of jobs this script does not get any information but it left the Space for us to know that one of the server information is not pulled by script also the script didn’t stop and it continue to next server to get the needed information.
I have Implemented this in my Current organization and it reduces our manually efforts .
Note : Before Implementing please do some testing from your end and get the confident to Implement in your Environment.
