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.

Changing Rows to Columns Using PIVOT - Dynamic columns for Pivoting in SQL Server by serverku

We have seen example of Changing Rows to Columns Using PIVOT. Hope you liked that post and you enjoyed a lot also. As we know that we need to hard-code the values for pivoting which we have made for color values. For the values updates in table or whenever values changed in the table for the column which we use in pivot then we have to update the script as per that otherwise it won't work or do not give correct information. Also, I got the same comment from a user in the post which you can reach from the above link.

I am going to explain the same scenario and the resolution for the same, but you need to read original post properly. So requesting you to read it first if you did not read. So whenever we add new color values in the table, the same pivot query does not return information about the new values. So let us add new value and prepare the query and run.

-- Inserting one more record in existing table created in earlier post
INSERT INTO TblPivot
(
 ItemCode,
 ItemName,
 ItemColour
)
SELECT 
 6,
 'Samsung Mobile',
 'Silver'

After adding above new value, the pivot query which used in earlier post does not return right result set. Here's the solution for such type of issue we need to make the query with dynamic columns as following, We have got distinct values and embedded as comma separated using ‘[‘ and ‘]’. To make dynamic we can use COALESCE or FOR XML PATH.

DECLARE @ColourColumn varchar(200)
DECLARE @sql varchar(1000)

CREATE TABLE #Colours
(
Colour varchar(500)
)

INSERT INTO #Colours (Colour)
SELECT 
 DISTINCT '[' + ItemColour + ']' 
FROM TblPivot

-- Creating Column Names for Pivot
SELECT @ColourColumn = COALESCE(@ColourColumn + ',', '') +
Colour
FROM #Colours

/*
--OR
SET @ColourColumn = (SELECT  SUBSTRING(
 (SELECT DISTINCT ',' + Colour
  FROM #Colours
  FOR XML PATH('')),2,200000) 
 )
*/

DROP TABLE #Colours

SET @sql = 
'
 SELECT
 *
  FROM
  (
  SELECT 
   ItemCode, 
   ItemName, 
   ItemColour
  FROM TblPivot
  ) AS P
 PIVOT
 (
   Count(ItemName) FOR ItemColour IN ('+@ColourColumn+')
 ) AS pv
'
EXEC (@sql)

I want you to share your ideas for such issues which you faced and the resolutions for the same.