Didn't see the attempted report designer. Can you post the query you are using that isn't working?
(InterfaceTraffic_Detail INNER JOIN Interfaces ON (Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID) )
INNER JOIN Nodes ON (Nodes.NodeID = Interfaces.NodeID)
(Nodes.HName LIKE 'R-XRT-%') AND (Nodes.ServiceFTP <> 1) AND
( (InterfaceName LIKE 'Ser%.%') AND (InterfaceName NOT LIKE 'Ser%.16') ) AND
(((InterfaceTraffic_Detail.In_Averagebps/Interfaces.InBandwidth)*100) > 45)
(((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) > 45)
Nodes.HName, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,
ORDER BY Nodes.HName
The red part indicates:::HName starts with 'R-XRT-' AND the service FTP is not selected (not running FTP over this ckt) AND it is a serial interface, but not the main circuit, only subinterfaces)
The blue part calculates average bandwidth then it MATCHES the criteria if IN or OUT is > 45%
Essentially, This Custom SQL query gives me a report for all my remote customer frame relay connections and counts how many times in a period that for a five minute period it averages over 45%.
hopefully that gives you an example of how you can use the where clause to filter for your reports.
This is a great example. I'm still kinda new to this... wondering where in SQL it counts times over the five minute period? Or is this implied due to polling period?
when I wrote this report, I had the raw statistics being saved for 7 days, which means the stats were averaged into 5 min intervals (my understanding of 5 min stat polling interval of router and how the system crunches that info).
If your settings are to compress raw after an hour or day, then it would be based on 15 min avg, I believe.
See if the filter criteria in the screenshot below does what you are looking for. I'm interpreting your criteria as follows:
Include all interfaces for node RDE-3640#1
Include only interfaces Fa2/0 and Fa0/1 for node RDE-3640#2
Include all interfaces for node RDE-C6513-1
Include only the interface that has interface index 53 for node RDE-C6513-2
I did this above and i still got the same error:
'Invalid filter, complex condition is empty , does not contain nested conditions'.
Have resent screen shot of my report designer
sam.zip 53.2 KB
The key is in creating the different levels of the criteria. Unfortunately, the interface is not particularly intuitive for doing this but once you understand how it works it's not so bad. For instance, when creating the complex conditions that read "Select records where all of the following apply" you need to start by clicking the condition at the very top of the list that says "Select records where any of the following apply". Then click the box at the left and select add new complex condition.
That works now.
I'd like to say a big thanks to everyone for their help.