SQL Analytics (Beta)

SQL Analytics allows you to carry out advanced manipulation of data using a SQL query before you visualize it.

For example:

  • You might ask 'What is the average time difference between a ticket created and resolved?' You can use SQL to calculate the difference between the ticket created and resolved time, then you can visualize the Mean Time To Resolution (MTTR).

  • For AWS CloudWatch data you might want to know 'What is the 95% percentile response time of my microservice?' use SQL to query the data, and then show the Service Level Indicator (SLI).

  • You can also combine entry points and query them before visualizing the data. You might combine your Azure Cost data with your Azure Monitor data and ask 'What is the potential cost saving of turning off my unused VMs?'

Switch to the Analytics Editor

When editing a tile, click on Enable SQL Analytics at the top of the screen. This changes your view from the simple tile editor into the more advanced SQL Analytics Editor. It's best to decide whether you want to stick with the SQL Analytics Editor for a particular tile, or with the simple editor, rather than switching between the two.

If you exit out of the SQL Analytics Editor, choosing to revert to using the simple editor for this tile, then you lose your SQL queries and datasets, as only the first dataset is saved.

As with the simple editor, you can still add Monitors or KPIs.

Datasets

A dataset is the data stream and selected objects combined. If you had already selected a data stream and objects in the simple tile editor, this will be shown as dataset1 and you will be taken straight to the SQL tab.

Datasets can be renamed by hovering over the tab of the dataset and clicking the pencil icon – hit Enter to save the change, hit Escape to cancel. For example, if you are looking at Zendesk tickets you might rename dataset1 to tickets.

The new name must be alphanumeric, with no spaces or punctuation. At the point of renaming, if the SQL query is unmodified, the name of the dataset is automatically updated in the SQL query.

You can add new datasets, allowing you to add in further data. For a new dataset, click + Add dataset and then configure the data stream and objects to show the data you want. A new tab appears with a sequential name – dataset2, dataset3, etc.

Each dataset can be configured to use a different timeframe, if required, or use the dashboard timeframe.

SQL Query

This uses standard Structured Query Language (SQL).

The SQL tab of the SQL Analytics Editor will show everything from the first dataset, as a simple query:

Select * FROM tickets

You can edit this query to be something more complex. For example, to find the average difference between two different times you can use DATEDIFF and display this it as 'MTTR':

SELECT AVG(DATEDIFF(HOUR, created_at, updated_at)) AS MTTR FROM tickets

You can also use SQL to query data from more than one dataset, or combine data with a JOIN, etc.

Click Execute to view the SQL Output at the bottom of the screen.

When you are happy with the tile configuration, click Save.

Visualization

Select the visualization for your tile. Which visualizations are offered to you depends on the data available, for example Line Graph will only be offered if there is time series data.

If you exit out of the SQL Analytics Editor, choosing to revert to using the simple editor for this tile, then you lose your SQL queries and datasets, as only the first dataset is saved.


Have more questions or facing an issue?