1 Reply Latest reply on Sep 21, 2015 6:17 PM by gnowak

    95th Percentile Reporting per ISP - Custom SQL Report

    gnowak

      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.