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:
A 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.
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
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!
where do we get the ConvertTo-DbaTimeline command?
Hi, it’s part of the dbatools offering: https://dbatools.io/download/
Nice work! Thanks.
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.
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)
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
“`
Pingback: Creating Timelines With dbatools – Curated SQL
Question – can we change the ORDER BY of the databases or backup types?
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.
I am unable to place ConvertTo-DbaTimeline command. Am I missing something? I checked the dbatools command list as well.
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?
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
Hi, After you have updated dbatools, I would try:
remove-module dbatools
and then:
import-module dbatools
Let me know if this works.
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
Pingback: SQL Agent Jobs Timeline with dbatools.io (download) - Marcin Gminski
Great work.
Would be nice to have the ConvertTo-DbaTimeline available on some of the other commands.
Cheers
Phil
Hi, I actually have this on my To-Do list 🙂
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?
Did you try something like
$date= (Get-date).AddDays(-7).ToString()
Get-date $date -format “yyyy-MM-dd”
BR
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.
Amazing. Thank you so much for this.
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
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)
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.
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.
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!!
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.
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?
Hi, A lot has changed since the release of the history timeline. The -NoJobSteps in Get-DbaAgentJobHistory is now -ExcludeJobSteps. You can see the full reference in the docs: https://docs.dbatools.io/#Get-DbaAgentJobHistory. Any questions please let me know. Thanks.
Is it possible to change the default row label from dd/mm hh:mm to mm/dd/yyyy hh:mm ?