Visualize the timeline of your SQL jobs using Google graph and email

Question

Visualize the timeline of your SQL jobs using Google graph and email

Viewing 15 posts - 76 through 90 (of 96 total)

You must be logged in to reply to this topic. Login to reply

Andrey Right there with Babe Points: 779 More actions · Answer 1

Hi Theo!

Thanks for your perfect solution. Couple of years ago it helped me and inspired to create SSRS report with similar functionality.

Also, it is possible to run it as Custom report in Management Studio (SSMS).

I'm glad to share it and looking forward for your comments.

https://github.com/ASamykin/PerformanceReports/blob/master/Report%20Project1/SQLJobs_Timeline.rdl

Regards,

Andrey.

Theo Ekelmans SSCarpal Tunnel Points: 4581 More actions · Answer 2

KEWL !!!!

This is exactly why i love this community so much, we constantly improve on each others ideas !

I'll give it a try 🙂

Thanks Andrey

eliassal Default port Points: 1456 More actions · Answer 3

Hi Theo, your new version 4 .1 is behaving exactly the same way when I had the issues because of dates and you fixed it.

Here is what I am getting with this 4.1

Job timeline on: SQL2K12 (Node: SQL2K12) from 2019-04-27 15:01:28 until 2019-05-02 15:01:28 (hiding jobs with runtime < 5 seconds)

and here is what I get with the earlier fixed one

Viz

The script also needs to be updated in order to announce in the email subject for which SQL server the jobtimeline is for something like

subject : JobTimeline whichSQL

eliassal Default port Points: 1456 More actions · Answer 4

I also updated the good version I have (not this 4.1) so the attachment will be name as follows

@query_attachment_filenameFull = 'JobTimeline' + @ServerName + '-' + @datestring + '.html'

and the subject as follows

@subjectWithSQL = 'JobTimeline' + ' ' + @ServerName

Do you want me to share this version?

Theo Ekelmans SSCarpal Tunnel Points: 4581 More actions · Answer 5

Yes please,

And i will see if i can add the fix to 4.1 🙂

elirueda Newbie Points: 3 More actions · Answer 6

Thanks for your help on this. I regret to say that the 4.1 doesnt show me any graphs, but 4.0 does. Let me know what I can provide to help troubleshoot this sir

eliassal Default port Points: 1456 More actions · Answer 7

OK, Theo you can download the file at https://github.com/eliassal/Visualize-timeline.git

Theo Ekelmans SSCarpal Tunnel Points: 4581 More actions · Answer 8

HUH ?!?

@elirueda.... 4.0 works and 4.1 does not ?!!?!?

@eliassal.... that is version 1.1 🙂

I think it might be better if i concentrate on 4.1 and see what goes wrong.

Can you both forward me the HTML files, the scripts send, so i can see what is missing. and please tell me what browsers you use (i use Google Chrome on Windows 10)

4.1 works on nearly all 104 SQL instances my team manages, so i'm curious why you have non-working. It may be that most of the instances we have are either US-US of NL-NL setup. FR-FR had problems which some else helped me figure out.

Hope te hear from you guys soon.

t

eliassal Default port Points: 1456 More actions · Answer 9

NO Theo, it is not version 1.1, it is the version you provided me after fixing several issues some time a go(January), when you asked me to update the 3 version with the following

Can you replace lines 344 and 345

"height": '+cast(@HeightInPixels as varchar(10))+',

"width": '+cast(@WidthInPixels as varchar(10))+'

By these 2 ?

"height": '+cast(isnull(@HeightInPixels, 250) as varchar(10))+',

"width": '+cast(isnull(@WidthInPixels, 1800) as varchar(10))+'

eliassal Default port Points: 1456 More actions · Answer 10

Theo, I uploaded 2 html files created by 4.1 on github

https://github.com/eliassal/Visualize-timeline

Theo Ekelmans SSCarpal Tunnel Points: 4581 More actions · Answer 11

Hi,

i know it's late (by almost a year) but I think I finally figured out what went wrong.

Please check out version 4.2 in the attachment, and let met know if that one does work work you.

Sorry about the late reply, but its been and.... ehhh... interesting year 😉

Theo.

You can download 4.2 here: http://www.ekelmans.com/GoogleJobTimeline.sql

This reply was modified 4 years, 2 months ago by Theo Ekelmans.

ne0gotenks Grasshopper Points: 14 More actions · Answer 12

Hi Theo, i just wanted to say thanks.. this is an immensely useful script. That said, sometime around june, the old version stopped working. Nothing renders. I tried to run the newer version but I still don't see any chart rendered. Would you be able to tell me what's wrong here? I tried to view in several chrome browsers and nothing. not sure how i can get you the sample, as it will not allow me to attach a text or an html file.

This reply was modified 4 years, 2 months ago by ne0gotenks.
This reply was modified 4 years, 2 months ago by ne0gotenks.

mcTDBA Default port Points: 1457 More actions · Answer 13

This is a great script and I use it often. It won't load the graph for me anymore. I also noticed in the demo link on the main page of this article it is no longer loading there either.

Theo Ekelmans SSCarpal Tunnel Points: 4581 More actions · Answer 14

Hi,

Open the output in notepad, and if it is not in UTF-8, save it in UTF-8.

There is a bug in google chart that "assumes" that if the script is saved in UTF-16 and such all the referenced scripts are also UTF-16 and they are not.

eliassal Default port Points: 1456 More actions · Answer 15

Theo, this is Salam, I am having the same thing in edge and Chrome, opened the file in notepad++, changed encoding to UTF-8 but it did not help, same thing I get

Job timeline on: SQL2K17 from 2019-04-29 07:18:48 until 2019-05-02 07:18:48 (hiding jobs with runtime < 1 seconds), however, when I see the source, there are a lot of code

<html>

<head>

google.setOnLoadCallback(drawChart);

function drawChart() {

var container = document.getElementById('JobTimeline');

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([

[ '[Uncategorized (Local)]', 'syspolicy_purge_history', new Date(2019, 3, 30, 2, 0, 1), new Date(2019, 3, 30, 2, 0, 39) ],

[ '[Uncategorized (Local)]', 'syspolicy_purge_history', new Date(2019, 4, 1, 7, 23, 47), new Date(2019, 4, 1, 7, 24, 24) ],

[ '[Uncategorized (Local)]', 'syspolicy_purge_history', new Date(2019, 4, 2, 4, 53, 28), new Date(2019, 4, 2, 4, 53, 50) ],

]);

var options =

{

timeline: {

groupByRowLabel: true,

colorByRowLabel: false,

singleColor: false,

rowLabelStyle: {fontName: 'Helvetica', fontSize: 14 },

barLabelStyle: {fontName: 'Helvetica', fontSize: 14 }

}

};

chart.draw(dataTable, options);

}

</script>

</head>

<body><font face="Helvetica" size="3" >Job timeline on: SQL2K17 from 2019-04-29 07:18:48 until 2019-05-02 07:18:48 (hiding jobs with runtime < 1 seconds)</font>

</body>

</html>