How to use the SQL tile in a perspective

The SQL tile enables you to pull data from external SQL data sources into your dashboards. 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.

Overview

You can use the SQL tile within a top-level dashboard by providing a fixed SQL query. For example, to show all open service desk tickets against Exchange, you might have a query like:

SELECT * FROM tickets WHERE status = "open" AND service_name = "Exchange"

By using the SQL tile within a perspective, you can dynamically pull data about a specific monitored object from an external database. This is called a parameterized query. So to show all open change requests against a server, you might have a query like:

SELECT * FROM change_requests WHERE status = "pending" AND target_id = {{id}}

This is a very powerful feature enabling you to create a unified operations console experience across multiple systems.

The rest of this article explains how to create parameterized queries for use in your perspectives.

This article uses fictional, simplified queries as examples. For the actual queries required to integrate with System Center Service Manager, see the references at the end of this article.

Using the object context to build a parameterized SQL query

As you can see in the above example, SQL queries in Squared Up can be parameterized using the {{parameter}} syntax. The double-braces syntax is used elsewhere in Squared Up and is referred to as mustache syntax.

The content of the mustache can refer to any property of the SCOM object that the perspective applies to. The SCOM object is called the context.

So for example, if you want to use the SCOM object ID in your SQL query, you use: {{id}}.

If you want to use the display name of the object, you use: {{displayName}}.

And if you want to use the VM name of the server (a class property), you use: {{properties.virtualMachineName}

When a perspective is shown for a particular object, Squared Up will will automatically replace these mustaches with their actual values for the object being viewed and the SQL query executed.

So a query like:

SELECT * FROM owner WHERE server_name = {{properties.dnsName}}

is resolved to:

SELECT * FROM owner WHERE server_name = "myserver.domain.local"

Squared Up internally executes the query using SQL parameterized queries, but that is outside the scope of this article.

When creating these queries, Squared Up provides a mustache helper that allows you to browse and select properties of the context. To access this helper, simply click the {{}} button in the input box, or type {{ at any point in the input box.

Limitations and special syntax

There are some special considerations when using parameterized SQL queries in Squared Up:

  1. Mustaches can only be used to parameterize values in your SQL query - you cannot use mustaches to dynamically generate sub-clauses or other non-values.

  2. When parameterizing string values, do not include quotes around the mustache. For example, server_name = "myserver.domain.local" should become server_name = {{properties.dnsName}}.

  3. Mustaches used in SQL queries cannot contain any JavaScript, for example string manipulation functions like .replace()

Examples

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

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

Service Manager Data Warehouse schema

Sample queries in the Squared Up GitHub repository label: How to use the SQL tile in a perspective keywords: sql tile perspective parameterize mustache query SCSM service manager