7 minute readApplies to: v4

How to use the SQL tile

This article describes how to use queries to display SQL data in SquaredUp.

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. 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.

Overview

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

SQL tile

After selecting the SQL tile you will get the choice of Grid or Scalar.

The SQL Grid tile should be used for queries that return a table of results:

SQL Grid tile

The SQL Scalar tile should be used when the result of the query is a single number:

SQL Scalar tile

Prerequisites

Configure permissions in SQL Server Management Studio

If the SquaredUp server is domain joined, then 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.

The SquaredUp application pool account needs to be given the db_datareader role on the database, as described below:

  1. First identify the user account that needs to be given permission.

    SquaredUp uses the application pool identity account to retrieve data for the SQL tile, NOT the account with which you log on to SquaredUp. By default the application pool identity is set to NetworkService, but you may have changed this.

    You will need to RDP to the machine where SquaredUp is installed, so you will need to open port 3389 as described in the Microsoft article: Cannot connect remotely to a VM because RDP port is not enabled in NSG, and then log in using the credentials specified when creating the VM.

  2. In IIS, click on Application Pools.
  3. Right-click on SquaredUpv4 and select Advanced Settings.
  4. Under Process Model, you will see Identity.

    • If the application pool identity is running as a custom account then add the relevant roles for this custom account.
    • If the application pool is set to NETWORK SERVICE and SquaredUp is installed on the same server as the database you are accessing then add the relevant role for the NT AUTHORITY\NETWORK SERVICE account.
    • If the application pool is set to NETWORK SERVICE and SquaredUp is NOT installed on the same server as the database you are accessing then add the relevant role for the SquaredUp server's AD computer account (for example, domain\mywebserver$)
  5. Open SQL Server Management Studio.
  6. Expand Security and then Logins. Look for a login with the same name as the application pool identity, as noted above, if one doesn't exist you will need to create one.
  7. Right-click on the relevant account (as described above) and select Properties.
  8. Click on User Mapping.
  9. Tick the database you wish to give access to (under Users mapped to this login).
  10. In the bottom panel tick db_datareader.
  11. Click OK.

Connection String

Once the permissions are in place the connection string in the SQL tile is used to connect to the SQL server and database you want to query.

Connection String: Data Source=<SQLservername>;Initial Catalog=<DatabaseName>;Integrated Security=True;

SQL Tile Connection

For example:

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

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.

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.

Specifying an instance

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

For example:

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

Specifying a port

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

For example:

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

Query String

First, run your query in SQL Server Management Studio, so you can see any errors and amend as necessary.

MS SQL Server Management Studio

Check that your connection string corresponds with the server and database you were using in SQL Server Management Studio, and then paste your query into the query string box in the appropriate SQL Grid or Scalar tile.

SQL Query

Walkthrough: Adding a SQL query to show a single figure

First, we will create a simple SQL Scalar tile:

  1. You can add the SQL tile to a new or existing dashboard or perspective. 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.

    SQL tile

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

    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.

Walkthrough: Adding a SQL query to show a table of results

  1. Under your SQL Scalar tile click the + to add another tile.
  2. Click on the SQL (Grid) button to create a SQL Grid tile.

    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 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 options row link option to add a hyperlink to each row.
  10. Click done.
  11. Test that the row linking works.

FAQs

How can I get dates and times to show correctly?

To to convert timestamps to show a readable date and time you can either edit the SQL query or customise the columns in the SquaredUp tile. See How to use the Grid designer when configuring tiles.

To show the date:

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

To show the date and time:

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

To return to showing how long ago it occurred:

{{timeago(value)}}

If you prefer to edit your SQL query to return your data in a date time format, you will need to insert the following into your SQL query string. The example below uses TimeRaised, but you will need to edit this according to your query string:

CONVERT(varchar(64), TimeRaised, 21) as TimeRaised

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

Squared Up Ltd. (c) 2020Report an issue with this article