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

bump please.

I believe this is an existing feature request. I'll mark it for a PM to see.

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. 

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?

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.

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?

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.

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?

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

Yeah, I think you may be right Andy. Maybe that's why it is a feature request. I personally see quite a few requests for this sort of report. It is not unusual to want to know how many times a particular alert fired over time.