Visualize the timeline of your SQL jobs using Google graph and email
-
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. -
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!!!!
-
Excellent!! Very usefull!! I implemented the solution, quite happy!! Thanks
-
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.
-
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
-
Thanks for the Reply Kees. Unfortunately, even with the updated version, I open the attachment with Chrome and it gives me the same results.
-
add this entry parameters @attach_query_result_as_file = 1 TO the call of msdb.dbo.sp_send_dbmail
-
Great post Theo. Very helpful.
-
@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
-
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..!
-
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
-
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
-
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
-
HI Matt
I ran into the same issue, and fount the answer in an other forum post: http://qa.sqlservercentral.com/Forums/Topic1639517-3411-1.aspx
Hope it helps
Theo
-
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...
Viewing 15 posts - 16 through 30 (of 96 total)
You must be logged in to reply to this topic. Login to reply