This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.
You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

95th Percentile Reporting per ISP - Custom SQL Report

We have several providers for our datacenters, and 10-20 datacenters across the world. Recently, we were hit with some higher-than-expected bills because of bandwidth overages, so we are hoping to take a proactive approach to our bandwidth monitoring to remedy it. Since we get billed on 95th percentile, we are looking to create a report that shows us the 95th percentile usage for each ISP over the last 30 days. We'll then use the reports to gauge where we are with our bandwidth usage and adjust as needed.

I had a call with Solarwinds and it was suggested that I post here for more help. Here's what I have so far... I'll use Level 3 as our ISP in the example.

SQL for report

DECLARE @StartDate DateTime

DECLARE @EndDate DateTime

SET @StartDate = ${FromTime}

SET @EndDate = ${ToTime}

SELECT    Interfaces.InterfaceId,

Nodes.NodeID,

Nodes.City,

Nodes.Caption AS NodeName,

Interfaces.Caption AS Interface_Caption,

Maxbps_In95,

Maxbps_Out95,

Maxbps_All95

FROM Nodes

INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID

INNER JOIN (

SELECT InterfaceID,

dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_In95,

dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_Out95,

dbo.GetInBpsPercentile(InterfaceID, @StartDate, @EndDate,95) +

dbo.GetOutBpsPercentile(InterfaceID, @StartDate, @EndDate,95) AS Maxbps_All95

FROM InterfaceTraffic

WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate

GROUP BY InterfaceID

) TrafficStat

ON Interfaces.InterfaceID = TrafficStat.InterfaceID

WHERE (Interfaces.Caption LIKE '%level 3%' OR Interfaces.Caption LIKE '%level3%')

This produces a table that shows me the 95th percentile usage. However, it doesn't show me the sum of all locations. We want to see the ISP and the total 95th percentile usage across all sites.

Screen Shot 2015-09-18 at 2.29.52 PM.png

The other option I tried was to put it on a graph. However, I'm unable to find a Timestamp field so that I can associate the data with a time and put it on a graph. I've tried some different SQL queries but haven't been able to get it yet.

So... does anybody know of a way to get a total sum for the column OR how I can associate the data with a time so that it will fit on a graph? Let me know if you need more information and I'll work with you.

  • Well an update for anyone who finds this... I stuck with what I have in my original post because it just takes an extra minute to do the math for the columns. We're better off having to do that than not having the data at all.

  • @gnowak Just wanted to mention that the query above with a new column that adds the two values are exactly what I was looking for. Also, by querying the port channel, I'm able to get both sides of a LAG and pull reports on all of our ISP/port-channel 95th percentile stats by tweaking you stated.