cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post
Level 7

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

0 Kudos
4 Replies
Level 7

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

0 Kudos

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)

0 Kudos

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

0 Kudos

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

0 Kudos