This article describes how to use fixed queries to display SQL data in SquaredUp. For information about how to create parameterised queries for use in perspectives see How to use the SQL tile in a perspective.
By default only SCOM administrators can create and edit SQL queries in SquaredUp. For more information see Signing and Security.
Who can create and edit SQL queries?
Walkthrough: Adding a SQL query to show a single figure
Walkthrough: Adding a SQL query to show a table of results
Walkthrough: Adding a SQL Line Graph showing number of events per server
- Line Graph shows "A value column and a timestamp column are mandatory in the query results, please modify your query"
- Can I use parameters on perspectives to customise a SQL query?
- Does the page timeframe affect the results?
- How can I get dates and times to show correctly?
SQL tile types
The SQL tile enables you to pull data from external SQL data sources into your dashboards or perspectives.
SQL Scalar
The SQL Scalar tile should be used when the result of the query is a single number:
SQL Grid
The SQL Grid tile should be used for queries that return a table of results:
SQL Line Graph
A line graph can be displayed for any query that contains both a DateTime field and a Numeric value field, as a minimum. See Walkthrough: Adding a SQL Line Graph showing number of events per server
Prerequisites
Configure permissions in SQL Server Management Studio
Permissions to the Data Warehouse are configured during the SquaredUp setup process, so if you are able to see graphs in SquaredUp 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 SquaredUp 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.
Who can create and edit SQL queries?
By default only SCOM administrators can create and edit SQL queries in SquaredUp. For more information see Signing and Security.
SQL tile options
Connection
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
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;
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 SquaredUp 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 SquaredUp 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>;
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
First, run your query in SQL Server Management Studio, so you can see any errors and amend as necessary.
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.
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.
You can also use parameters in the query when using the SQL tile on a perspective, for example {{displayName}} to pull in the server name. For more information about using parameterised queries on a perspective see How to use the SQL tile in a perspective.
Column Overrides
For the SQL Line Graph tiles there is a column overrides option following the query box. This important option allows you to choose how the data returned is grouped and displayed.
Timestamp
Use the timestamp dropdown to specify which data/column to use as the time series.
Value
Where there are multiple values it is possible to display these by expanding the column overrides section and then ticking the show all box next to value.
Grouping
If your results have more than one numeric column, SquaredUp will simply pick one to use as the line graph Y axis, or you can select the one you want here.
Data Range
The Data Range option allows you to choose the range of the y-axis or bar chart area. The min and max will be set, depending on the option selected
percentage shows 0 to 100
fit to data shows the data minimum to data maximum
fit to data (from zero) shows from 0 to the data maximum
custom fit allows you to specify the min and max
Display
The height slider allows you stretch or shrink the graph height.
Label
show legend allows you to display a key to the coloured graph lines, which is particularly important for Open Access dashboards when users are not able to click on the lines to drilldown.
The custom label section allows you to change from the automatic label to specify your own label format. For more information see How to use custom labelling.
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 SQL 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:
- You can add the SQL tile to a new or existing dashboard or perspective. For this walkthrough we'll create a new dashboard. In SquaredUp navigate to where you'd like the dashboard to be created. Hover over the + button and click dashboard.
- Give the dashboard a title, by replacing the text that says New Dashboard.
- A new tile has already been added to the dashboard. Edit the title by overwriting the placeholder value New tile with your own title.
-
The tile selector will already be open. Click SQL.
-
Click on the SQL (Scalar) button to create a SQL Scalar tile.
- Set the connection string to
global:dw
-
In the query string box paste the following:
SELECT count(*) from ManagedEntity
- Click next and you should see a number appear showing the number of objects monitored.
- In the Scalar > unit box type
alerts
to complete this simple tile. - Click done.
Walkthrough: Adding a SQL query to show a table of results
- Under your SQL Scalar tile click the + to add another tile.
-
Click on the SQL (Grid) button to create a SQL Grid tile.
- Set the connection string to
global:dw
-
In the query string box paste the following:
SELECT alt.RaisedDateTime, 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 RaisedDateTime desc
- Click next and you should see a table of results.
-
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 exampleDate
. -
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. - Click done and then next.
-
You can use the grid options row link option to add a parameterised hyperlink to each row. Edit the URL below to insert your SquaredUp server name, or correct SquaredUp URL, and then paste into the row link box.
http://SquaredUpServerName/SquaredUpv4/drilldown/scomobject?value=
- Click on the mustache helper button
-
Click on path.
- Click done.
- Test that the row linking works.
Walkthrough: Adding a SQL Line Graph showing the top 5 servers with most events
To draw a line graph your query results must include a DateTime field AND a Numeric value field. This walkthrough uses a query to show the "Top 5 Servers with most Events" across all objects that are reporting events for 30 days.
-
Click the orange plus to add a new tile, and then click on SQL.
-
Click on the SQL (Line Graph) button to create a SQL Line Graph tile.
- Set the connection string to
global:dw
-
In the query string box paste the following query:
-- Just in case we need a smaller sample size, create a variable for our StartDate
DECLARE @StartDate datetime
SET @StartDate = ISNULL(@StartDate, cast(cast(cast(DATEADD(DAY,-30,GetDate()) as int) as float) as datetime));
--Temp table to store our Top 5 Events
DECLARE @TopServers TABLE(
ComputerName nvarchar(255)
, LoggingComputerRowId int
, CountOfEvents int
)
--Find the servers that are collecting the most events
INSERT INTO @TopServers
SELECT TOP 5
elc.ComputerName as ComputerName
, e.LoggingComputerRowId as LogginComputerRowID
, Count(EventDisplayNumber) as CountOfEvents
FROM [Event].[vEvent] as e
INNER JOIN vEventLoggingComputer as elc ON e.LoggingComputerRowId = elc.EventLoggingComputerRowId
WHERE datetime >= @StartDate
GROUP BY elc.ComputerName, e.LoggingComputerRowId
ORDER BY CountOfEvents DESC
--Find our actual records
SELECT
ts.ComputerName
, CAST(CONVERT(VARCHAR(20), DateTime, 102) As DateTime) as DateTime
, Count(e.EventDisplayNumber) as value
FROM [Event].[vEvent] AS e
INNER JOIN @TopServers AS ts ON e.LoggingComputerRowId = ts.LoggingComputerRowId
WHERE DateTime >= @StartDate
GROUP BY ts.ComputerName, CAST(CONVERT(VARCHAR(20), DateTime, 102) As DateTime)You can of course use your own query, but the results must contain a DateTime field AND a Numeric value field, for a line graph to be drawn.
- Click next and you should see a graph.
- Click done.
FAQs
Line Graph shows "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 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 on perspectives to customise a SQL query?
For more information about using parameterised queries on a perspective see How to use the SQL tile on a perspective
Does the page timeframe affect the results?
No, the page timeframe has no effect on the query results. The timeframe should be configured in the SQL query.
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 SquaredUp tile, see How to use the Grid designer when configuring tiles.
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: