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 Monitoring 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 clicking the tab of the dataset to enable editing – 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.

Dataset buttons

Use the following buttons to create new, copy existing and delete data sets:

Add dataset
: Click to create a new dataset, allowing you to add in further data 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.
The dashboard timeframe is the current timeframe setting for a dashboard. Users can change the dashboard timeframe to see data for a different time span, for example, instead of showing data from "the last 12 hours" it can be changed to show data from "the last 7 days".
Tiles can be configured to:
- Use dashboard timeframe (default). For these tiles the data shown will change when the user changes the dashboard timeframe.
- Use a fixed timeframe from the options available. These tiles show a clock icon and hovering shows the fixed timeframe configured. The data will not change when the dashboard timeframe is changed.
If the dashboard timeframe is unavailable, such as when all the tiles on that dashboard are using a fixed timeframe, then the button is disabled. Likewise, if a specific timeframe is unsupported then it is disabled in the timeframe picker.
Set a default dashboard timeframe
To set the default dashboard timeframe, click the pin icon
when using the dashboard timeframe picker. This sets the initial timeframe for all viewers of the dashboard, including shared dashboards.
Tip: Indicate with the name of a tile if the tile's timeframe can be changed. For example, naming a tile "Performance during the last week" tells users that this tile always shows data for the last week. Naming a tile just "Performance" indicates to users that changing the dashboard timeframe will change the data.
Clone: Accessed by hovering over a tab and selecting more
. Click to create a new dataset using the properties of an existing one. The copy is initially assigned the name of the original dataset followed by "copy". For example, ticketsCopy. You can then rename and edit the dataset as required.
Delete: Accessed by hovering over a tab and selecting more
. Click to permanently delete a dataset.

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.

Columns

The Columns tab of the editor allows you to format the columns of the Data table you retrieve from data streams.

Typically, SquaredUp automatically defines the metadata retrieved from-built-in data streams so that the received data is assigned the correct data type, enabling visualizations to be configured out of the box.

However, in some circumstances (such as when retrieving data using the Web API plugin, scripting, or using custom query data streams such as Splunk Enterprise plugin) you may want to override the data type or the data type may not be quite correct - in which case this tab allows you to manually select a different data type from the Type dropdown.

Additionally, this tab also allows you to rename and copy the data columns you receive.

Formatting

The table contains each data column received by the data stream, and is used to format the following.

Option	Description
Name	The column name. This can be overwritten by clicking the cell and entering a new value.
Type	The data type of a column. This can be overwritten by selecting a new value from the dropdown. Some data types have advanced settings that can be configured in the Options section, displayed by clicking the expand button next to the Name, see Options.
Value	Displays the original value of the column.
Formatted	Displays the formatted value of the column.
Add a copy of this column (button)	Click to duplicate the column. The copied field displays `Copy of [field name]` above its Name.
Remove this column (button)	Click to delete a copied column.

Options

Some data types have additional formatting that can be applied to a column in the Options section, displayed by clicking the expand button next to the corresponding Name.

Option	Description
Output Format	Enter a custom format to display data values as a string. For example, `dd/mm/yy`.
Input Format	Enter the format that corresponds to the inputted date. For example, if your data has values such as `05/27/24 01:44 PM`, then the Input Format should be set to `dd/mm/yy hh:mm aa`. This field is required if the data string for the column are not ISO-8601 formatted. For example, `2024-09-09T13:52:25.281Z`.
Currency	Select the currency to display the value in. This does not convert the currency value.
Decimal Places	Formats the number of decimal places for a supported data type. Enter a value between 0 and 20.
Link Text	Specify the text of the URL links in the column.
Format as duration	Toggle between displaying the time value in minutes and seconds or seconds.
Map Values to States	Define the values that trigger states. Select a value for each of the corresponding dropdowns: Success Warning Error Unknown

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.

Table Settings

Configuration

Rename	Click on the column name in the Configuration panel and type to rename it.
Sort order	Click and drag a column name to change their order.
Hide/show	Click on the Toggle visibility eye button to hide or show a column.
Row Link	Hyperlinks each row in the table to the URL in the selected column.
Resizing columns	On the table preview hover between column names and use the handle to change the width. Changes will be saved while in the tile editor or in dashboard edit mode.
Swap rows and columns	Transpose the table to show the headings as the left-hand column. Particularly useful for single row tables.

Filtering, grouping and sorting of the data can be configured in the Shaping section.

Reset - reverts to the default settings.

Scalar settings

Data Mapping

Value

Choose the column to be used. The default is auto, where the column is chosen by the visualization.

Configuration

Label	Enter a label, for example `Tickets` or `ms`.
Show formatted value	This shows a simplified value. Toggle to Off to show the raw value. (On by default).

Show formatted value

Toggle whether the scalar shows the formatted string with units, or the raw value. By default this is set to on.

Manual size

Toggle whether to manually size the visualization. When enabled, use the slider to select a size. By default this is set to off.

Reset - reverts to the default settings.

Line Graph settings

Data Mapping

X-axis	Choose which data to show on the x-axis. Auto <column> shows the column which was selected automatically for you, usually a time field for the x-axis.
Y-axis	Choose which data to show on the y-axis. Auto <column> shows the column which was selected automatically for you, usually a numerical field for the y-axis.
Series	Choose how the stack is split into segments, usually a label or a string.
Unit	Select the column that you want to use for the unit label.

Configuration

