This article only applies to Squared Up Version 2.0. If you're looking for help with Squared Up Version 3.0, please visit our new knowledge base

Overview

The Squared Up SQL plugin enables you to enrich dashboards with queried data from any external Microsoft SQL Server database.

Download the SQL plugin

This plugin is not installed with Squared Up v2, please contact Support if you need it.

Download the SQL Plugin Demo Dashboard

If you wish, you can download a demo dashboard from Community Dashboards:

1. Download the SQL plugin demo dashboard:

SQL Plugin Demo Dashboard

2. Import it into Squared Up by clicking 'import dashboard' on the right-hand menu.

Configuring a SQL Query

Adding SQL Query to a dashboard

Create a new section within the dashboard designer, and select 'SQL Query' from the Type:

Connection

The Connection section is used to connect to the SQL Server (connection string).  The simplest option is to run a query against the SCOM data warehouse (as all permissions are already in place), where it can simply be set to: global:dw


To connect to another database you will need to specify the server and database as shown in this table (and configure access, as described below):


DatabaseConnection (Connection String)

Data Warehouse

OperationsManagerDW
global:dw
OperationsManager database
Data Source=<server-name>;Initial Catalog=<database-name>;Integrated Security=True;


If the Squared Up server and SQL server are in the same domain:

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

If the Squared Up server and SQL server are not in the same domain:
Data Source=SQLServer1.mydomain.local;Initial Catalog=OperationsManager;Integrated Security=True;


If the OperationsManager database is on the same server as the data warehouse you can use:
global:dw
and then put the following at the beginning of the query:
USE operationsmanager;
Any other database

Data Source=<server-name>;Initial Catalog=<database-name>;Integrated Security=True;

For example:
Data Source=SQLServer1;Initial Catalog=CRMDB;Integrated Security=True;
Using an instance
Data Source=<server-name>\<instance name>;Initial Catalog=<database-name>;Integrated Security=True;

Specifying a port

Data Source=<server-name>,<port number>;Initial Catalog=<database-name>;Integrated Security=True;

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



Configuring access to database

The connection to the data warehouse is configured just after installation, so these permissions are probably already in place for SQL queries on the SCOM data warehouse.  See Configuring the data warehouse connection


For queries on other databases, such as the OperationsManager database, the Squared Up application pool account needs to be given the 'apm_datareader', 'db_datareader' roles in the database.  See Configuring access to a database for use with the SQL plugin


You are also able to create an IIS application setting containing the connection string, and reference this using the following:

setting:XYZ

Where XYZ is the name setting.


Query


This where you type or paste the SQL command (query string) that will be executed against the database and return results. Place the cursor in the box and press the return key on the keyboard to expand the box for easier editing.

For example, a simple query to show the number of alerts:

Connection: global:dw

Query: SELECT COUNT(*) from Alert.vAlert

 



For any query, I would recommend testing it in SQL Server first, so you can see any errors and amend as necessary.  See below for some sample SQL queries.


Options

The Options section allows you to configure how the results are displayed, for example if your query produces a table of results you will need to change the display from scalar to tabular.


export

'add link' will add a link enabling you to export the query results to Excel.

'add link, don't render result' will not show the query results on the page, only the link 'export to excel'.


display

'scalar' if the query results are a single number.

'tabular' if the query produces a table of results.


scalar unit (only available with scalar selected)

This allows you to specify a unit description to appear next to the scalar value.



size  (only available with scalar selected)

Allows you to specify the font size of the figure displayed.


table headers (only available with tabular selected)

Uncheck this option to hide the table column headings.



Sample SQL Queries

See also 

Kevin Holman's Useful Operations Manager queries

Displaying Ops Mgr events data in Squared Up


Listing unexpected reboot events in the last week ordered by date

Connection=global:dw

Tabular

Query=

