Report Writer

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

Parents
  • 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

  • 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)

  • 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

Reply
  • 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

Children
  • 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