We want to calculate 95:th percentile on aggregated interfaces. This is not a feature in orion so one of our devs wrote a query in report writer to sort it out.
Note that we also separate incoming and outgoing traffic
The problem is that i´m not sure if it´s presenting us with the correct values..
Any input on the query is much appreciated.
Right now we input the id´s of the interfaces that we want to sum up.
Here´s the query:
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1- day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))
create table #traffic_in (id bigint identity, bps bigint, ts datetime)
create table #traffic_out (id bigint identity, bps bigint, ts datetime)
insert into #traffic_in (bps, ts) SELECT sum(In_Maxbps), DateTime FROM dbo.InterfaceTraffic_Hourly_View WHERE InterfaceID in(3919, 3921, 3883, 3887) AND DateTime >= @StartDate AND DateTime <= @EndDate group by DateTime
insert into #traffic_out (bps, ts) SELECT sum(Out_Maxbps), DateTime FROM dbo.InterfaceTraffic_Hourly_View WHERE InterfaceID in(3919, 3921, 3883, 3887) AND DateTime >= @StartDate AND DateTime <= @EndDate group by DateTime
delete from #traffic_in where id in(select TOP 5 PERCENT id from #traffic_in order by bps desc)
delete from #traffic_out where id in(select TOP 5 PERCENT id from #traffic_out order by bps desc)
select 'In' as Riktning, convert(DECIMAL(12, 2), max(bps) / 1000.0 / 1000.0) as 'Mbit/s' from #traffic_in
union
select 'Out' as Riktning, convert(DECIMAL(12, 2), max(bps) / 1000.0 / 1000.0) as 'Mbit/s' from #traffic_out
Drop table #traffic_in
drop table #traffic_out
