As 95th percentile-like options are not seemingly possible in the Report Writer, I wanted to modify the "95th Percentile Traffic Rate - This Month" report to my needs. This is a raw SQL defined report and I know nothing of SQL. Below is the report as-is. I want to modify this SQL so it only includes the following:
(custom property) DisplayGrouping CONTAINS 'WAN Sites'
I've tried to accomplish this with the following code with no luck. Report errors out for one reason or another with each attempt I've made. Please advise.
|
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
WHERE (A.DateTime >= @StartDate AND A.DateTime <= @EndDate)
AND
(
(Nodes.DisplayGrouping LIKE '%WAN%')
) |
---
ORIGINAL REPORT SYNTAX
---
DECLARE @StartDate DateTime
DECLARE @EndDate DateTime
SET @StartDate = CAST (DATEDIFF(d, 0, DATEADD(d, 1 - day(getdate()), getdate()))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,
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)
---------------------
INNER JOIN
(
SELECT InterfaceID, dbo.GetMaxBps95th(AA.InterfaceID, @StartDate, @EndDate) AS Maxbps_95
FROM (
SELECT DISTINCT A.InterfaceID
FROM dbo.InterfaceTraffic A
WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
) AS AA
) as RESULT_MAX
ON (dbo.Interfaces.InterfaceID = RESULT_MAX.InterfaceID)
---------------------
ORDER BY NodeName, Interface_Caption
