How to change the value of a Text widget on a Custom Dashboard based on a QoS metric value

Document ID : KB000112561
Last Modified Date : 30/08/2018
Show Technical Document Details
Introduction:
Need to be able to change the value of a Text widget based on the value of a QoS metric.
Background:
The QoS metric is always a numberic value, but the dashboard needs to display a text string based on the value of the QoS metric.
For example, a database state metric is collected and has a value of 1 (online) or 0 (offline).
The Text widget on the dashboard should display OnLine when the QoS value is 1 and OffLine if the value is 0.
Environment:
UIM/UMP 8.51
Instructions:
This can be done by associating a SQL datasource to the Text widget on the custom dashboard as follows:

1.  Add the Text Widget to the custom dashboard

Drag text widget to custom dashboard

2.  Select the text widget on the dashboard canvas, select the Widget Properties (wrench) from the toolbar, then change the Default Value to $VAR

Select the text widget on the dashboard canvas, select the Widget properties (wrench) from the toolbar, then change the Default text value to $VAR

3.  For the Data Source Type, select SQL from the drop down list:

For the datasource Type, select SQL from the drop down list

4.  Select the plus sign in the Data Source section to add a new query, then in the Create New Query dialogue, provide a name for the query, use the nis database, then provide the query to use.  For example, to use the QOS_SQLSERVER_CHECK_DBALIVE QoS metric to report the state of the monitored sqlserver database, you can use a SQL query similar to the following:

SELECT
CASE
WHEN
CAST((s.samplevalue) AS varchar(10)) = '0.00' THEN 'OffLine'
WHEN
CAST((s.samplevalue) AS varchar(10)) = '1.00' THEN 'OnLine'
END AS state
FROM S_QOS_DATA d
JOIN S_QOS_snapshot s
ON d.table_id = s.table_id
WHERE d.probe = 'sqlserver' and d.qos = 'QOS_SQLSERVER_CHECK_DBALIVE' and d.source = 'db_robot' amd d.target = 'my_database';

Select the plus sign in the Data Source section to add a new query, then in the Create New Query dialogue, provide a name for the query, use the nis database, then provide the query to use.

5.  Test the query to make sure it returns the expected value:

Test the query to make sure it returns the expected string

6.  Select the Create button to create the SQL Data Source for the text widget:

Setect the Create button to create the SQL Data Source for the text widget


Make any other text field property changes for the text widget as required.
To view the final dashboard, select the Live View option from the Dashboard dropdown list.
Save and publish the dashboard once it is completed.