How to use the SQL tile

About the SQL tile

The SQL tile enables you to pull data from external SQL data sources into your dashboards and perspectives.

By default, only SquaredUp administrators can create and edit SQL queries in the SQL tile.

The SQL tile will only work if the SquaredUp server is domain joined. This is because the SquaredUp application pool account must be given permission to access the SQL database (How to check and modify the application pool identity).

SQL tiles on dashboards and perspectives

There are two ways you can use the SQL tile:

• On a dashboard, with fixed SQL queries

• On a perspective, with parameterised SQL queries

For more information see How to use the SQL tile on a perspective.

This article describes how to use the SQL tile on a dashboard.

How to configure an SQL tile on a dashboard

1. Make sure you have access to the database you want to use.

   Configuring access to a database for an SQL tile

   To be able to query databases, such as the Operations Manager database, the SquaredUp application pool account needs to be given the db_datareader role in SQL Server Management Studio for the database you wish to query.  

2. Add a new tile to a dashboard and choose the SQL tile.

   

3. Choose the visualisation for your SQL tile:

   Scalar

   A single value such as a number of customers or incidents.

   Example:

   

   Grid

   A table of data, e.g. incidents.

   Example:

   

   Line Graph

   Shows data over time, in a graph with an x-axis (time) and a y-axis. You can show several objects, such as servers, in one graph.

   Example:

   

   Sparklines

   Shows data over time (like line graphs), but each item gets its own graph instead of showing all lines in one graph.

   Example:

   

   Bar Graph

   Visualises both a number and the resulting bar width based on the number value.

   Example:

   

   Donut

   Shows the results in a donut shape.

   Donuts can display a maximum of 10 items.

   Example:

   

   Status Icons

   Shows the state of items as icons with different colours. You can display the icons alone or together with a description.

   Example:

   

   

    

   Status Blocks

   Shows the state of items as blocks with different colours.

   Example:

   

   

    

4. Connection:

   Here you connect the SQL tile to the SQL server and database you want to query.

   connection string:

   Data Source=<SQLservername>;Initial Catalog=<DatabaseName>;Integrated Security=True;
   Example: Data Source=SQLServer1;Initial Catalog=CRMDB;Integrated Security=True;

   Note:

   We do not recommend including a username and password in the connection string, because this username and password will be visible to anyone with permission to view the page.

   Note:

   If the SquaredUp server and SQL server are in the same domain you can just use the SQL server name, if they are not in the same domain you should use the fully qualified domain name.

   Connection string if you want to specify an instance

   Data Source=<SQLservername>\\<InstanceName>;Initial Catalog=<DatabaseName>;Integrated Security=True;

   Example:

   Data Source=SQLServer1\\Main;Initial Catalog=CRMDB;Integrated Security=True;

   Connection string if you want to specify a port

   Data Source=<SQLservername>,<PortNumber>;Initial Catalog=<DatabaseName>;Integrated Security=True;

   Example:

   Data Source=SQLServer1,51433;Initial Catalog=CRMDB;Integrated Security=True;

    

5. Query:

   To test the query you want to use, run your query in SQL Server Management Studio, so you can see any errors and amend as necessary. Make sure that the server and database you use in SQL Server Management Studio for this test corresponds with the connection string you used in the Connection panel.

   After the test was successful, paste your query into the query string box.

   Example: A query string to show the number of alerts

   Copy

   SELECT COUNT(*) from Alert.vAlert

   Queries for Scalars

   Your query must return a single figure.

   Queries for Status Icons or Blocks

   The return data must include a state column which must contain the following values:

   healthy, critical, and/or warning (the values are not case-sensitive). Any other values will result in state unknown.

   The state values define the colour of the status icons or blocks:

   Healthy	green
   Critical	yellow
   Warning	red
   Unknown	grey

    

   My return data doesn't contain a state column

   If your return data doesn't contain a state column, you can define a state column in your query. You need to define which data you want to be interpreted as healthy, critical, and/or warning and use AS State to put the results in a state column.

   Example:

   Copy

   SELECT Name, CASE WHEN Duration < 500 THEN 'Healthy' ELSE 'Critical' END AS State

    

   My return data contains a state column, but different values

   If your return data contains a state column, but the values are not healthy, critical, or warning, you need to map the values you want to use to the values healthy, critical, and/or warning.

   Example:

   Copy

   SELECT
       Computer as Name,
       Max(TimeGenerated) as max_TimeGenerated,
       State as OriginalState,
       CASE
           WHEN State = 'up' THEN 'Healthy'
           WHEN State = 'down' THEN 'Critical'
           ELSE 'Warning'
       END AS State
   FROM
       StateTable
   GROUP BY
       Computer,
       State

    

   Queries and Column Overrides for Line Graphs and Sparklines

   Your query must return as a minimum a DateTime field and a Numeric value field.

   Column Overrides

   Here you define how the returned data is grouped and displayed. Use the dropdowns to specify which data/column you want to use:

   timestamp	Here you define the time series for the x-axis of the graph.
   value	Here you define which value the graph will show for the y-axis. For example, if you want to see the response time of different servers, your metric would be response time. If you want to see the amount of tickets, your metric would be amount (of tickets). show all: If there are multiple values, you can display these by ticking this box.
   grouping	Here you define what "things" you want the graph to represent. For example, if you want to see the response time of different servers, the grouping would be servers. If you want to see the amount of tickets, your grouping would be tickets.

   Queries and Column Overrides for Bar Graphs and Donuts

   Your query must return a numeric field for the value and a string for grouping.

   Column Overrides

   Here you define how the returned data is grouped and displayed. Use the dropdowns to specify which data/column you want to use:

   value	Here you define which value the graph will show for the y-axis. For example, if you want to see the response time of different servers, your metric would be response time. If you want to see the amount of tickets, your metric would be amount (of tickets). Only for Bar Graphs: show all: If there are multiple values, you can display these by ticking this box.
   grouping	Here you define what "things" you want the graph to represent. For example, if you want to see the response time of different servers, the grouping would be servers. If you want to see the amount of tickets, your grouping would be tickets.

    

   Note:

   By default, only SquaredUp administrators can create and edit SQL queries in the SQL tile.

    

