Severity 20 Runbook and PowerShell Function
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.
The SQL Server standard build configure alerts for 18-25 with email notification. An example of the email notification is below.
As you can see the email doesn’t show Which user has the problem or the workstation (you do get an IP though).
This alert is normally raised by team members with SSMS open having there AD password change. So are getting authentication issues.
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
Looking in the details we get the account Name, Domain etc. So we can contact the user and resolve the problem.
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:
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
I hope you found this post useful any feedback is welcome as always. 🙂