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

Alert visualization recipe: Get out your blender, drop in some sp_send_dbmail, Google Charts API, add your favorite colors and sprinkle with html. Blend till it’s smooth and looks pretty enough to taste.

 

I really like database monitoring. My email inbox have a constant flow of different types of alerts coming from our production servers with all kinds of information, sometimes more useful and sometimes less useful. Usually database alerts look really simple, it’s usually a plain text email saying “Prod1 Database data file on Server X is 80% used. You’d better grow it manually before some query triggers the AutoGrowth process”.

Imagine you could have received email like the one below.  In addition to the alert description it could have also included the the database file growth chart over the past 6 months. Wouldn’t it give you much more information whether the data growth is natural or extreme? That’s truly what data visualization is for.

Believe it or not, I have sent the graph below from SQL Server stored procedure without buying any additional data monitoring/visualization tool.

image

 

Would you like to visualize your database alerts like I do? Then like myself, you’d love the Google Charts.

All you need to know is a little HTML and have a mail profile configured on your SQL Server instance regardless of the SQL Server version.

First of all, I hope you know that the sp_send_dbmail procedure has a great parameter @body_format = ‘HTML’, which allows us to send rich and colorful messages instead of boring black and white ones. All that we need is to dynamically create HTML code.

This is how, for instance, you can create a table and populate it with some data:

DECLARE @html varchar(max)

SET @html = '<html>'
+ '<H3><font id="Text" style="color: Green;">Top Databases: </H3>'
+ '<table border="1" bordercolor="#3300FF" style="background-color:#DDF8CC" width="70%" cellpadding=3 cellspacing=3>'
+ '<tr><font color="Green"><th>Database Name</th><th>Size</th><th>Physical Name</th></tr>' +

CAST( (SELECT TOP 10
                            td = name,'',
                            td = size * 8/1024 ,'',
                            td = physical_name 
            FROM sys.master_files  
            ORDER BY size DESC
            FOR XML PATH ('tr'),TYPE
) AS VARCHAR(MAX))
+
'</table>'

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'PutYourEmailHere',
@subject ='Top databases',
@body = @html,
@body_format = 'HTML'

This is the result:

image

 

If you want to add more visualization effects, you can use Google Charts Tools https://google-developers.appspot.com/chart/interactive/docs/index which is a free and rich library of data visualization charts, they’re also easy to populate and embed.

There are two versions of the Google Charts

image Image based charts: https://google-developers.appspot.com/chart/image/docs/gallery/chart_gall

This is an old version, it’s officially deprecated although it will be up for a next few years or so. I really enjoy using this one because it can be viewed within the email body. For mobile devices you need to change the “Load remote images” property in your email application configuration.        

 

image Charts based on JavaScript classes: https://google-developers.appspot.com/chart/interactive/docs/gallery

This API is newer, with rich and highly interactive charts, and it’s much more easier to understand and configure. The only downside of it is that they cannot be viewed within the email body. Outlook, Gmail and many other email clients, as part of their security policy, do not run any JavaScript that’s placed within the email body. However, you can still enjoy this API by sending the report as an email attachment.

Here is an example of the old version of Google Charts API, sending the same top databases report as in the previous example but instead of a simple table, this script is using a pie chart right from  the T-SQL code

DECLARE @html  varchar(8000)

DECLARE @Series  varchar(800),@Labels  varchar(8000),@Legend  varchar(8000);
   
SET @Series = '';
SET @Labels = '';
SET @Legend = '';

SELECT TOP 5 @Series = @Series + CAST(size * 8/1024 as varchar) + ',',
                        @Labels = @Labels +CAST(size * 8/1024 as varchar) + 'MB'+'|',
                        @Legend = @Legend + name + '|'
FROM sys.master_files
ORDER BY size DESC

SELECT @Series = SUBSTRING(@Series,1,LEN(@Series)-1),
        @Labels = SUBSTRING(@Labels,1,LEN(@Labels)-1),
        @Legend = SUBSTRING(@Legend,1,LEN(@Legend)-1)

