Hi, Experts,
I want to build a report of regular average BW utilization for interface according to different regions, I used to create custom properties with value for each regions, like the following clause from other report
WHERE INT_FIL_TOPOLOGY like 'APACTIER1' AND A_B_End like 'A_End'
I got the SQL query from report writer, however it's only for all interfaces...i would like to know how can I add above filter into the following SQL query, then we can filter it and list all interface average utilization based on customer properties.
Thank you so much for your help
SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)),0) AS SummaryDate,
Nodes.Caption AS NodeName,
Interfaces.InterfaceName AS Interface_Name,
AVG(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 AVERAGE_of_CircuitUtil_AvgRecvXmit
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 43486 AND 43517 )
AND
(
(Convert(Char,DateTime,108) >= '08:00') AND
(Convert(Char,DateTime,108) <= '17:00')
)
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)),0),
Nodes.Caption, Interfaces.InterfaceName,
ORDER BY SummaryDate ASC, 2 ASC, 3 ASC