4 Replies Latest reply on Sep 25, 2012 5:42 PM by netlogix

    Report Writer

    dretzkho

      Hi, I have just put two filters with the Report Writer. One from UDT and another one from the traffic area. I want to connect these two reports into a single query - is this possible, how can I achieve this goal?

       

      -Holger

        • Re: Report Writer
          dretzkho

          I need to merge this two reports into one.

           

          1.

           

          SELECT

          OrionSwitchPortMapping.SourceMACAddress AS Source_Port_MAC_Address

          FROM

          (OrionSwitchPortMapping INNER JOIN Nodes ON (OrionSwitchPortMapping.SourceNodeID = Nodes.NodeID))  INNER JOIN Interfaces ON (OrionSwitchPortMapping.SourceInterfaceID = Interfaces.InterfaceID)

           

          2.

           

          SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

          Nodes.NodeID AS NodeID,

          Interfaces.InterfaceID AS InterfaceID,

          Nodes.Caption AS NodeName,

          Nodes.VendorIcon AS Vendor_Icon,

          Interfaces.Caption AS Interface_Caption,

          Interfaces.InterfaceIcon AS Interface_Icon,

          AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,

          MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps

           

          FROM

          (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

           

           

          WHERE

          ( DateTime BETWEEN 41170 AND 41171 )

          AND 

          (

            (Interfaces.Inbps > 0) AND

            (

             (Interfaces.Caption LIKE 'Ethernet%') OR

             (Interfaces.Caption LIKE 'Gigabit%') OR

             (Interfaces.Caption LIKE 'Ten%') OR

             (Interfaces.Caption LIKE 'Port 1/%')) AND

            (

             (Interfaces.Uplink <> 1) OR

             (Nodes.SwType_ACC = 1) OR

             (Nodes.SwType_IBP = 1) OR

             (Nodes.SwType_NEX = 1))

          )

           

           

          GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),

          Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon

           

           

          ORDER BY SummaryDate ASC, 4 ASC, 6 ASC

            • Re: Report Writer
              netlogix

              add "Full JOIN OrionSwitchPortMapping on Interfaces.InterfaceID = OrionSwitchPortMapping.SourceInterfaceID" to your Joins and "OrionSwitchPortMapping.SourceMACAddress AS Source_Port_MAC_Address" to your selection criteria.  (full join so you still get the interface even if there isn't a MAC in the Port Mapping)

                • Re: Report Writer
                  dretzkho

                  I always get this error message:


                  error.GIF


                  SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

                  Nodes.NodeID AS NodeID,

                  Interfaces.InterfaceID AS InterfaceID,

                  Nodes.Caption AS NodeName,

                  Nodes.VendorIcon AS Vendor_Icon,

                  Interfaces.Caption AS Interface_Caption,

                  Interfaces.InterfaceIcon AS Interface_Icon,

                  AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,

                  MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,

                  OrionSwitchPortMapping.SourceMACAddress AS Source_Port_MAC_Address

                   

                  FROM

                  (Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID))  INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID) Full JOIN (OrionSwitchPortMapping INNER JOIN Nodes ON (OrionSwitchPortMapping.SourceNodeID = Nodes.NodeID))  INNER JOIN Interfaces ON (OrionSwitchPortMapping.SourceInterfaceID = Interfaces.InterfaceID) Full JOIN OrionSwitchPortMapping on Interfaces.InterfaceID = OrionSwitchPortMapping.SourceInterfaceID

                   

                  WHERE

                  ( DateTime BETWEEN 41174 AND 41175 )

                  AND

                  (

                    (Interfaces.Inbps > 0) AND

                    (

                     (Interfaces.Caption LIKE 'Ethernet%') OR

                     (Interfaces.Caption LIKE 'Gigabit%') OR

                     (Interfaces.Caption LIKE 'Ten%') OR

                     (Interfaces.Caption LIKE 'Port 1/%')) AND

                    (

                     (Interfaces.Uplink <> 1) AND

                     (Nodes.SwType_AGG <> 1) AND

                     (Nodes.SwType_HEART <> 1) AND

                     (Nodes.SwType_IDR <> 1) AND

                     (Nodes.SwType_LOC <> 1) AND

                     (Nodes.SwType_CAT3012 <> 1) AND

                     (Nodes.SwType_ARE <> 1))

                  )

                   

                   

                  GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),

                  Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon

                   

                   

                  ORDER BY SummaryDate ASC, 4 ASC, 6 ASC

                    • Re: Report Writer
                      netlogix

                      Try this:

                      SELECT  TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,

                         Nodes.NodeID AS NodeID,

                         Interfaces.InterfaceID AS InterfaceID,

                         Nodes.Caption AS NodeName,

                         Nodes.VendorIcon AS Vendor_Icon,

                         Interfaces.Caption AS Interface_Caption,

                         Interfaces.InterfaceIcon AS Interface_Icon,

                         AVG(InterfaceTraffic.In_Averagebps) AS AVERAGE_of_Average_Receive_bps,

                         MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,

                         OrionSwitchPortMapping.SourceMACAddress

                      FROM Nodes

                        INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)

                        INNER JOIN InterfaceTraffic ON (Interfaces.InterfaceID = InterfaceTraffic.InterfaceID AND InterfaceTraffic.NodeID = Nodes.NodeID)

                        Full Join OrionSwitchPortMapping on (OrionSwitchPortMapping.SourceInterfaceID = Interfaces.InterfaceID)

                      WHERE

                      (

                        Interfaces.Inbps > 0 and DateTime BETWEEN 41170 AND 41171

                        and (

                         Interfaces.Caption LIKE 'Ethernet%' OR

                         Interfaces.Caption LIKE 'Gigabit%' OR

                         Interfaces.Caption LIKE 'Ten%' OR

                         Interfaces.Caption LIKE 'Port 1/%'

                        )

                        and (

                          Interfaces.Uplink <> 1 OR

                          Nodes.SwType_ACC = 1 OR

                          Nodes.SwType_IBP = 1 OR

                          Nodes.SwType_NEX = 1

                        )

                      )

                      GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon, OrionSwitchPortMapping.SourceMACAddress

                      ORDER BY SummaryDate ASC, 4 ASC, 6 ASC