I have a mental block when it comes to deciphering SQL code so I'm hoping someone can help.
I am trying to modify a copy of the canned historical traffic report that shows the 95th percentile of bandwidth used over the last month for each interface. I need a similar report, but for just one interface or a select group of interfaces. Please show me how to modify the following SQL code so that I can change which interface ID to run the report against.
I would also like to add the custom bandwidth field (InBandwidth, OutBandwidth) to the report, so if you could also show me where and what to add to achieve that as well, I would greatly appreciate it.
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))
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 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
WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
) 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
WHERE A.DateTime >= @StartDate AND A.DateTime <= @EndDate
) AS AA
) as RESULT_OUT
ON (Interfaces.InterfaceID = RESULT_OUT.InterfaceID)
---------------------
INNER JOIN
Thanks!
klc2009
