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

Blog Post created by BryanKMorrow Employee on Oct 14, 2015

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 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 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 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 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 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 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 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 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.