creating a history timeline

Hey all, my name is Marcin and this is my first post and my first contribution to the dbatools. I hope you find it useful.

introduction

Part of a successful administration and management of any database is to know what happens over a period of time, when scheduled jobs and backups run and whether they are successful or not.

The common difficulty is getting this information out of SQL Server and then – the most difficult part – understanding what it means. In case of scheduled jobs, we want to make sure that there are no clashing and those heavy workloads such as ETL, backups, integrity checks and index maintenance run in isolation as much as possible.

Before dbatools, getting the information out of SQL Server was often a tedious task that often involved relatively complex T-SQL script, spreadsheet and some copying and pasting. Thanks to all the hard work of the dbatools team this is a history. A simple command can retrieve all the information we want and now with the new ConvertTo-DbaTimeline, it can be easily plotted on a graphical timeline!

concept

The concept of a timeline is very simple and based on a Gantt chart used in project management and time schedule:

A Gantt chart is a type of bar chart that illustrates a project schedule, named after its inventor, Henry Gantt (1861–1919), who designed such a chart around the years 1910–1915
https://en.wikipedia.org/wiki/Gantt_chart

The idea is to show a graphical representation of an item or items of interest (task, job, meeting duration) with the start and end dates on a common timeline:

timeline is a chart that depicts how a set of resources are used over time. If you’re managing a software project and want to illustrate who is doing what and when, or if you’re organizing a conference and need to schedule meeting rooms, a timeline is often a reasonable visualization choice. One popular type of timeline is the Gantt chart.
https://developers.google.com/chart/interactive/docs/gallery/timeline

execution

Currently, the output from the following commands is supported:

  • Get-DbaAgentJobHistory
  • Get-DbaDbBackupHistory

You will run the above commands as you would normally do but pipe the output to ConvertTo-DbaTimeline, the same way as you would with any other ConverTo-* PowerShell function. The output is a string that most of the time you will save as file using the Out-File command in order to open it in a browser.

Examples

Get-DbaAgentJobHistory

#To generate the timeline for agent job history and save as html file:
Get-DbaAgentJobHistory -SqlInstance sql-1 -StartDate '2018-08-18 00:00' -EndDate '2018-08-19 23:59' -ExcludeJobSteps | ConvertTo-DbaTimeline | Out-File C:\temp\DbaAgentJobHistory.html -Encoding ASCII

Note the -Encoding ASCII – this is required for correct JavaScript and HTML formatting.

Get-DbaAgentJobHistory Timeline

Colours are applied automatically based on the job status:
SucceededFailedRetryCanceledIn Progress

 

Get-DbaDbBackupHistory

#Backup history timeline:
Get-DbaDbBackupHistory -SqlInstance sql-1 -Since '2018-08-18 00:00' | ConvertTo-DbaTimeline | Out-File C:\temp\Get-DbaDbBackupHistory.html -Encoding ascii

Get-DbaDbBackupHistory Timeline

And again, each backup type has its own colour. This time, however, they are not pre-configured but are set automatically by the Google’s framework so could be random.

We can also run it for multiple servers at once AND THIS IS GREAT as it allows to produce a comprehensive overview of the entire estate which can help to assess the impact on the network, storage or virtual cluster. And without passing the Out-File command it will simply output an HTML as a string:

Get-DbaDbBackupHistory -SqlInstance sql2017, sql2016 -Since '2018-08-13 00:00' | ConvertTo-DbaTimeline

Which can be assigned to a variable or used to compose an email as in the example below:

$messageParameters = @{
    Subject = "Backup history for sql2017 and sql2016"
    Body = Get-DbaDbBackupHistory -SqlInstance sql2017, sql2016 -Since '2018-08-13 00:00' | ConvertTo-DbaTimeline
    From = "[email protected]"
    To = "[email protected]"
    SmtpServer = "smtp.ad.local"
 }
Send-MailMessage @messageParameters -BodyAsHtml

the mechanics

