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
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)
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:
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
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
SolarWinds solutions are rooted in our deep connection to our user base in the THWACK® online community. More than 195,000 members are here to solve problems, share technology and best practices, and directly contribute to our product development process.