Custom Reporting--- 95th percentile for WAN interfaces.

charpham

 Hi All

 I want to modify an exisiting provided Custom SQL query from Report Writer called "95th Percentile Traffic Rate- Last 7 Days".. Unforntuately I am not very familiar with SQL.  I would like to provide a report that displays the 95th percentile for WAN interfaces for the last 7 days.  I have created a Custom interface field called "interface_type2" Below is the SQL query in ReportWriter that generates the 95th percentile for all interfaces:

 DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
SET @EndDate = GetDate()

SELECT Interfaces.InterfaceId,
Nodes.Caption AS NodeName,
Nodes.VendorIcon AS Vendor_Icon,       
Interfaces.Caption AS Interface_Caption,
Interfaces.InterfaceIcon AS Interface_Icon,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95
FROM Nodes
INNER JOIN Interfaces
    ON (Nodes.NodeID = Interfaces.NodeID)
---------------------
INNER JOIN    
(
    SELECT InterfaceID, dbo.GetInBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_In95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
    ) AS AA
) as RESULT_IN
    ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
---------------------
INNER JOIN    
(
    SELECT InterfaceID, dbo.GetOutBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
    ) AS AA
) as RESULT_OUT
    ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
---------------------
INNER JOIN    
(
    SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
    FROM (
        SELECT DISTINCT A.InterfaceID
        FROM dbo.InterfaceTraffic A
        WHERE A.DateTime >= @StartDate AND A.DateTime <=  @EndDate
    ) AS AA
) as RESULT_MAX
    ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
---------------------
 ORDER BY NodeName, Interface_Caption

 

I am wondering if someone help the SQL statements needed to "filter" and display only the interfaces that have the "WAN" in the Custom interface field called "interface_type2"

 

thanks for your help

 

chuck harpham

Symantec

Network Engineering/Monitoring

tdanner

If this report is otherwise doing what you want and you just need it to display only the WAN interfaces, just add this clause right before the ORDER BY at the end:

WHERE Interfaces.interface_type2='WAN'