Blog Viewer

UIM Dashboards - How to create a Line Series Chart from SQL

By BryanKMorrow posted 09-10-2015 11:53 AM

  

As some people are still new to HTML 5 Dashboard options in UIM, I wanted to give a brief example of using the Line Chart Widget.

 

First we will start with the Line Chart; this will provide you the ability to create a time series chart based on either a QOS metric or an SQL query. In this example I will be providing the SQL example.

 

Step 1 – Drag the Line Chart widget onto the dashboard canvas. This will leave give you a blank widget.

 

line_chart_blank.png

 

Step 2 – Configure the Chart

  • Give it a Y-Axis Label. In my case that is Messages Per Minute
  • Give it a Series Duration, I will be using 1 hour

 

line_chart_widget_chart.png

 

Step 3 – Configure the Series

  • Highlight the Line Chart widget on the canvas (will have a red border).
  • Press the + icon under the Series navigation section on the right column.
  • In the Series Data Source Type dropdown, select SQL.
  • Press the + icon under the SQL data source to create the datasource.
  • Give the Query a name, select the NIS option from the database dropdown.
  • Paste and Test your query.

In this example I am returning the last hour with the following query:

 

select d.sampletime, d.samplevalue as posted from S_QOS_DATA s join RN_QOS_DATA_1730 d on s.table_id=d.table_id where s.target = 'WorldWideHQ' and d.sampletime > DATEADD(HOUR, -1, GETDATE())

 

 

I like to go back and rename the Series 1 that was created earlier, this will help if you are creating your own legend.

 

       Repeat the steps required to create multiple series if needed.

 

NOTE: The format of the SQL query needs to return two columns, the first being the sampletime and the second being the value.

 

Step 4 – Create a custom legend. The current version of the dashboard does not have a legend associated with the Line Chart, so we will need to create one.

  • Drag a Rectangle Widget onto the canvas underneath your Line Chart.
  • Change the size to be appropriate to your Line Chart.
  • Change the color to match the Series.
    • Series 1: #1F77B4
    • Series 2: #FF7F0E
    • Series 3: #2CA02C
    • Series 4: #D62728
    • Series 5: #9467BD
    • Series 6: #8C564B
  • Give the Widget a Label to match the Series.

 

Clone the Rectangle Widget and repeat for each Series.

 

hub_health_line_chart_example.png

14 comments
13 views

Comments

06-14-2017 03:40 PM

Great!

04-12-2017 11:08 AM

Hello ,

 

Could you please supply SQL query for fetching  disk utilization for UNIX Servers.

09-06-2016 05:19 AM

Hi Bryan,

 

Could you please help me convert this for disk usage. My QOS-name is QOS_AS400_DISK_USAGE.

Also is this query for using Content Selector?

07-21-2016 09:23 AM

Thank you!!
What would it look like if I want to see, let's say QOS-data for CPU or Memory the last hour?

07-20-2016 09:49 AM

Here is a dynamic example, it queries the s_qos_Data table for your devices and then uses that variable in the query. This example is creating an availability uptime for the url_response probe but could easily be modified to fit your needs.  If you need me to convert it to a query like the example let me know. Also, sorry for the delay.

 

DECLARE @SQL NVARCHAR(1000)

DECLARE @RTABLE VARCHAR(50)

SET @RTABLE = (select distinct r_Table from S_QOS_DATA where qos='qos_url_response')

SET @SQL = '

  select

  [Target],

  [Available],

  [NotAvailable],

  CAST(CAST([Available] * 100.0 AS Numeric(10,2)) / sum([Available]+[NotAvailable]) as Numeric(10,2)) as [Percentage]

  from

  (

  select s.target as [Target],

  sum(case when [value] IS NOT NULL then 1 else 0 end) as [Available],

  sum(case when [value] IS NULL then 1 else 0 end) as [NotAvailable]

  from S_QOS_DATA s

  join (select

  DATEPART(yyyy, r.sampletime) as [sampleyear],

  DATEPART(mm, r.sampletime) as [samplemonth],

  DATEPART(dd, r.sampletime) as [sampleday],

  r.samplevalue as [value],

  r.sampletime as [time],

  r.table_id

  from ' +@RTABLE+ ' r

  join (select distinct table_id from ' + @RTABLE + ') rr

  on r.table_id = rr.table_id

  ) rx

  on s.table_id = rx.table_id

  group by s.target

  ) b

  group by b.target,b.Available,b.NotAvailable

  '

EXECUTE sp_executesql @SQL

07-20-2016 09:16 AM

Same here. Can't get it to work. But i'm also not so good at SQL

07-18-2016 11:42 AM

Hello,

 

Is this for mssql? It seems I have the same issue as you (trying to find rn tbales dynamically) but the sq you provided errors out. Also the "DATEADD(HOUR, -72, GETDATE())'" does not seem to jive with this dashboard query. Any input is appreciated!

 

Thanks

 

A

04-22-2016 08:48 AM

I found a way to do this

 

declare @rnqdt varchar(250)

declare @query nvarchar(1000)

set @rnqdt = (select r_table from s_qos_data where target = 'Availability' and source like '${device}')

set @query = 'select rt.sampletime, rt.samplevalue as posted from S_QOS_DATA q join '

set @query = @query + @rnqdt

set @query = @query + ' rt on q.table_id = rt.table_id where q.target = ''Availability'' and q.source like '${device}' and rt.sampletime > DATEADD(HOUR, -72, GETDATE())'

 

exec sp_executesql @query

04-20-2016 02:22 PM

Yes, sorry. I was not clear enough. The thing is how to make it dynamic. I mean, how to get the value from the rn_table column an use it replacing a variable to join the tables. Something like this:

 

select d.sampletime, d.samplevalue as posted from S_QOS_DATA s 

join $rnqosdtable d 

on s.table_id=d.table_id 

where s.target = 'Availability'

04-20-2016 01:51 PM

You will have to look at the S_QOS_DATA table, it will be shown there in the rn_table. 

04-20-2016 01:50 PM

How do you know which RN_QOS_DATA table you need to take the data from?

09-15-2015 12:02 PM

thanks for sharing this with the community BryanKMorrow

09-10-2015 02:31 PM

Garin,

 

It is not possible at this point to generate the series dynamically or via parameter, but you can use the parameters in the SQL queries.

 

- Bryan

09-10-2015 01:23 PM

Huge thank you for this sort of thing. Love to see it.

 

Now you teased me because you addressed 2/3rds the problem I was having with the new widget.

 

Is there a way to extend this so that you don't need to know ahead of time how many series there might be on the chart? For instance, to take advantage of the the $origins parameter that was added recently? I need to duplicate the functionality of the "Filter" tab on the Performance Report portlet but on a dashboard if possible.

 

-Garin