Visualize the timeline of your SQL jobs using Google graph and email
-
schilds (7/28/2015)
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...
WOW !!!!
Talking about sparking ideas! I did not know you could do that with the calender control 🙂
Your printscreen made me think of some uses of my own!
If the code is shareable and you are willing i would love to have a template of the code.
Grtz,
Theo
-
As someone above me said: this is why I keep coming back to SSC!
-
Hi Guys,
A tip based on feedback of bmergen77, do *not* use apostrophs ' in JOB names, that breaks the script and you end up with an unrenderable HTML.
Grtz,
Theo
-
Hi Theo,
"Geweldig gedaan!!":cool:
and Thanks for this script.
Is it also possible to generate this Graph for ... let's say a weekly overview
and then grouped per day 0:00 - 0:00 hrs?
Grtz
Martijn
-
Bedankt Martijn 🙂
What do you mean, like averaging runtimes per job over a week/month?
-
You now have a Graph for the period you set at the beginning of the script... If you change the period, you only see it in 1 graph the whole period.
What I like to see is a period of 1 week grouped per day, and every day below each other.
I have a few daily jobs. If the day starts at 0:00 and ends at 23:59 then you can see if jobs running slower at a certain point of time... and If you want to plan an new Job you can see which timeslots are free...
-
Good idea, i'll tweak my script so it generates one graph per day if you specify a date range > 1 day.
Grtz,t
-
WOW !!!!
Talking about sparking ideas! I did not know you could do that with the calender control 🙂
Your printscreen made me think of some uses of my own!
If the code is shareable and you are willing i would love to have a template of the code.
Grtz,
Theo
Here's a pastebin.
...and here's the documentation on the calendar.
https://developers.google.com/chart/interactive/docs/gallery/calendar
Most of what you originally posted is still there. I've commented the important parts on how to format your query.
I'm still toying with pulling in drilldown data.
-
Very cool, thanks for this.
-
Thanks for the article. I applied it to my backup monitoring process written in PowerShell. Using Google Charts I was able to create a nice custom HTML email attachment that shows database growth over a period of time.
-
And again a new version based on some feedback by the community"
A multi day version of the script (1 timeline per 24 hours, stacked on top of each other)
Download link: http://www.ekelmans.com/FTP/Web/GoogleJobTimelineV2.0.sql
Enjoy, Theo.
-
Thanks! I will check it out after my 2 weeks off....
-
Hi Theo,
Thanks for the script!
it's running great and certainly going to use it
-
When using case-sensitive collation, the following line results in error:
Msg 207, Level 16, State 1, Line 273
Invalid column name 'html'.
Changing 'html' to 'HTML' resolves the error:
--****************************************************************************************
-- Output HTML page - copy output & pastte to a .HTML file and open with google chrome
--****************************************************************************************
if @ReturnRecocordset = 1
select HTML from ##GoogleGraph order by ID
There's also a misspelling in @ReturnRecocordset, although it doesn't really hurt anything.
-
And here is the latest multi day version with the correct case this time (thx to alpinetrout)
/***************************************************************************************
This script returns a (graphical) timeline for all SQL jobs using google graph
****************************************************************************************
Version: 1.1
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2015-06-24
Version: 1.2
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2015-07-28
Change: Updated using feedback from the SqlServerCentral Community
Version: 2.0
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2015-07-28
Change: Added an option to split multiple days into seperate graphs as requested by
the SqlServerCentral Community
Version: 2.1
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2016-01-19
Change: Day of week added
Version: 2.2
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2016-02-22
Change: Option for DateRange bar added
****************************************************************************************/
set nocount on
declare @dt datetime
declare @StartDT datetime
declare @EndDT datetime
declare @SDT datetime
declare @EDT datetime
declare @MaxEDT datetime
declare @MinRuntimeInSec int
declare @SendMail int
declare @ReturnRecocordset int
declare @Emailprofilename varchar(50)
declare @EmailRecipients varchar(50)
declare @CategoryCount int
declare @CategoryJobCount float
declare @Teller int
declare @WidthInPixels int
declare @HeightInPixels int
declare @RowHeightInPixels int
declare @AddDateRangeBar int
--***************************************************************************************
-- Set variables
--***************************************************************************************
set @Teller = 1
set @StartDT = getdate() - 31
set @EndDT = getdate()
set @MinRuntimeInSec = 1 --Ignore jobs with runtime smaller then this
set @WidthInPixels = 1280 -- The width of the graph
set @RowHeightInPixels = 40 -- Base for graph height calculation
set @AddDateRangeBar = 1 -- Add the daterange bar for reference
set @ReturnRecocordset = 0
set @SendMail = 1
set @Emailprofilename = 'Ordina'--'<MailProfileName>'
set @EmailRecipients = 'theo.ekelmans@ordina.nl'--'<email>'
--***************************************************************************************
-- Pre-run cleanup (just in case)
--***************************************************************************************
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..##GoogleGraph') IS NOT NULL DROP TABLE ##GoogleGraph;
IF OBJECT_ID('tempdb..##GoogleDiv') IS NOT NULL DROP TABLE ##GoogleDiv;
IF OBJECT_ID('tempdb..##GoogleDraw') IS NOT NULL DROP TABLE ##GoogleDraw;
IF OBJECT_ID('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates;
--***************************************************************************************
-- Create the tables for HTML assembly
--***************************************************************************************
create table ##GoogleGraph ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](8000) NULL)
create table ##GoogleDiv ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](8000) NULL)
create table ##GoogleDraw ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](8000) NULL)
create table #Dates ([StartDT] datetime NOT NULL,
[EndDT] datetime NOT NULL)
--***************************************************************************************
-- Break the range into days
--***************************************************************************************
;WITH w(DT) AS
(SELECTcast(cast(@StartDT as int) as datetime)
UNION ALL
SELECTdateadd(day, 1 ,w.DT)
FROMw
WHEREw.DT < @EndDT - 1
)
INSERT INTO #Dates
SELECTDT
,dateadd(day, 1 ,DT)
FROMw
OPTION (MAXRECURSION 1000);
--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************
selectjob.name as JobName
,cat.name as CatName
,CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) as SDT
,dateadd(s,
((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 ,
CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120)
) as EDT
into#JobRuntime
FROMmsdb.dbo.sysjobs job
left JOIN msdb.dbo.sysjobhistory his
ON his.job_id = job.job_id
INNER JOIN msdb.dbo.syscategories cat
ON job.category_id = cat.category_id
whereCONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) between @StartDT and @EndDT
andstep_id = 0 -- step_id = 0 is the entire job, step_id > 0 is actual step number
and((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > @MinRuntimeInSec -- Ignore trivial runtimes
and job.name in('Ordina_Backup_Full','Ordina_Backup_Diff','Ordina_Backup_Log')
order by SDT
if not exists (select 1 from #JobRuntime)
goto NothingToDo
--***************************************************************************************
-- 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" src="https://www.google.com/jsapi?autoload={''modules'':[{''name'':''visualization'', ''version'':''1'',''packages'':[''timeline'']}]}"></script>
<script type="text/javascript">'
insert into ##GoogleGraph (HTML)
select 'google.setOnLoadCallback(drawChart);
function drawChart() {'
-- Loop all dates
DECLARE cDates CURSOR READ_ONLY FOR
select StartDT, EndDT
from #Dates
where [StartDT] >= (select min(SDT) from [#JobRuntime]) --Just in case there is no job run in the date range
and [EndDT] <= (select max(EDT) from [#JobRuntime]) --Just in case there is no job run in the date range
order by StartDT
OPEN cDates
FETCH NEXT FROM cDates INTO @SDT, @EDT
WHILE (@@fetch_status = 0)
BEGIN
insert into ##GoogleGraph (HTML)
select '//************************'+convert(varchar(10), @SDT, 120)+'******************************
var container'+cast(@Teller as varchar(10))+' = document.getElementById(''JobTimeline'+cast(@Teller as varchar(10))+''');
var chart'+cast(@Teller as varchar(10))+' = new google.visualization.Timeline(container'+cast(@Teller as varchar(10))+');
var dataTable'+cast(@Teller as varchar(10))+' = new google.visualization.DataTable();'
insert into ##GoogleGraph (HTML)
select 'dataTable'+cast(@Teller as varchar(10))+'.addColumn({ type: ''string'', id: ''Position'' });
dataTable'+cast(@Teller as varchar(10))+'.addColumn({ type: ''string'', id: ''Name'' });
dataTable'+cast(@Teller as varchar(10))+'.addColumn({ type: ''date'', id: ''Start'' });
dataTable'+cast(@Teller as varchar(10))+'.addColumn({ type: ''date'', id: ''End'' });
dataTable'+cast(@Teller as varchar(10))+'.addRows([
'
if @AddDateRangeBar = 1 -- Add the daterange bar for reference
begin
--***************************************************************************************
-- Insert placeholders for begin and end of day
--***************************************************************************************
-- De max value of a day is either the MaxDT of the longest job if it sticks out over the day boundry
set @MaxEDT = (SELECT max(EDT)
from #JobRuntime
where SDT between @SDT and @EDT
)
-- Or if the the longest job is within the day boundry than it the end of the day
if @MaxEDT < @EDT
set @MaxEDT = @EDT
insert into ##GoogleGraph (HTML)
SELECT '[ '
+'''' + 'DateRange' + ''', '
+'''' + ' ' + ''', '
+'new Date('
+ cast(DATEPART(year , @SDT) as varchar(4))
+', '+cast(DATEPART(month, @SDT) -1 as varchar(4)) --Java months count from 0
+', '+cast(DATEPART(day, @SDT) as varchar(4))
+', '+cast(DATEPART(hour, @SDT) as varchar(4))
+', '+cast(DATEPART(minute, @SDT) as varchar(4))
+', '+cast(DATEPART(second, @SDT) as varchar(4))
+'), '
+'new Date('
+ cast(DATEPART(year, @MaxEDT) as varchar(4))
+', '+cast(DATEPART(month, @MaxEDT) -1 as varchar(4)) --Java months count from 0
+', '+cast(DATEPART(day, @MaxEDT) as varchar(4))
+', '+cast(DATEPART(hour, @MaxEDT) as varchar(4))
+', '+cast(DATEPART(minute, @MaxEDT) as varchar(4))
+', '+cast(DATEPART(second, @MaxEDT) as varchar(4))
+ ') ],' --+ char(10)
end
--***************************************************************************************
-- Format for google graph - Data
--***************************************************************************************
insert into ##GoogleGraph (HTML)
SELECT '[ '
+'''' + CatName + ''', '
+'''' + JobName + ''', '
+'new Date('
+ cast(DATEPART(year , SDT) as varchar(4))
+', '+cast(DATEPART(month, SDT) -1 as varchar(4)) --Java months count from 0
+', '+cast(DATEPART(day, SDT) as varchar(4))
+', '+cast(DATEPART(hour, SDT) as varchar(4))
+', '+cast(DATEPART(minute, SDT) as varchar(4))
+', '+cast(DATEPART(second, SDT) as varchar(4))
+'), '
+'new Date('
+ cast(DATEPART(year, EDT) as varchar(4))
+', '+cast(DATEPART(month, EDT) -1 as varchar(4)) --Java months count from 0
+', '+cast(DATEPART(day, EDT) as varchar(4))
+', '+cast(DATEPART(hour, EDT) as varchar(4))
+', '+cast(DATEPART(minute, EDT) as varchar(4))
+', '+cast(DATEPART(second, EDT) as varchar(4))
+ ') ],' --+ char(10)
from#JobRuntime
whereSDT between @SDT and @EDT
order byCatName, JobName
select@CategoryCount = count(distinct CatName)
from#JobRuntime
whereSDT between @SDT and @EDT
select@CategoryJobCount = avg(cJobname * 1.0)
from(
-- Count JobNames per CatName
select CatName, count(JobName) as cJobname
from(
-- Get Cat CatName and JobName
selectdistinct CatName, JobName
from#JobRuntime
whereSDT between @SDT and @EDT
) as i
group by CatName
) as o
set @HeightInPixels = @RowHeightInPixels * (((@CategoryCount * 1.0) * @CategoryJobCount) + 2)
insert into ##GoogleGraph (HTML)
select ']);
var options'+cast(@Teller as varchar(10))+' =
{
timeline: {
groupByRowLabel: true,
colorByRowLabel: false,
singleColor: false,
rowLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },
barLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },
},
"height": '+cast(@HeightInPixels as varchar(10))+',
"width": '+cast(@WidthInPixels as varchar(10))+'
};
'
insert into ##GoogleDiv (HTML)
SELECT ' <hr>from '+datename(dw, @SDT)+' '+convert(varchar(20), @SDT, 120) +' until '+datename(dw, @EDT)+' '+convert(varchar(20), @EDT, 120)+'<hr> <div id="JobTimeline'+cast(@Teller as varchar(10))+'"></div>'
insert into ##GoogleDraw (HTML)
SELECT ' chart'+cast(@Teller as varchar(10))+'.draw(dataTable'+cast(@Teller as varchar(10))+', options'+cast(@Teller as varchar(10))+');'
set @Teller = @Teller + 1
FETCH NEXT FROM cDates INTO @SDT, @EDT
END
CLOSE cDates
DEALLOCATE cDates
--***************************************************************************************
-- Format for google graph - Footer
--***************************************************************************************
-- The DRAWs
insert into ##GoogleGraph (HTML)
SELECT HTML FROM ##GoogleDraw
-- Last part of the script + start of the body
insert into ##GoogleGraph (HTML)
select '
}
</script>
</head>
<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>'
-- The DIVs
insert into ##GoogleGraph (HTML)
SELECT HTML FROM ##GoogleDiv
-- And last but not least the ending tags
insert into ##GoogleGraph (HTML)
select '</body>
</html>'
--***************************************************************************************
-- Output HTML page - copy output & paste to a .HTML file and open with google chrome
--***************************************************************************************
if @ReturnRecocordset = 1
select HTML from ##GoogleGraph order by ID
--***************************************************************************************
-- Send Email -
--***************************************************************************************
if @SendMail = 1
execute msdb.dbo.sp_send_dbmail
@profile_name = @Emailprofilename
,@recipients = @EmailRecipients
,@subject = 'JobTimeline'
,@body = 'See attachment for JobTimeline, open with Google Chrome!'
,@body_format = 'HTML' -- or TEXT
,@importance = 'Normal' --Low Normal High
,@sensitivity = 'Normal' --Normal Personal Private Confidential
,@execute_query_database = 'master'
,@query_result_header = 0
,@query = 'set nocount on; SELECT HTML FROM ##GoogleGraph'
,@query_result_no_padding = 1 -- prevent SQL adding padding spaces in the result
--,@query_no_truncate= 1 -- mutually exclusive with @query_result_no_padding
,@attach_query_result_as_file = 1
,@query_attachment_filename= 'JobTimeline.HTML'
goto Cleanup
--***************************************************************************************
-- Just in case....
--***************************************************************************************
NothingToDo:
print 'No job runtime info found....'
--***************************************************************************************
-- Cleanup
--***************************************************************************************
Cleanup:
IF OBJECT_ID('tempdb..#JobRuntime') IS NOT NULL DROP TABLE #JobRuntime;
IF OBJECT_ID('tempdb..##GoogleGraph') IS NOT NULL DROP TABLE ##GoogleGraph;
IF OBJECT_ID('tempdb..##GoogleDiv') IS NOT NULL DROP TABLE ##GoogleDiv;
IF OBJECT_ID('tempdb..##GoogleDraw') IS NOT NULL DROP TABLE ##GoogleDraw;
IF OBJECT_ID('tempdb..#Dates') IS NOT NULL DROP TABLE #Dates;
Viewing 15 posts - 31 through 45 (of 98 total)
You must be logged in to reply to this topic. Login to reply