We are using Google Charts Framework to generate the graph in the ConvertTo-DbaTimeline. The framework is JavaScript based, very easy to use and fast. The rendering happens on the client PC and no data is ever sent to Google or anywhere.

You can read more about the Google Chart Framework:

https://developers.google.com/chart/

And specifically about the Timeline object:

https://developers.google.com/chart/interactive/docs/gallery/timeline

The creation of the HTML file is not much different to how dynamic websites work. If we look at Google’s generic example below, I have highlighted the part that contains JavaScript Array data rows which, in our case are being generated dynamically from the pipe the input. The rest of the code apart from few parameters is static. Once the input is transposed it is the being returned as a string. That’s it. It is very simple yet powerful.

  google.charts.load("current", {packages:["timeline"]});
  google.charts.setOnLoadCallback(drawChart);
  function drawChart() {
    var container = document.getElementById('example3.1');
    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([
      [ 'President', 'George Washington', new Date(1789, 3, 30), new Date(1797, 2, 4) ],
      [ 'President', 'John Adams', new Date(1797, 2, 4), new Date(1801, 2, 4) ],
      [ 'President', 'Thomas Jefferson', new Date(1801, 2, 4), new Date(1809, 2, 4) ],
      [ 'Vice President', 'John Adams', new Date(1789, 3, 21), new Date(1797, 2, 4)],
      [ 'Vice President', 'Thomas Jefferson', new Date(1797, 2, 4), new Date(1801, 2, 4)],
      [ 'Vice President', 'Aaron Burr', new Date(1801, 2, 4), new Date(1805, 2, 4)],
      [ 'Vice President', 'George Clinton', new Date(1805, 2, 4), new Date(1812, 3, 20)],
      [ 'Secretary of State', 'John Jay', new Date(1789, 8, 25), new Date(1790, 2, 22)],
      [ 'Secretary of State', 'Thomas Jefferson', new Date(1790, 2, 22), new Date(1793, 11, 31)],
      [ 'Secretary of State', 'Edmund Randolph', new Date(1794, 0, 2), new Date(1795, 7, 20)],
      [ 'Secretary of State', 'Timothy Pickering', new Date(1795, 7, 20), new Date(1800, 4, 12)],
      [ 'Secretary of State', 'Charles Lee', new Date(1800, 4, 13), new Date(1800, 5, 5)],
      [ 'Secretary of State', 'John Marshall', new Date(1800, 5, 13), new Date(1801, 2, 4)],
      [ 'Secretary of State', 'Levi Lincoln', new Date(1801, 2, 5), new Date(1801, 4, 1)],
      [ 'Secretary of State', 'James Madison', new Date(1801, 4, 2), new Date(1809, 2, 3)]
    ]);
    chart.draw(dataTable);
  }

final note

Although it’s a Google framework, this works great on any browser not only on Google Chrome. As per the Google Docs : Charts are rendered using HTML5/SVG technology to provide cross-browser compatibility (including VML for older IE versions).

However, one last thing to keep in mind is that it does require access to the internet in order to access Google Charts and Bootstrap frameworks. As these are based on JavaScript, it is best to run on a client PC and not on Windows Server.

my experience as a contributor

Although I have been using dbatools for quite some time now this was my first contribution to the project. I never really looked how it all works behind scenes but oh boy it is impressive. Chrissy has done a fantastic job designing the dbatools framework. All the internal functions are there, as a developer I did not have to worry about how to write a message or raise an error.

Chrissy is also a great mentor, she reviewed my code and tweaked it a bit where required with the full explanation of what she did and why, which made perfect sense. I probably learned more PowerShell in those couple of days working with Chrissy than in my whole career. Thank you!

