8 minute readApplies to: v4

How to use the SQL tile

This article describes how to use fixed queries to display SQL data in Squared Up. To information about how to create parameterised queries for use in perspectives see How to use the SQL tile in a perspective.

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 Grid

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

SQL Scalar tile Scalar

Prerequisites

Configure permissions in SQL Server Management Studio

Permissions to the Data Warehouse are configured during the Squared Up setup process, so if you are able to see graphs in Squared Up then the permissions are in place for SQL queries on the SCOM Data Warehouse.

For querying other databases, such as the Operations Manager database, the Squared Up application pool account needs to be given the db_datareader role in SQL Server Management Studio for the database you wish to query.  See How to configure access to a database for use with the SQL tile.

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.

Data Warehouse

To query Data Warehouse the connection string can simply be set to:

global:dw

SQL Tile Connection

Any other database

To connect to any other database you will need to specify the server and database as described below.

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. By default Squared Up uses the application pool account to access to the database.

Operations Manager database

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

If the Squared Up 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.

If the Operations Manager database is on the same server as the Data Warehouse you can use a connection string of global:dw and then put the following in front of the query itself:

USE <OperationsManagerDatabaseName>;

Use Operations Manager

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

For example, a simple query to show the number of alerts could be used in the SQL Scalar tile:

Connection String: global:dw

Query String: SELECT COUNT(*) from Alert.vAlert

You may find some useful SCOM queries on Kevin Holman's blog page SCOM SQL queries.

Walkthrough: Adding a SQL query to show a single figure

The SQL tile can be used on a dashboard or a perspective. This walkthrough shows you how to add a SLA tile to a dashboard. For more information about using parameterised queries on a perspective see How to use the SQL tile in a perspective

First, we will create a simple SQL Scalar tile to show the number of objects being monitored by SCOM:

  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 Squared Up 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. Give the tile the name Total objects monitored

  7. Type global:dw as the connection string.

  8. In the query string box paste the following:

    SELECT count(*) from ManagedEntity

    SQL Scalar tile

  9. Click next and you should see a number appear showing the number of objects monitored.

  10. In the Scalar > unit box type alerts to complete this simple tile.

  11. 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 global:dw as the connection string.

  4. In the query string box paste the following:

  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, in this case, raisedDateTime and overwrite this with your new column name, for example Date.

    Rename Column

  7. To format a column click on edit next to the column name, in this case for the Date column. For this column paste in {{timeago(value, true, true)}} and click done. For more information see How to use the Grid designer when configuring tiles.

    Custom Template

  8. Click done and then next.

  9. You can use the grid options row link option to add a parameterised hyperlink to each row. Edit the URL below to insert your Squared Up server name, or correct Squared Up URL, and then paste into the row link box.

    http://SquaredUpServerName/SquaredUpv4/drilldown/scomobject?value=

  10. Click on the mustache helper button Mustache Helper Button

  11. Click on path.

    Row Link

  12. Click done.

  13. Test that the row linking works.

FAQs

How can I get dates and times to show correctly?

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

Custom Template

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

Can I use parameters on perspectives to customise a SQL query?

For more information about using parameterised queries on a perspective see How to use the SQL tile in a perspective

Troubleshooting

Troubleshooting the SQL tile

How to configure access to a database for use with the SQL tile

How to use the SQL tile in a perspective

Kevin Holman's Useful Operations Manager queries

Sample queries in the Squared Up GitHub repository

Troubleshooting the SQL tile

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