5 Replies Latest reply on Dec 11, 2012 7:31 PM by RichardLetts

    Historical Percent Utilization

    jkevoian

      I'm trying to put together a report that would show when, how long, and how many times each week each of our MPLS router interfaces transmitted/received 75% utilization.  Basically, we want to keep track of how long the received/transmitted percent utilization occurred - based on the event alerts that we receive.

       

      Is there a report - or similar report - already in the Orion Report Writer that would help give me some or all of this information? (I don't necessarily need the "how many times each week" this happened to each MPLS router. I could instead list each individual time for either utilization that happened for each MPLS router.)

        • Re: Historical Percent Utilization
          Sohail Bhamani

          Hello,

           

          I found this in the content exchange area and think it may be a good starting point for you.

           

          Check for how long link was utilized above SLA Mark

           

          I havent looked at it yet, but regardless, it should provide something close to what you are asking for based on the description.

           

          Sohail Bhamani

          Loop1 Systems

          http://www.loop1systems.com

            • Re: Historical Percent Utilization
              jkevoian

              Sohail,

               

              Thanks for your help.  I think this is kind of what I'm looking for.  However, how would the SQL query be modified to display the utilization above 75% - instead of 65%?

               

              SELECT   Nodes.Caption AS NodeName,

              StartTime.EventTime,

              StartTime.Message,

              DATEDIFF(Mi, StartTime.EventTime,

                  (SELECT TOP 1

                      EventTime

                      FROM Events AS Endtime

                      WHERE EndTime.EventTime > StartTime.EventTime

                          AND EndTime.EventType = 5001

                          AND EndTime.NetworkNode = StartTime.NetworkNode

                          ORDER BY EndTime.EventTime)) AS OutageDurationInMinutes

               

              FROM Events StartTime INNER JOIN Nodes ON StartTime.NetworkNode = Nodes.NodeID

              WHERE

               

              (StartTime.EventType = 5000)

              AND 

              (StartTime.Message LIKE '%Utilization%')

              AND

              StartTime.EventTime between dateadd(month,-1, getdate()) and getdate()

               

               

              ORDER BY 2 DESC

                • Re: Historical Percent Utilization
                  RichardLetts

                  this works from the network utilization events. The thresholds are whatever you have set as the interface utilization alarms in the NPM thresholds (i.e. the 65%/75% are not coded into the query)

                    • Re: Historical Percent Utilization
                      jkevoian

                      Richard: I noticed that the time displayed in the report itself didn't match the time from when the alert e-mail was first sent and the reset e-mail that was sent later on.  For instance, an alert e-mail was sent today at around 3:10 PM and the reset at 3:21 PM.  However, the SQL query report did not match the e-mail time (11 minutes); I think the report itself showed something like 15 minutes.  Should this be the case?  Also, would there be a way to modify the "Where statement" to filter from the past seven days whenever the report is opened: "StartTime.EventTime between dateadd(month,-1, getdate()) and getdate()?"  I am somewhat of a SQL beginner.

                        • Re: Historical Percent Utilization
                          RichardLetts

                          Events != Alerts

                          If your alert setting has to wait for 5 minutes before sending an email for an over-utilization alert you will get a difference, this report works off events.

                           

                          google is your friend... DATEADD (Transact-SQL)

                          I'd try something like ... StartTime.EventTime between dateadd(week,-1, getdate()) and getdate() ...

                           

                          If you are going to be working with SQL I recommend SQL for Dummies (it's quite a well-written book). I've been doing database for many years now and I still check  functions in the manuals and use ANSI-SQL. (the above query is not ansi-sql and won't work on many other databases)