Find failed SQL Job in last 24 Hours using Powershell

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)

OutptutFailedJobs

 

Advertisements

About Prashanth Jayaram

I’m a Database technologist having 11+ years of rich, hands-on experience on Database technologies. I am Microsoft Certified Professional and backed with a Degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication and PowerShell You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in PowerShell, SQL, T-SQL and tagged , . Bookmark the permalink.

12 Responses to Find failed SQL Job in last 24 Hours using Powershell

  1. RK says:

    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

  2. Hi RK,

    Can you check the filename?
    $FailedJobFileName= “f:\PowerSQL\FailedSQLJob.htm”

    Best Regards,
    Prashanth

  3. RK says:

    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

  4. RK says:

    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

  5. RK says:

    Hi Prashanth,

    Any update for this?

  6. RK says:

    Hi Prashant,

    Please let me know what time ur free, will share my desktop.

  7. RK says:

    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)

  8. RK says:

    Many many thanks Prashanth………..

  9. RK says:

    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.

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