6. Configure the settings for your visualisation:

   Scalar

   Font size	Allows you to set the font size of the value in the tile.
   Unit	Allows you to add a unit to the value displayed in the Scalar tile. For example, if your value shows a time in milliseconds, you can enter "ms" or if your value shows pageviews, you can enter "pageviews".
   Value formatter	Allows you to format the value by using the mustache picker. For example, you can round the value up or down or convert it.

   Grid

   Grid colums

   Grid columns opens the grid designer, where you can show or hide columns, change the order of columns, edit column names or add custom columns.

   Grid options

   Row link

   Allows you to turn the graph items ( for example, the status blocks or the rows of a grid) into hyperlinks that lead you to more information about each item. For example, when you create a status tile for ServiceNow tickets, you can link each icon or block to the corresponding ticket. A click on the icon or block then opens the ticket in ServiceNow. To find out how the URL has to look like, take the URL to a specific ticket (or any other object you want to link to) as a template. Find the object ID in the URL and replace it with the value from the object ID column. You can use the mustache picker to select the object that contains the object ID. Example: The URL to a specific ticket is: https://my-ticket-system.com/ticketID-123456 The column that contains the ticket ID is called sys_id In this case, the URL would look like this: https://my-ticket-system.com/ticketID-{{sys_id}}

   Show column headers

   You can choose between showing or hiding the header for all columns.

   Line Graph

   Data range

   The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.

   percentage	Shows 0 to 100
   fit to data	Shows the data minimum to data maximum
   fit to data (from zero)	Shows from 0 to the data maximum
   custom	Allows you to specify the min and max
   custom fit	Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.

   Display

   Height: Allows you to set the height of the tile with a slider.

   Label

   Allows you to change the label of the results.

   Show legend:

   Allows you to show or hide the legend of the graph.

   Label:

   auto	Choose this option if you want to use the default label that has been created automatically.
   custom	Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels. For more information see How to use Custom Labels

   Sparklines

   Data Range

   The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.

   percentage	Shows 0 to 100
   fit to data	Shows the data minimum to data maximum
   fit to data (from zero)	Shows from 0 to the data maximum
   custom	Allows you to specify the min and max
   custom fit	Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.

    

   Label

   Allows you to change the label of the results.

   auto	Choose this option if you want to use the default label that has been created automatically.
   custom	Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels. For more information see How to use Custom Labels

   Bar Graph

   Data Range

   The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.

   percentage	Shows 0 to 100
   fit to data	Shows the data minimum to data maximum
   fit to data (from zero)	Shows from 0 to the data maximum
   custom	Allows you to specify the min and max
   custom fit	Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.

    

   Label

   Allows you to change the label of the results.

   auto	Choose this option if you want to use the default label that has been created automatically.
   custom	Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels. For more information see How to use Custom Labels

    

   Color

   Here you can enable or disable graph colour matching.

   Donut

   Display

   Size mode:

   Default	Displays the donut scaled to the height of the tile.
   Fill	Enlarges the donut to use the whole width of the tile. If you chose the fill option and show the legend, you can define the size of the legend with a slider.

    

   Show legend:

   Allows you to show or hide the legend of the graph.

    

   Color palette:

   You can choose between different colour palettes:

   General	10 different colours without specific meaning
   Priorities	5 different colours representing 5 different priority states
   Health1	3 different colours representing 3 different health states (red=unhealthy, green=healthy, grey=unknown)
   Health2	4 different colours representing 4 different health states (red=critical, orange=unhealthy, green=healthy, grey=unknown)

   Note:

   • If there are more items than colours, the colours repeat from the beginning.
   • If your using the Priorities" or "Health" palette for generic data without information about health or priority (for example, in the SQL or Web API tile), the colours get assigned in the order the results come in. You need to make sure to sort your results in the correct order to assign the correct colour to them. The order for health is from worst to best (unhealthy to healthy) and for priority from highest to lowest.
   Status Icons or Blocks

   Link options

   item link:

   Allows you to turn the graph items ( for example, the status blocks or the rows of a grid) into hyperlinks that lead you to more information about each item.

   For example, when you create a status tile for ServiceNow tickets, you can link each icon or block to the corresponding ticket. A click on the icon or block then opens the ticket in ServiceNow.

   To find out how the URL has to look like, take the URL to a specific ticket (or any other object you want to link to) as a template. Find the object ID in the URL and replace it with the value from the object ID column. You can use the mustache picker to select the object that contains the object ID.
   Example:
   The URL to a specific ticket is: https://my-ticket-system.com/ticketID-123456 The column that contains the ticket ID is called sys_id
   In this case, the URL would look like this: https://my-ticket-system.com/ticketID-{{sys_id}}

   Sort

   Sort allows you to change the order of the results displayed. You can also group them by their characteristics.

   default	By default, the sorting of the blocks or icons depends on the data source. This can be alphabetical sorting or the order in which data comes back from an API request.
   sort by	Sort by label or health state, ascending or descending
   group by	Group by label or health state, ascending or descending

    

   Blocks (only available for Block Status tiles)

   Here you can set the number of columns for the blocks, their height and the font size within the blocks.

   Label

   Allows you to change the label of the results.

   name	Choose this option if you want to use the default label that has been created automatically.
   custom	Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels. For more information see How to use Custom Labels

   Sublabel

   Allows you to add a sublabel of the results.

   custom	Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels. For more information see How to use Custom Labels
   none	By default, no sublabels are shown.

