I've created two reports to run a specific interface between monday and friday only. The first is between 08:00 and 12:00 the second is between 12:00 and 16:00. Both reports come out with identical reports. I'm running these reports over a 7 day period. Obviously I can't trust the data, if the time is not being actioned I have no faith that the day is either.. because of the similarity. These reports are picking up the 60 or so routers and the interfaces in question . Here's the afternoon for example. (the other is just a change in times at the bottom). I have also tried Hour of as well as time of day with no difference can anyone help
SELECT TOP 10000 CONVERT(DateTime,
LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),
101) AS SummaryMonth,
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,
MAX(InterfaceTraffic.In_Maxbps) AS MAX_of_Peak_Receive_bps,
AVG(InterfaceTraffic.Out_Averagebps) AS AVERAGE_of_Average_Transmit_bps,
MAX(InterfaceTraffic.Out_Maxbps) AS MAX_of_Peak_Transmit_bps,
Nodes.Department AS Department,
Nodes.City AS City,
Interfaces.InterfaceSpeed AS Interface_Speed
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 42529 AND 42536.5 )
AND
(
(Interfaces.Caption LIKE '%BTEE%') OR
(Interfaces.Caption LIKE '%SHUK%') OR
(Interfaces.Caption LIKE '%MFUK%') OR
(Interfaces.Caption LIKE '%CSAC%') OR
(
(DATEPART(weekday, DateTime) >= 2) AND
(DATEPART(weekday, DateTime) <= 6) AND
(Convert(Char,DateTime,108) >= '12:00') AND
(Convert(Char,DateTime,108) <= '16:00'))
)