# encoding: utf-8
# api: powershell
# title: SQL Agent Jobs to Excel
# description: NAME: Agent Job Status to Excel.ps1
# version: 0.1
# type: script
# author: SQLDBAWithABeard
# license: CC0
# x-poshcode-id: 5033
# x-archived: 2014-04-03T03:10:33
# x-published: 2014-03-31T20:27:00
#
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
# #
# COMMENTS: Iterates through the sqlservers.txt file to populate
# Excel File with colour coded status
# #
# WARNING – This will stop ALL Excel Processes. Read the Blog Post for more info
#
#############################################################################################
#
# NAME: Agent Job Status to Excel.ps1
# AUTHOR: Rob Sewell http://newsqldbawiththebeard.wordpress.com
# DATE:22/07/2013
#
# COMMENTS: Iterates through the sqlservers.txt file to populate
# Excel File with colour coded status
#
# WARNING - This will stop ALL Excel Processes. Read the Blog Post for more info
#
# ������������������������
# 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)="Back Up 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"
$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;
# Get List of sql servers to check
$sqlservers = Get-Content 'D:\SkyDrive\Documents\Scripts\Powershell Scripts\sqlservers.txt';
# 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\Test$filename.xlsx")
$xl.quit()
Stop-Process -Name EXCEL