I have several reports that I have been building and just recently discovered an issue with the Reports. I am pulling Historical Bandwidth Usage reports and the Fields it is supposed to be populating are as follows:
HourNode IDInterface IDNodeVendorInterfaceInterface
TypeAverage
Receive bpsAverage Recv
Percent UtilPeak
Receive bpsMax Recv
Percent UtilAverage
Transmit bpsAverage Xmit
Percent UtilPeak
Transmit bpsMax Xmit
Percent Util
Below is the SQL code for the report. Where the problem lies is in the code for the Max Receive percent Util / Max Xmit Precent Util Fields:
MAX(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
should be:
MAX(Case InBandwidth
When 0 Then 0
Else (In_Maxbps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
and:
MAX(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization
Should be:
MAX(Case OutBandwidth
When 0 Then 0
Else (Out_Maxbps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization
<CODE>
SELECT TOP 10000 Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0) AS SummaryDateTime,
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,
AVG(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
MAX(Case InBandwidth
When 0 Then 0
Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
AVG(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
MAX(Case OutBandwidth
When 0 Then 0
Else (Out_Averagebps/OutBandwidth) * 100
End) AS MAX_of_Xmit_Percent_Utilization
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 40763 AND 40793 )
AND
(
(Nodes.Caption LIKE '%Sonicwall - Firewall%') AND
(Interfaces.InterfaceName LIKE '%X1%')
)
GROUP BY Convert(DateTime,Floor(Cast((DateTime) as Float)*24)/24,0),
Nodes.NodeID, Interfaces.InterfaceID, Nodes.Caption, Nodes.VendorIcon, Interfaces.Caption, Interfaces.InterfaceIcon
ORDER BY SummaryDateTime ASC </CODE>
I have tried manually editing the report using a text editor and then when I reopen it, it just changes it back.. PLEASE help! I'm getting incorrect data on all my reports.
Thanks in Advance!
Norman