SET @html =
  '<H3><font color="Green"> '+@@ServerName+' top 5 databases : </H3>'+
   '<br>'+
   '<img src="
http://chart.apis.google.com/chart?'+
   'chf=bg,s,DDF8CC&'+
   'cht=p&'+
   'chs=400x200&'+
   'chco=3072F3|7777CC|FF9900|FF0000|4A8C26&'+
   'chd=t:'+@Series+'&'+
   'chl='+@Labels+'&'+
   'chma=0,0,0,0&'+
   'chdl='+@Legend+'&'+
   'chdlp=b"'+
   'alt="'+@@ServerName+' top 5 databases" />'
            
EXEC msdb.dbo.sp_send_dbmail @recipients = 'PutYourEmailHere',
                            @subject = 'Top databases',
                            @body = @html,
                            @body_format = 'HTML'

This is what you get. Isn’t it great?

image

Chart parameters reference:

chf     Gradient fill  bg - backgroud ; s- solid
cht     chart type  ( p - pie)
chs        chart size width/height
chco    series colors
chd        chart data string        1,2,3,2
chl        pir chart labels        a|b|c|d
chma    chart margins
chdl    chart legend            a|b|c|d
chdlp    chart legend text        b - bottom of chart

 

Line graph implementation is also really easy and powerful

DECLARE @html varchar(max)
DECLARE @Series varchar(max)
DECLARE @HourList varchar(max)

SET @Series = '';
SET @HourList = '';

SELECT @HourList = @HourList + SUBSTRING(CONVERT(varchar(13),last_execution_time,121), 12,2)  + '|' ,
             @Series = @Series + CAST( COUNT(1) as varchar) + ','
FROM sys.dm_exec_query_stats s 
   CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
WHERE last_execution_time > = getdate()-1
GROUP BY CONVERT(varchar(13),last_execution_time,121)
ORDER BY CONVERT(varchar(13),last_execution_time,121)

SET @Series = SUBSTRING(@Series,1,LEN(@Series)-1)

SET @html =
'<img src="http://chart.apis.google.com/chart?'+
'chco=CA3D05,87CEEB&'+
'chd=t:'+@Series+'&'+
'chds=1,350&'+
'chdl= Proc executions from cache&'+
'chf=bg,s,1F1D1D|c,lg,0,363433,1.0,2E2B2A,0.0&'+
'chg=25.0,25.0,3,2&'+
'chls=3|3&'+
'chm=d,CA3D05,0,-1,12,0|d,FFFFFF,0,-1,8,0|d,87CEEB,1,-1,12,0|d,FFFFFF,1,-1,8,0&'+
'chs=600x450&'+
'cht=lc&'+
'chts=FFFFFF,14&'+
'chtt=Executions for from'
+(SELECT CONVERT(varchar(16),min(last_execution_time),121)
         FROM sys.dm_exec_query_stats
         WHERE last_execution_time > = getdate()-1)
+' till '+
+(SELECT CONVERT(varchar(16),max(last_execution_time),121)
    FROM sys.dm_exec_query_stats)
+
'&'+
'chxp=1,50.0|4,50.0&'+
'chxs=0,FFFFFF,12,0|1,FFFFFF,12,0|2,FFFFFF,12,0|3,FFFFFF,12,0|4,FFFFFF,14,0&'+
'chxt=y,y,x,x,x&'+
'chxl=0:|1|350|1:|N|2:|'+@HourList+'3:|Hour&'+
'chma=55,120,0,0" alt="" />'

EXEC msdb.dbo.sp_send_dbmail
@recipients = 'PutYourEmailHere',
@subject ='Daily number of executions',
@body = @html,
@body_format = 'HTML'


image

Chart parameters reference:

chco    series colors
chd        series data
chds    scale format
chdl    chart legend
chf        background fills
chg        grid line
chls    line style
chm        line fill
chs        chart size
cht        chart type
chts    chart style
chtt    chart title
chxp    axis label positions
chxs    axis label styles
chxt    axis tick mark styles
chxl    axis labels
chma    chart margins

