Email Notification for DBA Monitoring and Alerts - Powershell by serverku

Last time I wrote about Powershell script to collect the SQL data collector and WMI data collector. Today I would like share one more Powershell script to send an email alerts for collected data statistics and some other counter queries.

1. Create a DBARepository database and following tables there

CREATE DATABASE [DBARepository]
GO
 
USE [DBARepository]
GO

CREATE TABLE [dbo].[SQLServerInstances](
 [InstanceID] [smallint] IDENTITY(1,1) NOT NULL,
 [DBName] [varchar](100) NULL,
 [IsActive] [bit] NULL,
 [UserName] [nvarchar](200) NULL,
 [Password] [nvarchar](200) NULL,
 [ServerName]  [varchar](100) NULL,
 [IsDataCollector] [bit] NOT NULL DEFAULT ((0)),
 [IsEmailAlert] [bit] NOT NULL DEFAULT ((0)),
 [IsIndexBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsSQLObjectBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsDatabaseObjectBackup] [bit] NOT NULL DEFAULT ((0)),
 [IsSchemaBackup] [bit] NOT NULL DEFAULT ((0))
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[EmailAlerts](
 [AlertID] [smallint] IDENTITY(1,1) NOT NULL,
 [AlertName] [varchar](100) NULL,
 [AlertScript] [varchar](8000) NULL,
 [InstanceIDs] [varchar](20) NULL,
 [EmailTo] [varchar](200) NULL,
 [EmailCC] [varchar](200) NULL,
 [EmailBCC] [varchar](200) NULL,
 [IsActive] [bit] NULL,
 [Schedule] [varchar](30) NULL,
 [Occurrence] [smallint] NULL,
 [StartDate] [smalldatetime] NULL,
 [LastRun] [smalldatetime] NULL,
 [NextRun] [smalldatetime] NULL,
 [FromEmail] [varchar](50) NULL,
 [smtp] [varchar](30) NULL,
 [EndDate] [datetime] NULL,
 [IsWeekendAlert] [bit] NOT NULL DEFAULT ((0)),
 [Note] [varchar](4000) NULL
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[DBAErrorLog](
 [CounterName] [varchar](200) NULL,
 [ServerName] [varchar](100) NULL,
 [ErrorMessage] [varchar](4000) NULL,
 [ErrorDate] [datetime] NULL,
 [IsSolved] [bit] NULL
) ON [PRIMARY]
 
GO
CREATE FUNCTION [dbo].[GetNextScheduledRunDate] 
(
    @StartDateTime     DATETIME
    ,@PreNextRunDateTime DATETIME
    ,@FrequencyType     VARCHAR(10) -- YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND
    ,@FrequencyInterval  INT
 ,@IsWeekendAlert     BIT
)
RETURNS DATETIME
AS
BEGIN

    DECLARE @StartDateTime_   DATETIME    = @StartDateTime
    DECLARE @NextRunDateTime_   DATETIME    = @PreNextRunDateTime
    DECLARE @FrequencyType_   VARCHAR(10) = @FrequencyType
    DECLARE @FrequencyInterval_ INT     = @FrequencyInterval
    DECLARE @CurrentDate_   DATETIME    = GETDATE()
    DECLARE @TempRunDateTime_   DATETIME

    SET @NextRunDateTime_ = ISNULL(@NextRunDateTime_,@StartDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_

    IF @FrequencyType_ = 'YEAR'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(YEAR, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END
    
    IF @FrequencyType_ = 'MONTH'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(MONTH, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'WEEK'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(WEEK, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'DAY'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(DAY, @FrequencyInterval_, @NextRunDateTime_)
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'HOUR'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(HOUR, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'MINUTE'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(MINUTE, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END

    IF @FrequencyType_ = 'SECOND'
    BEGIN
    WHILE @NextRunDateTime_ <= @CurrentDate_
    BEGIN
    SET @NextRunDateTime_ = DATEADD(SECOND, @FrequencyInterval_, @NextRunDateTime_)
    IF DATEPART(DAY,@NextRunDateTime_) > DATEPART(DAY,@TempRunDateTime_)
    BEGIN
    SET @NextRunDateTime_ =  CAST(CAST(@NextRunDateTime_ AS DATE) AS DATETIME) + CAST(CAST(@StartDateTime_ AS TIME) AS DATETIME)
    END
    SET @TempRunDateTime_ = @NextRunDateTime_
    END
    END
 
 
 IF (@IsWeekendAlert = 0 AND @FrequencyType_ IN ('HOUR','MINUTE','SECOND'))
 BEGIN
  IF (DATENAME(WEEKDAY,@NextRunDateTime_) = 'Saturday')
  BEGIN
   SET @NextRunDateTime_ = DATEADD(DAY,2,@NextRunDateTime_)
  END
  ELSE IF (DATENAME(WEEKDAY,@NextRunDateTime_) = 'Sunday') 
  BEGIN
   SET @NextRunDateTime_ = DATEADD(DAY,1,@NextRunDateTime_)
  END
 END
 
    RETURN @NextRunDateTime_
END


GO

[dbo].[EmailAlerts] - Includes the queries with particular schedule which run against specified servers and send data in table format to specified emails, Need to pass Instance Ids comma separated
[dbo].[DBAErrorLog] - Log every errors occurred in the script for easy debug

2. Create following PS1 file and schedule in SQL Server Agent or Windows Task

<#
File : EmailAlerts.ps1
Created By : Paresh Prajapati
Created date : 01/09/2015
Purpose : Send an email notification for queries data
#>

Try
{
$Counter = 'EmailAlerts - '
$DCInstance = '.'
$DCDatabase = 'dbarepository'
$DCQuery = "SELECT AlertName, AlertScript,InstanceIDs,EmailTo,EmailCC,Schedule,Occurrence,smtp,FromEmail,Note FROM [dbo].[EmailAlerts] WHERE IsActive = 1 AND InstanceIDs IS NOT NULL AND ISNULL(NextRun,0) <= GETDATE() AND (GETDATE() BETWEEN StartDate AND EndDate)"
$dccn=new-object System.Data.SqlClient.SQLConnection
#$dcconstring = "Server=$DCInstance;Database=$DCDatabase;user id=$DCUserName;password=$DCPassword" 
$dcconstring = "Server=$DCInstance;Database=$DCDatabase;Integrated Security=sspi" 

#$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Username $DCUserName  -Password $DCPassword -Query $DCQuery
$AlertScripts = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $DCQuery

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter

$dccn.ConnectionString=$dcconstring 
$dccn.Open()

if ($AlertScripts -ne $null)
{
 foreach ($AlertScript in $AlertScripts) 
 {
  Try
  {
    $DataSet = New-Object System.Data.DataSet
    $Result = New-Object System.Data.DataTable

    $InstanceIDs = $AlertScript.InstanceIDs.ToString()
    $AlertName = $AlertScript.AlertName.ToString()
    $Script = $AlertScript.AlertScript.ToString()
    $EmailTo = $AlertScript.EmailTo.ToString()
    $emailCC = $AlertScript.EmailCC.ToString()
    $Schedule = $AlertScript.Schedule.ToString()
    $Occurrence = $AlertScript.Occurrence.ToString()
    $smtp = $AlertScript.smtp.ToString()
    $FromEmail = $AlertScript.FromEmail.ToString()
    $Note = $AlertScript.Note.ToString()
    
        
    IF ($InstanceIDs -ne $null -and $InstanceIDs -ne "")
    {
        $Query = "SELECT [ServerName],[DBName],[UserName],[Password] FROM [SQLServerInstances] WHERE [IsActive] = 1 AND [InstanceID] > 0 AND [InstanceID] IN (" +  $InstanceIDs + ") AND IsEmailAlert = 1"
    }

    $Instances = invoke-sqlcmd -ServerInstance $DCInstance -Database $DCDatabase -Query $Query
    
            
    foreach ($Instance in $Instances) 
    {
     Try
     {   
        $Server = $Instance.ServerName.ToString()
        $DBName = $Instance.DBName.ToString()
        $UserName = $Instance.UserName.ToString()
        $Password = $Instance.Password.ToString()
        
        
        $SqlConnection.ConnectionString = “Server = $Server; Database = $DBName;Integrated Security=sspi" 
        $SqlConnection.Open()
        $SqlCmd.CommandText = $Script
        $SqlCmd.Connection = $SqlConnection

        
            $SqlAdapter.SelectCommand = $SqlCmd
            $SqlAdapter.Fill($DataSet)
            
         
            if($Result -eq $null)
            {
                $Result =  $DataSet.Tables[0].Clone()
                $Result.Clear()
            }

            $Result.Merge($DataSet.Tables[0])
            $DataSet.Tables[0].Clear()


        $SqlConnection.Close()
        #$SqlConnection.Dispose()
      }
     Catch
     {
          #  Write-Host "Error Occured - Instance"

           if ($SqlConnection.State -eq "Open")
           {
           $SqlConnection.Close()
           }

           $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
           $ErrorMessage = $ErrorMessage.replace("'","''")
           $CounterName = $Counter + $AlertName
           $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
           $ErrorLogSQL = $dccn.CreateCommand()
           $ErrorLogSQL.CommandText = $ErrorLogScript
           $ErrorLogSQL.ExecuteNonQuery()

       }
                       
     }
    
    $Style = "
     
    "
    
   
    
    If ($Result.Columns.ColumnName -contains "Alert")
    {
         $IsAlertSet  = 1
    }
    else
    {
        $IsAlertSet  = 0
    }
    
    $PreContent = ""+$AlertName+""+"    "+"Run on: $(Get-Date)

"

    if($Note -ne $null -and $Note -ne "")
    {    
        $PostContect =  "Note:"+"    "+""+$Note+"

"
    }
    else 
    {
        $PostContect =  " "
    }

  
        #$results = $DataSet.Tables[0] | format-table -autosize | out-string
        $results = $Result  | select * -ExcludeProperty RowError, RowState, HasErrors, Name, Table, ItemArray| ConvertTo-Html -head $Style -PreContent $PreContent -PostContent $PostContect | out-string 
        

        If ($IsAlertSet -eq 1)
        {
            $results = $results.Replace("High

","")
            $results = $results.Replace("Medium

","")
            $results = $results.Replace("Low

","")
            $results = $results.Replace("Alert

","")        
        }        
        
                
                        
        if($Result -ne $null -and $Result.Rows.Count -gt 0)
        {
            
            $message = new-object System.Net.Mail.MailMessage 
            $message.From = $FromEmail 
            $message.To.Add($emailTo) 

            $message.ReplyTo = "prajapatipareshm@gmail.com"

            if ($results -like '*Class="High"*' -and $emailCC -ne $null -and $emailCC -ne "")
            {
                $message.CC.Add($emailCC) 
                
            }
            
            #$message.Bcc.Add($bccaddress) 
            $message.IsBodyHtml = $True 
            $message.Subject = $AlertName 
            $message.body = $results 
            $smtpServer = $smtp
            $smtp = new-object Net.Mail.SmtpClient($smtpServer) 
            $smtp.Send($message) 
            #$message.dispose()

            $Result.Clear()
            $Result.Dispose()
            $Result = $null
            $results = $null
    
        }




    $NextRunUpdateScript = "UPDATE [dbo].[EmailAlerts] SET [LastRun] = GETDATE(), [NextRun] = dbo.[GetNextScheduledRunDate] (StartDate,NextRun,Schedule,Occurrence,IsWeekendAlert) WHERE AlertName = "+ "'" + $AlertName + "'"
    $NextRunSQL = $dccn.CreateCommand()
    $NextRunSQL.CommandText = $NextRunUpdateScript
    $NextRunSQL.ExecuteNonQuery()
  }
  Catch
  {
   # Write-Host "Error Occured - Script"

   $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
   $ErrorMessage = $ErrorMessage.replace("'","''")
   $CounterName = $Counter + $AlertName
   $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
   $ErrorLogSQL = $dccn.CreateCommand()
   $ErrorLogSQL.CommandText = $ErrorLogScript
   $ErrorLogSQL.ExecuteNonQuery()

   $NextRunUpdateScript = "UPDATE [dbo].[EmailAlerts] SET [LastRun] = GETDATE(), [NextRun] = dbo.[GetNextScheduledRunDate] (StartDate,NextRun,Schedule,Occurrence,IsWeekendAlert) WHERE AlertName = "+ "'" + $AlertName + "'"
   $NextRunSQL = $dccn.CreateCommand()
   $NextRunSQL.CommandText = $NextRunUpdateScript
   $NextRunSQL.ExecuteNonQuery()

  }
    
 }
}
}
Catch
{
   # Write-Host "Error Occured - Main"

   $ErrorMessage = "Line: "  + $_.InvocationInfo.ScriptLineNumber + " - Message: "  + $_.Exception.Message
   $ErrorMessage = $ErrorMessage.replace("'","''")
   $CounterName = $Counter + $AlertName
   $ErrorLogScript = "INSERT INTO DBAErrorLog (CounterName, ServerName,  ErrorMessage, ErrorDate) SELECT '"+$CounterName+"', '"+$Server+"',  '"+$ErrorMessage+"', GETDATE()"
   $ErrorLogSQL = $dccn.CreateCommand()
   $ErrorLogSQL.CommandText = $ErrorLogScript
   $ErrorLogSQL.ExecuteNonQuery()

}
Finally
{
   $dccn.Close()
}

You can find above script here too.  

3. Sample notification queries
Download sample email alert queries here.

• [AlertName]: Name of Alert [AlertScript]: Queries
• [InstanceIDs]: Comma separated InstancesIds where script will be executed against 
• [EmailTo]: Query output which send to 
• [EmailCC]: Query output which send in CC 
• [EmailBCC]: Query output which send in BCC 
• [FromEmail]: Email notification come from 
• [IsActive]: Flag to disable/enable the counter 
• [Schedule]: When script will run, It has possible values are Year, Month, Week, Day, Hour, Minute, Second 
• [Occurrence] : When it will occur or frequency 
• [StartDate] : Start date and time of collector counter 
• [LastRun] : When it last ran, It will update by PS1 script 
• [NextRun]: Next schedule to run data collector, It will be calculated by PS1 script 
• [EndDate]: counter execution end date 
• [IsWeekendAlert]: Notification should be sent in weekend or not 
• [Note]: Remarks which you want to put in notification 
• [smtp]: smtp id for email

 Change Server and Instance Ids in the sample queries. You can add  more counters and schedule it which you want email notification for. I am still enahancing it and you can put your comment for the same if any.