This discussion has been locked. The information referenced herein may be inaccurate due to age, software updates, or external references.

You can no longer post new replies to this discussion. If you have a similar question you can start a new discussion in this forum.

Business reporting

andy.reynolds over 7 years ago

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

)

Top Replies

mesverrum over 7 years ago +1

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…

0 andy.reynolds over 7 years ago

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.
Cancel
Vote Up 0 Vote Down

Cancel
0 mesverrum over 7 years ago
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
                LinkedIN: Loop1 Systems
                Facebook: Loop1 Systems
                Twitter: @Loop1Systems
Cancel
Vote Up +1 Vote Down

Cancel

0 zackm over 7 years ago in reply to mesverrum

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