Powershell to send E-Mail alerts from DB


Scenario:

Send  an automated E-Mail every 30 mins to sweep all the accepted/rejected  items in DB table. C# cannot be used to achieve this since SMTP is not installed on web front end.

Steps:

1) Install SMTP on DB server.

2)Initiate SMTP connection

3) Initiate DB connection string

4)Create T-SQL query to sweep all the updated items over past 30 mins

5) Construct E-mail message

6) Deploy the powershell as SQL agent job to run every 30 mins.

Script:

#
#.SYNOPSIS
#Sends email on rejection or accepatance of reports
#
#.EXAMPLE
#.\Rejection_mail.ps1 
#
 

#variables start

#$errorFile = "C:\Script\RejectionErrorLog.csv"
$errorLimit = 1
$errorTime = 30 #Error Time in minutes
$rejectedUsers = $null #string array for rejected users
$acceptedUsers = $null #string array for rejected users
$tempDate = $null
 
#Gmail SMTP settings
#<#
$emailTo = "receipient@gsi.com"
$subject = "consent form notification"
$SMTPServer = "smtp.gmail.com" #using GMAIL SMTP address
$SMTPPort = "587"
$Username = "test01@gmail.com"
$Password = "password@123"
##>

#AD SMTP settings
<#
$emailTo = "receipient@gsi.com,receipient1@gsi.com,receipient2@gsi.com"
$subject = " consent notification"
$SMTPServer = "smtp-relay.us.gsi.biz" #using SMTP address
$SMTPPort = "25"
$Username = "sharepoint@gsi.com"
$Password = "p@ssword@123"
#>




$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "server=localhost; database=ExtranetMembership; uid=ProviderUser; password=password@123;"
$SqlQuery = "select * from dbo.UserAdditionalInfo where TermsDisagreeDate < CURRENT_TIMESTAMP and TermsDisagreeDate > DateADD(mi, -$errorTime, Current_TimeStamp) or TermsAgreeDate < CURRENT_TIMESTAMP and TermsAgreeDate > DateADD(mi, -$errorTime, Current_TimeStamp)"

#variables end

$SqlConnection.open()
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$rows = $SqlAdapter.Fill($DataSet)
#$rows
$SqlConnection.Close()

if($rows -ge $errorLimit)
{


foreach ($Row in $DataSet.Tables[0].Rows)
{
 IF( $Row["Funds TermsAgreeDate"] -is [DBNull])
 {
 $rejectedUsers += "`n"
 $rejectedUsers += "Consent form Rejected by: "
 $rejectedUsers += $Row["FileAs"] + " ("
 $rejectedUsers += $Row["Email"] + ") "
 #$rejectedUsers += "`n"
 }
 IF( $Row["Funds TermsDisagreeDate"] -is [DBNull])
 {
 #$acceptedUsers += "`n"
 $acceptedUsers += "Consent form Approved by: "
 $acceptedUsers += $Row["FileAs"] + " ("
 $acceptedUsers += $Row["Email"] + ") "
 #$acceptedUsers += "`n"
 }
}
$Emailbody = @"
Hello Administrator,

Below listed user(s) agreed/disagreed consent forms.
$rejectedUsers
$acceptedUsers

Thanks,
ITSupportTeam
"@

$message = New-Object Net.Mail.MailMessage($Username, $emailTo, $subject, $Emailbody)
$smtp = New-Object System.Net.Mail.SmtpClient($SMTPServer, $SMTPPort)
$smtp.EnableSSL = $true
$smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
#$smtp = New-Object Net.Mail.SmtpClient($SMTPServer)
$smtp.Send($message)


}
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s