August 2, 2023

How to format and customize data using SQL Analytics

Monitoring my car with SquaredUp (Part 2)

Dave Clarke

Product Manager, SquaredUp

This is part 2 of a three-part series where I showcase just how easy it is to build dashboards in SquaredUp using our WebAPI integration and our recently re-designed tile editing experience, centered around building a dashboard for my car. In my last post I shared how I configured our Web API data source and built my first tile.

If you missed part 1, check it out here.

In this post we’ll take a look at what our SQL Analytics feature is and the value it offers for two use cases:

Converting and formatting values
Combining datasets

So, what is SQL Analytics?

In SquaredUp, we allow you to query data from over 80+ out of the box data sources, as well as data from any Web API as we saw in the last post. Sometimes that data can be returned in a different format than you need or perhaps with different units (bytes vs GB, or C° vs F°). SquaredUp offers a few options for shaping and manipulating your data in the ‘Shaping’ and ‘Data Mapping’ areas of the tile editor but occasionally more flexibility is required - that’s where SQL Analytics comes in.

Using the power of standard SQL (Structured Query Language) you can manipulate and transform data as needed. For example:

You might want to calculate Mean Time To Resolution (MTTR) for service desk tickets. Using SQL you can calculate the average difference between when the ticket was open and when it was closed.
Perhaps you want to calculate a specific percentile for a given metric, e.g. calculating the 95th percentile response time for a lambda.
Or you can combine data from different sources. For example you might combine Azure Cost data with metrics from Azure Monitor to assess the cost savings for turning off underutilized VMs.

SQL query converting kilometers to miles

Converting and formatting values

Let’s take a look at an example. As you saw in the first part of this series we created a tile showing our fuel level, represented as a number of kilometres till the tank is empty. The data comes back from the Ford API as kilometres but I’d prefer to show that as miles.

In the tile editor enable SQL Analytics using the toggle.
Enter the SQL query, e.g. SELECT FLOOR(`fuel.distanceToEmpty`*0.621371) as fuel FROM dataset1
- This query converts kilometers to miles as well as rounding the value down to the nearest integer.
- Note: To reference columns from the table that contain periods or other characters, surround the column name in backticks.
Click Execute to run the query, the output will be shown below the query.
Depending on your configuration you might need to tweak some of the visualization mapping & configuration options too.
- e.g. We previously had the Range set to 0→500, but we’ll tweak that to 0→300 now we’re showing miles.

Combining datasets

As well as manipulating data, you can also use SQL to combine multiple datasets. This is particularly useful to combine data from two different sources. In doing so we can explore data in different ways and garner new insights. For example:

Using JOIN to merge two datasets from two different data sources, e.g. cost and performance data, or bringing in tags from a CMDB.
Using UNION to merge two dataset from two different sources, e.g. multiple metrics into a single chart.

Learn more

What's next?

This blog forms part of a three-part series where I showcase just how easy it is to build dashboards in SquaredUp using our Web API integration and our recently re-designed tile editing experience, centered around building a dashboard for my car.

In the next part we’ll take a look at an advanced feature of SquaredUp: scripting. Using scripts we can really transform and customize data to show on our dashboards.