Customer can use percentile functions that are supported by SQL Server 2012, Oracle 10g R2, like percentile_cont or percentile_disc, to compute nth percentile of interface utilization.
First query table S_QOS_DEFINITION for raw data tables for QOS_INTERFACE_UTILIZATIONIN and QOS_INTERFACE_UTILIZATIONOUT:
SELECT * FROM S_QOS_DEFINITION d WHERE D.NAME LIKE 'QOS_INTERFACE_UTILIZATION%'
Environment
Release: Component: UIMUMD
Resolution
I need to compute the 95th percentile for interfaces of a particular device within last 30 days:
select interface, [QOS_INTERFACE_UTILIZATIONIN] AS 'Interface IN', [QOS_INTERFACE_UTILIZATIONOUT] AS 'Inerface OUT' from ( select distinct s.target+ '@' +s.source as interface, s.qos as QOS, usg.percentile from (select r.table_id, PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY r.samplevalue) OVER(PARTITION BY r.table_id) as percentile from RN_QOS_DATA_1134 r where r.sampletime > GETDATE() -30 UNION select r.table_id, PERCENTILE_DISC(0.95) WITHIN GROUP(ORDER BY r.samplevalue) OVER(PARTITION BY r.table_id) as percentile from RN_QOS_DATA_1138 r where r.sampletime > GETDATE() -30) as usg join s_qos_data s on s.table_id = usg.table_id where s.source like '%DEV_NAME%' ) as metric pivot ( SUM(percentile) For qos in ([QOS_INTERFACE_UTILIZATIONOUT],[QOS_INTERFACE_UTILIZATIONIN]) ) as usg_table order by 'Interface IN' DESC;
-- RN_QOS_DATA_1134 and RN_QOS_DATA_1138 are raw tables for QOS_INTERFACE_UTILIZATIONIN and QOS_INTERFACE_UTILIZATIONOUT respectively.
Additional Information
Performance: Customer need to check performance of this query before running it in HTML5 dashboards of UMP, this query relies on the index performance on raw sample table involved.
Limitations: SQL Server 2008 R2 does not support analytic function like percentile_cont or percentile_disc.
MySQL 5.5/5.6 does not support analytic functions.