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 - 16 through 30 (of 98 total)

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

starunit SSCommitted Points: 1817 More actions · Answer 1

Thanks for the example/exercise. That's really a great way to leverage existing tools (I hadn't seen Google Charts before and will keep that nugget in my toolbox too).

I've been using SQLjobvis from SQLSoft. It's free also, but a bit limited. I'm going to spend some time with your contribution to see if it's a better fit for us.

Thanks Again!

Mark
Just a cog in the wheel.

fschavez Valued Member Points: 53 More actions · Answer 2

Excellent work!!! Thanks for sharing.:w00t::w00t:

Now i will play with this and I hope can create more graphics, like the CPU graphics.

Excellent!!!!

frossasco SSC Enthusiast Points: 126 More actions · Answer 3

Excellent!! Very usefull!! I implemented the solution, quite happy!! Thanks

bmergen77 SSC Veteran Points: 269 More actions · Answer 4

Seems like everything is working for me, but I am not getting any graphics....

Only seeing the top line that says "HTML --- Job timeline on:........" the rest of the page is blank.

Any help would be appreciated.

k.schouten Mr or Mrs. 500 Points: 526 More actions · Answer 5

You probably have an old version. In the newest version a line is added to send the html-file as an attachement. When you open the attachment you will see the graph.

Kees

bmergen77 SSC Veteran Points: 269 More actions · Answer 6

Thanks for the Reply Kees. Unfortunately, even with the updated version, I open the attachment with Chrome and it gives me the same results.

frossasco SSC Enthusiast Points: 126 More actions · Answer 7

add this entry parameters @attach_query_result_as_file = 1 TO the call of msdb.dbo.sp_send_dbmail

para40tn SSChasing Mays Points: 605 More actions · Answer 8

para40tn

SSChasing Mays

Points: 605

July 28, 2015 at 9:38 am

#1815468

Great post Theo. Very helpful.

bmergen77 SSC Veteran Points: 269 More actions · Answer 9

@attach_query_result_as_file = 1 is already a part of the query. I am getting the attachment correctly, but when I go to open the attachment it is not displaying the graphics. Everything looks correct when I view the source

T Norton SSC Enthusiast Points: 106 More actions · Answer 10

Outstanding job sir....!! 🙂

Been looking for something like this for a LONG time...!! And now have ideas to use the Google Charts for even more dashboard items.

Much appreciated and thanks again..!

jpratt-797544 SSC Eights! Points: 900 More actions · Answer 11

I had the same situation as posted with the results not being returned as expected, except the results weren't returned at all. I am running SQL 2012 Enterprise 64-bit 11.0.5556 (SP2 + CU3). Adding the extra parameter to the send email call immediately returned the results as an attachment.

This solution is an awesome combination of Google Charts and dynamic local data.

I had previously wrapped Google Charts with an ASPX page that called a variable stored procedure using a combination of query string and config file settings. The stored procedure returns a dataset using FOR XML, with a structure I specify. The ASPX page reads the XML elements and populates the required Google Chart values, outputting dynamic local data rendered in the requested chart format. For a variety of reasons, the dashboard I created contains IFRAMEs which each call their own instance of the ASPX page. Technically, a dashboard can be created from a single HTML/ASPX page with multiple charts included (per the Google dev info and examples).

I also created an SSRS report for SQL Agent job and step durations and statuses. I implemented it as a custom report that I run through SSMS from my local PC. The only down side is that it requires a stored procedure and UDF in the msdb database on each server it runs against, although the code is nicely encapsulated.

I've already made a few tweaks to the JobTimeline SQL to show jobs and job steps instead of categories and jobs, and I got the emailed attachment to work (it opens in IE just fine!). I'm very excited to research other options, including combining the techniques you've demonstrated, the timeline chart, and the ASPX page that supports query string and config setting parameters.

Thanks for posting this!

J Pratt

jamie.chicago SSC Veteran Points: 286 More actions · Answer 12

Awesome, Theo, and thank you for sharing. I wish I knew how to tweak the code to add some of the other functionality you showed in your post. 🙂

On a different note, when I execute the script and receive the email with attachment if I merely open the attachment (in chrome) it is plain text. However, if I forward the email, at the bottom of the forwarded message is a link that when clicked opens into exactly the type of graph your blog showed. Any idea how I might make it do that without having to forward the message? While I could setup an auto-forward for these messages that seems clunky.

Thanks again!

Jamie

Theo Ekelmans SSCarpal Tunnel Points: 4593 More actions · Answer 13

As requested, i made a quick standalone port of my Cpuload Warning graph query as a base for tweaking by you guys.

http://www.ekelmans.com/FTP/Web/GoogleCpuLoadInfoV1.0.sql

And a new version of the script in the article: http://www.ekelmans.com/FTP/Web/GoogleJobTimelineV1.2.sql

Have fun with it....

Theo

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

HI Matt

I ran into the same issue, and fount the answer in an other forum post: http://www.sqlservercentral.com/Forums/Topic1639517-3411-1.aspx

Hope it helps

Theo

Sonny Childs SSC-Addicted Points: 411 More actions · Answer 15

Theo Ekelmans (7/28/2015)
As requested, i made a quick standalone port of my Cpuload Warning graph query as a base for tweaking by you guys.
http://www.ekelmans.com/FTP/Web/GoogleCpuLoadInfoV1.0.sql
And a new version of the script in the article: http://www.ekelmans.com/FTP/Web/GoogleJobTimelineV1.2.sql
Have fun with it....
Theo

Theo, this is wonderful!

You've actually sparked me to modify some of this to suit other business-case uses. For example, I've created a datapull to a Google Graph Calendar that shows ticket counts by day.

This will probably open up a good business intelligence path for me to wander down for a bit. Here's a screenshot of the HTML file I got:

Now I just need to add some drilldowns and procedurally host this on an internal website...