95th percentile per interface

jpdeboer

Hi,

I am looking for a SQL query to get the 95th percentile data for the last month on 1 interface. I did some searching on here, but the only working query i could find was per node id see below:

1. DECLARE @StartDate DateTime 
2. DECLARE @EndDate DateTime 
3.  
4. SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime) 
5. SET @EndDate = GetDate() 
6.  
7. SELECT Interfaces.InterfaceId, 
8. Nodes.Caption AS NodeName, 
9. Nodes.VendorIcon AS Vendor_Icon,       
10. Interfaces.Caption AS Interface_Caption, 
11. Interfaces.InterfaceIcon AS Interface_Icon, 
12. Maxbps_In95, 
13. Maxbps_Out95, 
14. CASE WHEN Maxbps_In95 > Maxbps_Out95 THEN Maxbps_In95 ELSE Maxbps_Out95 END AS Maxbps_95 
15. FROM dbo.Nodes 
16. INNER JOIN dbo.Interfaces 
17.     ON (dbo.Nodes.NodeID = dbo.Interfaces.NodeID) 
18. INNER JOIN    
19. ( 
20. ---------------- 
21.     SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95 
22.     FROM ( 
23.         SELECT DISTINCT A.InterfaceID 
24.         FROM dbo.InterfaceTraffic A 
25.         WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate 
26.     ) AS AA 
27. --------------------- 
28. ) as RESULT_IN 
29.     ON (dbo.Interfaces.InterfaceID = RESULT_IN.InterfaceID) 
30. INNER JOIN    
31. ( 
32. ---------------- 
33.     SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95 
34.     FROM ( 
35.         SELECT DISTINCT A.InterfaceID 
36.         FROM dbo.InterfaceTraffic A 
37.         WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate 
38.     ) AS AA 
39. --------------------- 
40. ) as RESULT_OUT 
41.     ON (dbo.Interfaces.InterfaceID = RESULT_OUT.InterfaceID) 
42.     WHERE dbo.Nodes.NodeID = '44' 
43.  
44. ORDER BY NodeName, Interface_Caption 

But can this query be adjusted so it will only show 1 interface, so sort on interface ID ?

Thanks in advance.