Visualize the timeline of your SQL jobs using Google graph and email
-
Probably take this line out of the script 🙂
line 131
and job.name in('Ordina_Backup_Full','Ordina_Backup_Diff','Ordina_Backup_Log')--------------------
Colt 45 - the original point and click interface
-
I stand corrected 🙂
Thx for the heads-up, and yes that line should have been commented out, since it is intended to be an option.
/***************************************************************************************
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 = '<MailProfileName>'
set @EmailRecipients = '<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
( 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
--***************************************************************************************
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 @StartDT and @EndDT
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 > @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
where SDT between @SDT and @EDT
order by CatName, 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: {
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;
-
Great stuff!
-
It would be interesting to check if the same can be done with PowerBI Desktop.
-
That's looks really neat and I definitely will give it a shot!
-
Promising. I'll have a look at it.
Could it be that there is a size limit?
-
This looks really cool. Does it work with Azure? I can't test at work but definitely will when i get home.
-
Hi Jo.
I have seen that really large data sets dont get rendered right. Probably some limit in google visualisation?
-
It works if you have mail set up.... no other requirements 🙂
-
Mail is disabled on most all the hosts I hit. But, I was able to write some procs that generate/print html to the output tab. Then I just invoke a macro to grab the output and display it as a webpage (using Quick Macros). I can't wait to implement some of the fancier google graphs!
-
Ow?
How did you pull that one off?
-
Theo Ekelmans (10/30/2016)
Ow?How did you pull that one off?
Actually I already had the macro written for working with html samples I find. It just does four simple steps:
1. Select all (ctrl+a) Optionally include this line for selected snippets or for the full output pane.
2. Copy to clipboard (ctrl+c)
3. Save clipboard to temp file
4. Open temp file in browser
SSMS allows saving output to a file but it is bothersome to specify a file name and manually load it into a browser. I use quickmacros for hundreds of macros so it easily pays for itself in time saved.
An additional problem that every DBA should already have handled is a PrintMax function that can print output line by line to avoid truncation in the messages pane.
I don't know why Microsoft builds in limitations such as 255 chars of data output displayed. It can be raised to 8192 per data item for text output. This "per data item" query text options setting is not a problem if you have your own PrintMax function and control output.
Hope that answers your question.
-
Check... thx for the clarification 🙂
-
I am just starting out with this script, but would there be an easy way to have this run on a centralized server but ping multiple servers?
-
Sure,
Assuming you have a central server, just add linked servers, and add the servername to the queries in the script. :)0
Viewing 15 posts - 46 through 60 (of 96 total)
You must be logged in to reply to this topic. Login to reply