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.

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.


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

1. 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.       

2. 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

Line graph implementation is also really easy and powerful

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.


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

Comments

Post a Comment

Popular posts from this blog

SQL Awesomeness: Finding a QUALIFY query clause in the depths of the database ocean

-
Image
After so many years of writing SQL queries, I cannot believe I never came across a QUALIFY query clause. It feels like discovering a treasure in the depths of the database ocean. Even ChatGPT was not aware of it! QUALIFY query clause was invented by Teradata and is not a part of SQL Standart but apparently, multiple database vendors are supporting it, for instance, Snowflake and Databricks. QUALIFY clause solves the challenge when applying a window function as a query filter. SQL Standart and most database vendors will not allow to run of a window function as a part of a WHERE clause because window functions get evaluated after the HAVING clause. We typically create a CTE or subquery adding a window function to be able to filter on a it at a later query stage. Here is an example of how you can use QUALIFY to piece-of-cake filter on the window function.  I will use the Snowflake database, as an example. Let us consider that we have a list of queries and we need to find the last query ex
Read more

Look back and realize how far you came

-
Image
It appeared out of nowhere while I was having a strong argument with the ton of dust that had taken over my computer room. My former favourite book, "Advanced Transact-SQL for SQL server 2000" brought an upset look to my face, the one I usually get from our NetApp admin. He usually gives me such a look when I ask him for more disk space. At the time of SQL 2000 there were far less good SQL books available than today and amazing SQL programming tricks were living on Itzik’s book pages; and, similar to the Rubeus Hagrid’s “The Monster Book of Monsters” it attacked anyone who attempted to open it, unleashing wild and exciting brilliant ideas. Nowadays there are a lot of SQL books but not all of them are worth reading. Some of them should be strongly avoided and if you touch them you should immediately wash your hands and have a drink or three. A few month ago I was asked to technically review some new SQL Server book. Reading their missleading claims of how the optim
Read more

The Greatest Reasons to use or not to use a Centralized Data Access Architecture

-
Image
When developing a modern Data Platform Layer, one of the main decisions is whether to opt for centralized or decentralized data access architecture.  There is no “one-fits-all” solution, both have advantages and disadvantages. A Centralized Data Access Architecture would usually mean duplicating data from the operational layer into the analytical layer and applying various transformations to data to support and speed up data analytics. Operational online transaction processing Layer , where all microservices and their operational databases are located. Analytical Data Layer , where we would have data lakes that support data Scientists' work and a data warehouse, that supports Business Intelligence. Transformations, ETL or ELT data pipelines , which are moving data from the operational layer into the analytical layer. I f we opt for a Centralized Data Access architecture, what would be the benefits and the drawbacks? CONSISTENCY : consolidating data into a central location can
Read more