Hi guys,
I have two reports that I want to combine as one, my sql is not that good.
Can you help me, I will paste the sql statements from the two reports. I basically want availability, Bandwidth in and out for the nodes.
Statement 1
SELECT TOP 10000 Interfaces.InterfaceName AS Interface_Name,
Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(InterfaceAvailability.Availability) AS AVERAGE_of_Availability
FROM
(Nodes INNER JOIN Interfaces ON (Nodes.NodeID = Interfaces.NodeID)) INNER JOIN InterfaceAvailability ON (Interfaces.InterfaceID = InterfaceAvailability.InterfaceID AND Nodes.NodeID = InterfaceAvailability.NodeID)
WHERE
( DateTime BETWEEN 43707 AND 43736.9999884259 )
AND
(
(Nodes.Caption LIKE '%R3INTCOR%') AND
(Interfaces.Caption LIKE '%Tun%')
)
GROUP BY Interfaces.InterfaceName, Nodes.Caption, Interfaces.Caption
Statement 2
SELECT TOP 10000 Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
MAX(Case
When InBandwidth+OutBandwidth=0 Then 0
When InBandwidth=0 Then
(Out_Averagebps/OutBandwidth) * 100
When OutBandwidth=0 Then
(In_Averagebps/InBandwidth) * 100
Else
( (Out_AverageBps/OutBandwidth)+(In_AverageBps/InBandwidth))*50
End) AS MAX_of_CircuitUtil_AvgRecvXmit,
MAX(Case
When InBandwidth+OutBandwidth=0 Then 0
When InBandwidth=0 Then
(Out_Averagebps/OutBandwidth) * 100
When OutBandwidth=0 Then
(In_Averagebps/InBandwidth) * 100
Else
Case
When (Out_AverageBps/OutBandwidth) > (In_AverageBps/InBandwidth) Then
(Out_AverageBps/OutBandwidth)*100
Else
(In_AverageBps/InBandwidth) *100
End
End) AS MAX_of_CircuitUtil_MaxRecvXmit
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 43736 AND 43743.5833333333 )
AND
(
(Nodes.Comments LIKE '%International%') AND
(Case
When InBandwidth+OutBandwidth=0 Then 0
When InBandwidth=0 Then
(Out_Averagebps/OutBandwidth) * 100
When OutBandwidth=0 Then
(In_Averagebps/InBandwidth) * 100
Else
Case
When (Out_AverageBps/OutBandwidth) > (In_AverageBps/InBandwidth) Then
(Out_AverageBps/OutBandwidth)*100
Else
(In_AverageBps/InBandwidth) *100
End
End >= 60)
)
GROUP BY Nodes.Caption, Interfaces.Caption
ORDER BY 4 DESC
