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

Powershell to Change Modified data for published documents


Issue:

Some of the documents are published or overwritten accidentally.  This made some of older documents show upfront in the ContentQuery or Content Search webparts which is configured sort latest.

Solution:

To update the modified date  to an older date (created date) of the published documents and make sure the published status is not changed.

Steps:

1) Identify the published date of accidentally overwritten documents

2) Disable any eventhandler associated with the document library

3) Use $listitem.UpdateOverwriteVersion() to change the modified date to created date

4) Enable event handler.

Script:


#
#.SYNOPSIS
#Resets the modified date to created date for list of items when created date is between
# Jan 1 2014 to feb 1 2014 and modified date is April 4 2014
#
#
Add-PSSnapin Microsoft.SharePoint.Powershell -ea SilentlyContinue

$web = Get-SPWeb -Identity "http://gsidev/sites/site"
$list = $web.GetList("http://gsidev/sites/list/")
$startDate = Get-Date "3/12/2014" #Select all the items with start date ranging from
$interval = [TimeSpan] "30.00:00:00" #Range in days
$setModifiedDate = Get-Date "5/2/2014" #with modified date
$Logfile = "C:\Scripts\ModifiedReport.log"

# End of variables

Function LogWrite
{
 Param ([string]$logstring)

 Add-content $Logfile -value $logstring
}

function UpdateModifiedDate {
 param($folderUrl)
 $folder = $web.GetFolder($folderUrl)
 Write-Host(" ")
 Write-Host("**-- Updating Carry holder folder: " + $folder.Name +" --*")
 LogWrite(" ")
 LogWrite("**-- Updating Carry holder folder: " + $folder.Name +" --*")


 foreach ($file in $folder.Files) {
 
 $listItem = $file.Item
 $modifiedDate = ([DateTime]$listItem["Modified"]).Date
 $createdDateTime = ([DateTime]$listItem["Created"]).DateTime
 $createdDate = ([DateTime]$listItem["Created"]).Date
 $days = $createdDate - $startDate 
 
 if(($days -ge 0)-and($days -le $interval)-and ($modifiedDate -eq $setModifiedDate))
 #if(($days -ge 0)-and($days -le $interval))
 { 
 
 if( $listItem.File.Level -eq "Published")
 {
 
 Write-Host("--> Updating Carry holder file: " + $file.name +" <--")
 LogWrite("--> Updating Carry holder file: " + $file.name +" <--")
 
 #<#
 $web.AllowUnsafeUpdates = $true
 $listItem["Modified"]= $createdDateTime
 $listItem.UpdateOverwriteVersion()
 $listItem.File.Publish("Added carryholder Name");
 $web.AllowUnsafeUpdates = $false
 ##>
 
 
 }
 else
 {
 Write-Host("--> Updating Carry holder file: " + $file.name +" <--")
 LogWrite("--> Updating Carry holder file: " + $file.name +" <--")
 
 
 #<#
 $web.AllowUnsafeUpdates = $true
 $listItem["Modified"]= $createdDateTime
 $listItem.UpdateOverwriteVersion()
 $web.AllowUnsafeUpdates = $false
 ##>
 
 
 }
 }
 
 }
}

foreach ($folder in $list.Folders) {
 UpdateModifiedDate($folder.Url)
}