cancel
Showing results for 
Search instead for 
Did you mean: 
Create Post

Business reporting

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'))

)

Labels (1)
0 Kudos
3 Replies

andy.reynolds@morgansindall.com

Are you running the new NPM 12 version?  They added business hours capabilities directly into the GUI now so you won't have to rely on writing the SQL.  I didn't have time to load up your query to test in my environment but I would recommend giving it a go in the GUI if you are using the latest version.  At a glance it seems like you nested your where conditions wrong but I won't get a chance to load it up to test and figure out the correct syntax today.

-Marc Netterfield

    Loop1 Systems: SolarWinds Training and Professional Services

- Marc Netterfield, Github

Coming off of Marc's point,

It looks like the problem is that you are saying this: (Interface Names) OR (Time Limitations)

When you should be saying this: (Interface Names) AND (Time Limitations)

What happens if you change the WHERE condition to this?

AND (

(Interfaces.Caption LIKE '%BTEE%') OR (Interfaces.Caption LIKE '%SHUK%') OR (Interfaces.Caption LIKE '%MFUK%') OR (Interfaces.Caption LIKE '%CSAC%')

)

AND (

   (DATEPART(weekday, DateTime) >= 2) AND

   (DATEPART(weekday, DateTime) <= 6) AND

   (Convert(Char,DateTime,108) >= '12:00') AND

   (Convert(Char,DateTime,108) <= '16:00'))

)

0 Kudos

Also,   I've tried run same reports for yesterday only incase its just an averaging issue, and they both show the same. I know yesterday afternoon the network was hammered with people stream the football.

0 Kudos