pchaves replied on Tue, Jan 13 2009 11:57 AM

rated by 0 users

Sure, no problem. Here is what I have.

SELECT  TOP 10000 Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
MAX(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
AVG(Case OutBandwidth
    When 0 Then 0
    Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
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)

WHERE
DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
 AND 
(
(Nodes.Caption LIKE '%RI-NCC-2811%') AND
  (Interfaces.InterfaceName = 'Serial0/0/1') OR 
(Interfaces.Caption LIKE '%Qwest%') OR
  (Interfaces.Caption LIKE '%COX-20MB%') OR
  (Interfaces.Caption LIKE '%One-Communications%') OR
  (Interfaces.Caption LIKE '%Verizon-ISP%') OR
(Nodes.Caption LIKE '%Cov2-3745-WAN%') AND
  (Interfaces.InterfaceName = 'Multilink1') OR
(Nodes.Caption LIKE '%East-Greenwich-Warehouse%') AND
  (Interfaces.InterfaceName = 'Serial1/0') OR
(Nodes.Caption LIKE '%PVD-7200%') AND
  (Interfaces.InterfaceName = 'GigabitEthernet0/1') OR
(Nodes.Caption LIKE '%PVD-7200%') AND
  (Interfaces.InterfaceName = 'GigabitEthernet0/2')

 AND

(DATEPART(weekday, DateTime) <> 7)AND
(DATEPART(weekday, DateTime) <> 1) AND
(CONVERT(Char, DateTime, 108) > '0800') AND
(CONVERT(Char, DateTime, 108) < '1700')

)

GROUP BY Nodes.Caption, Interfaces.Caption

Thanks again!

sedmo replied on Tue, Jan 13 2009 2:30 PM

rated by 0 users

I think you have some problems with the groupings in your where statement.  Try this.

SELECT  TOP 10000 Nodes.Caption AS NodeName,
Interfaces.Caption AS Interface_Caption,
AVG(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS AVERAGE_of_Recv_Percent_Utilization,
MAX(Case InBandwidth
    When 0 Then 0
    Else (In_Averagebps/InBandwidth) * 100
End) AS MAX_of_Recv_Percent_Utilization,
AVG(Case OutBandwidth
    When 0 Then 0
    Else (Out_Averagebps/OutBandwidth) * 100
End) AS AVERAGE_of_Xmit_Percent_Utilization,
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)

WHERE
DateTime between  (DATEADD(m, DATEDIFF(m, 0, getdate()) -1 , 0)) AND (DATEADD(m, DATEDIFF(m, 0, getdate()) , 0))
 AND
(
 (Nodes.Caption like '%RI-NCC-2811%' and
  (Interfaces.InterfaceName = 'Serial0/0/1' or
  Interfaces.Caption like '%Qwest%' or
  Interfaces.Caption like '%COX-20MB%' or
  Interfaces.Caption like '%One-Communications%' or
  Interfaces.Caption like '%Verizon-ISP%')
 )
or
 (Nodes.Caption like '%Cov2-3745-WAN%' and
  (Interfaces.InterfaceName = 'Multilink1')
 )
or
 (Nodes.Caption LIKE '%East-Greenwich-Warehouse%' and
  (Interfaces.InterfaceName = 'Serial1/0')
 )
or
 (Nodes.Caption LIKE '%PVD-7200%' and
  (Interfaces.InterfaceName = 'GigabitEthernet0/1' or
   Interfaces.InterfaceName = 'GigabitEthernet0/2')
 )
)
AND
(
(DATEPART(weekday, DateTime) <> 7)AND
(DATEPART(weekday, DateTime) <> 1) AND
(CONVERT(Char, DateTime, 108) > '0700') AND
(CONVERT(Char, DateTime, 108) < '1700')
)

GROUP BY Nodes.Caption, Interfaces.Caption

pchaves replied on Tue, Jan 13 2009 2:41 PM

rated by 0 users

That seems to give me the correct timeframe but now it's ignoring the first section of nodes and interfaces (from RI-NCC-2811 to Verizon-ISP), only giving information fro the last three....

sedmo replied on Tue, Jan 13 2009 3:21 PM

rated by 0 users

I assumed that those interfaces where on the RI-NCC-2811 device.  If that is not the case then the grouping will need to be modified.  The way it is currently written, the interface has to match one of the five statements below.

caption like RI-NCC-2811 and interfacename = Serial0/0/1

caption like RI-NCC-2811 and interfaces.caption like '%Qwest%'

caption like RI-NCC-2811 and interfaces.caption like '%COX-20MB%'

caption like RI-NCC-2811 and interfaces.caption like '%One-Communications%'

caption like RI-NCC-2811 and interfaces.caption like '%Verizon-ISP%'

BakerD replied on Wed, Jan 14 2009 2:33 PM

rated by 0 users

In one of my reports on the Time Frame tab I set it to last 30 days.  Then on the Filter Results tab I have two lines that narrow it down to working hours for me of 8am - 6pm.

Records where Time of Day (24 hour format) is less than 08:00

Records where Time of Day (24 hour format) is greater than or equal to 18:00

I use this ina filter to give me WAN utilization for the last 30 days during working hours.