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?'
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.
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.
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 setting lets you choose the time span that a dashboard displays data for. For example, instead of showing data from the last 12 hours, you can choose to show data from the last 7 days.
Tiles on a dashboard inherit that dashboard's timeframe by default, meaning that the tile data shown changes whenever you change the dashboard timeframe. However, you can instead choose to set a fixed timeframe for a tile via its timeframe setting.
Fixed-timeframe tiles display their set timeframe in a bubble next to their title and the tile data does not change when the dashboard timeframe is changed.If all the tiles on a dashboard use a fixed timeframe then the timeframe dropdown is disabled. Likewise, if a specific timeframe is unsupported then it is disabled in the dropdown.
You should indicate if a tile's timeframe can be changed in the name of a tile. For example, naming a tile Performance during the last week indicated that the tile always displays data for the last week.
As well as determining the period for the data that displays, the dashboard timeframe also controls the cache refresh frequency of that data. In other words, the timeframe you pick also affects how "fresh" that data is.
Dashboard timeframe
Cache expiry
1 hour
1 minute
12 hours
5 minutes
24 hours
15 minutes
7 days
6 hours
30 days
12 hours
Up to 6 months
24 hours
Over 6 months
7 days
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.
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':
SELECTAVG(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.
Click
and then Force refresh to re-run the query and re-request any data stream data to ensure that it's up to date.
When you are happy with the tile configuration, click Save.
Use the Columns tab of the tile editor to format the columns of the table on the Data tab.The Columns tab with a formatted Data tableSquaredUp 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 Update to create the comparison. A new column is added to the Output table. The Output table with a comparison column added
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
Define the values that trigger states. Select a value for each of the corresponding dropdowns:
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.
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.