7. Click done to save the tile.

   The tile now shows data according to your settings.

Walkthroughs

Scalar: Using an SQL query to show a single figure

In this walkthrough, we will create a simple SQL Scalar tile:

1. For this walkthrough we'll create a new dashboard. In SquaredUp navigate to where you'd like the dashboard to be created. Hover over the + button and click dashboard.
2. Give the dashboard a title, by replacing the text that says New Dashboard.
3. A new tile has already been added to the dashboard. Edit the title by overwriting the placeholder value New tile with your own title.

4. The tile selector will already be open. Click SQL.

   

5. Click on the SQL (Scalar) button to create a SQL Scalar tile.

   

6. Type in the connection string for your database.

7. In the query string box paste in the SQL query you have tested in MS SQL Server Management Studio.

8. Click next and the result of the query, as a single number will be shown.
9. In the Scalar > unit box type the name of the units returned, for example it might be disks.
10. Click done.

Grid: Using an SQL query to show a table of results

1. Create a new SQL tile on a dashboard.

2. Click on the SQL (Grid) button to create a SQL Grid tile.

   

3. Type in the connection string for your database.

4. In the query string box paste in the SQL query you have tested in MS SQL Server Management Studio.

5. Click next and you should see a table of results.

6. In the grid columns section we are now going to rename a column header and improve its formatting. To rename a column click on the column title and overwrite this with your new column name.

7. To format a column click on edit next to the column name. For example, for Unix timestamps you can paste in {{timeago(value, true, true)}}. For more information see How to use the Grid designer when configuring tiles.

8. Click done and then next.

9. You can use the grid optionsrow link option to add a hyperlink to each row.

10. Click done.

11. Test that the row linking works.

FAQs

Who can create and edit SQL queries?

By default, only SquaredUp administrators can create and edit SQL queries in the SQL tile.

How do I configure access to a database?

To be able to query databases, such as the Operations Manager database, the SquaredUp application pool account needs to be given the db_datareader role in SQL Server Management Studio for the database you wish to query.  

Line Graph shows "A value column and a timestamp column are mandatory in the query results, please modify your query"

Both a DateTime field AND a Numeric value field are necessary for a line graph to be drawn. This message indicates that the query results are missing either a DateTime field, a Numeric value field or both.

Can I use parameters to customise a SQL query?

There are two ways you can use the SQL tile:

• On a dashboard, with fixed SQL queries

• On a perspective, with parameterised SQL queries

For more information see How to use the SQL tile on a perspective.

Does the page timeframe affect the results?

The page timeframe (How to use Timeframe) has no effect on the query results. The timeframe should be configured in the SQL query.

How can I get dates and times to show correctly in my Grid?

If your grid columns show times in the Unix timestamp format (milliseconds), there are two ways for you to convert them into a readable date and time format:

• convert the timestamp directly with your SQL query

• use a custom template for your grid columns and insert the conversion there

Converting the timestamp with your SQL query

You need to insert CONVERT into your SQL query string.

For example, if your query string is SELECT TOP 10 * from Alert.vAlert and dwLastModifiedDateTime is the name of the column you want to convert, your conversion would look like this:

SELECT TOP 10 CONVERT(varchar(64), dwLastModifiedDateTime, 21) AS dwLastModifiedDateTime from Alert.vAlert

You can configure the date format by editing 21, replacing it with a chosen format which can be found in the following article: MSDN: CAST and CONVERT

Converting the timestamp with a custom template

Edit the column that contain the time in the grid columns panel and enter a custom template that converts the time:

To show the date:

{{timeago(value, true, false)}}

To show the date and time:

{{timeago(value, true, true)}}

To show how long ago it occurred:

{{timeago(value)}}