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.
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 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.)
Add a new tile to a dashboard or perspective and click on Integrations > SQL.
Choose the visualization for your SQL tile:
A Scalar displays one value. A Scalar is useful to show a specific number like "total cost of my services" or "free disk space on this server". When multiple values are returned (meaning a table with multiple rows), you will still be able to pick the Scalar visualization, but the Scalar will only show the value of the first row.
Example:
A table of data, for example incidents or tickets.
Tip: You can turn the individual rows into links in the settings. For example, if you're displaying tickets in your grid, you can link the rows to the ticket in your external ticket system.
Did you know? Since SquaredUp DS 5.4 users can search the grid, and temporarily change the column size and sorting of the grid (by clicking on the column headers) without having to access the settings. They can also expand a row by clicking on the three dots at the end of each row if cells are too small to show their entire content.
Shows time-series data over time, in a graph with an x-axis (time) and a y-axis. You can show several objects, such as servers, in one graph.
Example:
Visualizes time-series data as vertical columns.
Example:
Shows data over time (like line graphs), but each item gets its own graph instead of showing all lines in one graph.
Example:
Visualizes both a number and the resulting bar width based on the number value.
Example:
Shows the results in a donut shape.
Example:
Shows the state of items as icons with different colors. You can display just the icons or together with a description. You can also use a background image and drag the icons into position on the image.
Example:
Shows the state of items as blocks with different colors.
Example:
The Date Heatmap shows the number of events per hour of the day. It uses lighter colors to show higher values and darker for lower values.
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.
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
SELECTCOUNT(*) from Alert.vAlert
You can use page timeframe variables in your SQL query if you want your query to consider the current page timeframe. For example WHERE TimeAdded >= {{timeframe.isoStart}}
You can use the clock insert time value button to insert page timeframe and date variables in your query.
Dynamic page timeframe formats
When you use page timeframe variables, the dynamic page timeframe will be inserted as a string in your search query, script, field, or wherever you use the variable.
timeframe.isoDuration
Use this format when you want to insert the page timeframe according to the ISO 8601 format
Example: When the page timeframe is set to "last 12 hours" the string PT12H is inserted.
timeframe.isoStart
Use this format to insert a "from" time when you want the starting point to be "now minus page timeframe". The page timeframe will be inserted as a starting time according to the ISO 8601 format
Example: When the page timeframe is set to "last 12 hours" the starting time is "now minus 12 hours".
timeframe.isoEnd
Use this format to insert a "to" time when you need to specify the end time. The end time is always "now". The current time will be inserted according to the ISO 8601 format
timeframe.unixStart
Use this format to insert a "from" time when you want the starting point to be "now minus page timeframe". The page timeframe will be inserted as a starting time in milliseconds according to the UNIX standard.
Example: When the page timeframe is set to "last 12 hours", the starting time is "now minus 12 hours".
timeframe.unixEnd
Use this format to insert a "to" time when you need to specify the end time. The end time is always "now". The current time will be inserted in milliseconds according to the Unix standard.
Math.floor(timeframe.UnixStart / 1000)
Use this format when you want to use the unixStart time but need to convert it from milliseconds to seconds.
Math.floor(timeframe.UnixEnd / 1000)
Use this format when you want to use the unixEnd time but need to convert it from milliseconds to seconds.
Fixed timeframe formats (without using the dynamic page timeframe)
Date.now() - 86400 * 1000 *14
This is a template format to express the fixed timeframe "14 days ago" (now minus 14 days in milliseconds). You can use this template to create your own fixed timeframe.
How to read the parameters of the template:
Date.now() = the current date and time (now) in milliseconds 86400 = 24 hours in seconds 1000 = converts the seconds into milliseconds 14 = 14 days
If you want to use a fixed timeframe in seconds, you need to convert the Date.now() into seconds, for example "4 days ago in seconds": (Date.now() /1000) - 86400 * 4.
new Date().toISOString()
A text based representation of "now". Use this format if you want to insert the date as a string in ISO 8601 format
Note: Using the SCOM object only works on SQL tiles on perspectives, SQL tiles on dashboards can't use the SCOM object.
When you are using the SQL tile on a perspective, the query string field shows you a mustache picker that you can use to insert dynamic properties into your search query. The available properties refer to the SCOM object ("context") that the perspective applies to.
Examples for dynamic properties of SCOM objects:
{{id}}
SCOM object ID
{{displayName}}
Display name of the SCOM object
{{properties.virtualMachineName}
Virtual Machine name of the server (class property)
Every time you view the perspective for a particular object, SquaredUp DS will automatically replace the dynamic properties and the SQL query will be executed with the actual values that apply to the object being viewed.
Example: Your viewing the perspective for the object Local Server, which has the DNS name myserver.domain.local.
Query with dynamic property:
SELECT*FROM owner WHERE server_name = {{properties.dnsName}}
Resolved query when viewing the perspective for the object Local Server:
SELECT*FROM owner WHERE server_name ='myserver.domain.local'
Notes for using dynamic properties
Dynamic properties can only be used for values in your SQL query - you can't use them to dynamically generate sub-clauses or other non-values.
Don't use quotes in or around the mustache syntax when referring to string values. For example, server_name = 'myserver.domain.local' becomes server_name = {{properties.dnsName}} in mustache syntax.
Mustaches used in SQL queries cannot contain any JavaScript (for example, string manipulation functions like .replace())
Mustaches may be used inside T-SQL expressions, for example: SELECT COUNT(*) FROM ManagedEntity WHERE [Name] LIKE '%' + {{name}} + '%';
Your query must return a single figure.
There are no special requirements for grid queries, since an SQL query will always return a grid.
Tip: How to convert a Unix timestamp column into a more readable format with your 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 10CONVERT(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
The return data must include a state column which must contain the following values:
healthy, critical, and/or warning (the values are not case-sensitive). Any other values will result in state unknown.
The state values define the color of the status icons or blocks:
Healthy
green
Warning
yellow
Critical
red
Unknown
gray
My return data doesn't contain a state column
If your return data doesn't contain a state column, you can define a state column in your query. You need to define which data you want to be interpreted as healthy, critical, and/or warning and use AS State to put the results in a state column.
Example:
SELECT Name, CASEWHEN Duration <500THEN'Healthy'ELSE'Critical'ENDAS State
My return data contains a state column, but different values
If your return data contains a state column, but the values are not healthy, critical, or warning, you need to map the values you want to use to the values healthy, critical, and/or warning.
Example:
SELECT Computer as Name,
Max(TimeGenerated) as max_TimeGenerated,
State as OriginalState,
CASEWHEN State ='up'THEN'Healthy'WHEN State ='down'THEN'Critical'ELSE'Warning'ENDAS State
FROM StateTable
GROUPBY Computer,
State
Your query must return as a minimum a DateTime field and a Numeric value field.
Column Overrides
Here you define how the returned data is displayed. Use the dropdowns to specify which data/column you want to use:
timestamp
Here you define the time series to be used for the visualization.
value
Here you define which value the graph will show. For example, if you want to see the response time of different servers, your metric value would be response time. If you want to see the number of tickets, your metric value would be number (of tickets).
show all: If there are multiple values, you can display these by ticking this box. Each value will get its own series (a line in a line graph, a bar in a bar graph etc.).
grouping
Here you can define a group to take a closer look at the graph's value(s). Each item in the group will create its own series (a line in a line graph, a bar in a bar graph etc.). For example, if you group the value response time by servers on a line graph, you'll see one line per server, each line showing you the response time for this one server over time.
Your query must return a numeric field for the value and a string for grouping.
Column Overrides
Here you define how the returned data is displayed. Use the dropdowns to specify which data/column you want to use:
value
Here you define which value the graph will show. For example, if you want to see the response time of different servers, your metric value would be response time. If you want to see the number of tickets, your metric value would be number (of tickets).
Only for Bar Graphs:
show all: If there are multiple values, you can display these by ticking this box. Each value will get its own series (a line in a line graph, a bar in a bar graph etc.).
grouping
Here you can define a group to take a closer look at the graph's value(s). Each item in the group will create its own series (a line in a line graph, a bar in a bar graph etc.). For example, if you group the value response time by servers on a line graph, you'll see one line per server, each line showing you the response time for this one server over time.
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.
Configure the settings for your visualization:
Scalar
Font size
Allows you to set the font size of the value in the tile.
Alignment
Select the scalar text alignment. Choose from left, center or right.
Unit
Allows you to add a unit to the value displayed in the Scalar tile. For example, if your value shows a time in milliseconds, you can enter "ms" or if your value shows pageviews, you can enter "pageviews".
Value formatter
Allows you to format the value by using the mustache picker. For example, you can round the value up or down or convert it.
Color
Conditional formatting:
You can display the data in different colors based on values you define here. For example, you can display the data in green when the value is below 100 and in red when it is above 100.
Click on add to configure a condition.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the value property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Value is greater than something, less than something, etc. For example: {{value < 10}} (The color you pick will be used if the value is less than 10)
Value is present in the result (scalar tiles only) For example: value.IndexOf('error') != -1 (The color you pick will be used if the string value "error" is present in the results)
Value matches one of the regular expressions you defined (scalar tiles only) For example: value.match(/healthy|good|up/) (The color you picked will be used if the string values are healthy, good, or up)
Display:
Here you decide how the color is used:
Tile background
Highlight the tile in the color you defined.
Text foreground
Display the text in the color you defined.
Link options
Allows you to turn the graph item(s) into links. You can either enter plain text to create a fixed link (URL always stays the same) or use dynamic properties to create a dynamic link.
Dynamic links make use of dynamic properties which are inserted as part of the URL. This creates a template URL that will be resolved to an actual URL based on the items properties.
For example, if you want to link to tickets in your ticket system and the format of the URL for tickets in your system is https://www.my-system/ticket-123, where 123 is the ticket ID, you can use the dynamic property that contains the ticket ID and enter the dynamic URL https://www.my-system/ticket-{{ticketID}}.
For scalars, you can only use the dynamic property value in dynamic links, which means the link changes when the value of the scalar changes. Since a scalar is just one item, it would also make sense to use a fixed link, for example the link to the website of which you are displaying the response time.
For status icon or bars and the rows of a grid, you usually want to use a dynamic link since you get multiple items or rows that represent different things. You can use any of the dynamic properties the mustache picker offers you.
Dynamic mustache properties and values you need to change according to your instance are highlighted in bold.
Allows you to turn the graph item(s) into links. You can either enter plain text to create a fixed link (URL always stays the same) or use dynamic properties to create a dynamic link.
Dynamic links make use of dynamic properties which are inserted as part of the URL. This creates a template URL that will be resolved to an actual URL based on the items properties.
For example, if you want to link to tickets in your ticket system and the format of the URL for tickets in your system is https://www.my-system/ticket-123, where 123 is the ticket ID, you can use the dynamic property that contains the ticket ID and enter the dynamic URL https://www.my-system/ticket-{{ticketID}}.
For scalars, you can only use the dynamic property value in dynamic links, which means the link changes when the value of the scalar changes. Since a scalar is just one item, it would also make sense to use a fixed link, for example the link to the website of which you are displaying the response time.
For status icon or bars and the rows of a grid, you usually want to use a dynamic link since you get multiple items or rows that represent different things. You can use any of the dynamic properties the mustache picker offers you.
Dynamic mustache properties and values you need to change according to your instance are highlighted in bold.
You can choose between showing or hiding the header for all columns.
Expand rows automatically
Activate this checkbox if you want the row height to expand automatically based on the row content, for example if your grid uses increased text size, images, emojis etc.
Limit number of results displayed
You can set a limit of the initial number of results displayed in the grid. If you have set a limit and there are more results to display, users will see a "show all" button below the grid.
Font size
Use the slider to adjust the font size.
Tip for column sizing: You can change the column width directly in the grid by clicking on the divider lines between columns and dragging them to the width you want. You need to show column headers (by activating the show column headers check box) to be able to change the column width. Resizing columns while in edit mode affects how the grid looks by default when users open the dashboard. Users can temporarily change the column sizes by dragging them, but those changes only last until they leave the page.
Threshold
You can choose to apply a threshold line at a specified value, and whether you wish to fill above or below this value, or just show the line. For example, for free disk space you might want to fill below the line to highlight when space goes below a particular threshold. For processor information you might want to fill above the line to highlight when processor percentage goes above that threshold. The threshold is also shown on the drilldown view.
Max, min, avg
When drilled-down to view a graph, you can select the min, max and avgoptions for each object (displayed to the right of the graph), which displays a line cutting horizontally across the graph a each of the selected value points.
Data range
The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.
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
Allows you to specify the min and max
custom fit
Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.
Display
Height:
Allows you to set the height of the tile with a slider.
Show hover details:
Shows the value for all lines at any point you hover. There may not be a value exactly where you hover so the value is interpolated from the values either side.
Show points:
Shows where the data points are on the line. Useful to identify missing points, or detail for changing data.
Show trend
Enable the Show Trend Linestoggle to display a trend line for the line graph data. Disable the toggle to hide the trend line.
Custom colors:
You can display the data in different colors based on labels. For example, you can display data in green for a specific user.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the label property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Condition is true if the label contains something For example: {{label.indexOf('SQL') != -1}} (The color you pick will be used if the label contains 'SQL')
Condition is true if the label contains multiple things For example: {{label.match(/C:|D:|E:/) != null}} (The color you pick will be used if the label contains 'C:', 'D:' or 'E:')
Condition is true if the label contains multiple things with multiple variations For example: {{label.match(/^[Ss]erver[0-9]+$/) != null}} (The color you pick will be used if the label is 'Server' or 'server' with a number after it)
Label
Allows you to change the label of the results.
Show legend:
Allows you to show or hide the legend of the graph.
Label:
auto
Choose this option if you want to use the default label that has been created automatically.
custom
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
You can choose to apply a threshold line at a specified value, and whether you wish to fill above or below this value, or just show the line. For example, for free disk space you might want to fill below the line to highlight when space goes below a particular threshold. For processor information you might want to fill above the line to highlight when processor percentage goes above that threshold. The threshold is also shown on the drilldown view.
Data range
The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.
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
Allows you to specify the min and max
custom fit
Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.
Display
Height:
Allows you to set the height of the tile with a slider.
Show hover details:
Shows the value for all lines at any point you hover. There may not be a value exactly where you hover so the value is interpolated from the values either side.
Solid bars:
Show the bars as solid color or translucent.
Custom colors:
You can display the data in different colors based on labels. For example, you can display data in green for a specific user.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the label property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Condition is true if the label contains something For example: {{label.indexOf('SQL') != -1}} (The color you pick will be used if the label contains 'SQL')
Condition is true if the label contains multiple things For example: {{label.match(/C:|D:|E:/) != null}} (The color you pick will be used if the label contains 'C:', 'D:' or 'E:')
Condition is true if the label contains multiple things with multiple variations For example: {{label.match(/^[Ss]erver[0-9]+$/) != null}} (The color you pick will be used if the label is 'Server' or 'server' with a number after it)
Label
Allows you to change the label of the results.
Show legend:
Allows you to show or hide the legend of the graph.
Label:
auto
Choose this option if you want to use the default label that has been created automatically.
custom
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.
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
Allows you to specify the min and max
custom fit
Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.
Label
Allows you to change the label of the results.
auto
Choose this option if you want to use the default label that has been created automatically.
custom
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
If you turn color matching on, one item (a specific resource, object, site, anything you are displaying in your graphs) is shown in the same color in different graphs on one dashboard or one perspective. You can use either color matching or custom colors (colors based on values), they cannot both be used at the same time.
custom colors
Setting colors based on values
You can display the data in different colors based on values you define here. For example, you can display the data in green when the value is below 100 and in red when it is above 100.
Click on add to configure a condition.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the value property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Value is greater than something, less than something, etc. For example: {{value < 10}} (The color you pick will be used if the value is less than 10)
Value is present in the result (scalar tiles only) For example: value.IndexOf('error') != -1 (The color you pick will be used if the string value "error" is present in the results)
Value matches one of the regular expressions you defined (scalar tiles only) For example: value.match(/healthy|good|up/) (The color you picked will be used if the string values are healthy, good, or up)
You are able to combine value matching with label matching.
Setting colors based on labels
You can display the data in different colors based on labels. For example, you can display data in green for a specific user.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the label property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Condition is true if the label contains something For example: {{label.indexOf('SQL') != -1}} (The color you pick will be used if the label contains 'SQL')
Condition is true if the label contains multiple things For example: {{label.match(/C:|D:|E:/) != null}} (The color you pick will be used if the label contains 'C:', 'D:' or 'E:')
Condition is true if the label contains multiple things with multiple variations For example: {{label.match(/^[Ss]erver[0-9]+$/) != null}} (The color you pick will be used if the label is 'Server' or 'server' with a number after it)
You are able to combine label matching with value matching.
Data Range
The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.
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
Allows you to specify the min and max
custom fit
Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.
Value
Value formatter
Allows you to format the value by using the mustache picker. For example, you can round the value up or down or convert it.
Label
Allows you to change the label of the results.
auto
Choose this option if you want to use the default label that has been created automatically.
custom
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
If you turn color matching on, one item (a specific resource, object, site, anything you are displaying in your graphs) is shown in the same color in different graphs on one dashboard or one perspective. You can use either color matching or custom colors (colors based on values), they cannot both be used at the same time.
custom colors
Setting colors based on values
You can display the data in different colors based on values you define here. For example, you can display the data in green when the value is below 100 and in red when it is above 100.
Click on add to configure a condition.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the value property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Value is greater than something, less than something, etc. For example: {{value < 10}} (The color you pick will be used if the value is less than 10)
Value is present in the result (scalar tiles only) For example: value.IndexOf('error') != -1 (The color you pick will be used if the string value "error" is present in the results)
Value matches one of the regular expressions you defined (scalar tiles only) For example: value.match(/healthy|good|up/) (The color you picked will be used if the string values are healthy, good, or up)
You are able to combine value matching with label matching.
Setting colors based on labels
You can display the data in different colors based on labels. For example, you can display data in green for a specific user.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the label property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Condition is true if the label contains something For example: {{label.indexOf('SQL') != -1}} (The color you pick will be used if the label contains 'SQL')
Condition is true if the label contains multiple things For example: {{label.match(/C:|D:|E:/) != null}} (The color you pick will be used if the label contains 'C:', 'D:' or 'E:')
Condition is true if the label contains multiple things with multiple variations For example: {{label.match(/^[Ss]erver[0-9]+$/) != null}} (The color you pick will be used if the label is 'Server' or 'server' with a number after it)
You are able to combine label matching with value matching.
Sort
Sort allows you to change the order of the results displayed. You can sort by value (ascending or descending) or label (alphabetically ascending or descending).
Value formatter
Allows you to format the value by using the mustache picker. For example, you can round the value up or down or convert it.
Display
Size mode:
Default
Displays the donut scaled to the height of the tile.
Fill
Enlarges the donut to use the whole width of the tile. If you chose the fill option and show the legend, you can define the size of the legend with a slider.
Show legend:
Allows you to show or hide the legend of the graph.
Table or Inline:
Show the legend as a separate table or as labels pointing to the segments. When using Inline you can also hide the segment values, and use the slider to change the size of the labels.
Show zero values in legend:
Will show legend items for values of zero which are otherwise missing from the donut.
Fixed height scrollable legend:
Sets the legend to a fixed height where you can scroll through the items. This means that the tile doesn't become too large if there are many items.
Display mode:
Allows you to switch between displaying absolute values or percentages.
Color palette:
Here you can choose between different color palettes.
Note: If there are more items than colors, the colors repeat from the beginning.
Tip for displaying priorities or health states: If you want to display priorities or health states from a data source that doesn't enrich the data with information about priority or health (like the SQL tile or external APIs), use the custom color option and map the results to the correct color. This way, you can make sure that healthy or low priority results are displayed in green, unhealthy or high priority results are displayed in red, etc. If you use the color palettes Priorities, Health1, or Health2 the colors get assigned depending on how the results are sorted, which doesn't guarantee that the colors make sense for the priority or state they represent.
General
10 different colors without specific meaning
Priorities
5 different colors representing 5 different priority states
Health1
3 different colors representing 3 different health states (red=unhealthy, green=healthy, gray=unknown)
Health2
4 different colors representing 4 different health states (red=critical, orange=unhealthy, green=healthy, gray=unknown)
Pastel
10 different pastel colors without specific meaning
Blue
4 different shades of blue from dark to light
Orange
4 different shades of orange from dark to light
Green
4 different shades of green from dark to light
Pink
4 different shades of pink from dark to light
Custom
Setting colors based on values
You can display the data in different colors based on values you define here. For example, you can display the data in green when the value is below 100 and in red when it is above 100.
Click on add to configure a condition.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the value property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Value is greater than something, less than something, etc. For example: {{value < 10}} (The color you pick will be used if the value is less than 10)
Value is present in the result (scalar tiles only) For example: value.IndexOf('error') != -1 (The color you pick will be used if the string value "error" is present in the results)
Value matches one of the regular expressions you defined (scalar tiles only) For example: value.match(/healthy|good|up/) (The color you picked will be used if the string values are healthy, good, or up)
You are able to combine value matching with label matching.
Setting colors based on labels
You can display the data in different colors based on labels. For example, you can display data in green for a specific user.
Click on select color.... to open the color picker. Select the color for this condition.
Enter your condition in the field next to the color. You can use the label property and manipulate it with JavaScript String and Regex APIs. When you click on the mustache picker, you'll get some examples:
Condition is true if the label contains something For example: {{label.indexOf('SQL') != -1}} (The color you pick will be used if the label contains 'SQL')
Condition is true if the label contains multiple things For example: {{label.match(/C:|D:|E:/) != null}} (The color you pick will be used if the label contains 'C:', 'D:' or 'E:')
Condition is true if the label contains multiple things with multiple variations For example: {{label.match(/^[Ss]erver[0-9]+$/) != null}} (The color you pick will be used if the label is 'Server' or 'server' with a number after it)
You are able to combine label matching with value matching.
Link options
item link:
Allows you to turn the graph item(s) into links. You can either enter plain text to create a fixed link (URL always stays the same) or use dynamic properties to create a dynamic link.
Dynamic links make use of dynamic properties which are inserted as part of the URL. This creates a template URL that will be resolved to an actual URL based on the items properties.
For example, if you want to link to tickets in your ticket system and the format of the URL for tickets in your system is https://www.my-system/ticket-123, where 123 is the ticket ID, you can use the dynamic property that contains the ticket ID and enter the dynamic URL https://www.my-system/ticket-{{ticketID}}.
For scalars, you can only use the dynamic property value in dynamic links, which means the link changes when the value of the scalar changes. Since a scalar is just one item, it would also make sense to use a fixed link, for example the link to the website of which you are displaying the response time.
For status icon or bars and the rows of a grid, you usually want to use a dynamic link since you get multiple items or rows that represent different things. You can use any of the dynamic properties the mustache picker offers you.
Dynamic mustache properties and values you need to change according to your instance are highlighted in bold.
Choose this option if you want to use the default label that has been created automatically.
custom
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
Sort allows you to change the order of the results displayed. You can also group them by their characteristics.
default
By default, the sorting of objects depends on the data source. This can be alphabetical sorting or the order in which data comes back from an API request.
sort by
Sort by label, health state, or health state + availability where objects are sorted by availability (offline or maintenance mode) as well as health state. Ascending or descending
group by
Group by label, health state, or health state + availability where objects are Grouped by availability (offline or maintenance mode) as well as health state, for example Error (Available) and Error (Unavailable). Ascending or descending
Limit:
Allows you to define a maximum number of objects that will be shown. When 'group by' is used the limit applies to each group individually, for example to show 10 objects in each health state.
Image
Here you can choose one of the provided images or upload your own. Tip: If you want a different selection of maps, you can download more at https://freevectormaps.com/
Supported image formats: png, jpg, jpeg, gif, tif, tiff. svg, bmp Tip: SVG images resize best since they are vector images.
File size limit: 10MB
Image size: Images fill the size of the tile, which means you can resize the image by adjusting the tile's size. The size of the tile also depends on the screen the dashboard is being viewed on.
Icons
Here you can customize the icons on the image:
You can change the size of the icons with the slider
You can change the shape of the icons (square or circle)
You can drag the icons on the image into position
Display styles for Status icons
This setting is not done in a panel, you can change the display style even after you finished configuring the tile.
You can use toggle zoom button at the top right of the tile to change between the different ways Status icons can be displayed.
One long list
Column list
Icons only
Link options
item link:
Allows you to turn the graph item(s) into links. You can either enter plain text to create a fixed link (URL always stays the same) or use dynamic properties to create a dynamic link.
Dynamic links make use of dynamic properties which are inserted as part of the URL. This creates a template URL that will be resolved to an actual URL based on the items properties.
For example, if you want to link to tickets in your ticket system and the format of the URL for tickets in your system is https://www.my-system/ticket-123, where 123 is the ticket ID, you can use the dynamic property that contains the ticket ID and enter the dynamic URL https://www.my-system/ticket-{{ticketID}}.
For scalars, you can only use the dynamic property value in dynamic links, which means the link changes when the value of the scalar changes. Since a scalar is just one item, it would also make sense to use a fixed link, for example the link to the website of which you are displaying the response time.
For status icon or bars and the rows of a grid, you usually want to use a dynamic link since you get multiple items or rows that represent different things. You can use any of the dynamic properties the mustache picker offers you.
Dynamic mustache properties and values you need to change according to your instance are highlighted in bold.
Choose this option if you want to use the default label that has been created automatically.
custom
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
Here you can change the label to a custom label. You can use static text and dynamic properties. Use the mustache picker to select dynamic properties from the response data to use them as labels.
Sort allows you to change the order of the results displayed. You can also group them by their characteristics.
default
By default, the sorting of objects depends on the data source. This can be alphabetical sorting or the order in which data comes back from an API request.
sort by
Sort by label, health state, or health state + availability where objects are sorted by availability (offline or maintenance mode) as well as health state. Ascending or descending
group by
Group by label, health state, or health state + availability where objects are Grouped by availability (offline or maintenance mode) as well as health state, for example Error (Available) and Error (Unavailable). Ascending or descending
Limit:
Allows you to define a maximum number of objects that will be shown. When 'group by' is used the limit applies to each group individually, for example to show 10 objects in each health state.
Blocks
Here you can set the number of columns for the blocks, their height and the font size within the blocks.
Display
Choose to use squares or circles, and optionally whether to hide weekends, week days and/or empty days.
Color
Here you can choose the color scheme for your heatmap. Higher values are shown in a lighter shade of the color, and lower values in darker shades.
Data Range
The Data Range option allows you to choose the range of data the graph will display. For line graphs, this means the data on the y-axis.
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
Allows you to specify the min and max
custom fit
Allows you to specify the min and max limits, so that data outside your settings will not be shown. If all the data falls within your specified ranges then the y-axis range will fit to the data rather than your caps.
Click done to save the tile.
The tile now shows data according to your settings.
In this walkthrough, we will create a simple SQL Scalar tile to show the number of objects being monitored by SCOM:
For this walkthrough we'll create a new dashboard. In SquaredUp DS 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.
Create a new SQL tile on a dashboard.
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
INNERJOIN Alert.vAlertDetail AS adt ON ars.AlertGuid=adt.AlertGuid
INNERJOIN Alert.vAlert AS alt ON ars.AlertGuid=alt.AlertGuid
INNERJOIN vManagedEntity ON alt.ManagedEntityRowId=vManagedEntity.ManagedEntityRowId
WHERE alt.AlertName='Available Megabytes of Memory is too low'AND alt.RaisedDateTime >= DATEADD(day,-7, GETDATE()) ORDERBY 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 example Date.
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.
Click done and then next.
You can use the grid optionsrow link option to add a hyperlink to each row. Edit the URL below to insert your SquaredUp server name, or correct SquaredUp DS URL, and then paste into the row link box. http://SquaredUpServerName/SquaredUp/drilldown/scomobject?value=
Click on the mustache helper button
Click on displayName.
Click done.
Test that the row linking works.
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.
Add a new tile to a dashboard, 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 StartDateDECLARE@StartDate datetime
SET@StartDate= ISNULL(@StartDate, cast(cast(cast(DATEADD(DAY,-30,GetDate()) asint) asfloat) as datetime));
--Temp table to store our Top 5 EventsDECLARE@TopServersTABLE(
ComputerName nvarchar(255)
, LoggingComputerRowId int, CountOfEvents int)
--Find the servers that are collecting the most eventsINSERTINTO@TopServersSELECT TOP 5elc.ComputerName as ComputerName
, e.LoggingComputerRowId as LogginComputerRowID
, Count(EventDisplayNumber) as CountOfEvents
FROM [Event].[vEvent] as e
INNERJOIN vEventLoggingComputer as elc ON e.LoggingComputerRowId = elc.EventLoggingComputerRowId
WHERE datetime >=@StartDateGROUPBY elc.ComputerName, e.LoggingComputerRowId
ORDERBY CountOfEvents DESC--Find our actual recordsSELECTts.ComputerName
, CAST(CONVERT(VARCHAR(20), DateTime, 102) As DateTime) as DateTime
, Count(e.EventDisplayNumber) asvalueFROM [Event].[vEvent] AS e
INNERJOIN@TopServersAS ts ON e.LoggingComputerRowId = ts.LoggingComputerRowId
WHERE DateTime >=@StartDateGROUPBY 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.
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.
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.
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.
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 10CONVERT(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:
One long list
Column list
Icons only
