functions/Get-DbaDbMailLog.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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
function Get-DbaDbMailLog {
<#
 .SYNOPSIS
  Gets the DBMail log from a SQL instance
  
 .DESCRIPTION
  Gets the DBMail log from a SQL instance
  
 .PARAMETER SqlInstance
  The SQL Server instance, or instances.
  
 .PARAMETER SqlCredential
  Allows you to login to servers using SQL Logins as opposed to Windows Auth/Integrated/Trusted.
 
 .PARAMETER Since
 Datetime object used to narrow the results to the send request date
  
 .PARAMETER Type
 Narrow the results by type. Valid values include Error, Warning, Success, Information, Internal
  
 .PARAMETER Silent
  Use this switch to disable any kind of verbose messages
  
 .NOTES
  Tags: Logging
  Website: https://dbatools.io
  Copyright: (C) Chrissy LeMaire, clemaire@gmail.com
  License: GNU GPL v3 https://opensource.org/licenses/GPL-3.0    
  
 .LINK
  https://dbatools.io/Get-DbaDbMailLog
  
 .EXAMPLE
  Get-DbaDbMailLog -SqlInstance sql01\sharepoint
   
  Returns the entire dbmail log on sql01\sharepoint
 
 .EXAMPLE
  Get-DbaDbMailLog -SqlInstance sql01\sharepoint | Select *
   
  Returns the entire dbmail log on sql01\sharepoint then return a bunch more columns
 
 .EXAMPLE
  $servers = "sql2014","sql2016", "sqlcluster\sharepoint"
  $servers | Get-DbaDbMailLog -Type Error, Information
   
  Returns only the Error and Information dbmail log for "sql2014","sql2016" and "sqlcluster\sharepoint"
 
#>
    
    [CmdletBinding()]
    param (
        [Parameter(ValueFromPipeline = $true)]
        [Alias("ServerInstance", "SqlServer")]
        [DbaInstanceParameter[]]$SqlInstance,
        [Alias("Credential")]
        [PSCredential]
        $SqlCredential,
        [DateTime]$Since,
        [ValidateSet('Error', 'Warning', 'Success', 'Information', 'Internal')]
        [string[]]$Type,
        [switch]$Silent
    )
    process {
        foreach ($instance in $SqlInstance) {
            Write-Message -Level Verbose -Message "Attempting to connect to $instance"
            
            try {
                $server = Connect-SqlInstance -SqlInstance $instance -SqlCredential $SqlCredential
            }
            catch {
                Stop-Function -Message "Failure" -Category Connectiondbmail -dbmailRecord $_ -Target $instance -Continue
            }
            
            $sql = "SELECT SERVERPROPERTY('MachineName') AS ComputerName,
     ISNULL(SERVERPROPERTY('InstanceName'), 'MSSQLSERVER') AS InstanceName,
     SERVERPROPERTY('ServerName') AS SqlInstance,
     log_id as LogId,
     CASE event_type
     WHEN 'error' THEN 'Error'
     WHEN 'warning' THEN 'Warning'
     WHEN 'information' THEN 'Information'
     WHEN 'success' THEN 'Success'
     WHEN 'internal' THEN 'Internal'
     ELSE event_type
     END as EventType,
     log_date as LogDate,
     REPLACE(description, CHAR(10)+')', '') as Description,
     process_id as ProcessId,
     mailitem_id as MailItemId,
     account_id as AccountId,
     last_mod_date as LastModDate,
     last_mod_user as LastModUser,
     last_mod_user as [Login]
     FROM msdb.dbo.sysmail_event_log"

            
            if ($Since -or $Type) {
                $wherearray = @()
                
                if ($Since) {
                    $wherearray += "log_date >= '$($Since.ToString("yyyy-MM-ddTHH:mm:ss"))'"
                }
                
                if ($Type) {
                    $combinedtype = $Type -join "', '"
                    $wherearray += "event_type in ('$combinedtype')"
                }
                
                $wherearray = $wherearray -join ' and '
                $where = "where $wherearray"
                $sql = "$sql $where"
            }
            
            Write-Message -Level Debug -Message $sql
            
            try {
                $server.Query($sql) | Select-DefaultView -Property ComputerName, InstanceName, SqlInstance, LogDate, EventType, Description, Login
            }
            catch {
                Stop-Function -Message "Query failure" -InnerErrorRecord $_ -Continue    
            }
        }
    }
}