DX Infrastructure Manager

UIM Dashboard Example - Network Device Summary with Context Selector and Metric Conversion

By BryanKMorrow posted 10-14-2015 02:43 PM


As a follow-up to the Executive Level Scorecard dashboard example posted previously, this is a summary of a network device. It contains a context selector that pulls from devices being polled by SNMP Collector. It also has some predefined parameters that allow you to modify the interface utilization metrics that are collected in bytes, allowing you to convert to kilobytes, megabytes or gigabytes.






As you can see its the same type of information that can be found in USM, but in what I feel is a more presentable format.


  • All the data is driven by the context selector at the top right.
  • All of the datasources are SQL driven to use the context selector.
  • The KiloBytes In and Kilobytes Out columns are modified using a parameter






select distinct source from s_Qos_Data where probe = 'pollagent' order by source asc  


This query retrieves all devices that are collecting data from the snmp_collector probe






As you can see its driven by a SQL datasource, pulling from the S_QOS_SNAPSHOT table.


select s.samplevalue from s_Qos_data d join s_qos_snapshot s on d.table_id = s.table_id where d.source = '${device}' and d.qos = 'qos_cpu_utilization'  







Here I am just mapping the column headers to the column output and adjusting the width so the information fits without a horizontal scrollbar


I am by no means an SQL expert so this query may not be very optimized but it works!


NOTE: This is for MSSQL, queries for MYSQL and Oracle will most likely need to be adjusted.


select i.interface, i.utilization_in, gg.bytes_in / ${kilo} as bytes_in, i.utilization_out, gg.bytes_out / ${kilo} as bytes_out, i.errors_in, i.errors_out, j.discards_in, j.discards_out from (select g.interface, g.utilization_in, g.utilization_out, h.errors_in, h.errors_out from   (select a.interface, a.utilization_in, b.utilization_out from   (select z.target as interface, y.samplevalue as utilization_in from s_Qos_data z   join s_qos_snapshot y   on z.table_id = y.table_id   where z.source = '${device}' and z.qos = 'qos_interface_utilizationin') a     INNER JOIN     (select x.target as interface, w.samplevalue as utilization_out from s_Qos_data x   join s_qos_snapshot w   on x.table_id = w.table_id   where x.source = '${device}' and x.qos = 'qos_interface_utilizationout') b   on a.interface=b.interface ) g   INNER JOIN     (select c.interface, c.errors_in, d.errors_out from   (select v.target as interface, u.samplevalue as errors_in from s_Qos_data v   join s_qos_snapshot u   on v.table_id = u.table_id   where v.source = '${device}' and v.qos = 'qos_interface_pcterrorsin') c     INNER JOIN     (select t.target as interface, s.samplevalue as errors_out from s_Qos_data t   join s_qos_snapshot s   on t.table_id = s.table_id   where t.source = '${device}' and t.qos = 'qos_interface_pcterrorsout') d   on c.interface=d.interface) h   on g.interface=h.interface) i   INNER JOIN     (select e.interface, e.discards_in, f.discards_out from   (select p.target as interface, o.samplevalue as discards_in from s_Qos_data p   join s_qos_snapshot o   on o.table_id = p.table_id   where p.source = '${device}' and p.qos = 'qos_interface_pctdiscardsin') e     INNER JOIN     (select n.target as interface, m.samplevalue as discards_out from s_Qos_data n   join s_qos_snapshot m   on n.table_id = m.table_id   where n.source = '${device}' and n.qos = 'qos_interface_pctdiscardsout') f   on e.interface=f.interface) j   on i.interface=j.interface   INNER JOIN     (select cc.interface, cc.bytes_in, ff.bytes_out from   (select aa.target as interface, bb.samplevalue as bytes_in from s_Qos_data aa   join s_qos_snapshot bb   on bb.table_id = aa.table_id   where aa.source = '${device}' and aa.qos = 'qos_interface_bytesin') cc     INNER JOIN     (select dd.target as interface, ee.samplevalue as bytes_out from s_Qos_data dd   join s_qos_snapshot ee   on dd.table_id = ee.table_id   where dd.source = '${device}' and dd.qos = 'qos_interface_bytesout') ff   on ff.interface=cc.interface) gg   on j.interface=gg.interface  


As you can see I am using ${device} to pull in the device.  As for the metric conversion you can see where I'm using the front slash to mark the division and ${kilo} as the conversion.


select i.interface, i.utilization_in, gg.bytes_in / ${kilo} as bytes_in, i.utilization_out, gg.bytes_out / ${kilo} as bytes_out, i.errors_in, i.errors_out, j.discards_in, j.discards_out 






Here I'm just entering the conversion numbers and assigning them a parameter.



Please let me know your thoughts on the dashboard and any ideas on improving it.





01-30-2019 12:41 PM

ElioteArg Were you able to get this working? I have the gauges working however I am not able to see any interface data in the center? Any thoughts on what might be causing this?

01-29-2019 04:06 PM

Has anyone gotten this working or had it updated so that its working? It seems some of the MS SQL is broken

07-19-2016 03:40 AM

Great! Thank you!!

07-11-2016 09:13 AM

Thank you for this!!
What QoS-table should I use to get history-data for a Line-chart-widget. For let's say the last 4 hours?

06-13-2016 03:54 PM

S_qos_snapshot is just the last reported metric, so it's a much smaller table. I've never used a ReadOnly database but any JDBC capable DB should work. 

06-13-2016 03:49 PM

Great stuff Bryan! Thanks for sharing.


Quick questions;

1. what is the advantage of using the s_qos_snapshot table instead of qos_data?


2. Have you ever used a shadow database local to primary or UMP to query (read only) the DB and not put so much pressure on backend?


Any input is appreciated! Cheers,



04-21-2016 03:27 PM

It's just showing the last, it's just an example of something you could do. 

04-21-2016 03:18 PM

BryanKMorrow, what do the gauge widgets show? I mean, do they show the last value (from s_qos_data) or do they show an aggregated value, an average... Because it does not make any sense to show the Availability metric for the last value only.

04-13-2016 03:36 PM

Is it possible to use regular expressions in the "value" column in the parameters table?

10-21-2015 09:27 AM

Thanks for sharing this with the community! BryanKMorrow