If you don’t mind to get your charts as an email attachment, you can enjoy the Java based Google Charts which are even easier to configure, and have much more advanced graphics. In the example below, the sp_send_email procedure uses the parameter @query which will be executed at the time that sp_send_dbemail is executed and the HTML result of this execution will be attached to the email.

DECLARE @html varchar(max),@query varchar(max)
DECLARE @SeriesDBusers  varchar(800);
   
SET @SeriesDBusers = '';

SELECT @SeriesDBusers = @SeriesDBusers +  ' ["'+DB_NAME(r.database_id) +'", ' +cast(count(1) as varchar)+'],'
FROM sys.dm_exec_requests r
GROUP BY DB_NAME(database_id)
ORDER BY count(1) desc;

SET @SeriesDBusers = SUBSTRING(@SeriesDBusers,1,LEN(@SeriesDBusers)-1)

SET @query = '
PRINT ''
<html>
  <head>
    <script type="text/javascript" src="
https://www.google.com/jsapi"></script>
    <script type="text/javascript">
      google.load("visualization", "1", {packages:["corechart"]});
       google.setOnLoadCallback(drawChart);
      function drawChart() {
                     var data = google.visualization.arrayToDataTable([
                       ["Database Name", "Active users"],
                       '+@SeriesDBusers+'
                     ]);
 
                     var options = {
                       title: "Active users",
                       pieSliceText: "value"
                     };
 
                     var chart = new google.visualization.PieChart(document.getElementById("chart_div"));
                     chart.draw(data, options);
      };
    </script>
  </head>
  <body>
    <table>
    <tr><td>
        <div id="chart_div" style="width: 800px; height: 300px;"></div>
        </td></tr>
    </table>
  </body>
</html>
'''

EXEC msdb.dbo.sp_send_dbmail
   @recipients = 'PutYourEmailHere',
   @subject ='Active users',
   @body = @html,
   @body_format = 'HTML',
   @query = @Query
   @attach_query_result_as_file = 1, 
   @query_attachment_filename = 'Results.htm'

After opening the email attachment in the browser you are getting this kind of report:


image

In fact, the above is not only for database alerts. It can be used for applicative reports if you need high levels of customization that you cannot achieve using standard methods like SSRS.

If you need more information on how to customize the charts, you can try the following:

Use the above examples as a starting point for your procedures and I’d be more than happy to hear of your implementations of the above techniques.

Yours,

Maria

Published Wednesday, June 26, 2013 6:08 PM by Maria Zakourdaev

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

 

Mike Lawell said:

Very cool, thanks Maria

June 26, 2013 11:06 AM
 

spaghettidba said:

Absolutely awesome! Thanks for sharing.

June 26, 2013 11:50 AM
 

Lazydba247 said:

Thanks for sharing!

June 27, 2013 4:04 AM
 

Argenis Fernandez said:

Thanks for this tip! Awesome idea.

June 27, 2013 1:12 PM
 

Maria Zakourdaev said:

Mike, Spaghettidba, Lazydba247, Argenis, many thanks for your feedback. I'm really happy that you liked the idea!!  

June 27, 2013 3:12 PM
 

Kevin Lan said:

very cool, good point

June 27, 2013 11:42 PM
 

Kevin Kline said:

Very good blog post, Maria. Glad to see you've started writing such top-quality content!  -Kev

June 28, 2013 9:35 AM
 

Maria Zakourdaev said:

Thank you, Kevin!

June 28, 2013 10:19 AM
 

Neeraj said:

Nice.. Going to use this in my alert. Thanks Maria !!

July 23, 2013 2:29 PM
 

Maria Zakourdaev said:

Excellent, Neeraj, I'm glad that you are going to use it!

July 24, 2013 5:19 AM
 

Tadeusz said:

That's simply brilliant!

August 26, 2013 9:33 AM
 

Stephane said:

exactly what I was trying to archieve !

thank you Maria.

September 3, 2013 2:43 AM
 

Maria Zakourdaev said:

Tadeusz and Stephane: huge thanks for leaving such a positive feedback! I'm glad that you will be using this technique.

September 3, 2013 3:40 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.
Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement