This post is based on the request to get failed SQL jobs in last 24 hours and output should be displayed in a HTML format.
The verification of SQL job steps code is taken from the below blog.
http://www.sqlservercentral.com/blogs/sqlsandwiches/2012/01/29/find-failed-sql-jobs-with-powershell/
The only difference is that the output, it’s formatted HTML output.
#let's get our list of servers. For this, create a .txt files with all the server names you want to check. $sqlservers = Get-Content "f:\powersql\server.txt"; $FailedJobFileName= "f:\PowerSQL\FailedSQLJob.htm" New-Item -ItemType file $FailedJobFileName -Force #we'll get the long date and toss that in a variable $datefull = Get-Date #and shorten it $today = $datefull.ToShortDateString() #let's set up the email stuff $from="pjayaram@appvion.com" $to="pjayaram@appvion.com" $smtp_address = "valid SMTP Address" #Formatting a message body Function writeHtmlHeader { param($fileName) $date = ( get-date ).ToString('yyyy/MM/dd') Add-Content $fileName "<html>" Add-Content $fileName "<head>" Add-Content $fileName "<meta http-equiv='Content-Type' content='text/html; charset=iso-8859-1'>" Add-Content $fileName '<title>SQL Job Failure Report </title>' add-content $fileName '<STYLE TYPE="text/css">' add-content $fileName "<!--" add-content $fileName "td {" add-content $fileName "font-family: Tahoma;" add-content $fileName "font-size: 11px;" add-content $fileName "border-top: 1px solid #999999;" add-content $fileName "border-right: 1px solid #999999;" add-content $fileName "border-bottom: 1px solid #999999;" add-content $fileName "border-left: 1px solid #999999;" add-content $fileName "padding-top: 0px;" add-content $fileName "padding-right: 0px;" add-content $fileName "padding-bottom: 0px;" add-content $fileName "padding-left: 0px;" add-content $fileName "}" add-content $fileName "body {" add-content $fileName "margin-left: 5px;" add-content $fileName "margin-top: 5px;" add-content $fileName "margin-right: 0px;" add-content $fileName "margin-bottom: 10px;" add-content $fileName "" add-content $fileName "table {" add-content $fileName "border: thin solid #000000;" add-content $fileName "}" add-content $fileName "-->" add-content $fileName "</style>" Add-Content $fileName "</head>" Add-Content $fileName "<body>" add-content $fileName "<table width='100%'>" add-content $fileName "<tr bgcolor='#CCCCCC'>" add-content $fileName "<td colspan='4' height='25' align='center'>" add-content $fileName "<font face='tahoma' color='#003399' size='4'><strong>SQL Job Stauts Report - $date</strong></font>" add-content $fileName "</td>" add-content $fileName "</tr>" add-content $fileName "</table>" } # Function to write the HTML Header to the file Function writeTableHeader { param($fileName) Add-Content $fileName "<tr bgcolor=#CCCCCC>" Add-Content $fileName "<td width='10%' align='LEFT'>ServerName</td>" Add-Content $fileName "<td width='50%' align='left'>JobName</td>" Add-Content $fileName "<td width='10%' align='left'>JobLastRun Outcome</td>" Add-Content $fileName "<td width='10%' align='left'>JobLastRunStatus</td>" Add-Content $fileName "</tr>" } Function writeHtmlFooter { param($fileName) Add-Content $fileName "</body>" Add-Content $fileName "</html>" } Function writeInfo { param($filename,$Servername,$name,$outcome,$Status) Add-Content $fileName "<tr>" Add-Content $fileName "<td align=left ><b>$servername</td>" Add-Content $fileName "<td align=left ><b>$name</td>" Add-Content $fileName "<td align=left ><b>$Outcome</td>" Add-Content $fileName "<td align=left ><b>$Status</td>" Add-Content $fileName "</tr>" } writeHtmlHeader $FailedJobFileName Add-Content $FailedJobFileName "<table width='100%'><tbody>" Add-Content $FailedJobFileName "<tr bgcolor='#CCCCCC'>" Add-Content $FailedJobFileName "<td width='100%' align='center' colSpan=4><font face='tahoma' color='#003399' size='2'><strong> SQL Job Details</strong></font></td>" Add-Content $FailedJobFileName "</tr>" writeTableHeader $FailedJobFileName #here, we will begin with a foreach loop. We'll be checking all servers in the .txt referenced above. foreach($sqlserver in $sqlservers) { #here we need to set which server we are going to check in this loop $srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver; #now let’s loop through all the jobs foreach ($job in $srv.Jobserver.Jobs) { #now we are going to set up some variables. #These values come from the information in $srv.Jobserver.Jobs $jobName = $job.Name; $jobEnabled = $job.IsEnabled; $jobLastRunOutcome = $job.LastRunOutcome; $jobLastRun = $job.LastRunDate; #we are only concerned about jos that are enabled and have run before. #POSH is weird with nulls so you check by just calling the var #if we wanted to check isnull() we would use !$jobLastRun if($jobEnabled = “true” -and $jobLastRun) { # we need to find out how many days ago that job ran $datediff = New-TimeSpan $jobLastRun $today #now we need to take the value of days in $datediff $days = $datediff.days #gotta check to make sure the job ran in the last 24 hours if($days -lt 1 ) { #and make sure the job failed IF($jobLastRunOutcome -eq “Failed”) { #now we add the job info to our email body. use `n for a new line Write-Host $sqlserver $jobName $jobLastRunOutcome $JobLastRun writeInfo $FailedJobFileName $sqlserver $jobName $jobLastRunOutcome $JobLastRun } } } } } Add-Content $FailedJobFileName "</table>" writeHtmlFooter $FailedJobFileName $date = ( get-date ).ToString('yyyy/MM/dd') $body = Get-Content $FailedJobFileName $body = New-Object System.Net.Mail.MailMessage $from,$to, "Job Status - $Date", $body $body.isBodyhtml = $true $smtp = new-object Net.Mail.SmtpClient($smtp_address) $smtp.Send($body)
Outptut
Advertisements
Hi Prashanth,
I’m getting below error.
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:84 char:12
+ Add-Content <<<< $fileName "”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Hi RK,
Can you check the filename?
$FailedJobFileName= “f:\PowerSQL\FailedSQLJob.htm”
Best Regards,
Prashanth
file name is there but inside file no data only empty file created.
$sqlservers = Get-Content “D:\powershell\server.txt”;
$FailedJobFileName= “D:\Powershell\FailedSQLJob.htm”
New-Item -ItemType file $FailedJobFileName -Force
SQL Job Stauts Report – 2016/03/06
SQL Job Details
ServerName JobName JobLastRun Outcome JobLastRunStatus
Again same error.
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:91 char:12
+ Add-Content <<<< $fileName "”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:92 char:12
+ Add-Content <<<< $fileName "$servername”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:93 char:12
+ Add-Content <<<< $fileName "$name”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:94 char:12
+ Add-Content <<<< $fileName "$Outcome”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:95 char:12
+ Add-Content <<<< $fileName "$Status”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Add-Content : Cannot bind argument to parameter ‘Path’ because it is null.
At D:\Powershell\SQL_jobs.PS1:96 char:12
+ Add-Content <<<< $fileName "”
+ CategoryInfo : InvalidData: (:) [Add-Content], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Microsoft.PowerShell.Commands.AddContentCommand
Hi Prashanth,
Any update for this?
Hi RK,
There are two things.
I think there is some problem with the filename. check the path one more time
or
you don’t have access to create the file.
–Prashanth
file is creating without any issue, already shared the report also. There is something else in code.
Hi Prashant,
Please let me know what time ur free, will share my desktop.
Hi Prashant,
I have made few modification for below script, script is working fine but out put not getting in HTML format.
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.SqlServer.SMO”) | out-null;
#let’s get our list of servers. For this, create a .txt files with all the server names you want to check.
$sqlservers = Get-Content “D:\powershell\server.txt”;
$FailedJobFileName = “D:\Powershell\FailedSQLJob.htm”
$ServiceFileName = “D:\Powershell\Report_SQL.htm”
New-Item -ItemType file $FailedJobFileName -Force
#we’ll get the long date and toss that in a variable
$datefull = Get-Date
#and shorten it
$today = $datefull.ToShortDateString()
#let’s set up the email stuff
$from=”smpt.mail.com”
$to=”email.com”
$smtp_address = “email.com”
#Formatting a message body
Function writeHtmlHeader
{
param($fileName)
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
Add-Content $fileName “”
Add-Content $fileName “”
Add-Content $fileName “”
Add-Content $fileName ‘SQL Job Failure Report ’
add-content $fileName ‘’
add-content $fileName “”
add-content $fileName “”
Add-Content $fileName “”
Add-Content $fileName “”
add-content $fileName “”
add-content $fileName “”
add-content $fileName “”
add-content $fileName “SQL Job Stauts Report – $date”
add-content $fileName “”
add-content $fileName “”
add-content $fileName “”
}
# Function to write the HTML Header to the file
Function writeTableHeader
{
param($fileName)
Add-Content $fileName “”
Add-Content $fileName “ServerName”
Add-Content $fileName “JobName”
Add-Content $fileName “JobLastRun Outcome”
Add-Content $fileName “JobLastRunStatus”
Add-Content $fileName “”
}
Function writeHtmlFooter
{
param($fileName)
Add-Content $fileName “”
Add-Content $fileName “”
}
Function writeInfo
{
param($filename,$Servername,$name,$outcome,$Status)
Add-Content $fileName “”
Add-Content $fileName “$servername”
Add-Content $fileName “$name”
Add-Content $fileName “$Outcome”
Add-Content $fileName “$Status”
Add-Content $fileName “”
}
writeHtmlHeader $ServiceFileName
Add-Content $ServiceFileName “”
Add-Content $ServiceFileName “”
Add-Content $ServiceFileName “ SQL Job Details”
Add-Content $ServiceFileName “”
writeTableHeader $ServiceFileName
#here, we will begin with a foreach loop. We’ll be checking all servers in the .txt referenced above.
foreach($sqlserver in $sqlservers)
{
#here we need to set which server we are going to check in this loop
$srv = New-Object “Microsoft.SqlServer.Management.Smo.Server” $sqlserver;
#now let’s loop through all the jobs
foreach ($job in $srv.Jobserver.Jobs)
{
#now we are going to set up some variables.
#These values come from the information in $srv.Jobserver.Jobs
$jobName = $job.Name;
$jobEnabled = $job.IsEnabled;
$jobLastRunOutcome = $job.LastRunOutcome;
$jobLastRun = $job.LastRunDate;
#we are only concerned about jos that are enabled and have run before.
#POSH is weird with nulls so you check by just calling the var
#if we wanted to check isnull() we would use !$jobLastRun
if($jobEnabled = “true” -and $jobLastRun)
{
# we need to find out how many days ago that job ran
$datediff = New-TimeSpan $jobLastRun $today
#now we need to take the value of days in $datediff
$days = $datediff.days
#gotta check to make sure the job ran in the last 24 hours
if($days -lt 1 )
{
#and make sure the job failed
IF($jobLastRunOutcome -eq “Failed”)
{
#now we add the job info to our email body. use `n for a new line
Write-Host $sqlserver $jobName $jobLastRunOutcome $JobLastRun
writeInfo $ServiceFileName $sqlserver $jobName $jobLastRunOutcome $JobLastRun
}
}
}
}
}
Add-Content $ServiceFileName “”
writeHtmlFooter $ServiceFileName
$date = ( get-date ).ToString(‘yyyy/MM/dd’)
$body = Get-Content $ServiceFileName
$body = New-Object System.Net.Mail.MailMessage $from,$to, “Job Status – $Date”, $body
$body.isBodyhtml = $true
$smtp = new-object Net.Mail.SmtpClient($smtp_address)
$smtp.Send($body)
Many many thanks Prashanth………..
Hi Prashanth,
I need few modification on this script. I’m looking only stopped service details. As per the script getting Running and stopped. I’m looking for only stopped service no need for running.
Hi Prashanth,
Do have chance to look on this?