Y-axis range	Auto - graph is fitted to the data automatically Percentage - shows 0-100 Fit to data from zero - shows from 0 to the data maximum Custom - allows you to specify the min and max
Data points	This shows where the data points are on the line. Useful to identify missing points, or detail for changing data.
Shading	Adds shading below each line.
Grid lines	Adds a horizontal line to each y-axis increment, spanning the x-axis.
Show as cumulative	Displays the line graph as cumulative. For example, when showing a cumulative cost over time, based on daily data points.
Show trend line	Adds a line indicating the tendency of the data points, calculated using linear regression.
X-axis label	Allows you to override the default and enter a label, for example `Date` or `Time`.
Y-axis label	Allows you to override the default and enter a label, for example `Tickets` or `ms`.
Show legend	Toggle whether to display a colored legend for each series of data in the graph. Enabling the toggle displays the Legend position field below.
Legend position	Select where the legend is displayed in relation to the graph. This field is only visible when the Show legend toggle is enabled.

Bar Chart settings

Data Mapping

X-axis	Choose which data to show on the x-axis. Auto <column> shows the column which was selected automatically for you, usually a time field for the x-axis.
Y-axis	Choose which data to show on the y-axis. Auto <column> shows the column which was selected automatically for you, usually a numerical field for the y-axis.
Series	Select the column used to split the x-axis values into series.

Configuration

Show as groups	Toggle whether to display the Series as grouped bars. This option is only enabled when a Series is selected.
Mode	Percentage mode displays a series as a percentage of the overall bar, only available when a Series is selected.
Layout	Vertical or horizontal.
Y-axis range	Auto - graph is fitted to the data automatically Percentage - shows 0-100 Fit to data from zero - shows from 0 to the data maximum Custom - allows you to specify the min and max
X-axis label	Allows you to override the default and enter a label, for example `Date` or `Time`.
Y-axis label	Allows you to override the default and enter a label, for example `Tickets` or `ms`.
Grid lines	Select whether to include grid lines.
Annotation	Display an annotation showing the value for each series.
Show legend	Toggle whether to display a colored legend for each series of data in the graph. Enabling the toggle displays the Legend position field below.
Legend position	Select where the legend is displayed in relation to the graph. This field is only visible when the Show legend toggle is enabled.

Custom Palette

It is possible to use custom colors for this visualization by creating a custom palette. Custom palettes can be declared as either an array or an object as follows:

Array: Select the colors based on the order of the data (for example, the first series is the first color).
"palette": ["red", "#abcdef", "green"]
Object: Select colors based on the series label.
"palette": { "EC2": "red", "Lambda": "#abcdef", "CloudWatch": "blue" }

To apply a custom palette, do the following:

Click the More Options ellipsis button then select Edit JSON.

Enter the JSON for the palette you want to apply under visualisation/config/data-stream-bar-chart. For example:

"visualisation": {
    "type": "data-stream-bar-chart",
    "config": {
      "data-stream-bar-chart": {
        "horizontalLayout": "vertical",
        "grouping": false,
        "showValue": false,
        "displayMode": "actual",
        "xAxisLabel": "",
        "yAxisLabel": "",
        "range": {
          "type": "auto"
        },
        "showGrid": true,
        "showLegend": false,
        "legendPosition": "bottom",
        "palette": {
          "EC2": "red",
          "Lambda": "#abcdef",
          "CloudWatch": "blue"
        }
      }
    }
  },

Click Save to apply the changes.

Blocks settings

Data Mapping

State	Select the column that you want to use for the state color of each block. Available options will vary depending on the data stream that you have selected. By default, the State column is automatically selected.
Label	Choose the label for the block.
Sublabel	Choose the sublabel to be shown beneath the main block label.
Link	Select the column that you want to use for the link of each block. Available options will vary depending on the data stream that you have selected.

Configuration

Columns	Set the number of columns the blocks are displayed in.
Manual height	Select this to be able to resize the blocks, larger or smaller.

Reset - reverts to the default settings.

Donut settings

Data Mapping

Value	Choose the column to use for the value for each segment.
Label	Choose the label for each segment.

Configuration

Legend	Select how the donut labels are displayed. Choose from: Inline: The labels are attached to the corresponding donut segment. This is the default style. Table: The labels are displayed adjacent to the donut.
Legend Position	Select where the donut labels are displayed. This option is only visible when Table is selected from the Legend field.

Custom Palette

It is possible to use custom colors for this visualization by creating a custom palette. Custom palettes can be declared as either an array or an object as follows:

Array: Select the colors based on the order of the data (for example, the first series is the first color).
"palette": ["red", "#abcdef", "green"]
Object: Select colors based on the series label.
"palette": { "EC2": "red", "Lambda": "#abcdef", "CloudWatch": "blue" }

To apply a custom palette, do the following:

Click the More Options ellipsis button then select Edit JSON.

Enter the JSON for the palette you want to apply under visualisation/config/data-stream-donut-chart. For example:

"visualisation": {
    "type": "data-stream-donut-chart",
    "config": {
      "data-stream-bar-chart": {
        "horizontalLayout": "vertical",
        "grouping": false,
        "showValue": false,
        "displayMode": "actual",
        "xAxisLabel": "",
        "yAxisLabel": "",
        "range": {
          "type": "auto"
        },
        "showGrid": true,
        "showLegend": false,
        "legendPosition": "bottom",
        "palette": {
          "EC2": "red",
          "Lambda": "#abcdef",
          "CloudWatch": "blue"
        }
      }
    }
  },

Click Save to apply the changes.

Range	Specify the min and max of the gauge.
Label	Add a label to be shown beneath the gauge.
Monitoring	You can configure Monitoring (from the Monitoring tab) and then the colors configured for the conditions will show on the gauge. Check that the Data chosen in the Visualization (e.g. `count`) and the Value (and Column where applicable) that you are Monitoring (e.g. `count`) are as intended.

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.

Was this article helpful?

Have more questions or facing an issue?

Submit a ticket