locked
WMI alert for event 162 User Error Message RRS feed

  • 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?

    Wednesday, July 23, 2014 8:38 PM

Answers

  • 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

    • Proposed as answer by Prashanth Jayaram Thursday, July 24, 2014 8:22 PM
    • Marked as answer by tracycai Friday, July 25, 2014 1:24 AM
    Wednesday, July 23, 2014 10:15 PM

All replies

  • 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

    • Proposed as answer by Prashanth Jayaram Thursday, July 24, 2014 8:22 PM
    • Marked as answer by tracycai Friday, July 25, 2014 1:24 AM
    Wednesday, July 23, 2014 10:15 PM
  • Thanks Jon,   I tried that but it will not fire off.  I am guessing the Message id is to low.
    Thursday, July 24, 2014 3:40 PM
  • MY MISTAKE!!!!!

    It worked great.  Thanks

    Thursday, July 24, 2014 3:48 PM
  • 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

    Thursday, July 24, 2014 8:20 PM