May 3, 2017 at 12:39 pm
Hi!
Thanks for this good looking sql job timeline!
I have a couple questions:
- How get sql job steps visible to same chart? Example if I click some job then opens that job steps or something other solution.
- And if there a job which have a trigger and that launch new job, how get those to same row?
Br,
Mikko S.
May 3, 2017 at 1:08 pm
Hmmm... interesting idea Mikko,
Aa drilldown version of the graph....
Unfortunalely google graph does not support a drilldown, but i could write a other version of this script, that would use the label column for the jobname, and show the jobsteps on the timeline.
Would that be usefull for you?
About the "trigger" you mentioned, nope... there is no logical group that hold that in the MSDB execution history.
grtz,
Theo
May 3, 2017 at 1:56 pm
Yes, okey.. I have also that idea, showing job name in side and then jobsteps show in timeline but I wasn't yet tried that.
But that would be what I need, just easy readable job tracking chart.
Br,
Mikko S.
May 3, 2017 at 2:04 pm
Hi Mikko,
If you replace the query under "Create the Job Runtime information table" by the query below, you will get a plot of step runtimes per job
SELECT h.step_name as JobName
,j.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
FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j
ON j.job_id = h.job_id
where CONVERT(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
and ((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > @MinRuntimeInSec -- Ignore trivial runtimes
and h.step_id > 0
ORDER BY j.name, h.run_date, h.run_time, step_id
grtz,
Theo.
May 3, 2017 at 3:49 pm
Okay...
And here is version 4.0, which has a new option: the capability to show Category/Job or Job/Step and the option to show a single timeline or one timeline per day.
Downloadlink: http://www.ekelmans.com/FTP/Web/GoogleJobTimeline.sql
enjoy,
Theo.
/***************************************************************************************
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
Version: 2.3
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2016-05-20
Change: DateRange fixed to begin and end of day
Version: 3.0
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2017-05-03
Change: Added an option to show either show Category/Job or Job/Step
Solved a few bugs that limited the number of days this report can span
Note: 14 days will take about 40 seconds to render !!
Version: 4.0
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2017-05-12
Change: Added an option to show either one timeline, or one timeline per day
****************************************************************************************/
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 @HeightInPixels int
declare @WidthInPixels int
declare @RowHeightInPixels int
declare @AddDateRangeBar int
declare @Show varchar(50)
declare @ShowDays varchar(50)
declare @sql nvarchar(4000)
--***************************************************************************************
-- Set variables
--***************************************************************************************
set @Teller = 1
set @StartDT = getdate() - 5
set @EndDT = getdate()
set @MinRuntimeInSec = 5 --Ignore jobs with runtime smaller then this
set @WidthInPixels = 2560 -- The width of the graph per day (increase this value to show more detail)
set @RowHeightInPixels = 35 -- Base for graph height calculation
set @AddDateRangeBar = 1 -- Add the daterange bar for reference (ignored for single timeline)
set @ReturnRecocordset = 0
set @SendMail = 1
set @Emailprofilename = '<MailProfileName>'
set @EmailRecipients = '<email>'
--Choose one of the 2 show days options below
set @ShowDays = 'OneTimeline'
--set @ShowDays = 'MultiDayTimeline'
--Choose one of the 2 show options below
set @Show = 'Category/Job'
--set @Show = 'Job/Step'
--***************************************************************************************
-- 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](max) NULL)
create table ##GoogleDiv ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](max) NULL)
create table ##GoogleDraw ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](max) NULL)
create table #Dates ([StartDT] datetime NOT NULL,
[EndDT] datetime NOT NULL)
--***************************************************************************************
-- Break the range into days
--***************************************************************************************
;WITH w(DT) AS
( SELECT cast(cast(@StartDT as int) as datetime)
UNION ALL
SELECT dateadd(day, 1 ,w.DT)
FROM w
WHERE w.DT < @EndDT - 1
)
INSERT INTO #Dates
SELECT DT
,dateadd(day, 1 ,DT)
FROM w
OPTION (MAXRECURSION 1000);
--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************
if @Show = 'Category/Job'
set @sql = '
select job.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
FROM msdb.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
where CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) between '''+convert(nvarchar(20), @StartDT, 120)+''' and '''+convert(nvarchar(20), @EndDT, 120)+'''
and step_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 > '+cast(@MinRuntimeInSec as nvarchar(50))+' -- Ignore trivial runtimes
--and job.name in(''Ordina_Backup_Full'',''Ordina_Backup_Diff'',''Ordina_Backup_Log'')
ORDER BY SDT
'
if @Show = 'Job/Step'
set @sql = '
SELECT h.step_name as JobName
,j.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
FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j
ON j.job_id = h.job_id
where CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) between '''+convert(nvarchar(52), @StartDT, 120)+''' and '''+convert(nvarchar(20), @EndDT, 120)+'''
and ((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > '+cast(@MinRuntimeInSec as nvarchar(50))+' -- Ignore trivial runtimes
and h.step_id > 0
ORDER BY j.name, h.run_date, h.run_time, step_id
'
exec (@sql)
if not exists (select 1 from ##JobRuntime)
goto NothingToDo
update ##JobRuntime
set JobName = replace(JobName, '''', '`')
,CatName = replace(CatName, '''', '`')
--***************************************************************************************
-- 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() {'
--***************************************************************************************
-- One timeline over all
--***************************************************************************************
if @ShowDays = 'OneTimeline'
begin
-- Calculate new width
set @WidthInPixels = @WidthInPixels * datediff(day, @StartDT, @EndDT)
insert into ##GoogleGraph (HTML)
select '
var container = document.getElementById(''JobTimeline'');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();'
insert into ##GoogleGraph (HTML)
select ' 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([
'
--***************************************************************************************
-- Format for google graph - Data
--***************************************************************************************
insert into ##GoogleGraph (HTML)
SELECT ' [ '
+'''' + CatName + ''', '
+'''' + replace(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
where SDT between @StartDT and @EndDT
order by CatName, SDT, JobName
select @CategoryCount = count(distinct CatName)
from ##JobRuntime
where SDT between @StartDT and @EndDT
select @CategoryJobCount = avg(cJobname * 1.0)
from (
-- Count JobNames per CatName
select CatName, count(JobName) as cJobname
from (
-- Get Cat CatName and JobName
select distinct CatName, JobName
from ##JobRuntime
where SDT between @StartDT and @EndDT
) as i
group by CatName
) as o
set @HeightInPixels = @RowHeightInPixels * (((@CategoryCount * 1.0) * @CategoryJobCount) + 2)
insert into ##GoogleGraph (HTML)
select ' ]);
var options =
{
timeline: { '
if @Show = 'Category/Job'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: true,'
if @Show = 'Job/Step'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: false,'
insert into ##GoogleGraph (HTML)
select ' colorByRowLabel: false,
singleColor: false,
rowLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },
barLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },'
insert into ##GoogleGraph (HTML)
select '
},
"height": '+cast(@HeightInPixels as varchar(10))+',
"width": '+cast(@WidthInPixels as varchar(10))+'
};
'
insert into ##GoogleDiv (HTML)
SELECT ' <div id="JobTimeline"></div>'
insert into ##GoogleDraw (HTML)
SELECT ' chart.draw(dataTable, options);'
end
--***************************************************************************************
-- One timeline per day
--***************************************************************************************
if @ShowDays = 'MultiDayTimeline'
begin
-- 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 + ''', '
+'''' + replace(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
where SDT between @SDT and @EDT
order by CatName, SDT, JobName
select @CategoryCount = count(distinct CatName)
from ##JobRuntime
where SDT 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
select distinct CatName, JobName
from ##JobRuntime
where SDT 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: { '
if @Show = 'Category/Job'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: true,'
if @Show = 'Job/Step'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: false,'
insert into ##GoogleGraph (HTML)
select ' colorByRowLabel: false,
singleColor: false,
rowLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },
barLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },'
insert into ##GoogleGraph (HTML)
select '
},
"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
end
--***************************************************************************************
-- 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 order by ID'
,@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;
February 13, 2018 at 3:43 am
Hi Theo
This looks very interesting, however I am hitting a conversion error in the dynamic SQL...
SELECT h.step_name as JobName
,j.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
FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j
ON j.job_id = h.job_id
where CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) between '2018-01-30 09:41:37' and '2018-02-13 09:41:37'
and ((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > 0 -- Ignore trivial runtimes
and h.step_id > 0
ORDER BY j.name, h.run_date, h.run_time, step_id
Msg 242, Level 16, State 3, Line 2
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
This is occurring on the Where clausewhere CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + ' ' + STUFF(STUFF(RIGHT('000000' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, ':'), 3, 0, ':'), 120) between '2018-01-30 09:41:37' and '2018-02-13 09:41:37'
I had to change this line towhere CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) between CONVERT(DATETIME,'''+convert(nvarchar(20), @StartDT, 120)+''',121) and CONVERT(DATETIME,'''+convert(nvarchar(20), @EndDT, 120)+''',121)
to encapsulate the where criteria in a 121 date format. I am in the UK, and believe this is the same problem as seen here https://dba.stackexchange.com/questions/86313/conversion-of-a-varchar-data-type-to-a-datetime-data-type-resulted-in-an-out-of
February 14, 2018 at 2:45 am
Hi Paul,
I hate localization issues like this, and i was hoping by using ISO8601 and ODBC canonical formatting would work in most cases, but alas... i'm proved wrong by an island dweller 🙂
I will give your code a try using the US en NL formats i'm used to, and post an update to the script.
Thanks for the feedback
Theo
January 28, 2019 at 10:00 am
Excellent job Theo. However, when I execute 1st version I get a nice html page but executing version 4 gives only the range of dates with no details as follows:
Job timeline on: SQL2K12 (Node: SQL2K12) from 2019-01-25 16:38:54 until 2019-01-28 16:38:54
from Saturday 2019-01-26 00:00:00 until Sunday 2019-01-27 00:00:00
from Sunday 2019-01-27 00:00:00 until Monday 2019-01-28 00:00:00
I check by running "Select * from ##JobRuntime", I have a lot of records as follows:
IndexOptimize - USER_DATABASES IndexOptimize - USER_DATABASES 2019-01-28 15:20:57.000 2019-01-28 15:27:07.000
Purge MessageBox_UpdateStats_BizTalkMsgBoxDb 2019-01-28 15:30:00.000 2019-01-28 15:30:01.000
Purge MessageBox_UpdateStats_BizTalkMsgBoxDb 2019-01-28 15:45:00.000 2019-01-28 15:45:01.000
nd so on
any idea? thanks
January 29, 2019 at 1:08 am
Ow?
Strange, V4 works in most cases. can you mail me the script you have (to rule out script issues) the records in "Select * from ##JobRuntime", and the HTML output file to theo@ekelmans.com, and i will have a look for you 🙂
grtz, Theo 🙂
January 29, 2019 at 6:14 am
Done Theo, I sent an email with 4 docs
May 1, 2019 at 6:16 pm
I am trying to get this to work with Registered Servers, so I can query multiple at a time. When I run it I get a similar error as Eliasaal. The HTML shows data in it, but it won't render correctly in any browser. I will see the results for 1 server, and the rest show up like this.
Job timeline on: SERVER101 (Node: SERVER101) from 2019-04-26 13:09:26 until 2019-05-01 13:09:26
Job timeline on: SERVER102 (Node: SERVER102) from 2019-04-26 13:09:26 until 2019-05-01 13:09:26
Job timeline on: SERVER107 (Node: SERVER107) from 2019-04-26 13:09:26 until 2019-05-01 13:09:26
Job timeline on: SERVER105 (Node: SERVER105) from 2019-04-26 13:09:26 until 2019-05-01 13:09:26
Job timeline on: SERVER06 (Node: SERVER106) from 2019-04-26 13:09:26 until 2019-05-01 13:09:26
May 1, 2019 at 7:02 pm
Hi eliassal / elirueda,
I think i have found it, try downloading version 4.1 : http://www.ekelmans.com/FTP/Web/GoogleJobTimeline.sql
grtz, Theo
May 1, 2019 at 8:27 pm
I downloaded the file, but the comments say its 3.1, not 4.1. is the link you gave correct?
May 2, 2019 at 7:26 am
Hi eliruda,
Yes i am sure i uploaded 4.1 to my server, try ctrl-F5 because you are probably seeing the cached version.
gr, t 🙂
May 2, 2019 at 7:28 am
But just in case.... here is the code as well
/***************************************************************************************
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
Version: 2.3
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2016-05-20
Change: DateRange fixed to begin and end of day
Version: 3.0
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2017-05-03
Change: Added an option to show either show Category/Job or Job/Step
Solved a few bugs that limited the number of days this report can span
Note: 14 days will take about 40 seconds to render !!
Version: 3.1
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2019-02-01
Change: Fixed a minor NULL bug for a French Admin (Thanks for the help Salam :)
"height": '+cast(isnull(@HeightInPixels, 250) as varchar(10))+',
"width": '+cast(isnull(@WidthInPixels, 1800) as varchar(10))+'
Version: 4.0
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2017-05-12
Change: Added an option to show either one timeline, or one timeline per day
Version: 4.1
Author: Theo Ekelmans
Email: theo@ekelmans.com
Date: 2019-05-01
Change: split a few strings into smaller ones
****************************************************************************************/
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 @HeightInPixels int
declare @WidthInPixels int
declare @RowHeightInPixels int
declare @AddDateRangeBar int
declare @Show varchar(50)
declare @ShowDays varchar(50)
declare @sql nvarchar(4000)
--***************************************************************************************
-- Set variables
--***************************************************************************************
set @Teller = 1
set @StartDT = getdate() - 5
set @EndDT = getdate()
set @MinRuntimeInSec = 5 --Ignore jobs with runtime smaller then this
set @WidthInPixels = 2560 -- The width of the graph per day (increase this value to show more detail)
set @RowHeightInPixels = 35 -- Base for graph height calculation
set @AddDateRangeBar = 1 -- Add the daterange bar for reference (ignored for single timeline)
set @ReturnRecocordset = 0
set @SendMail = 1
set @Emailprofilename = '<MailProfileName>'
set @EmailRecipients = '<email>'
--Choose one of the 2 show days options below
--set @ShowDays = 'OneTimeline'
set @ShowDays = 'MultiDayTimeline'
--Choose one of the 2 show options below
set @Show = 'Category/Job'
--set @Show = 'Job/Step'
--***************************************************************************************
-- 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](max) NULL)
create table ##GoogleDiv ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](max) NULL)
create table ##GoogleDraw ([ID] [int] IDENTITY(1,1) NOT NULL,
[HTML] [varchar](max) NULL)
create table #Dates ([StartDT] datetime NOT NULL,
[EndDT] datetime NOT NULL)
--***************************************************************************************
-- Break the range into days
--***************************************************************************************
;WITH w(DT) AS
( SELECT cast(cast(@StartDT as int) as datetime)
UNION ALL
SELECT dateadd(day, 1 ,w.DT)
FROM w
WHERE w.DT < @EndDT - 1
)
INSERT INTO #Dates
SELECT DT
,dateadd(day, 1 ,DT)
FROM w
OPTION (MAXRECURSION 1000);
--***************************************************************************************
-- Create the Job Runtime information table
--***************************************************************************************
if @Show = 'Category/Job'
set @sql = '
select job.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
FROM msdb.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
where CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) between '''+convert(nvarchar(20), @StartDT, 120)+''' and '''+convert(nvarchar(20), @EndDT, 120)+'''
and step_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 > '+cast(@MinRuntimeInSec as nvarchar(50))+' -- Ignore trivial runtimes
--and job.name in(''Ordina_Backup_Full'',''Ordina_Backup_Diff'',''Ordina_Backup_Log'')
ORDER BY SDT
'
if @Show = 'Job/Step'
set @sql = '
SELECT h.step_name as JobName
,j.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
FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j
ON j.job_id = h.job_id
where CONVERT(DATETIME, CONVERT(CHAR(8), run_date, 112) + '' '' + STUFF(STUFF(RIGHT(''000000'' + CONVERT(VARCHAR(8), run_time), 6), 5, 0, '':''), 3, 0, '':''), 120) between '''+convert(nvarchar(52), @StartDT, 120)+''' and '''+convert(nvarchar(20), @EndDT, 120)+'''
and ((run_duration/10000)%100 * 3600) + ((run_duration/100)%100 * 60) + run_duration%100 > '+cast(@MinRuntimeInSec as nvarchar(50))+' -- Ignore trivial runtimes
and h.step_id > 0
ORDER BY j.name, h.run_date, h.run_time, step_id
'
exec (@sql)
if not exists (select 1 from ##JobRuntime)
goto NothingToDo
update ##JobRuntime
set JobName = replace(JobName, '''', '`')
,CatName = replace(CatName, '''', '`')
--***************************************************************************************
-- 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() {'
--***************************************************************************************
-- One timeline over all
--***************************************************************************************
if @ShowDays = 'OneTimeline'
begin
-- Calculate new width
set @WidthInPixels = @WidthInPixels * datediff(day, @StartDT, @EndDT)
insert into ##GoogleGraph (HTML)
select '
var container = document.getElementById(''JobTimeline'');
var chart = new google.visualization.Timeline(container);
var dataTable = new google.visualization.DataTable();'
insert into ##GoogleGraph (HTML)
select ' dataTable.addColumn({ type: ''string'', id: ''Position'' });
dataTable.addColumn({ type: ''string'', id: ''Name'' });
'
insert into ##GoogleGraph (HTML)
select ' dataTable.addColumn({ type: ''date'', id: ''Start'' });
dataTable.addColumn({ type: ''date'', id: ''End'' });
dataTable.addRows([
'
--***************************************************************************************
-- Format for google graph - Data
--***************************************************************************************
insert into ##GoogleGraph (HTML)
SELECT ' [ '
+'''' + CatName + ''', '
+'''' + replace(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
where SDT between @StartDT and @EndDT
order by CatName, SDT, JobName
select @CategoryCount = count(distinct CatName)
from ##JobRuntime
where SDT between @StartDT and @EndDT
select @CategoryJobCount = avg(cJobname * 1.0)
from (
-- Count JobNames per CatName
select CatName, count(JobName) as cJobname
from (
-- Get Cat CatName and JobName
select distinct CatName, JobName
from ##JobRuntime
where SDT between @StartDT and @EndDT
) as i
group by CatName
) as o
set @HeightInPixels = @RowHeightInPixels * (((@CategoryCount * 1.0) * @CategoryJobCount) + 2)
insert into ##GoogleGraph (HTML)
select ' ]);
var options =
{
timeline: { '
if @Show = 'Category/Job'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: true,'
if @Show = 'Job/Step'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: false,'
insert into ##GoogleGraph (HTML)
select ' colorByRowLabel: false,
singleColor: false,
rowLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },
barLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },'
insert into ##GoogleGraph (HTML)
select '
},
"height": '+cast(isnull(@HeightInPixels, 250) as varchar(10))+',
"width": '+cast(isnull(@WidthInPixels, 1800) as varchar(10))+'
};
'
insert into ##GoogleDiv (HTML)
SELECT ' '
insert into ##GoogleDraw (HTML)
SELECT ' chart.draw(dataTable, options);'
end
--***************************************************************************************
-- One timeline per day
--***************************************************************************************
if @ShowDays = 'MultiDayTimeline'
begin
-- 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))+''');'
insert into ##GoogleGraph (HTML)
select '
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'' });'
insert into ##GoogleGraph (HTML)
select '
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 + ''', '
+'''' + replace(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
where SDT between @SDT and @EDT
order by CatName, SDT, JobName
select @CategoryCount = count(distinct CatName)
from ##JobRuntime
where SDT 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
select distinct CatName, JobName
from ##JobRuntime
where SDT 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: { '
if @Show = 'Category/Job'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: true,'
if @Show = 'Job/Step'
insert into ##GoogleGraph (HTML)
select ' groupByRowLabel: false,'
insert into ##GoogleGraph (HTML)
select ' colorByRowLabel: false,
singleColor: false,
rowLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },
barLabelStyle: {fontName: ''Helvetica'', fontSize: 14 },'
insert into ##GoogleGraph (HTML)
select '
},
"height": '+cast(isnull(@HeightInPixels, 250) as varchar(10))+',
"width": '+cast(isnull(@WidthInPixels, 1800) 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> '
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
end
--***************************************************************************************
-- 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 order by ID'
,@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 - 61 through 75 (of 97 total)
You must be logged in to reply to this topic. Login to reply