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.
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:
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.
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
CREATE 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 ASDate),
Change = ObjectName +' (by '+LoginName+')',
NumOfChanges =COUNT(1)
INTO #FinalData
FROM TestAuditTable
GROUPBYCAST(ChangeDate ASDate),ObjectName,LoginName;
-- Get distinct dats for the X axis
SELECT @AxisLabels = @AxisLabels +LEFT(DATENAME(m,ChangeDate),3)+' '+CONVERT(varchar(5),DAY(ChangeDate))+'|'
FROM #FinalData
GROUPBY ChangeDate
ORDERBY 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 (ORDERBY Change)-1 ASVARCHAR(10))+',-1,10|',
@ChartSeries = @ChartSeries +(SELECTCAST(ISNULL(NumOfChanges,0)ASvarchar(50))+','
FROM #FinalData f4
RIGHTJOIN(SELECTDISTINCT ChangeDate,f2.Change
FROM #FinalData
OUTERAPPLY(SELECTDISTINCT Change FROM #FinalData ) f2
) f3
ON f3.ChangeDate = f4.ChangeDate and f3.Change = f4.Change
WHERE f3.Change = f1.Change
ORDERBY f3.ChangeDate
FORXMLPATH ('')
)+'|'
FROM #FinalData f1
GROUPBY Change
ORDERBY 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 =
'
cht=bvg&
chs=660x250&
chdlp=t&
chco='+@ChartColors+'&
chd=t:'+@ChartSeries+'&
chds='+CAST(@Min asvarchar)+','+CAST(@Max asvarchar)+'&
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
CREATETABLE dbo.TestAuditTable(
ID int NOT NULL DEFAULT NEXT VALUE FOR dbo.GetNextNumber PRIMARY KEY,
ChangeDate datetime NULL,
ObjectName sysname,
LoginNamesysname);
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
Comments
Post a Comment