denny.lecompte replied on Wed, Apr 22 2009 2:48 PM

rated by 0 users

This is the kind of question best answered by NetFlow.  NetFlow looks at what's consuming bandwidth.  Within the Orion product like, NetFlow Traffic Analyzer would provide that data.

netadm1n replied on Wed, Apr 22 2009 2:57 PM

rated by 0 users

Denny thanks for the response.  My thought is that netflow will tell you what applications the traffic belongs to.  I'm not so much interested in that as in simply where the traffic is coming from and going to.  Orion is already gathering that information.  Are you saying though that the problem is just that Orion's reporting is not built to provide the kind of analysis I'm looking for, whereas the Netflow tool's reporting can, as well as being able to show what applications are creating the traffic?

denny.lecompte replied on Wed, Apr 22 2009 3:04 PM

rated by 0 users

Every NetFlow packet contains and source and destination IP address.

netadm1n replied on Wed, Apr 22 2009 3:16 PM

rated by 0 users

That doesn't sound like it is what I'm looking for.  I want to be able to go to Orion (or even Netflow) and pull up a report that shows me a ranking of how each site's traffic has increased or decreased for a given period of time, perhaps even at specific intervals during that period of time.

The scenario is that I buy a certain amount of bandwidth from an ISP.  I aggregate a bunch of sites behind the link to that ISP.  If the link to the ISP suddenly begins hitting it's maximum, I'd like to look at a ranking of the utilization of all my sites to see which one has suddenly increased it's traffic.  If none of them stand out that way, then the explanation has to be that utilization at all sites has increased.

Can you see any way for Orion or it's Netflow component to provide that kind of a high level report?

Thanks.

netadm1n replied on Thu, Apr 23 2009 11:39 AM

rated by 0 users

Does anyone else have any ideas on this?  Thanks!

ljenkins replied on Fri, Apr 24 2009 10:19 AM

rated by 0 users

I tried pasting this into Report Designer and it seems to have absolutely killed it... but it runs ok in SQL Query Analyser... and to me sounds like what you're looking for:

Create Table #Yesterday (InterfaceID int, AvgBPS real )

Insert Into #Yesterday (InterfaceID, AvgBPS )
(Select InterfaceID, Avg(AverageBPS) as AvgBPS from 
(Select InterfaceID, (In_AverageBPS + Out_AverageBPS) / 2 as AverageBPS, DateTime from InterfaceTraffic_Hourly
Union
Select InterfaceID, (In_AverageBPS + Out_AverageBPS) / 2 as AverageBPS, DateTime from InterfaceTraffic_Daily 
Union
Select InterfaceID, (In_AverageBPS + Out_AverageBPS) / 2 as AverageBPS, DateTime from InterfaceTraffic_Detail) As MinusOneWeek
  where
    MinusOneWeek.DateTime >= upper(month(getdate()-1))+'-'+upper(day(getdate()-1))+'-'+upper(year(getdate()-1)) and 
    MinusOneWeek.DateTime < upper(month(getdate()))+'-'+upper(day(getdate()))+'-'+upper(year(getdate()))
Group by InterfaceID )

Create Table #MinusOneMonth (InterfaceID int, AvgBPS real )

Insert Into #MinusOneMonth (InterfaceID, AvgBPS )
(Select InterfaceID, Avg(AverageBPS) as AvgBPS from 
(Select InterfaceID, (In_AverageBPS + Out_AverageBPS) / 2 as AverageBPS, DateTime from InterfaceTraffic_Hourly
Union
Select InterfaceID, (In_AverageBPS + Out_AverageBPS) / 2 as AverageBPS, DateTime from InterfaceTraffic_Daily 
Union
Select InterfaceID, (In_AverageBPS + Out_AverageBPS) / 2 as AverageBPS, DateTime from InterfaceTraffic_Detail) As MinusOneWeek
  where
    MinusOneWeek.DateTime >= upper(month(getdate()-29))+'-'+upper(day(getdate()-29))+'-'+upper(year(getdate()-29)) and 
    MinusOneWeek.DateTime < upper(month(getdate()-28))+'-'+upper(day(getdate()-28))+'-'+upper(year(getdate()-28))
Group by InterfaceID )

Select Nodes.SysName, Interfaces.InterfaceName , #Yesterday.AvgBPS as CurrentBW,
    #MinusOneMonth.AvgBPS as PreviousBW, 
    (#Yesterday.AvgBPS - #MinusOneMonth.AvgBPS) * 100 / (#MinusOneMonth.AvgBPS+ 0.01) as BWChange
  from Interfaces 
  join nodes on Interfaces.NodeID=Nodes.NodeID
  join #Yesterday on Interfaces.InterfaceID=#Yesterday.InterfaceID
  join #MinusOneMonth on Interfaces.InterfaceID=#MinusOneMonth.InterfaceID
  Order by BWChange Desc

Drop table #Yesterday
Drop table #MinusOneMonth

BWChange column is in percent.

Sorry my SQL isn't great, there's probably better ways to do this.

netadm1n replied on Fri, Apr 24 2009 11:42 AM

rated by 0 users

Hmm.  I don't seem to have SQL Query ANalyzer installed with my MS SQL Server Enterprise 2005 software.  I ran the installer for it again and don't seem to see what will give me that component.  I'm a little leery of messing with the live system anyway.  I'll work on it though if I have to.

I tried seeing where I'd get with Report Designer.  I think it's something basic that's not right, like permissions, perhaps.  I tried have it process even just the first line of your code and it does the same thing you describe - it just hangs.  I wonder if the problem is that the SQL server will not allow any write operations, like creating a new table?  Or maybe a database has to be selected first?  I'm not sure.  I know just enough to be dangerous, I think.  And I don't wanna burn myself!! :)  Thanks for your reply.  I REALLY appreciate it!!

ljenkins replied on Fri, Apr 24 2009 1:47 PM

rated by 0 users

In SQL 2005, if you have the Management Studio, you should just right-click on NetPerfMon database and select "New Query"...

Theoretically, the # at the beginning of the create tables should put them in the TempDB, so I'd think you'd have write access there. 

But like I said my SQL isn't all that great either.  I just saw your problem and was like "I understand what he's getting at and I don't think NetFlow is going to answer the question and the data IS already collected by NPM!"

Heck, maybe someone who is good with SQL will see what this does and now make a proper query.  :)