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
[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
3. Sample notification queries
Download sample email alert queries here.
1. Create a DBARepository database and following tables there
CREATE DATABASE [DBARepository][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
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].[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
<#You can find above script here too.
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()
}
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
