Hi there, would appreciate some help. 2 part question on 95th percentile reporting.
The company needs to reduce bandwidth at as many sites as possible to save money. So we've been tasked with providing to management a number for each site that represents how much bandwidth to keep from the current bandwidth allocation. To do this, our Netops team is using the 95% line on the traffic graphs. But there is a problem with that value because it includes all the samples from the off-hours, when there is almost zero traffic. I need a "smarter" 95-Percentile line, one that only includes values from 8am - 6pm, for example, over the span of 30, 60 days.
Q1) I wonder EVEN IF i can have only business hours, if the 95th-percentile is a calculated value, or if it's somethign out of the database that cannot be changed (thus inherently flawed no matter what I do)?
Q2) I called support and they gave me a possible way to do it using custom SQL query. It didnt work. Basically, they said to edit the canned 95% Percentile report, and include a time limitation in it from another report. I did that, pasted in the time limitation from another report (in red below), but that generates an error that says “Invalid column name DateTime”. . Is anyone here familiar with how to fix the SQL query below in order to get the 95-Percentile calculation to exclude non-business hours?
Thank you much!!
----------------From a known-working 95% Percentile Report for the Last 30 days, in RED is what I added that broke it------------------------------
SET @StartDate = CAST((ROUND(CAST(GetDate() - 30 AS FLOAT), 0, 1)) as datetime)
SELECT
Interfaces.InterfaceId,
Interfaces.SiteName,
Interfaces.Site_Type,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Interfaces.Carrier,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95,
Interfaces.InterfaceSpeed
FROM Nodes
INNER JOIN Interfaces
ON (Nodes.NodeID = Interfaces.NodeID)
---------------------
INNER JOIN
(
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
) AS AA
) as RESULT_IN
ON (Interfaces.InterfaceID = RESULT_IN.InterfaceID)
---------------------
INNER JOIN
(
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
) AS AA
) as RESULT_OUT
ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
---------------------
INNER JOIN
(
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
) AS AA
) as RESULT_MAX
ON (Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
---------------------
WHERE
( Interfaces.PRINT_FLAG LIKE 'xxx' )
AND
(
(DATEPART(weekday, DateTime) > 1) AND
(DATEPART(weekday, DateTime) < 7) AND
(Convert(Char, DateTime,108) > '070000') AND
(Convert(Char, DateTime,108) < '190000')
)
ORDER BY Maxbps_95 DESC
---------------------------------------------------------------------------------------------------------------------------------
