As part of team documentation I’m getting everyone to write runbooks for known issues. This post is part of series of posts based on those books.

Problem

The SQL Server standard build configure alerts for 18-25 with email notification. An example of the email notification is below.

Sev20_01

As you can see the email doesn’t show Which user has the problem or the workstation (you do get an IP though).

Reason

This alert is normally raised by team members with SSMS open having there AD password change. So are getting authentication issues.

Solution

Manual Solution

To manually work out which user and machine the error is coming from you can use the Windows Event Log (Using either the SSMS log as in this example, or Event viewer and doing a connection to the remote server).

SSMS management → Logs → Windows NT → Security

Sev20_02

Looking in the details we get the account Name, Domain etc. So we can contact the user and resolve the problem.

PowerShell Solution

While the manual solution is nice, its time consuming if we have multiple alerts, slow and cumber-sum (we could easily miss a solution doing it manually). The solution of course. Is PowerShell.. Using Get-EventLog and parsing for the specific error ID. We can filter the results quickly. From there some text parsing of the message and we can get something like:

Sev20_03.png

The function takes 2 parameters (at the time of writing). -SQLServer (The server that is receiving the Severity 20 alerts) and the max number of events you wish to return (This isn’t needed as the default is 5).

The code for the function is on GitHub: https://github.com/Staggerlee011/PowerShell/blob/master/SQLServer/Get-DbaSev20.ps1

 

Round Up

I hope you found this post useful any feedback is welcome as always. 🙂

Advertisements