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

  • I thought maybe the content filter at work started blocking me from it for some reason so I disconnected from vpn and tested and it still loads up a blank page. I also noticed the demo page (http://www.ekelmans.com/FTP/Web/JobTimeline.html) was doing the same thing. I did the same on vpn / off vpn test just make sure it wasn't a content filter for the test page.

     

     

  • Hi Guys,

    Version 4.3 is available for download, with a special thanks to Hendrik Burmann for his feedback 🙂

    Please beware! I know some email services change the file encoding to UTF16, that also prevents the script from running, opening and saving then with notepad as utf8 fixes the problem. As of yet I have had no reply from google why script files encoded in UTF16 fail.

    In the zip file the script and 2 versions of the HTML files are saved, identical save for the encoding ; UTF8 (works), UTF16 (fails). If you have some HTML skills and want to think along with us, Hit F12 in Chrome to see why it fails.

    grtz,

    Theo

    Attachments:
    You must be logged in to view attached files.
  • Almost forgot; Salam Admin also helped with this version 🙂

    Teamwork makes the dream work !

  • Updating the charts loader.js script call and specifying UTF-8 charset & Javascript as well as an older version as follows resolves the UTC-16 issue. Looks like google changed something in the most recent version release 49. I've tested my own job with success.

    EDIT: Realized a bit after that the UI popups on mouseover were not working and that you can continue to use current chart load. This edit accommodates those 2 points by explicitly calling version 49 UTF-8 of the CSS for the popups. I'm sure there's some way to grab that version and pass it in but this works as a workaround.

    --***************************************************************************************
    -- Format for google graph - Header
    -- (Split into multiple inserts because the default text result setting is 256 chars)
    --***************************************************************************************
    insert into ##GoogleGraph (HTML)
    select '<html>
    <head>
    <!--<META HTTP-EQUIV="refresh" CONTENT="1">--> '
    insert into ##GoogleGraph (HTML)
    select '
    <script type="text/javascript"></script>
    <script charset="UTF-8" src="https://www.gstatic.com/charts/loader.js"></script>
    <script>google.charts.load(''current'', {packages: [''timeline'']})</script>
    </head>
    '

    -- start of the body
    insert into ##GoogleGraph (HTML)
    select '
    <body>
    <font face="Helvetica" size="3" >'

    +'Job timeline on: '+@@servername + ' (Node: ' +cast(SERVERPROPERTY('ComputerNamePhysicalNetBIOS') as varchar(128)) +')'
    +' from '+convert(varchar(20), @StartDT, 120)
    +' until '+convert(varchar(20), @EndDT, 120)
    +case when @MinRuntimeInSec = 0 then '' else ' (hiding jobs with runtime < '+cast(@MinRuntimeInSec as varchar(10))+' seconds)' end
    +'</font>'

    insert into ##GoogleGraph (HTML)
    select
    '<link charset="UTF-8" id="load-css-0" rel="stylesheet" type="text/css" href="https://www.gstatic.com/charts/49/css/core/tooltip.css">'
    insert into ##GoogleGraph (HTML)
    select
    '<link charset="UTF-8" id="load-css-1" rel="stylesheet" type="text/css" href="https://www.gstatic.com/charts/49/css/util/util.css">'
    insert into ##GoogleGraph (HTML)
    select '
    <script language = "JavaScript">'
    insert into ##GoogleGraph (HTML)
    select 'function drawChart() {'

    --***************************************************************************************
    -- One timeline over all
    --***************************************************************************************

    • This reply was modified 1 year, 8 months ago by  RobertsonSQL.
  • This is awesome. However, I am seeing a no graphs within email attachment. Any idea on this specific issue.

    "Job timeline on: <Server name>from 2020-12-26 22:36:56 until 2020-12-29 22:36:56 (hiding jobs with runtime < 1 seconds)

  • Now I am able to view the charts after modifying the HTML section with UTF 8 scripts. Thankyou for the updates.

  • Thanks RobertsonSQL,

    Since some people reported that Outlook 365 alters the HTML attachments in files to UTF-16 for some reason, your fix will help by forcing google to use UTF-8!  Thanks for sharing 🙂

    @All; please download version 4.4 : http://www.ekelmans.com/FTP/Web/GoogleJobTimeline.sql

Viewing 7 posts - 91 through 96 (of 96 total)

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