SELECT Cast(Evt.DateTime AS varchar) AS Date, Cmp.ComputerName as [Computer Name], Det.RenderedDescription As Description FROM Event.vEvent AS Evt WITH (NoLock) INNER JOIN Event.vEventRule AS EvtRule ON Evt.EventOriginId = EvtRule.EventOriginId INNER JOIN vManagedEntity AS MgtEntity ON EvtRule.ManagedEntityRowId = MgtEntity.ManagedEntityRowId INNER JOIN EventLoggingComputer AS Cmp WITH (NoLock) ON Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId INNER JOIN Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE (Evt.EventDisplayNumber = 6008) AND Evt.DateTime >= DATEADD(day,-7, GETDATE()) ORDER BY Date desc


Listing all reboot events in the last week ordered by date

Connection=global:dw

Tabular

Query=

SELECT Cast(Evt.DateTime AS varchar) AS Date, Cmp.ComputerName as [Computer Name], Det.RenderedDescription As Description, Evt.EventDisplayNumber As [Event ID] FROM Event.vEvent AS Evt WITH (NoLock) INNER JOIN Event.vEventRule AS EvtRule ON Evt.EventOriginId = EvtRule.EventOriginId INNER JOIN vManagedEntity AS MgtEntity ON EvtRule.ManagedEntityRowId = MgtEntity.ManagedEntityRowId INNER JOIN EventLoggingComputer AS Cmp WITH (NoLock) ON Evt.LoggingComputerRowId = Cmp.EventLoggingComputerRowId INNER JOIN Event.vEventDetail AS Det WITH (NoLock) ON Evt.EventOriginId = Det.EventOriginId WHERE (Evt.EventDisplayNumber = 1074 OR Evt.EventDisplayNumber = 1076 OR Evt.EventDisplayNumber = 6008 OR Evt.EventDisplayNumber = 6006) AND Evt.DateTime >= DATEADD(day,-7, GETDATE()) ORDER BY Date desc


Number of active alerts

Connection=global:dw

Scalar

Scalar Unit=active alerts

Query=

SELECT COUNT (AlertGuid) FROM Alert.vAlertResolutionState WHERE ResolutionState != '255'


Listing a particular Alert "Available Megabytes of Memory is too low"

Connection=global:dw

Tabular

Query=

SELECT Cast(alt.RaisedDateTime AS varchar) AS Date, alt.AlertName, alt.AlertDescription, alt.Severity, alt.Priority, alt.Category, vManagedEntity.DisplayName, vManagedEntity.Path FROM Alert.vAlertResolutionState AS ars INNER JOIN Alert.vAlertDetail AS adt ON ars.AlertGuid=adt.AlertGuid INNER JOIN Alert.vAlert AS alt ON ars.AlertGuid=alt.AlertGuid INNER JOIN vManagedEntity ON alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId WHERE alt.AlertName='Available Megabytes of Memory is too low' AND alt.RaisedDateTime >= DATEADD(day,-7, GETDATE()) ORDER BY Date desc


Number of 'Available Megabytes of Memory is too low' Alerts in the last week

Connection=global:dw

Scalar

Scalar Unit=alerts

Query=

SELECT COUNT(AlertGuid) FROM Alert.vAlert WHERE AlertName='Available Megabytes of Memory is too low' AND RaisedDateTime >= DATEADD(day,-7, GETDATE())


5 most recent alerts

Connection=global:dw

Tabular

Query=

SELECT TOP 5 AlertName,AlertDescription,Category from Alert.vAlert


Total objects monitored

Connection=global:dw

Scalar

Scalar Unit=objects

Query=

SELECT count(*) from ManagedEntity


20 noisiest objects

Connection=global:dw

Tabular

Query=

