functions/Get-DbaAgentAlert.ps1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
function Get-DbaAgentAlert {
    <#
    .SYNOPSIS
    Returns all SQL Agent alerts on a SQL Server Agent.
 
    .DESCRIPTION
    This function returns SQL Agent alerts.
 
    .PARAMETER SqlInstance
    SqlInstance name or SMO object representing the SQL Server to connect to.
    This can be a collection and receive pipeline input.
 
    .PARAMETER SqlCredential
    PSCredential object to connect as. If not specified, current Windows login will be used.
 
    .NOTES
    Author: Klaas Vandenberghe ( @PowerDBAKlaas )
    Tags: Agent, SMO
    Website: https://dbatools.io
    Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
    License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0
 
    .PARAMETER EnableException
    By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
    This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
    Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.
 
    .LINK
    https://dbatools.io/Get-DbaAgentAlert
 
    .EXAMPLE
    Get-DbaAgentAlert -SqlInstance ServerA,ServerB\instanceB
    Returns all SQL Agent alerts on serverA and serverB\instanceB
 
    .EXAMPLE
    'serverA','serverB\instanceB' | Get-DbaAgentAlert
    Returns all SQL Agent alerts on serverA and serverB\instanceB
 
#>

    [CmdletBinding()]
    param (
        [parameter(Position = 0, Mandatory = $true, ValueFromPipeline = $True)]
        [Alias("ServerInstance", "Instance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [PSCredential]
        $SqlCredential,
        [switch][Alias('Silent')]$EnableException
    )

    process {
        foreach ($instance in $SqlInstance) {
            try {
                Write-Message -Level Verbose -Message "Connecting to $instance"
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $sqlcredential
            }
            catch {
                Stop-Function -Message "Failure" -Category ConnectionError -ErrorRecord $_ -Target $instance -Continue
            }

            Write-Message -Level Verbose -Message "Getting Edition from $server"
            Write-Message -Level Verbose -Message "$server is a $($server.Edition)"

            if ($server.Edition -like 'Express*') {
                Stop-Function -Message "There is no SQL Agent on $server, it's a $($server.Edition)" -Continue
            }

            $defaults = "ComputerName", "SqlInstance", "InstanceName", "Name", "ID", "JobName", "AlertType", "CategoryName", "Severity", "IsEnabled", "DelayBetweenResponses", "LastRaised", "OccurrenceCount"

            $alerts = $server.Jobserver.Alerts

            foreach ($alert in $alerts) {
                $lastraised = [dbadatetime]$alert.LastOccurrenceDate

                Add-Member -Force -InputObject $alert -MemberType NoteProperty -Name ComputerName -value $server.NetName
                Add-Member -Force -InputObject $alert -MemberType NoteProperty -Name InstanceName -value $server.ServiceName
                Add-Member -Force -InputObject $alert -MemberType NoteProperty -Name SqlInstance -value $server.DomainInstanceName
                Add-Member -Force -InputObject $alert -MemberType NoteProperty Notifications -value $alert.EnumNotifications()
                Add-Member -Force -InputObject $alert -MemberType NoteProperty LastRaised -value $lastraised

                Select-DefaultView -InputObject $alert -Property $defaults
            }
        }
    }
}