Custom reporting---Display 95th percentile report as percentages instead of bandwidth

jcooler

My boss is wanting to see the monthly 95th percentile of bandwidth usage for all WAN interfaces. No problem, but he wants to see the report in bandwidth % usage instead of the actually bps numbers.

I have looked at the SQL query, and its way over my head. I know that the database stores the In and Out bandwidth for each interface. I just dont know how to use it with this query to make it happen.

Any SQL gurus know how to modify the the query to show the 95th percentile as percent of avail bandwdth? If so, you would make my Boss a happy man, which, in turn, would make me a happy man.

 

Here is the existing SQL query for the report that is built in. Running NPM 9.5 SP2

 

DECLARE @StartDate DateTime
DECLARE @EndDate DateTime

SET @StartDate = DATEADD(d, DATEDIFF(d, 0, DATEADD(m, -1, DATEADD(d, 1 - day(getdate()), getdate()))), 0)
SET @EndDate = DATEADD(ms, -2,DATEADD(d, DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate())), 0))

create table #tmpJoin (fromDate datetime, toDate datetime)
insert into #tmpJoin values(@StartDate,@EndDate)

SELECT Interfaces.InterfaceId,
Nodes.NodeID,
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
        JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
    ) 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
        JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
    ) 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
        JOIN #tmpJoin ON A.DateTime >= #tmpJoin.fromDate AND A.DateTime <=  #tmpJoin.toDate
    ) AS AA
) as RESULT_MAX
    ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)

 ORDER BY NodeName, Interface_Caption

Drop table #tmpJoin

qle

Ok, I'm relatively new to this SQL stuff so I figured I'd try my hand at this for fun. Replace the following two lines in the report

Maxbps_In95,
Maxbps_Out95,

with these two lines

round(Maxbps_In95/Maxbps_95*100,0) as In_Percent,
round(Maxbps_Out95/Maxbps_95*100,0) as Out_Percent,

Then on the Field Formatting tab, set InPercent and OutPercent to the following

Format: Numeric Data
Units: %

You'll probably want to save it as a new report as well. Let me know how it works out for you.