30 thoughts on “creating a history timeline

  1. Robert Plata Reply

    This will help me immensely! Thank you.

    I’m having a problem executing the Send-MailMessage as you have posted it.
    + CategoryInfo : InvalidArgument: (:) [Send-MailMessage], ParameterBindingException
    + FullyQualifiedErrorId : CannotConvertArgument,Microsoft.PowerShell.Commands.SendMailMessage

    I am not that familiar with PowerShell, so there is probably something glaringly simple that I am missing.

  2. Greg Reply

    Maybe I made a mistake while trying to generate the mail.
    When I fire up the PS Script I get the error message
    Cannot convert ‘System.Object[]’ to the type ‘System.String’ required by parameter ‘Body’

    Do you maybe know what I missed? (Sorry I am a beginner with Powershell)

    • Marcin Gminski Post authorReply

      Hi, can you please try adding | Out-String after the ConvertTo-DbaTimeline and see if it works for you. We may have omitted that in the example. Apologies.
      “`
      $messageParameters = @{
      Subject = “Backup history for sql2017 and sql2016”
      Body = Get-DbaBackupHistory -SqlInstance sql2017, sql2016 -Since ‘2018-08-13 00:00’ | ConvertTo-DbaTimeline | Out-String
      From = “[email protected]
      To = “[email protected]
      SmtpServer = “smtp.ad.local”
      }
      Send-MailMessage @messageParameters -BodyAsHtml
      “`

  3. Pingback: Creating Timelines With dbatools – Curated SQL

  4. Randal Hartwig Reply

    Question – can we change the ORDER BY of the databases or backup types?

  5. Marcin Gminski Post authorReply

    Very good question. You could try sorting output form the Backup and Agent history functions by using Sort-Object before you pass it into the Timeline function however, Google’s framework also does its own sorting which we have no control over so you may be out of luck. Give it a go, it’s worth trying.

  6. Shivram Reply

    I am unable to place ConvertTo-DbaTimeline command. Am I missing something? I checked the dbatools command list as well.

    • Marcin Gminski Post authorReply

      Hi, can you please describe a little bit more what the problem is, the exact command you are trying and any errors that you get?

  7. Shivram Reply

    Hi Marcin,
    I was trying to use ConvertTo-TimeLine for my SQL Agent job history. When I execute the command Get-DbaAgentJobHistory | ConvertTo-TimeLine, it gave me the below error.

    ConvertTo-DbaTimeline : The ‘ConvertTo-DbaTimeline’ command was found in the module ‘dbatools’, but the module could not be loaded. For more information, run ‘Import-Module dbatools’.
    At line:1 char:26
    + Get-DbaAgentJobHistory | ConvertTo-DbaTimeline
    + ~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo : ObjectNotFound: (ConvertTo-DbaTimeline:String) [], CommandNotFoundException
    + FullyQualifiedErrorId : CouldNotAutoloadMatchingModule

    I then did a Find-DbaCommand ConvertTo-TimeLine and it returned nothing.
    I also performed an Update-DbaTools and also tried Update-DbaTools -dev but still I do not see the command.

    Thank you,
    Regards

    • Marcin Gminski Post authorReply

      Hi, After you have updated dbatools, I would try:
      remove-module dbatools

      and then:
      import-module dbatools

      Let me know if this works.

      • Greg Reply

        In order to have the dbatools Package to work, I had to start Powershell as an Administrator.
        I then disabled the UAC and everything has been working fine from that time on.
        Might be a point to check.
        BR
        Greg

  8. Pingback: SQL Agent Jobs Timeline with dbatools.io (download) - Marcin Gminski

  9. Phil Reply

    Great work.

    Would be nice to have the ConvertTo-DbaTimeline available on some of the other commands.

    Cheers
    Phil

  10. Mulham Reply

    Hello,
    I’m trying to return last seven days of backup history report by running this script.

    $date= (Get-date).AddDays(-7).ToString()
    Get-DbaBackupHistory -SqlInstance Server -Since $Date | ConvertTo-DbaTimeline| Out-File C:\temp\Get-DbaBackupHistory.html -Encoding ascii -Force
    I’m getting the date formatted with Europeans format day/month/year.
    any way I could change this order?

    • Greg Reply

      Did you try something like
      $date= (Get-date).AddDays(-7).ToString()
      Get-date $date -format “yyyy-MM-dd”

      BR

    • Marcin Gminski Post authorReply

      Hi, This is difficult for me to replicate. It may be due to Google Charts interpretation rather than PowerShell but need to double check. I think we pass ISO date into Charts. I’ll have a look.

  11. rich Reply

    Is there any way to overlay the results of both the AgenJobHistory and BackupHistory on the same gantt chart. I was thinking that would be a great tool to see if any of the jobs overlay any of the backups to identify contention of resources of long running jobs or backups within the same time slots..

    thank you

    • Marcin Gminski Post authorReply

      Hi, I presume you are asking about a scenario where backups aren’t driven by the SQL agent? Ie NetBackup or other third party solution? The simple answer is no.
      If you have backups triggered by the agent then the job “Database backup” will correlate with the backup itself and will be enough to establish contention.

      To address the first scenario may I recommend SQLWATCH.IO? (Disclaimer, This is a tool witten by myself, shameless self promotion but it will likely give you what you are asking for)

      • Rich Reply

        Yes, we do have an agent that manages our backups, however SQL server records the history just like if it was triggered by the agent. I have no issue running either Get-DbaAgentJobHistory or Get-DbaBackupHistory and getting the results. All i was hoping to do is run both at the same time or merge the results so Backups and Jobs would all be listed on the left and i can scan through to see what jobs were running while the backups were running or what backups were running while one of my ETL processes was running. It would be very powerful to see those all on the same chart.

        • Marcin Gminski Post authorReply

          I see. Bit of a hack but if you dump both outputs from Get-DbaBackupHistory and Get-DbaAgentJobHistory into JSON (ConvertTo-JSON) and load that json into PowerBI you should be able to correlate results together. You can then refresh the JSON daily and refresh powerBI to load new data.

          • rich

            Hmm.. ok.
            I really like the google gantt chart look that dbatools provided.

            Since querying the History of Jobs and Backups is not a too big a deal, it might be easier to create procedure to gather data then use PowerBI to call the procedure, that is assuming you can create a nice Gantt chart in PowerBI, which i’m not familiar with doing at this time.

            I’m a newbie in PowerBi so this is a new thing for me. I literally just downloaded PowerBI about 2 weeks ago to play and learn a little. looks like i might be digging in to learn a little more about it.

            BTW, thank you for the great set of tools.
            dbatools is an awesome tool-set, I wish i would’ve found this earlier!!

  12. Marcin Gminski Post authorReply

    Yeah I like Google Charts too but they can be difficult to work with. They are really designed for a client-server architecture. Take a look at some of the SQLWATCH reports https://sqlwatch.io/ , there is a Gantt Chart in PowerBI which should get you started.

  13. Ken Hemmerling Reply

    I ran “remove-module dbatools” followed by “Install-module dbatools’ in an PowerShell window run as administrator to make sure I’ve got the most recent version. When I run:
    > Get-DbaAgentJobHistory -SqlInstance nex-srv1\dev -StartDate ‘2019-04-01 00:00’ -EndDate ‘2019-04-20 23:59’ -NoJobSteps
    I get:
    > Get-DbaAgentJobHistory : A parameter cannot be found that matches parameter name ‘NoJobSteps’.
    > At line:1 char:108
    > + … -StartDate ‘2019-04-01 00:00’ -EndDate ‘2019-04-20 23:59’ -NoJobSteps
    > + ~~~~~~~~~~~
    > + CategoryInfo : InvalidArgument: (:) [Get-DbaAgentJobHistory], ParameterBindingException
    > + FullyQualifiedErrorId : NamedParameterNotFound,Get-DbaAgentJobHistory

    Any idea what the problem is?

  14. Tim Toennies Reply

    Is it possible to change the default row label from dd/mm hh:mm to mm/dd/yyyy hh:mm ?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.