SQL Analytics

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

The analytics editor provides full featured SQL support and is powered by DuckDB—an in-process database management system designed for efficient analytical query processing.

For example, you could create an SQL query to:

Calculate the average time difference between when a ticket is created and resolved using SQL, then visualize the Mean Time To Resolution (MTTR).
Query AWS CloudWatch data to find the 95th percentile response time of a microservice, and display it as a Service Level Indicator (SLI).
Combine entry points from different sources, such as Azure Cost data and Azure Monitor data, to analyze the potential cost savings of turning off unused virtual machines.

The beta version of SQL Analytics previously used AlaSQL. While you can still copy the tiles created using the beta version, copying an AlaSQL query may not always be possible due to compatibility reasons.

Switching to the analytics editor

In the tile editor, click 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.

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

If you exit out of the SQL Analytics mode, reverting to using the default tile editor for a tile, then you lose your SQL queries and datasets, as only the first dataset is saved.

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.

Button	Description
Add dataset	Click to create an additional dataset. The Data Stream tab displays and you can progress through data stream configuration flow as you would in the default tile editor view. For each new dataset you add, a new tab displays with a sequential name (dataset2, dataset3 etc.) and can be configured independently as required.
Hover over a dataset > select more > Clone	Click to create a new dataset from the properties of the one you selected. The new dataset takes the name of the original dataset, suffixed with "Copy" (for example, ticketsCopy).
Hover over a dataset > select more > Delete	Click to permanently remove a dataset.

Creating SQL queries

This uses standard Structured Query Language (SQL) and is and is powered by DuckDB.

By default, the Query tab in the SQL section of the SQL Analytics Editor contains a query that returns everything from the first dataset. Edit this query to return and manipulate your data as needed.

Select * FROM tickets

If you perform a Count aggregation on the Shaping tab for a dataset and therefore have a column named "count", you must wrap the column name in `` to reference it in SQL: `count`.

Query editor buttons

Use the following buttons to interact with the Enter your query window.

Button	Description
Execute	Click to run the query and display the result in the SQL Output section at the bottom of the page.
More > Force refresh	Click to to re-run the query and re-request all data stream data to ensure that it's up to date.
Done	Click to save the query and close the window.
Dock	Click to expand the Enter your query window to full size to maximize editing space. This also adds your datasets tabs next to the SQL output tab at the bottom of the page.
Close	Click to close the Enter your query window and discard your changes.

IntelliSense

The SQL query editor comes packed with IntelliSense, designed to streamline query creation. Each keystroke triggers the assistant and and provides:

Context-aware auto-complete: The editor suggests relevant SQL syntax, table names, and column fields based on your current input, reducing the chance of errors and speeding up query writing.
Real-time error detection: Common mistakes such as syntax errors or missing keywords are flagged as you type, helping you correct issues early.

Querying multiple datasets

A major advantage of SQL analytics mode is that you can query data from more than one dataset at a time, and even combine datasets by using a JOIN, etc.

For example, the following SQL query compares the number of new and solved tickets per day by combining data from two different datasets.

WITH created AS (
                 SELECT `count` AS NewTickets,
                 created_at_byDay AS TDay
                 FROM dataset1
                 ORDER BY TDay),

     solved AS (
                 SELECT `count` AS SolvedTickets,
                 solved_at_byDay AS TDay
                 FROM dataset2
                 ORDER BY TDay)

SELECT TDay, 
       COALESCE(NewTickets, 0) AS Created, 
       COALESCE(SolvedTickets,0) AS Solved
FROM (
      SELECT * from created
      OUTER JOIN solved on solved.TDay = created.TDay
     )
ORDER BY TDay

This builds a daily comparison of ticket creation and resolution, which you can can then visualize as separate trend lines on a line graph:

Columns

The Columns section of the SQL tab functions exactly the same same as the Columns tab in the default tile editor, and is used to to format the columns of your datasets.

SquaredUp automatically defines the metadata retrieved from data streams so the data is assigned the correct data type, however in some circumstances you may want to override this.

For example, when retrieving data using the Web API plugin, scripting, or custom query data streams (such as Splunk Enterprise plugin), the assigned data type may not be quite correct or as you expect.

Formatting columns

Use the following options to format your columns.

Option	Description
Name	To rename a column, click the current Name value and enter a new one. Columns that can be renamed display the Rename column icon when hovered over.
Type	Select an option from the Type dropdown to change the data type of the column. If any additional options are available to configure, the dropdown is expanded below.
Value	Displays the original value of the column.
Formatted	Displays the formatted value of the column.
Add a copy of this column	Click to duplicate a column. The copied field displays `Copy of [field name]` above its Name.
Remove this column	Click to delete a cloned column.

Comparison columns

Comparison columns are used to compare two values, for example you may want to compare the number of tickets raised this month to the number of tickets raised last month. You can choose to show the value as an absolute change (for example, 12 more tickets) or as a percentage change (for example, a 28% increase).

When a column has a Type of Number, the Add comparison

button displays at the end of the row, which you can click to open the Add comparison window.

From this window, if you have multiple columns with a Type of Number, you can create a comparison column by doing the following:

Column A:
Select the first column to compare against. Automatically populated with the column of which you clicked Add comparison
.
Column B:
Select the second column to compare against.
Output:
Select how to display the comparison value. This value is displayed in the Preview field. Choose from:
- Absolute: Show the numerical value of Column A - Column B.
- Percentage: Show the ratio of Column B to Column A as a percent.
Click Add to create the comparison.

Additional options

Some data types have advanced settings that can be configured in the options section, which is displayed whenever you change the data Type or by clicking expand

next to the column Name.

Option

Description

Output Format

Enter a custom format to display date values as a string. Any specified output format is supported. For example, dd/mm/yy, dd/mm/yyyy or d/M/Y.

By default, dates and times are displayed in your local timezone to ensure the data makes sense to you.

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.

Any input format is supported, however if you have a custom input format that is missing any time zone information, the input is always assumed to be UTC.

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

You can map the states you're getting back from your data to the states SquaredUp expects.

SquaredUp expects the following values for states to be able to show Status Blocks in the correct matching color:

`success`	green
`warning`	yellow
`error`	red
`unknown`	gray

If your data uses different values for states, you can map them to the values SquaredUp expects.

Tip: Any state value that SquaredUp doesn't recognize gets automatically set to unknown. You can usually just leave out the unknown state from your mapping and just specify the other three states.

Visualization

Visualization settings are configured on the Visualization tab of the right-hand panel in the tile editor.

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 in your dataset.

For detailed information on configuring visualizations, see Visualization Settings.

Was this article helpful?

Have more questions or facing an issue?

Submit a ticket