Balancing an environment monitor using SQL

I am working on something very specific so I’m hoping others have an idea of what approach might work.

I’m trying to make it so a monitoring script that runs once every 5 minutes will not blast everyone with redundant notifications. I’m attempting to use SQL to balance the load but I’m struggling to come up what queries I need.

I’ve set up this table:

Create Table MonitorDetails (
    MonitorID int IDENTITY(1,1) PRIMARY KEY,
    IssueType nvarchar(255) Not Null,
    IssueDetails Nvarchar(max) not null,
    Hostname nvarchar(255),
    Criticality int not null,
    ReportedTime Datetime not null,
    ResolvedTime Datetime,
    NotificationSent bit Default 0
)

And all failures are dumping into that table.

So here would be an example case:

• Error is thrown
• Error is recorded in the table
• 5 minutes later the error is recorded again because it hasn’t been resolved

The script I’m running is going to look at the records in the table and I want the first time the error is recorded to send the notification and set the notification flag, but if the error shows up again and a notification has already been sent, it will not send another notification.

Finding the duplicates is the easy part, but how do I exclude duplicates (or bulk update duplicates) where ONE of the records has the notification flag set?

SELECT
    IssueType, IssueDetails, Hostname, Criticality, ReportedTime, COUNT(*)
FROM
    MonitorDetails
Where ResolvedTime is null
GROUP BY
    IssueType, IssueDetails, Hostname, Criticality, ReportedTime
HAVING 
    COUNT(*) > 1

I may be over thinking this, but I’m trying to keep the queries lightweight so they aren’t a burden on the SQL server itself. Anyone have suggestions?

Answer

Add MAX(NotificationSent) to the selection list. If the value is 1, notification is already sent.

If you want to return only those that notification is not send, update the HAVING condition:

HAVING COUNT(*) > 1 AND MAX(NotificationSent)=0