This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Report Writer

dretzkho over 11 years ago

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 dretzkho over 11 years ago

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
Cancel
Vote Up 0 Vote Down

Cancel
0 netlogix over 11 years ago in reply to dretzkho

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)
Cancel
Vote Up 0 Vote Down

Cancel
0 dretzkho over 11 years ago in reply to netlogix

I always get this error message:

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
Cancel
Vote Up 0 Vote Down

Cancel
0 netlogix over 11 years ago in reply to dretzkho

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
Cancel
Vote Up 0 Vote Down

Cancel