12 Replies Latest reply on Sep 8, 2011 10:36 AM by rothabe

    How to report on how many times a bandwidth threshold has been exceeded?

    patriot

      I would like to create a report that shows how many times a site (or switch port) has met or exceeded 100% transmit or receive bandwidth over a given period of time.

      Can anyone help me with that? Would it be an event report or an alert report type?

      Thanks for any assistance.

        • Re: How to report on how many times a bandwidth threshold has been exceeded?
          lag

          You could probably do this using the events table and an alert. As an alert trigger action, log a message to the NPM Eventlog that is explicit enough to filter on in the report.

          I have something similar for a memory alert here: 

           

          Select

            Nodes.Caption AS "Node Name",

            Nodes.NodeID,

            Count(Events.EventType) AS "Count"

           

          From

            Events

            Inner Join Nodes on Events.NetworkNode = Nodes.NodeID

           

          Where

            Events.Message LIKE '%Memory Alert: %'

           

          AND 

            eventtime between dateadd(month, -1, getdate()) AND getdate() AND (

            (DATEPART(weekday, EventTime) >= 1) AND 

            (DATEPART(weekday, EventTime) <= 7) AND 

            (Convert(Char,EventTime,108) >= '00:01') AND 

            (Convert(Char,EventTime,108) <= '23:59'))

           

           

           

          Hope that helps. 

            • Re: How to report on how many times a bandwidth threshold has been exceeded?
              patriot

              Thanks lag, but when I run this query I get the following error message in Report Writer:

              "SQL Error: Column 'Nodes.Caption' is invalid in the select list because it is not contained in either an aggregate function or the Group By clause."

              Can you troubleshoot?

                • Re: How to report on how many times a bandwidth threshold has been exceeded?
                  lag

                  Sorry, forgot to add "group by nodes.caption, nodes.nodeid" at the end like so:

                   

                  Select

                    Nodes.Caption AS "Node Name",

                    Nodes.NodeID,

                    Count(Events.EventType) AS "Count"

                   

                  From

                    Events

                    Inner Join Nodes on Events.NetworkNode = Nodes.NodeID

                   

                  Where

                    Events.Message LIKE '%Memory Alert: %'

                   

                  AND 

                    eventtime between dateadd(month, -1, getdate()) AND getdate() AND (

                    (DATEPART(weekday, EventTime) >= 1) AND 

                    (DATEPART(weekday, EventTime) <= 7) AND 

                    (Convert(Char,EventTime,108) >= '00:01') AND 

                    (Convert(Char,EventTime,108) <= '23:59'))

                   

                    group by nodes.caption, nodes.nodeid

                   

                   

                   

                  is that any better?

                   

                  As an additional note, in Field Formatting select NodeID as a hidden field. This will let you link from the report to the node in NPM without having to look at the nodeid on the report.

                    • Re: How to report on how many times a bandwidth threshold has been exceeded?
                      patriot

                      That worked great lag! Thanks. Now, let me throw in one more wrinkle please.

                      I would like to incorporate a particular alert into this report. In other words I want to count the number of times a particular alert fired on a particular interface. I think I can reference the AlertID in the Alerts table, but I don't know the JOIN statement that would have to be written.

                      Can you help?

                        • Re: How to report on how many times a bandwidth threshold has been exceeded?
                          lag

                          ok.. An interface alert is just a little different:

                           

                               Select

                            Interfaces.FullName,

                            Interfaces.InterfaceID,

                            Count(Events.EventType) AS "Count"

                           

                          From

                            Events

                            Inner Join Interfaces on Events.NetObjectID = Interfaces.InterfaceID

                           

                          Where

                            Events.Message LIKE '%Interface%'

                           

                          AND 

                            eventtime between dateadd(month, -1, getdate()) AND getdate() AND (

                            (DATEPART(weekday, EventTime) >= 1) AND 

                            (DATEPART(weekday, EventTime) <= 7) AND 

                            (Convert(Char,EventTime,108) >= '00:01') AND 

                            (Convert(Char,EventTime,108) <= '23:59'))

                           

                            group by Interfaces.FullName, Interfaces.InterfaceID

                           

                          Let me know if that works out.

                            • Re: How to report on how many times a bandwidth threshold has been exceeded?
                              patriot

                              That works too, but what I was hoping to do was include information about the actual alerts that are triggered for an interface. Specifically, in the EventTypes table are two fields, "Name" and EventType", while in the Alerts table are, "AlertID" and "AlertName".

                              I would like to total the number of times a particular AlertID and/or AlertName are triggered by interface over a period of time.

                              Can I do that?

                              What would the join statement look like?

                                • Re: How to report on how many times a bandwidth threshold has been exceeded?
                                  lag

                                  I'm not sure how to go about that one. I could be wrong, but any alert only has one EventID (5000), which is why I base it off the message that you send to the events table.

                                  I think I see what you mean, though.

                                  Interface  -  Alert Type/Name  -   How Many

                                   

                                  I haven't been able to figure that one out myself. The trouble is that any alert creates eventID 5000, so it's not unique to a specific alertID. Also, I haven't been able to get the message field into the query above (SQL Error - The text, ntext, and image data types cannot be compared or sorted...).

                                  If you have a DBA on site, you might try exchanging a 6 pack of beer for the proper method to get that sorted. I'm not convinced you can do it given how that works at the moment.  =)

                                  • Re: How to report on how many times a bandwidth threshold has been exceeded?
                                    AndyCoates


                                    ....while in the Alerts table are, "AlertID" and "AlertName".

                                    I would like to total the number of times a particular AlertID and/or AlertName are triggered by interface over a period of time.



                                    Alerts table is for Basic Alerts if I recall, not Advanced Alerts (which I presume you're using).

                                    The AlertLog table is probably what you want to be looking in, but unfortunately you won't be able to tie it to the Events table - I can't see any relationship between the two for pulling out matching events.

                                    So whilst you can't do this at 'trigger time' (from what I can tell) you could still produce a separate report by using the AlertLog table (joining with AlertDefinitions) to do general analysis on alerts per node/interface etc.

                                      • Re: How to report on how many times a bandwidth threshold has been exceeded?
                                        rothabe

                                        I know it hasn't been that long since this thread was updated, but I was just wondering if you'd gotten any farther on this.

                                        I'm trying to figure out how to get a report that shows - for a given monitor, within a given time frame, show the number of critical alerts generated and show % of total time monitor was in alert state.

                                        I realize this isn't the exact report you were looking at, but it's similar enough that I was hoping to use some of what you learned here.

                                        Any advice/feedback would be appreciated.

                                        -Blake