SQL tile

An EAM edition license (or above) is required for this feature. To upgrade please contact [email protected].

To check the license edition you are using see How to check which license key is being used. To see what is included in different product edition licenses see the Licensing Overview.

About the SQL tile

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

A typical example of an external database would be the System Center Service Manager database or another CMDB, but any data that is stored in any SQL database can be integrated.

By default, only SquaredUp DS administrators can create and edit SQL queries in the SQL tile. To delegate permissions to other users see Signing and security for sensitive tiles.

How to configure an SQL tile

Since SquaredUp DS version 5.4 you need an SQL provider to use the SQL tile. Your Data Warehouse is available as a provider by default but if you want to use any other connection you'll need to create a provider.

See How to add an SQL provider.

If you created SQL tiles before SquaredUp DS version 5.4, they were created with a connection string in the tile instead of a provider.
As soon as you update to v5.4 those connection strings will automatically be converted into providers. If identical connection strings are detected, they'll be merged into one provider.
The new SQL providers will be named in the following format: servername.databasename (if server and database name can't be identified they'll be named SQL provider 1, SQL provider 2, etc.)

  1. Add a new tile to a dashboard or perspective and click on Integrations > SQL.
  2. Choose the visualization for your SQL tile:

  3. Connection:
    Choose the SQL provider you want to use.

    Since SquaredUp DS version 5.4 you need an SQL provider to use the SQL tile. Your Data Warehouse is available as a provider by default but if you want to use any other connection you'll need to create a provider.

    If you haven't updated to v5.4 yet, you need to enter a connection string.
    For more information about adding SQL providers and entering connection strings see How to add an SQL provider.
  4. Query:

    By default, only SquaredUp DS administrators can create and edit SQL queries in the SQL tile. To delegate permissions to other users see Signing and security for sensitive tiles.

    Note: When an SQL tile uses an ODBC provider, you can't use mustache values for your queries in that tile.

    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
    SELECT COUNT(*) from Alert.vAlert
    Note: If you connected to an Operations Manager database that is on the same server as the Data Warehouse and used the connection stringglobal:dw, you need to put the following in front of the query:
    USE <OperationsManagerDatabaseName>;Tip: You may find some useful SCOM queries on Kevin Holman's blog page SCOM SQL queries.
  5. Configure the settings for your visualization:
  6. Click done to save the tile.

    The tile now shows data according to your settings.

Walkthroughs

FAQs

Who can create and edit SQL queries?

By default, only SquaredUp DS administrators can create and edit SQL queries in the SQL tile. To delegate permissions to other users see Signing and security for sensitive tiles.

How do I configure access to a database?

Access to the SCOM Data Warehouse

Access to the SCOM Data Warehouse is configured during the SquaredUp DS setup process. If you are able to see graphs in SquaredUp DS, SQL queries have access to the SCOM Data Warehouse (see How to check and reconfigure the Data Warehouse connection).

Access to other databases

To be able to query other databases, such as the Operations Manager database, the SquaredUp DS application pool identity 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.

"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 or column 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 customize a SQL query?

You can use the following parameters in your SQL query:

On dashboards
Page timeframe parameters
On perspectives
Page timeframe and scope parameters

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

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

You can use timeago to convert Unix timestamps from milliseconds to show a readable date and time. You can either display an absolute time (for example, August 20th 2021) or a relative time (for example, 20 hours ago).

There are four parameters you can use for converting the time, written in the following format:
timeago(Value you want to convert, Show Absolute, Show Time, Without Suffix, With Prefix).

The parameters for the value you want to convert can either be true or false:

Show Absolute
true = show absolute time (date) rather than relative time
false = show relative time
Show Time
true = if showing absolute time, show the time as well as the date
false = do not show the time with the date
Without Suffix
true = if showing relative time, this removes the "ago"
false = "ago" at the end will be shown (e.g. 10 hours ago)
With Prefix
true = adds "since" ahead of the absolute time, or "for" ahead of the relative time
false = no prefix will be shown

Tip: The default setting for all parameters is false. If you only want to change the first parameters, you can just use timeago(value, true, true) and it will be interpreted as timeago(value, true, true, false, false).

Examples:

To display the relative time (how long ago something occurred):

{{timeago(value)}}

To display the absolute time as the date without the time:

{{timeago(value, true)}}

To display the absolute time as the date with the time:

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

To display the time with the prefix "for" for relative time and "since" for absolute time (for example, "the status has been unhealthy for 10 hours")

Option A) Use the fourth parameter and set it to true. This will insert the appropriate prefix for the absolute or relative time:

timeago(value, false, false, true, true)

Option B) You can leave out the fourth parameter that controls if the prefix "for" or "since" is inserted automatically. Since you left it out, it defaults to false, causing no prefix to be shown. Instead you insert the word "for" or "since" manually before the timeago function.

for timeago(value, false, false, true)

To convert a time value from seconds to milliseconds:

Some APIs, like Pingdom for example, return the value in seconds. Since Timeago uses the time value in milliseconds, you need to multiply by 1000 in that case:

{{timeago(value*1000)}}

Example with a Pingdom property:

{{timeago(properties.lasttesttime*1000)}}

Troubleshooting

Troubleshooting the SQL tile

Webinars

The following webinars cover how to use the SQL tile to integrate with System Center Service Manager:

Was this article helpful?


Have more questions or facing an issue?