Show job execution results in a timeline

Show job execution results in a timeline

20, Jun Klaas

In this article I show how to arrange the sql agent job executions on a Google timeline and send this as a HTML report attached to an e-mail.

Case

Besides receiving alerts when jobs fail, we like to have an overview of all jobs that were executed recently. Instead of having a list to scroll through, we prefer a visualisation to show the outcome and the duration of all jobs in a way that pleases the eye.
This article partly overlaps with Mail me the backup status: gathering sql instances and sending the e-mail is the same, but we look at a different item and use a more fancy output method.

Solution

Theo Ekelmans put us on the right track with this article: 'Visualize the timeline of your SQL jobs using Google graph and email' on SQL Server central. When I read this article I decided to try Google graph for my purpose too.
Some more info on how to use it I found at Google developers site.

The pleasant part of this method is that Google does all the hard work for us: it draws a chart and provides information on start and end times, and creates pop ups on mouse over.

This is an example where there are no failed jobs, and you can see some of Ola Hallengren's Maintenancesolution jobs while my mouse was on the 'Database Backup - USER_DATABASES - FULL' job:

Timeline

The red bar and the upper green one are fixed headers.
You can see two more bars that appear solid, but those are actually log backups every two minutes on two servers, while on the third server the log backups are scheduled every 15 minutes.
The chart creation is not always perfect, but it is decent enough.

Steps

Roughly, this is what we want to do:

  1. list our SQL Servers
  2. ask those SQL Servers what the status, start and end time of every job execution was
  3. convert the answers into a nice timeline
  4. send an e-mail to whom it may concern with the html attached

First some preparations

First of all we will import the sqlserver module. In PowerShell V3 and higher, there is auto-loading. That means a module will implicitly be loaded the moment you call a function in it. In a script we have to import explicitly, however. 

Import-Module sqlserver

Actually, we will only be using invoke-sqlcmd. There are some known issues with this command, and maybe you have an invoke-sqlcmd2, written wy Chad Miller, or another version by Warren F RamblingCookieMOnster. In that case, there's even no need to import the sqlserver module.

Next, we need some information to compose the e-mail we will send:

  • the date, to add in the e-mail subject
  • e-mail addresses
  • a mail server
  • an HTML file 
$today = get-date -Format "dd MMM yyyy"

$To = '[email protected]', '[email protected]', '[email protected]'
$MailServer = 'mailout.mycompany.com'

$Jobhtml = "$env:temp\Jobs24H.html"

To make sure the timeline looks as a 24 hour period expanded over our HTML page, we use a little trick: we will start with two fixed rows and use them as headers, one for the failed jobs, and one for the succeeded.
We let these fake jobs start 24 hours ago, and end this very minute. That will create the first 'job' with a bar that represents the last 24 hours, and the next jobs will automatically be positioned relative to this one.

A timeline row needs a start and end datetime, delivered in dateparts. For that, we declare some variables with year, month, day, hour and minute. 

# create the variables to use in the Javascript 'new Date' functions as start and end times for the header lines
$Ys = (get-date).AddDays(-1).ToString('yyyy')
$Ms = (get-date).AddDays(-1).ToString('MM') -1    ## Months are numbered 0 to 11 for the timeline
$Ds = (get-date).AddDays(-1).ToString('dd')
$Ye = Get-Date -Format yyyy
$Me = (get-date).ToString('MM') -1
$De = Get-Date -Format dd
$h = Get-Date -Format HH
$m = Get-Date -Format mm

# Construct a header line with a fake server and fake job to make sure the graph is 24h wide
$success = @("[ 'SQL', 'Agent Jobs Outcome SUCCES', new Date( $Ys, $Ms, $Ds, $h, $m, 0), new Date( $Ye, $Me, $De, $h, $m, 0)],")
$failed = @("[ 'SQL', 'Agent Jobs Outcome FAILED, RETRY, CANCELED', new Date( $Ys, $Ms, $Ds, $h, $m, 0), new Date( $Ye, $Me, $De, $h, $m, 0)],")

