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.
THE DASHBOARD
--------------------------
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
THE CONTEXT SELECTOR
-------------------------------------
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
THE GAUGES
--------------------
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'
THE INTERFACE TABLE
----------------------------------
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
THE PARAMETERS
----------------------------
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.
Thanks,
Bryan