THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Maria Zakourdaev

Using Google Charts API to Visualize Schema Changes

 

Last week I have worked on the new email report using Google Charts and liked it so much that decided to share it here with anyone who finds it useful.

I have a Schema Changes Audit table which is being maintained by the DDL Trigger. The relevant record is added to this table every time anyone changes objects on the server .

image              image

If you are not familiar with the Google Charts, you can read my old post about it and how it works here. It is easy to use, very customizable and FREE. The above visualization is using Google Bar Chart.

To keep things short, I am using Transact SQL to build an HTML Image tag. The above email body contains this HTML:

-------------------------------------------------------------------------

<img src="http://chart.apis.google.com/chart?
cht=bvg&
chs=660x250&
chco=CF9036,90062D,67E13B,82088D,319CBA&
chd=t:0,1|1,3|3,2|18,5|4,8&
chds=0,19&
chxt=x&
chxs=0,ff0000,12,0,lt&
chxl=0:|Sep%202|Sep%203&
chm=N,000000,0,-1,10|N,000000,1,-1,10|N,000000,2,-1,10|N,000000,3,-1,10|N,000000,4,-1,10&
chdl=SendBarReport%20(by%20Maria)|TestAuditTable%20(by%20Diana)|TestAUditTable%20(by%20Maria)|TestAUditTable%20(by%20Roy)|V1%20(by%20Diana)
" />

In order to test the HTML chart in usual Browser, you need take the source of the IMG tag and remove the line breaks.

http://chart.apis.google.com/chart?cht=bvg&chs=660x250&chco=CF9036,90062D,67E13B,82088D,319CBA&chd=t:0,1|1,3|3,2|18,5|4,8&chds=0,19&chxt=x&chxs=0,ff0000,12,0,lt&chxl=0:|Sep%202|Sep%203&chm=N,000000,0,-1,10|N,000000,1,-1,10|N,000000,2,-1,10|N,000000,3,-1,10|N,000000,4,-1,10&chdl=SendBarReport%20(by%20Maria)|TestAuditTable%20(by%20Diana)|TestAUditTable%20(by%20Maria)|TestAUditTable%20(by%20Roy)|V1%20(by%20Diana)

------------------------------------------------------------------------

Here is a script that produces the HTML code and sends out an email. Take into consideration that, if you have high variety of objects that are changing – render this report for 1 day only. Otherwise the chart will be too big.

 

Create Stored Procedure that builds the HTML and sends it

 

In order to make sure that your Server can send emails, you will need to enable mailing functionality. Here you can find a nice article on how to configure mailing profile.

/*

 

EXEC SendBarReport 'yourmailgoeshere', 1,1

*/

 

ALTER PROCEDURE [dbo].[SendBarReport]

                                  @MailRecipient  varchar(256),

                                  @PrintMode  bit = 1,

                                  @MailMode bit = 1

AS

BEGIN

 

DECLARE @Legend varchar(max);

DECLARE @ChartSeries varchar(max) = '';

DECLARE @ChartColors varchar(max)= '';

DECLARE @AxisLabels varchar(max)= '';

DECLARE @ChartLegend varchar(max)= '';

DECLARE @DataValueMarkers varchar(max)= '';

DECLARE @Min int, @Max int;

DECLARE @html varchar(max)='';

 

-- Prepare the data for the chart

SELECT  ChangeDate   = CAST(ChangeDate AS Date),

              Change = ObjectName +' (by '+LoginName+')' ,

              NumOfChanges = COUNT(1)

INTO #FinalData

FROM TestAuditTable

GROUP BY CAST(ChangeDate AS Date),ObjectName,LoginName;

 

-- Get distinct dats for the X axis

SELECT @AxisLabels = @AxisLabels + LEFT(DATENAME(m,ChangeDate),3) +' '+ CONVERT(varchar(5),DAY(ChangeDate)) + '|'

FROM #FinalData

GROUP BY ChangeDate

ORDER BY ChangeDate

 

-- Get MIN and MAX values to scale the chart correctly

SELECT        @Min = MIN(NumOfChanges) -1,

              @Max = MAX(NumOfChanges) +1

FROM #FinalData;

 

-- Prepare Chart legend, dynamically generate colors, define data value markers and chart series

