DX UIM Business Intelligence Multi-Line Chart widget

Products

DX Unified Infrastructure Management (Nimsoft / UIM)

Issue/Introduction

The DX UIM Business Intelligence Portlet that ships with DX UIM 23.4.6 and higher includes a new Multi-Line Chart Widget.

The Multi-Line Chart can be used to display multiple series at once, similar to the existing Line Chart widget, but with more flexibility for creating dynamic reports.

Two data sources are supported:

ODATA API
SQL

Both datasources require a return of values labeled as "sampletime" and "samplevalue" to create a line chart.

This article explores the use of SQL data sources. For information on using ODATA sources for this widget, see this KB.

Environment

DX UIM - 23.4.6 (aka 23.4 CU6) or higher

DX UIM Business Intelligence (UIM BI) portlet (formerly Dashboard Designer)

Resolution

In this example, we will create a simple dashboard that shows CPU and Memory Usage data for a selected set of robots.

The Multi-Line Widget is intended to be used alongside a Context Selector widget, so we will set that up first.

Drag and drop a Context Selector to your canvas.
Select the widget and navigate to Widget Properties (wrench icon).
Click the Plus (+) above to add another selector of type “Date Time”.
For the parameter name on this selector enter: StartDate
Double-Click "Date Time" to rename the selector and rename it to something like "Start Date".
Repeat steps 3-5, creating another Date Time selector. Name this one "End Date" and for the parameter name enter: EndDate
Finally, click the Plus (+) one more time and this time, add a "Drop Down" selector. Double-Click the selector name and rename it something like "Device Name". For this selector, choose the following options:
Data Type: String
Sorting Options: No Sort
Parameter Name: CsIds
Select the bullet for “Get values from SQL data source”, select the “nis” database option and click the Plus (+) to create a new query.
Name the query something like “Get Devices”. Use the following query to get a list of all devices and their CsIds:

select distinct name,cs_id from cm_computer_system

Click Create after testing the query.

At this point, save your dashboard and go to Live View. Click the Device Name dropdown and verify that it shows your list of devices.

Setting Up The Multi-Line Chart

Once you have verified that the dropdown list of devices is working, edit the dashboard and follow these steps:

Drag-and-drop the Multi-Line Chart Widget to your Canvas.
Navigate to the Widget Properties (wrench icon).
Under the "Chart" section give the chart an appropriate title, for example "CPU Usage". If desired, adjust the other options here as you see fit. For example, on a chart that shows percentages, you might set a Min. Value of 0 and a Max Value of 100.
Under “Data Source”, set the type to SQL and choose the “nis” database. Click the Plus (+) to add a new query.
You will need to determine the name of the RN Table which contains the data you need in order to formulate the query. This can be obtained from the S_QOS_DATA table. You can run the following query against your UIM Database:

select r_table from S_QOS_DATA where qos = ‘QOS_NAME’;

For example:

select r_table from S_QOS_DATA where qos = ‘QOS_CPU_USAGE’;

The value returned will need to be plugged into the following query – in this example the value returned was RN_QOS_DATA_0009.
```
select q.source, rn.sampletime, rn.samplevalue
from S_QOS_DATA q, RN_QOS_DATA_0009 rn
where q.table_id = rn.table_id
and rn.sampletime > '${StartDate}'
and rn.sampletime < '${EndDate}'
and q.source in (select name from cm_computer_system where cs_id in (${CsIds}))
```
If you click Test Query you may receive an error related to the CsId’s parameter. To avoid this, select one or more systems in your Device Name dropdown, then go back and Edit the query and test it.
Click Create to save the query.

Save the dashboard again and go to Live View for testing.

At this point you should have a working dashboard that allows you to select one or more devices from the Drop Down and set a start and end date and populate the chart.

To add additional charts to display e.g. Memory and Disk metrics, repeat the same steps, substituting the appropriate RN Table value in the query as needed.

Additional Information

If you want to use a dropdown to select time frames like "Last Hour", "Last Week", and so on instead of selecting specific dates, this is possible with the following considerations:

1. You cannot use both the "Time Range" drop down and the Start/End Dates in the same context selector when using an SQL Data Source. If you want to use both, you can do so with the ODATA version. With SQL you have to choose one or the other.

2. Use the "Time" selector and not the "TimeRange" selector.

3. Alter the query used as follows:

select q.source, rn.sampletime, rn.samplevalue
from S_QOS_DATA q, RN_QOS_DATA_#### rn
where q.table_id = rn.table_id
and rn.sampletime > dateadd (SECOND, ${startTime}/1000, '1970-1-1') 
and rn.sampletime < dateadd (SECOND, ${endTime}/1000, '1970-1-1') and
and q.source in (select name from cm_computer_system where cs_id in (${CsIds}))