Those last two lines construct the row declaration as Google's timeline script will accept them. In a further step we add the real job rows to appropriate collection and end up with two 'tables' we can embed in the HTML.

Gather SQL Instances

I described some methods to collect our SQL Instances in an article Gather Computers.
Let's assume we have a txt file that contains the SQL Instances we want to monitor. We get them out with a Get-Content: 

$MyInstances = Get-Content C:\Scripts\SQL\MyInstances.txt

How are your sql agent jobs doing?

To ask the SQL Instance information about the job execution results, we use a T-SQL query, assigned to a variable as a here-string: 

$JobQuery = @"
WITH Results
AS
(
SELECT    Host = @@SERVERNAME
        , JobName = job.name
        , run_status
        , SDT = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)
        , EDT = DATEADD(s,((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 ,
                    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) 
                )

FROM    msdb.dbo.sysjobs job
        LEFT JOIN msdb.dbo.sysjobhistory his    ON his.job_id = job.job_id

WHERE   CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)
            BETWEEN CAST(DATEADD(DAY,-1,GETDATE()) AS DATETIME2) AND CAST(GETDATE() AS DATETIME2)
    AND     step_id = 0 -- step_id = 0 is the entire job, step_id > 0 is actual step number
    -- AND      (((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > 60 OR run_status <> 1 ) -- Ignore trivial runtimes < 60 sec if success
    AND job.name NOT LIKE 'database mirroring monitor %'
)
SELECT    Host
        , JobName
        , run_status
        , SYear = cast(DATEPART(year ,  SDT) as varchar(4))
        , SMonth = cast(DATEPART(month,  SDT) -1 as varchar(2)) --Java months count from 0
        , SDay = cast(DATEPART(day,    SDT) as varchar(2))
        , SHour = cast(DATEPART(hour,   SDT) as varchar(2))
        , SMinute = cast(DATEPART(minute, SDT) as varchar(2))
        , SSecond = cast(DATEPART(second, SDT) as varchar(2))
        , EYear = cast(DATEPART(year ,  EDT) as varchar(4))
        , EMonth = cast(DATEPART(month,  EDT) -1 as varchar(2)) --Java months count from 0
        , EDay = cast(DATEPART(day,    EDT) as varchar(2))
        , EHour = cast(DATEPART(hour,   EDT) as varchar(2))
        , EMinute = cast(DATEPART(minute, EDT) as varchar(2))
        , ESecond = cast(DATEPART(second, EDT) as varchar(2))
FROM Results
ORDER BY SDT
;
"@

We need the dateparts as parameters for the JavaScript. 'SYear' stands for the starting year, 'EYear' for the ending year, and so on. Since we will always select one day, you might think we only need one of those to supply to both start and end parameters. But:

  • the last 24 hours start yesterday and end today
  • on the first of each month, it starts on the previous month
  • on january 1st, it starts last year

We excluded database monitoring jobs. It's up to you to alter the WHERE clause if you want other selections.

An easy loop construct lets us query all instances and create two collections: the failed jobs and the succeeded jobs. As we do it here, 'failed' stands for anything that is not 'succeeded', so not strictly the 'failed' status. 

$instances |
ForEach-Object {
                $jobs = $null
                $jobs = invoke-sqlcmd -ServerInstance $_ -Database master -Query $JobQuery
                $AllJobs += $jobs
                }

# Construct JS rows and add to the proper collection 'failed' or 'success'
foreach ($JobResult in $AllJobs)
    {
    $ResultRow = "[ `'" + $JobResult.host + "`', `'" + $JobResult.jobname + "`', new Date(" + $JobResult.SYear + ", " + $JobResult.SMonth + ", " + $JobResult.SDay + ", " + $JobResult.SHour + ", " + $JobResult.SMinute + ", " + $JobResult.SSecond +"), new Date(" + $JobResult.EYear + ", " + $JobResult.EMonth + ", " + $JobResult.EDay + ", " + $JobResult.EHour + ", " + $JobResult.EMinute + ", " + $JobResult.ESecond +")],`n`t"
    if ($JobResult.run_status -eq 1 ) {$success += $ResultRow }
    else {$failed += $ResultRow}
    }

Construct the body

Once we have all our data, we feed it into our HTML page. We supply all parts as here-strings with the variables containing the job results in it. We have

  • a HTML header
  • some JavaScript
  • a bit of inline CSS
  • our failed and succeeded jobs 
$html = @"
<html>
    <head>
    <!--<META HTTP-EQUIV=`"refresh`" CONTENT=`"3`">-->
    <script type=`"text/javascript`" src=`"https://www.google.com/jsapi?autoload={`'modules`':[{`'name`':`'visualization`', `'version`':`'1`',`'packages`':[`'timeline`']}]}`"></script>
    <script type=`"text/javascript`">
    google.setOnLoadCallback(drawChart);
    function drawChart() {
    var container = document.getElementById(`'FailedTimeline`');
    var chart = new google.visualization.Timeline(container);
    var dataTable = new google.visualization.DataTable();
    dataTable.addColumn({ type: `'string`', id: `'Position`' });
    dataTable.addColumn({ type: `'string`', id: `'Name`' });
    dataTable.addColumn({ type: `'date`', id: `'Start`' });
    dataTable.addColumn({ type: `'date`', id: `'End`' });
    dataTable.addRows([
"@
$html += $failed

$html += @"
]);
var options =
{
timeline:  {
groupByRowLabel: true,
colorByRowLabel: false,
singleColor: '#C1073F',
rowLabelStyle: {fontName: `'Helvetica`', fontSize: 14 },
barLabelStyle: {fontName: `'Helvetica`', fontSize: 14 }
}
};
chart.draw(dataTable, options);

    var container = document.getElementById(`'SuccessTimeline`');
    var chart = new google.visualization.Timeline(container);
    var dataTable2 = new google.visualization.DataTable();
    dataTable2.addColumn({ type: `'string`', id: `'Position`' });
    dataTable2.addColumn({ type: `'string`', id: `'Name`' });
    dataTable2.addColumn({ type: `'date`', id: `'Start`' });
    dataTable2.addColumn({ type: `'date`', id: `'End`' });
    dataTable2.addRows([
"@

$html += $success

$html += @"
]);
var options =
{
timeline:  {
groupByRowLabel: true,
colorByRowLabel: false,
singleColor: '#1CA589',
rowLabelStyle: {fontName: `'Helvetica`', fontSize: 14 },
barLabelStyle: {fontName: `'Helvetica`', fontSize: 14 }
}
};
chart.draw(dataTable2, options);
}
</script>
</head>
<body><font face=`"Helvetica`" size=`"4`" > SQL Agent Jobs op SQL Servers afgelopen 24 u</font></br>
<div id=`"FailedTimeline`" style=`"width: 1885px; height: 500px;`"></div>
<div id=`"SuccessTimeline`" style=`"width: 1885px; height: 1200px;`"></div>
</body>
</html>
"@

This results in one variable $html that holds the whole content of our HTML page, which we will write with one simple statement: 

Set-Content -Path $Jobhtml -Value $html

Send the E-mail

The Send-MailMessage command is delivered with PowerShell itself, so, no need to install it. It's ready for us. 

Send-MailMessage -To $To -Subject "SQL Agent jobs of the last 24 hours on $today" –From "[email protected]" -SmtpServer $MailServer -Body "SQL Agent jobs of the last 24 hours" -BodyAsHtml -Attachments $Jobhtml

We use some of the variables we declared in the beginning of the script. There are more options available, see msdn.

The complete script

Import-Module sqlserver

$today = get-date -Format "dd MMM yyyy"

$To = '[email protected]', '[email protected]', '[email protected]'
$MailServer = 'mailout.mycompany.com'

$Jobhtml = "$env:temp\Jobs24H.html"

$MyInstances = Get-Content C:\Scripts\SQL\MyInstances.txt

# create the variables to use in the Javascript 'new Date' functions as start and end times for the header lines
$Ys = (get-date).AddDays(-1).ToString('yyyy')
$Ms = (get-date).AddDays(-1).ToString('MM') -1    # Months are numbered 0 to 11 for the timeline
$Ds = (get-date).AddDays(-1).ToString('dd')
$Ye = Get-Date -Format yyyy
$Me = (get-date).ToString('MM') -1
$De = Get-Date -Format dd
$h = Get-Date -Format HH
$m = Get-Date -Format mm

# Construct a header line with a fake server and fake job to make sure the graph is 24h wide
$success = @("[ 'SQL', 'Agent Jobs Outcome SUCCES', new Date( $Ys, $Ms, $Ds, $h, $m, 0), new Date( $Ye, $Me, $De, $h, $m, 0)],")
$failed = @("[ 'SQL', 'Agent Jobs Outcome FAILED, RETRY, CANCELED', new Date( $Ys, $Ms, $Ds, $h, $m, 0), new Date( $Ye, $Me, $De, $h, $m, 0)],")

$JobQuery = @"
WITH Results
AS
(
SELECT    Host = @@SERVERNAME
        , JobName = job.name
        , run_status
        , SDT = CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)
        , EDT = DATEADD(s,((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 ,
                    CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) 
                )

FROM    msdb.dbo.sysjobs job
        LEFT JOIN msdb.dbo.sysjobhistory his    ON his.job_id = job.job_id

WHERE   CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)
            BETWEEN CAST(DATEADD(DAY,-1,GETDATE()) AS DATETIME2) AND CAST(GETDATE() AS DATETIME2)
    AND     step_id = 0 -- step_id = 0 is the entire job, step_id > 0 is actual step number
    -- AND      (((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > 60 OR run_status <> 1 ) -- Ignore trivial runtimes < 60 sec if success
    AND job.name NOT LIKE 'database mirroring monitor %'
)
SELECT    Host
        , JobName
        , run_status
        , SYear = cast(DATEPART(year ,  SDT) as varchar(4))
        , SMonth = cast(DATEPART(month,  SDT) -1 as varchar(2)) --Java months count from 0
        , SDay = cast(DATEPART(day,    SDT) as varchar(2))
        , SHour = cast(DATEPART(hour,   SDT) as varchar(2))
        , SMinute = cast(DATEPART(minute, SDT) as varchar(2))
        , SSecond = cast(DATEPART(second, SDT) as varchar(2))
        , EYear = cast(DATEPART(year ,  EDT) as varchar(4))
        , EMonth = cast(DATEPART(month,  EDT) -1 as varchar(2)) --Java months count from 0
        , EDay = cast(DATEPART(day,    EDT) as varchar(2))
        , EHour = cast(DATEPART(hour,   EDT) as varchar(2))
        , EMinute = cast(DATEPART(minute, EDT) as varchar(2))
        , ESecond = cast(DATEPART(second, EDT) as varchar(2))
FROM Results
ORDER BY SDT
;
"@

$instances |
ForEach-Object {
                $jobs = $null
                $jobs = invoke-sqlcmd -ServerInstance $_ -Database master -Query $JobQuery
                $AllJobs += $jobs
                }

# Construct JS rows and add to the proper collection 'failed' or 'success'
foreach ($JobResult in $AllJobs)
    {
    $ResultRow = "[ `'" + $JobResult.host + "`', `'" + $JobResult.jobname + "`', new Date(" + $JobResult.SYear + ", " + $JobResult.SMonth + ", " + $JobResult.SDay + ", " + $JobResult.SHour + ", " + $JobResult.SMinute + ", " + $JobResult.SSecond +"), new Date(" + $JobResult.EYear + ", " + $JobResult.EMonth + ", " + $JobResult.EDay + ", " + $JobResult.EHour + ", " + $JobResult.EMinute + ", " + $JobResult.ESecond +")],`n`t"
    if ($JobResult.run_status -eq 1 ) {$success += $ResultRow }
    else {$failed += $ResultRow}
    }

$html = @"
<html>
    <head>
    <!--<META HTTP-EQUIV=`"refresh`" CONTENT=`"3`">-->
    <script type=`"text/javascript`" src=`"https://www.google.com/jsapi?autoload={`'modules`':[{`'name`':`'visualization`', `'version`':`'1`',`'packages`':[`'timeline`']}]}`"></script>
    <script type=`"text/javascript`">
    google.setOnLoadCallback(drawChart);
    function drawChart() {
    var container = document.getElementById(`'FailedTimeline`');
    var chart = new google.visualization.Timeline(container);
    var dataTable = new google.visualization.DataTable();
    dataTable.addColumn({ type: `'string`', id: `'Position`' });
    dataTable.addColumn({ type: `'string`', id: `'Name`' });
    dataTable.addColumn({ type: `'date`', id: `'Start`' });
    dataTable.addColumn({ type: `'date`', id: `'End`' });
    dataTable.addRows([
"@
$html += $failed

$html += @"
]);
var options =
{
timeline:  {
groupByRowLabel: true,
colorByRowLabel: false,
singleColor: '#C1073F',
rowLabelStyle: {fontName: `'Helvetica`', fontSize: 14 },
barLabelStyle: {fontName: `'Helvetica`', fontSize: 14 }
}
};
chart.draw(dataTable, options);

    var container = document.getElementById(`'SuccessTimeline`');
    var chart = new google.visualization.Timeline(container);
    var dataTable2 = new google.visualization.DataTable();
    dataTable2.addColumn({ type: `'string`', id: `'Position`' });
    dataTable2.addColumn({ type: `'string`', id: `'Name`' });
    dataTable2.addColumn({ type: `'date`', id: `'Start`' });
    dataTable2.addColumn({ type: `'date`', id: `'End`' });
    dataTable2.addRows([
"@

$html += $success

$html += @"
]);
var options =
{
timeline:  {
groupByRowLabel: true,
colorByRowLabel: false,
singleColor: '#1CA589',
rowLabelStyle: {fontName: `'Helvetica`', fontSize: 14 },
barLabelStyle: {fontName: `'Helvetica`', fontSize: 14 }
}
};
chart.draw(dataTable2, options);
}
</script>
</head>
<body><font face=`"Helvetica`" size=`"4`" > SQL Agent Jobs op SQL Servers afgelopen 24 u</font></br>
<div id=`"FailedTimeline`" style=`"width: 1885px; height: 500px;`"></div>
<div id=`"SuccessTimeline`" style=`"width: 1885px; height: 1200px;`"></div>
</body>
</html>
"@

Set-Content -Path $Jobhtml -Value $html

Send-MailMessage -To $To -Subject "SQL Agent jobs of the last 24 hours on $today" –From "[email protected]" -SmtpServer $MailServer -Body "SQL Agent jobs of the last 24 hours" -BodyAsHtml -Attachments $Jobhtml

Finishing touch

Now we have the complete script, we can choose to deliver it as is to a coworker who can execute it ad hoc. Maybe our manager prefers to receive the e-mail daily at a fixed time. In that case, we create a scheduled task.
It all depends on the customs and needs of your company. Keep in mind that permissions on the SQL Instances and the e-mail server are needed too. If a recipient does not have those permissions, he or she will not get the required information.

To be continued

Now you have one example of how you can send all job execution results as attachment to an e-mail.

You can alter this in many ways. Maybe you want to show not only the date, but also the time the script was executed. Or you could add other filters, defined in the where clause.
Maybe you have different teams responsible for production and test or development servers. Or when your helpdesk rotates every 8 hours, you may prefer to show the latest 8 hours and send e-mails 3 times a day,...
You can split the failed jobs into 'Failed', 'Retyr', 'Cancelled' and 'Unknown'.
You can add more CSS style, either to the HTML header, or in the table cells, like bold or italic or whatever.
You can use a different method to list your SQL Instances.

Let the creativity flow.

Previous Post Next Post

[email protected]