The built-in report that shows 95th percentile traffic rate - last month works fine, but I manage over 3000 nodes and would like to parse this report to only show WAN Circuits, of which there are about 50. Anyone know how to do this?
Thanks,
Hey orionfan,
Working with my DBA, we modified the report in my original post to use a custom field called WANCircuits which in my case is a simple no/yes (0/1) field. The code is below; look at the WHERE statement. You can see that it will include only interfaces where the WANCircuit = 1. If your custom field WAN_ID is a similar type field, then you just need to make sure the interface that is one of your WAN circuits has this field checked so the query can pick it up as a "yes" (1).
==============================================================================
SET NOCOUNT OFF
SET ROWCOUNT 0
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 AS InterfaceID,
Nodes.NodeID AS NodeID,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
Interfaces.InBandwidth AS Interface_Bandwidth,
-- RAW NUMBERS
Maxbps_In95 / 1000000.0 AS Maxbps_In95,
Maxbps_Out95 / 1000000.0 AS Maxbps_Out95,
Maxbps_95 / 1000000.0 AS Maxbps_95,
-- PERCENT CALCULATION
Maxbps_In95 / Interfaces.InBandwidth * 100 AS ReceivePCT,
Maxbps_Out95 / Interfaces.InBandwidth * 100 AS TransmitPCT,
Maxbps_95 / Interfaces.InBandwidth * 100 AS MaxPCT
FROM Nodes
INNER JOIN Interfaces ON Nodes.NodeID = Interfaces.NodeID
INNER JOIN (
SELECT InterfaceID,
dbo.GetInBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_In95,
dbo.GetOutBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_Out95,
dbo.GetMaxBps95th(InterfaceID, @StartDate, @EndDate) AS Maxbps_95
FROM InterfaceTraffic
WHERE
InterfaceTraffic.DateTime >= @StartDate
AND InterfaceTraffic.DateTime <= @EndDate
AND InterfaceID in (SELECT InterfaceID FROM dbo.Interfaces WHERE WANCircuit = 1)
GROUP BY InterfaceID
) TrafficStat ON Interfaces.InterfaceID = TrafficStat.InterfaceID
(1=1)
ORDER BY
NodeName,
Interface_Caption
You can have your WAN circuits in one group and then try to run the report.
Hi haydanb
Or you can create an account with permission to devices and run report when login using that account.
Regards
Hanif
I created a group that has all the WAN circuit interfaces. Where can the group be defined in the report? I'm using NPM 11.01 and the 95th Percentile Traffic Rate report is a historical report and can only be edited within the Orion Report Writer. I saw this post, but looks like its for Nodes | SQL or VIEW to select group-members
Here's the SQL from the existing report for the SELECT and FROM (see below). The name of the group is called 'WAN Circuits' Thanks
SELECT Interfaces.InterfaceId,
Nodes.NodeID,
Nodes.VendorIcon AS Vendor_Icon,
Interfaces.InterfaceIcon AS Interface_Icon,
Maxbps_In95,
Maxbps_Out95,
Maxbps_95
WHERE InterfaceTraffic.DateTime >= @StartDate AND InterfaceTraffic.DateTime <= @EndDate
) TrafficStat
ON Interfaces.InterfaceID = TrafficStat.InterfaceID
WHERE ${LIMITATION}
ORDER BY NodeName, Interface_Caption
I am also interested in this. However, I have a custom property called WAN_ID. We have three WAN's so I would like to create a report where it just polls interfaces with WAN_ID = 1, WAN_ID=2..... Any suggestions?
I used what I found on this thread to help me modify my reports. I need to report the utilization on our internet circuits. However, I would like to set a second condition so that I can have 4 reports for North America, South America, EMEA and APAC.
I want to set a second condition so that the report will only pull the information on the specified internet interface and then also I can also set a second condition to only poll the specified region. Can anyone help?
AND InterfaceID in (SELECT InterfaceID FROM dbo.Interfaces WHERE Internet_Interface = 1)
