Community
- Command Central
- MVP Program
- Monthly Mission
- Blogs
- Groups
- Events
- Media Vault
Products
- Observability
- Network Management
- Application Management
- IT Security
- IT Service Management
- System Management
- Database Management
Content Exchange
- SolarWinds Platform
- Server & Application Monitor
- Database Performance Analyzer
- Server Configuration Monitor
- Network Performance Monitor
- Network Configuration Manager
- SQL Sentry
- Web Help Desk
Free Tools & Trials
Store

95th Percentile report for single interface

The 95th Percentile reports in 8.5 are great, but how can I get information on just one interface or a set of interfaces using a custom property? Unfortunately, trying the default report ends up in a timeout because it's trying to grab the stats from all 3800+ interfaces. I attempted a couple of modifications to the SQL code, but unfortunately, my SQL-fu is not strong.

Find more posts tagged with

95th_percentile

Accepted answers

All comments

FormerMember

I'll take this as a feature request, but in the meantime, it should be do-able with editing of the SQL. Maybe you could post what you've got and the rest of the Thwack community could help.

bleearg13

I'll take this as a feature request, but in the meantime, it should be do-able with editing of the SQL. Maybe you could post what you've got and the rest of the Thwack community could help.

That's the problem - I don't have anything. My SQL syntax always fails. All I need to know is where I can put one of the following WHERE statements:

WHERE Nodes.NodeID = 'x'

WHERE Interfaces.InterfaceID = 'x'

EDIT: So I think I figured it out, as usual, 5 minutes after posting my plea for help. Looks like I can add the line in right before the ORDER statement.

bleearg13

Okay, so now that I have the individual Node interfaces figured out, I'm trying to understand how the 95th percentile is created. I don't see any calculations happening in this SQL statement. In the beta version the 95th percentile had what looks like a calculation statement:

                  SELECT TOP 5 PERCENT AVG(Out_Maxbps) AS Out_Maxbps
                  FROM dbo.InterfaceTraffic
                  WHERE InterfaceID = A.InterfaceID
                        AND (DateTime >= CAST((ROUND(CAST(GetDate() - 7 AS FLOAT), 0, 1)) as datetime)
                        AND DateTime <= GetDate())
                  GROUP BY FLOOR(CAST(DateTime AS float) * 288)
                  ORDER BY AVG(Out_Maxbps) DESC) AS BB

The full code of the one that comes with the public 8.5 release shows no such statement:

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,
CASE WHEN Maxbps_In95 > Maxbps_Out95 THEN Maxbps_In95 ELSE Maxbps_Out95 END AS Maxbps_95
FROM dbo.Nodes
INNER JOIN dbo.Interfaces
    ON (dbo.Nodes.NodeID = dbo.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 (dbo.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 (dbo.Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
    WHERE dbo.Nodes.NodeID = '44'

ORDER BY NodeName, Interface_Caption

The way I understand the 95th percentile, 30 or 31 days worth of measurements (288 per day, assuming a 5-minute polling interval) is "lined up" from least usage to most and the top 5% of that usage is chopped off. Where is that happening in this code?

josh.clark

The calculations for the 95th percentile are happening in the User Defined Functions dbo.GetInBps95th() and dbo.GetOutBps95th()

seudo2k

This is what I am trying to do as well but I need to list like 5 InterfaceID's Also is there a way to pull and insert a graph showing the last 7 days as well in the report?

bleearg13

There's no way to include a graph in the report. If you want to do 5 Interface IDs, then just change the last 'RESULT_OUT' statement to read:


) as RESULT_OUT
    ON (dbo.Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
    WHERE dbo.Interfaces.InterfaceID = '1'
    OR dbo.Interfaces.InterfaceID = '2'
    OR dbo.Interfaces.InterfaceID = '3'
    OR dbo.Interfaces.InterfaceID = '4'
    OR dbo.Interfaces.InterfaceID = '5'

An easier way to do this is to assign a "CheckBox" custom property to the interfaces in question, such as "95_reports" and modify the above code to:


) as RESULT_OUT
    ON (dbo.Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
    WHERE dbo.Interfaces.95_reports = '1'