How to exclude SQL Error Log alerts for certain errors and clients?

PeterDanielsCRB

Apparently, our network admins use a port vulnerability scanner that causes a variety SQL Server Error Log errors.  With the default settings, these fired SQL Monitor alerts, which sent a flood of email notifications to my team (my bad). I want to find a way to exclude these from SQL Monitor alerts.  However, I would prefer not to just completely stop/exclude all alerts with these error #s as I would like to be notified when we get "real" errors.  I considered excluding errors with messages that contained [CLIENT: ##.###.##.##] (the scanner IP address). Unfortunately, there are separate lines in the error log for the Error and the Message that contains the CLIENT IP.  Any advice on handling this situation?

TIA,

-Peter

Russell D

You can filter out errors - using regex - by severity, error number or error message. Go to the Alert configuration page and edit the SQL Error log alert, it should hopefully be self explantory, but let me know if its not clear.

Regarding the seaparate lines, you can enter multiple lines to filter out by just entering a new line in the Regex box. So something akin to:

Is that sufficient?

PeterDanielsCRB

Thank you, @Russell D.  That is helpful.  I'm not sure it will fully fulfill the ideal requirement, though.  I want to filter out a list of log entries with a list of 4 error numbers only when that log entry is followed by one with the [CLIENT: ###.###.###.###].  Does the filtering allow for that?  Seems like it would be difficult to implement.

On a side note, for now, I am just not raising a medium alert unless the severity is >=21 (it was 20).  That keeps us from getting spammed when the scanner runs, but does not give us alerts from "real" error log entries that are sev 20.  

Russell D

It may be possible to do through some convoluted regex. Is the CLIENT ip address always the same?

PeterDanielsCRB

The client IP is always the same, so that's helpful.  I can filter those items.  It's the additional log entry with (only) the actual error #, severity, and state that is problematic.

Russell D

Can you give me an example as it looks from the SQL Log please? Don't forget to redact the IP address.

Russell D

Just out of interest, you've said that there are separate lines but they both include the IP, so why isn't something like \[Client:\s192\.168\.1\.1\] enough? But if there are two messages that are separate and you can't be sure what the second message is going to be I can't think off hand how to solve this.

^\[Client:\s192\.168\.1\.1\]$\x0d\x0a.* for example will match the IP and any subsequent line, but I dont think this will work if its two separate error mesages.

PeterDanielsCRB

Hi @Russell D.  To clarify, the challenge is that the client IP is NOT on both lines.  See image below (IP address has been fuzzed out.  Thank you.

Russell D

Yes ok that makes it a lot more difficult, but given that its the same line it should be doable.
 
You just need to come up with some regex that will match a list of known errors that you want to ignore (granted you may not currently know all messages you want to ignore), and then filter those out in the same regex to ignore messages from the IP address. Though you did say in the initial email that the ClientIP address and messages were on different lines.

Mgtow

did you come up with a solution for this?  I have the exact same thing going on. thanks

PeterDanielsCRB

No, @Mgtow, I had to stick with "I am just not raising a medium alert unless the severity is >=21 (it was 20).  That keeps us from getting spammed when the scanner runs, but does not give us alerts from "real" error log entries that are sev 20. "

Mgtow

@PeterDanielsCRB I was able to finally get SQL Monitor to ignore those port scanning errors in the SQL logs by using the expression below.  I put the expression inside the box "Exclude error log entries by error message matching the following regular expressions:" under the SQL SERVER ERROR LOG ENTRY ALERT.
BTW, I have the medium alert severity set to >=10 to catch all non-info messages as well.  I came across this cool website which helped me fine tune the solution, highly recommended.  https://regexr.com/

/CLIENT: 12.34.56.78/

KHsql01

@Mgtow How did you get SQL monitor to ignore those port scanning errors in the sql logs? Where is the tool interface you put the expression inside the box "Exclude error log entries by error message matching the following regular expressions:" under the SQL SERVER ERROR LOG ENTRY ALERT.

I couldn't find that interface on SSMS. Please advise!