October 30, 2020

Add context to your dashboards from SQL data sources

New SQL tile: Line graph

Vincent Babin

Senior Product Manager, SquaredUp

Monitoring information that matters to you will often come from disparate sources – whether you are a server engineer, a SQL database administrator, or an application owner wanting a 360 view of your applications’ health.

For example, you may want to visualise your server metrics from SCOM alongside historical trends from the SCOM Data Warehouse. Or you may want to show your applications’ SLA metrics alongside its user experience from New Relic and the order revenue generated from your internal ecommerce database.

Whatever your data sources, being able to bring all these metrics into a unified dashboard provides valuable context, which in turn helps you identify and correlate problems more quicky, resulting in improved reaction time and decision making.

To add said context, we typically need a dashboard administrator to bring in data from various sources via a Web API, a SQL database, or through native integrations.

SquaredUp can do all of that for you. Thanks to our powerful Web API, SQL and native integrations, you can easily create visualizations from Azure Log Analytics, SCOM Data Warehouse, ServiceNow, Azure DevOps, Dynatrace, your internal BI tool, and infinitely more.

In this blog post, we are going to zoom in on one of our most highly requested tools – version 4.8's SQL line graph. Let’s see how it helps provide valuable context!

How does the new SQL line graph help me add context to my dashboards?

Version 4.8 of SquaredUp for SCOM and Azure contains several features highly requested by our customers, including the new SQL line graph.

In addition to the existing SQL grid and SQL scalar visualizations, the new SQL line graph visualization allows you to graph a time series or any time-based data, well suited for detailed performance information as well as trends, average and more metrics over any period.

For example, you may want to track your server’s patching overtime, the number of events on a given resource overtime, or more business-oriented metrics such as the number of orders. You may even want to pull in a time series from your favourite monitoring tool that supports SQL (e.g. Solarwinds).

You can do all that! Our SQL visualizations can integrate to any SQL database including the SCOM Data Warehouse. Thanks to the easy-to-use mustache picker, you can also parameterise values in your SQL queries that will change dynamically based on the object you are looking at.

Let’s take a look at the new line graph’s capabilities in action.

The feature in action

Example: Add context to an application dashboard

In this example our Order Processing application is a typical e-commerce portal that takes orders in various geographies across several product lines. The dashboard below provides our applications team and stakeholders with meaningful context, so they can quickly understand how the application is performing:

Total orders and revenue this month. This is using our SQL scalar tiles
From an availability point-of-view, the uptime SLA and Health State give the availability of our application in the last 30 days. This is using the Enterprise Applications feature of SquaredUp for SCOM
The graphs at the bottom display the order and revenue trend in the last 2 years, grouped by month. This is using our new SQL line graph in version 4.8.

Let’s take a closer look at the two-line graphs above. To achieve the desired visualization, we are providing three pieces of information:

A connection string to the SQL database. This is a standard syntax, usually in the following format: Data Source=XYZ;Initial Catalog=XYZ;Trusted_Connection=False;User Id=XYZ;Password=XYZ
An SQL query, which returns at least a datetime field and a value. In our example, the simple SQL query returns four fields: model, quantity, amount and a date.
Set the X axis, Y axis and value to draw the line. This is achieved using the “column overrides” options. By default, the timestamp selector is set to the datetime field returned from the query (used on the X axis). The first value returned is assigned to the value selector is set to our quantity field (used on the Y axis). By ticking “show all”, it is possible to display more than one value. In our example, we also set the grouping field to be the product model so we can show one order line per product model.

For more information on how to configure the SQL tiles, check out this article.

Example: Add context to a server perspective (SCOM Data Warehouse)

A perspective in SquaredUp is a type of dashboard that you can create once and re-use across similar objects dynamically. For example, a Windows server performance dashboard that is created once and gives you the same visualizations across any of your Windows servers. The example below is the perfect example to illustrate this.

This is a standard perspective dashboard in SquaredUp. What’s new is that, thanks to the new SQL line graph, we have added some insightful metrics:

CPU Analysis shows the CPU’s max value and trend over the last 30 days
Events / Day shows the number of events per day on this server over the last 30 days.

These two tiles give us more information as to how this server has been performing over a longer period of time. In this example, the CPU trend is going down which may warrant a downsizing and save cost.

These two SQL line graphs are making full use of the direct access to the Data Warehouse using SquaredUp for SCOM. Let’s take a look at the configuration.

The connection string to the Data Warehouse is easy from a SquaredUp for SCOM environment. Simply use global:dw as the connection string.

The SQL query is somewhat more complex that the previous example we looked at on our application dashboard. In this example, we need to pull enough data from various tables, calculate the trend and max value and group the results by day. Ultimately, the query returns a date field, trend and max value. Using the column override options, we are able to display both the trend and max value on one graph by selecting the “show all” option.

Lastly, for the CPU analysis graph to display dynamically on this perspective dashboard when we navigate to another Windows server, we use the mustache picker that’s built-in the query window to parameterise the server name. Path = {displayName}

The sample SQL query above, along with other sample queries are available when you upgrade to version 4.8 of SquaredUp for SCOM. Select Teams > Monitoring > Sample DW SQL Queries from the navigation bar.

In summary, the SQL tiles are a great way to graph your SQL data from any SQL databases into one dashboarding experience in SquaredUp for a unified experience. You can create more meaningful dashboards that mix business metrics, application metrics and custom performance metrics. Ultimately, to let you build and share more relevant dashboards for yourself, and your stakeholders in your organisation (CxO, applications team, product team and more)

H2: How do I get started?

All the SQL tiles are available in both SquaredUp for SCOM and Squared for Azure.

If you’re an existing SquaredUp user and want to start using the new SQL line graph, you simply need to upgrade to version 4.8 (see SCOM / Azure upgrade documentation for help). Head over to SquaredUp downloads to get version 4.8.

Some sample SQL queries in this blog article are available when you upgrade to version 4.8 of SquaredUp for SCOM, select Teams > Monitoring > Sample DW SQL Queries from the navigation bar.

For more information, check this article: How to use the SQL tile.

Thank you for reading this blog post. Stay tuned for more blogs on SquaredUp version 4.8.