9 Replies Latest reply on Apr 24, 2009 1:47 PM by ljenkins

    Who Grew? (utilization investigation)

    netadm1n

      Hello :)

           I'm running Orion 9.1 SP3.  I'm monitoring ~200 Cisco routers.  I'm trying to figure out if there's a way I can get a report which will show me where daily traffic flows are growing among those routers.  Overall traffic flow incoming from our ISP recently grew suddenly a bit and I want to see if that appears to be from a particular site(s) with increasing traffic or if it's just an effect of traffic growing among all our sites overall.

          At first thought this seems like an obvious kind of question to ask from Orion and all it's gathered data.  However, as I start looking at the canned reports and also at the custom report writer, it seems to me there is no straightforward way to try and put something like that together, if at all.

           Either that or I am completely blowing it for due dilligence.  Which I won't swear to never suffering from :)  Thank you for any advice or pointers anyone has time to give!

      -John

        • Re: Who Grew? (utilization investigation)
          denny.lecompte

          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.

            • Re: Who Grew? (utilization investigation)
              netadm1n

              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?

                • Re: Who Grew? (utilization investigation)
                  denny.lecompte

                  Every NetFlow packet contains and source and destination IP address.

                    • Re: Who Grew? (utilization investigation)
                      netadm1n

                      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.

                        • Re: Who Grew? (utilization investigation)
                          netadm1n

                          Does anyone else have any ideas on this?  Thanks!

                            • Re: Who Grew? (utilization investigation)
                              ljenkins

                              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.
                                • Re: Who Grew? (utilization investigation)
                                  netadm1n

                                  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!!

                                    • Re: Who Grew? (utilization investigation)
                                      ljenkins

                                      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.  :)

                                • Re: Who Grew? (utilization investigation)
                                  Congo

                                  I do something similar, and I use Netflow to achieve this. However, I use a program called Netflow Trakker from Fluke Networks. Much higher granularity than the Solarwinds equivalent.

                                  In your situation, I think that you would want to use some clever alerting on % utilization or even traffic rate and have it alert you when they exceed their aggregated allocation.