Create report based on nth percentile

Document ID : KB000095992
Last Modified Date : 16/05/2018
Show Technical Document Details
Introduction:
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%'
Instructions:
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.