Skip to main content

How to use the SQL tile on a perspective

Catherine
  • Updated

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.

This article describes how to create parameterised queries for use in your perspectives. For more general use of the SQL tile for fixed queries see How to use the SQL tile.

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 resource from an external database. This is called a parameterised 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 parameterised 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 resource context to build a parameterised SQL query

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

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

So for example, if you want to use the resource name in your SQL query, you use: {{name}}.

If you want to use the resource type, you use: {{azure/resource}}.

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

So a query like:

SELECT * FROM owner WHERE server_name = {{name}}

is resolved to:

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

SquaredUp internally executes the query using SQL parameterised queries, but that is outside the scope of this article.

When creating these queries, SquaredUp 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.

SNAG-0031.png

Limitations and special syntax

There are some special considerations when using parameterised SQL queries in SquaredUp:

  1. Mustaches can only be used to parameterise values in your SQL query - you cannot use mustaches to dynamically generate sub-clauses or other non-values.
  2. When parameterising string values, do not include quotes around the mustache. For example, server_name = 'myserver' should become server_name = {{name}}.
  3. Mustaches used in SQL queries cannot contain any JavaScript, for example string manipulation functions like .replace()
  4. Mustaches may be used inside T-SQL expressions, if required, like this:
    SELECT COUNT(*) FROM ManagedEntity WHERE [Name] LIKE '%' + {{name}} + '%';

Examples

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