8 Replies Latest reply on Dec 3, 2009 6:23 AM by menz

    Report Write Issues

      I'm having real trouble filtering on the report writer.

      I want to be able to say only these nodes and only these interfaces.

       

      This never works and the interfaces are all shown.

      I've tried many different combinations of complex/elementary conditions etc and any

      help is greatly appreciated.

      I've included my attempted report designer

       

      Sam

        • Re: Report Write Issues
          sotherls

          Didn't see the attempted report designer. Can you post the query you are using that isn't working?

            • Re: Report Write Issues
              njoylif

              SELECT
              Nodes.NodeID,
              Nodes.HName,
              Interfaces.InterfaceID,
              Interfaces.Caption,
              Interfaces.OutBandwidth,
              Interfaces.InBandwidth,
              COUNT(*)

              FROM
              (InterfaceTraffic_Detail INNER JOIN Interfaces ON (Interfaces.InterfaceID = InterfaceTraffic_Detail.InterfaceID) )
              INNER JOIN Nodes ON (Nodes.NodeID = Interfaces.NodeID)

              WHERE
              (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)
                       OR
                  (((InterfaceTraffic_Detail.Out_Averagebps/Interfaces.OutBandwidth)*100) > 45)
              )

              GROUP BY
              Nodes.HName, Nodes.NodeID, Interfaces.InterfaceID, Interfaces.OutBandwidth,
              Interfaces.InBandwidth, Interfaces.Caption

              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.

                • Re: Report Write Issues
                  ecklerwr1

                  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?

                  Regards,

                  B.

                    • Re: Report Write Issues
                      njoylif

                      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.

                • Re: Report Write Issues

                  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

                    • Re: Report Write Issues

                      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

                       

                      thanks

                      sam

                        • Re: Report Write Issues

                          Sam,

                          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.