New Check - Agent Alerts are configured

[jpomfret]

Agent alerts are setup.

Glenn Berry suggests 19-25 & 825
https://www.red-gate.com/simple-talk/sql/database-administration/provisioning-a-new-sql-server-instance-part-three/

Brent Ozar suggests 16-25 & 823-825
https://www.brentozar.com/blitz/configure-sql-server-alerts/

[wsmelton]

This one would be difficult in my opinion, only because just having the alert configured does not mean anything if you don't have the notification for it setup as well.

[jpomfret]

Just logged off for the night, but is there no way to check for the notification piece also? Also kind of envisioning the agent tests to build up a better picture, with database mail configured, operator set up, alerts configured etc.

[wsmelton]

Well, an alert being configured is irrelevant on whether they are using Database mail or not...there are other options for notifications.

It is a matter of saying what items should be in place (or validated) to say an alert is configured properly.

[jpomfret]

Looked more into this today - there is a 'HasNotification' property in Server.JobServer.Alerts. I would propose three checks around this.

• Alert exist
• Alert is enabled
• Alert has notification

We could also check that 'DelayBetweenResponses' is set to something other than 0. I saw this in a presentation somewhere, basically sets it so the event can only fire after X time since the last alert.

I setup the following alerts and set 20 to disabled:

Also didn't set up notification on 19:

Results from $server.JobServer.Alerts

[wsmelton]

DelayBetweenResponses would be a good one, can make that value configured item so folks can ensure they have it set to their standard across the board.

[PsPsam]

Hi
I have started to look at this I have done the following below in my repo. Since we have SCOM monitor at our place we have created an agent job that will write to eventlog incase we have one of these alerts instead of having SQL notify us by email. I choose to set notification by operator as default. Any thoughts? Will add for 823-825 also but since that is on messageID instead of severity I think it will be in another Describe block since I named this Agent Alerts Severity

Set-PSFConfig -Module dbachecks -Name agent.alert.Severity -Value @('16', '17', '18', '19', '20', '21', '22', '23', '24', '25')  -Initialize -Description "Agent alert severity alerts to check if in place https://www.brentozar.com/blitz/configure-sql-server-alerts/"
Set-PSFConfig -Module dbachecks -Name agent.alert.Job -Value $false -Initialize -Description "Agent alert job in place to catch the alert check. Ex job to write to eventlog for SCOM monitoring"
Set-PSFConfig -Module dbachecks -Name agent.alert.Notification -Value $true -Initialize -Description "Agent alert notification in place check"

Describe "Agent Alerts Severity" -Tags AgentAlertSeverity, $filename {
    $severity = Get-DbcConfigValue agent.alert.Severity
    $AgentAlertJob = Get-DbcConfigValue agent.alert.Job
    $AgentAlertNotification = Get-DbcConfigValue agent.alert.Notification
    write-host $severity
    write-host $AgentAlertJob
    write-host $AgentAlertNotification
    @(Get-SqlInstance).ForEach{
        write-host $psitem
        Context "Testing Agent Alerts Severity settings on $psitem" {
            $alerts = Get-DbaAgentAlert -SqlInstance $psitem
            ForEach ($sev in $severity) {
                It "Should have Severity $sev Alert" {
                    ($alerts.Where{$psitem.Severity -eq $sev}) | Should -be $true -Because "Recommended Agent Alerts to check for http://blog.extreme-advice.com/2013/01/29/list-of-errors-and-severity-level-in-sql-server-with-catalog-view-sysmessages/"
                }
                It "Should have Severity $sev Alert enabled" {
                    ($alerts.Where{$psitem.Severity -eq $sev}) | Should -be $true -Because "Configured alerts should be enabled check"
                }
                if ($AgentAlertJob) {
                    It "Should have Jobname for $sev Alert" {
                        ($alerts.Where{$psitem.Severity -eq $sev}).jobname -ne $null | Should -be $true  -Because "Notify by SQL Agent job check"
                    }
                }
                if ($AgentAlertNotification) {
                    It "Should have notification for $sev Alert" {
                        ($alerts.Where{$psitem.Severity -eq $sev}).HasNotification -eq 1 | Should -be $true -Because "Notify by Notifications check"
                    }
                }
            }
        }
    }
}

[SQLDBAWithABeard]

I like this :-)

Good work @PsPsam , Thank you

Please can you run the Unit checks to ensure that the output is formatted correctly
Please can you create a PR with only those changes to the two files

[PsPsam]

PR created

I hope I did i right this time. If not let me know, git is new to me and PR is scary for me :D