-- Chart series must be in the following format |series1_val1,series1_val2,series1_val3|series2_val1,series2_val2,series2_val3| … where each part between | | belongs to separate value on X axis

SELECT  @ChartLegend = @ChartLegend + Change + '|',

           @ChartColors = @ChartColors + CONVERT(varchar(6),LEFT(newid(),6)) + ',',

           @DataValueMarkers = @DataValueMarkers + 'N,000000,'+CAST(ROW_NUMBER() OVER ( ORDER BY Change) -1 AS VARCHAR(10)) +',-1,10|',

           @ChartSeries = @ChartSeries + ( SELECT CAST(ISNULL(NumOfChanges,0) AS varchar(50)) +','

                                           FROM #FinalData f4

                                           RIGHT JOIN (SELECT DISTINCT ChangeDate,f2.Change

                                                       FROM #FinalData

                                                       OUTER APPLY (SELECT DISTINCT Change FROM #FinalData ) f2

                                                       ) f3

                                                ON f3.ChangeDate = f4.ChangeDate and f3.Change = f4.Change

                                           WHERE f3.Change = f1.Change

                                           ORDER BY f3.ChangeDate

                                           FOR XML PATH ('')

                                   ) + '|'

FROM #FinalData f1

GROUP BY Change

ORDER BY Change;

 

-- Remove last character “|” or “,” from dynamically created strings

set @ChartSeries = SUBSTRING(@ChartSeries,1,LEN(@ChartSeries)-2);

set @ChartSeries = REPLACE(@ChartSeries,',|','|');

set @ChartColors = SUBSTRING(@ChartColors,1,LEN(@ChartColors)-1);

set @AxisLabels = SUBSTRING(@AxisLabels,1,LEN(@AxisLabels)-1);

set @ChartLegend = SUBSTRING(@ChartLegend,1,LEN(@ChartLegend)-1);

set @DataValueMarkers = SUBSTRING(@DataValueMarkers,1,LEN(@DataValueMarkers)-1);

 

-- Prepare HTML

set @html =

 

'<img src="http://chart.apis.google.com/chart?

cht=bvg&

chs=660x250&

chdlp=t&

chco='+@ChartColors+'&

chd=t:'+@ChartSeries+'&

chds='+CAST(@Min as varchar)+','+CAST(@Max as varchar)+'&

chxt=x&

chxs=0,ff0000,12,0,lt&

chxl=0:|'+@AxisLabels+'&

chm='+@DataValueMarkers+'&

chdl='+@ChartLegend+'&

 

" />'

 

IF @PrintMode = 1 BEGIN

       PRINT @html

END

 

-- Send email using prepared HTML as an email body

IF @MailMode = 1 BEGIN

       EXEC msdb.dbo.sp_send_dbmail

       @recipients = @MailRecipient,

       @subject ='Schema Changes',

       @body = @html,

       @body_format = 'HTML'

END

 

END

Audit table script

 

CREATE SEQUENCE dbo.GetNextNumber AS int START WITH 1 INCREMENT BY 1;

 

CREATE TABLE dbo.TestAuditTable(

       ID int NOT NULL DEFAULT NEXT VALUE FOR dbo.GetNextNumber PRIMARY KEY,

       ChangeDate datetime NULL,

ObjectName sysname,

       LoginName sysname) ;

P.S.

Please, don’t say “Have you noticed that Google Charts API has been deprecated?”

They have announced the deprecation of this tool about two years ago. We can still enjoy this awesome API, Google will not make any changes to it during the next year and will announce if they intend to discontinue or make any changes to it. As soon as this sad day arrives, we will adjust ourselves to their new API https://google-developers.appspot.com/chart/interactive/docs/gallery based on the Java classes.

Yours,

Maria

Published Monday, September 8, 2014 1:52 PM by Maria Zakourdaev
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

LazyDBA247 said:

Hi,

until April 20 2015, they promise to stay backward compatible, after that they didn't say it will stop work.... but they stop to make sure they are backward compatible

:)

https://developers.google.com/chart/terms

September 8, 2014 10:27 AM

Leave a Comment

(required) 
(required) 
Submit

About Maria Zakourdaev

The shortest word in the English language that contains the letters: abcdef is… feedback! Let me know if I have touched something that is alive in the cosmos.
Privacy Statement