WMI alert for event 162 User Error Message

Question

Can anybody help.   I am new to WMI.

I am trying to create an alert for trace event 162 - User Error Message

I already have standard alerts for Login failed for user - 18452, 18456, 18457.  The problem is that these errors don't tell you the 'real' database the user is trying to log into.  They show the database as master.

The other day I started a profiler trace and looked for event 162 - User Error Message and 20- Login Failed.  In the trace file I saw messages like 

- Cannot open database "<DATABASENAME>" requested by the login. The login failed.
- 2014-07-22 20:05:56.60 Logon       Error: 18456, Severity: 14, State: 38.  2014-07-22 20:05:56.60 Logon       Login failed for user '<UserName>'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]  
- Login failed for user '<UserName>'. Reason: Failed to open the explicitly specified database. [CLIENT: xxx.xxx.xxx.xxx]
- Login failed for user '<UserName>'.

The first error is is event 162 and contains my database name. The error number for this error is 4060.

Is there any way to create a SQL Server alert for this event/error and capture the text string?  I tried standard alerts but was un successful so I thought WMI might be the answer.

Is there a wmi class for user error messages?  Does anybody have any ideas on how to get this to work?

Answer 1

If you already have alerts in place for the others then can you not add 4060?

EXEC msdb.dbo.sp_altermessage 4060,'WITH_LOG','true';
GO
SELECT * FROM sys.messages WHERE [message_id] = 4060;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Message 4060', 
		@message_id=4060, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

---

Jon

Answer 2

Thanks Jon,   I tried that but it will not fire off.  I am guessing the Message id is to low.

Answer 3

MY MISTAKE!!!!!

It worked great.  Thanks

Answer 4

Glad it works for you now. In order for the alert to be fired some messages need to have the with_log setting changed to true.

---

Jon