SELECT TOP 20 ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS [#], me.DisplayName AS "Object name", COUNT(al.ManagedEntityRowId) AS "Alert count"FROM Alert.vAlert al JOIN vManagedEntity me on me.ManagedEntityRowId = al.ManagedEntityRowId GROUP BY me.ManagedEntityRowId, me.DisplayName ORDER BY COUNT(al.ManagedEntityRowId) DESC


10 most common alerts

Connection=global:dw

Tabular

Query=

SELECT TOP (15) ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS "#", AlertName AS "Alert name" FROM Alert.vAlert GROUP BY WorkflowRowId, AlertNameORDER BY COUNT(*) DESC


TOP 10 Alerts by Repeat

Connection=global:dw

Tabular

Query=

SELECT TOP (15) ROW_NUMBER() OVER(ORDER BY COUNT(*) DESC) AS "#", AlertName AS "Alert name", COUNT(al.ManagedEntityRowId) AS "Alert count"FROM Alert.vAlert alJOIN vManagedEntity me on me.ManagedEntityRowId = al.ManagedEntityRowIdGROUP BY WorkflowRowId, AlertNameORDER BY COUNT(*) DESC


Number of events (this queries the OperationsManager database so access must be configured, see above)

Connection=global:dw

Tabular

Query=

USE operationsmanager;SELECT top 20 LoggingComputer as ComputerName, COUNT(*) AS TotalEvents, Number as EventID FROM EventallView with (NOLOCK) GROUP BY LoggingComputer, Number ORDER BY TotalEvents DESC;


Date Time Format

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:

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Troubleshooting

Troubleshooting SQL dashboards

XML Configuration

The dashboard designer writes these settings as XML to a dashboard file within C:\inetpub\wwwroot\SquaredUpv2\Configuration\Views. Below is an example of the XML it would write for this section:  

<Sections>
    <Section>
      <Id>1452687684868</Id>
      <Title>Scalar</Title>
      <Plugin>Sql</Plugin>
      <RequiredPluginVersion>
        <Major>2</Major>
        <Minor>8</Minor>
      </RequiredPluginVersion>
      <Settings>
        <NameValueSetting>
          <Name>connectionstring</Name>
          <Value>global:dw</Value>
        </NameValueSetting>
        <NameValueSetting>
          <Name>querystring</Name>
          <Value>SELECT COUNT(*) from Alert.vAlert</Value>
        </NameValueSetting>
        <NameValueSetting>
          <Name>isscalar</Name>
          <Value>true</Value>
        </NameValueSetting>
        <NameValueSetting>
          <Name>exportresults</Name>
          <Value>false</Value>
        </NameValueSetting>
        <NameValueSetting>
          <Name>tableshowheaders</Name>
          <Value>true</Value>
        </NameValueSetting>
        <NameValueSetting>
          <Name>scalarfontsize</Name>
          <Value>117</Value>
        </NameValueSetting>
        <NameValueSetting>
          <Name>scalarunit</Name>
          <Value>alerts</Value>
        </NameValueSetting>
      </Settings>
    </Section>

  

XML Settings Reference

NameValuesCommentsRequiredDesigner
connectionStringglobal:dwUse the existing data warehouse connection.YesYes
setting:<setting-name>Specifies an IIS application setting that contains the full connection string to use (where <setting-name> is the name of the setting).
The full connection string, e.g.
Data Source=sqlserver1;Initial Catalog=catalog1;Integrated Security=True;
Full connection string to use when performing the query. NB: This is stored as plain text and should not contain usernames / passwords.
queryStringe.g. SELECT TOP 5 AlertName, AlertDescription, Category FROM Alert.vAlertThe query to perform. This example selects the top 5 alerts.YesYes
isScalarTrue | False (default)The result should be displayed a scalar value. Please ensure the query will return a single value.NoYes
scalarFontSizeDefault: 80Font size used when displaying the scalar value.NoNo
scalarUnite.g. Alerts or KBDisplays a unit after a scalar value.NoNo
tableShowHeadersTrue (default) | FalseToggle the display of headers for tabular data.NoNo


See also:

Troubleshooting SQL dashboards

The Dashboard Designer - The Quick Start Guide

Configuring access to a database for use with the SQL plugin

The SELECT permission was denied on the object 'vRule', database 'OperationsManagerDW', schema 'dbo'