I've created a report for Last Month utilization on all store wan interfaces and set it to summarize to the month but instead of returning 1 record per interface, it returns the max of 10,000 records. I just want what the avg utilization was for the

narvak

This is what my sql looks like ....

SELECT  TOP 10000 Interfaces.InterfaceID AS InterfaceID,

1. Nodes.Caption AS NodeName,
2. Interfaces.Caption AS Interface_Caption,
3. Interfaces.InterfaceSpeed AS Interface_Speed,

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 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 42642 AND 42672.9999884259 )

AND 

(

  (Interfaces.InterfaceAlias LIKE '%ATT%') AND

  (DatePart(Hour,DateTime) >= 8) AND

  (DatePart(Hour,DateTime) < 21) AND

  (

   (Nodes.Caption LIKE '%us%st_%'))

)

ORDER BY 5 DESC

ctlswadmin

Shot in the dark here, add this to the query and see if it works:

CONVERT(DateTime,

LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

101) AS SummaryMonth

This is what it should look like:

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 CircuitUtil_AvgRecvXmit,

CONVERT(DateTime,

LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),

101) AS SummaryMonth