Document Expiration Monitor - Automation with PowerShell Script
Requirement: There are bunch of document libraries with several documents loaded on various site collections. We've to monitor their expiry date based on a column and send e-mail notification to all members of a SharePoint Group. Also all E-mails sent should be logged in a tracking list.
Solution: Lets write a PowerShell script to scan required document libraries and send E-mail. BTW - Its for MOSS 2007 (But the script works on any SharePoint versions, of course!)
PowerShell Script:
Template.htm File content:
PowerShell Script in Action:
Tracking List:
Solution: Lets write a PowerShell script to scan required document libraries and send E-mail. BTW - Its for MOSS 2007 (But the script works on any SharePoint versions, of course!)
PowerShell Script:
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") ################################################################################## ## VARIABLES SECTION ## ################################################################################## $LibraryURLs = @("https://intranet.Crescent.com/sites/IT/docs/", "https://intranet.Crescent.com/sites/operations/inventory/", "https://intranet.Crescent.com/sites/Sales/documents") ### Variables for Email setup #URL of the Site where the SharePoint group exists $EmailGroupSite = "https://intranet.Crescent.com/sites/IT/" #Name of the SharePoint Group to which emails to be sent $EmailGroupName= "Report Group" #Internal Name of the Column which stores the expiration date of documents $ExpiryDateColumnInternalName = "Expiration_x0020_Date0" #How to get the Internal name of a SharePoint Column? #Refer http://www.sharepointdiary.com/2011/06/sharepoint-field-display-name-vs-internal-name.html #Site URL where the tracking list "Document Expiration Monitor - Tracking" to be maintained $TrackingSiteURL = "https://intranet.Crescent.com/sites/IT/" $ExpiryDays = 7 $SMTPServer = "smtp.Crescent.com" $EmailFrom = "[email protected]" $EmailSubject = "Document Expiration Monitor Report as on $(Get-Date)" #Region Functions ############################################################################### ## FUNCTIONS ## ############################################################################### #Functions for Get-SPSite & Get-Web in MOSS 2007 function global:Get-SPSite($url) { return new-Object Microsoft.SharePoint.SPSite($url) } function global:Get-SPWeb($url) { $site= New-Object Microsoft.SharePoint.SPSite($url) if($site -ne $null) { $web=$site.OpenWeb(); } return $web } #Validate the Web at given URL function Validate-Web($WebURL) { try { $web = Get-SPWeb $WebURL return $web } catch { $Message = "Error in Site URL:$WebURL. Please provide valid Site URL!" Write-Host $Message -ForegroundColor red Append-LogMessage $Message $error[0] #Continue executing code continue return $null } } #Validate the Web at given URL function Validate-SPSite($SiteURL) { try { $Site = Get-SPSite $SiteURL return $Site } catch { $Message = "Error in Site Collection URL:$SiteURL." Write-Host $Message -ForegroundColor red Append-LogMessage $Message $error[0] #Continue executing code continue return $null } } #Function to validate given List URL function Validate-List($ListURL) { try { $list = $web.GetList($ListURL) return $list } catch { $Message = "Error in List URL: $ListURL! Please provide valid List URL!" Write-Host $Message -ForegroundColor red Append-LogMessage $Message $error[0] #Continue executing code continue return $null } } function Send-Email ($EMailBody, $EmailTo) { try { #Send the Mail $MailMessageParameters = @{ SmtpServer = $SMTPServer Subject = $EmailSubject Body = $EmailBody From = $EmailFrom To = $EmailTo } Send-MailMessage @MailMessageParameters -BodyAsHtml Append-LogMessage "Email has been sent to :"$EmailTo } catch { Write-Host "Error sending email, Please check log file!" -ForegroundColor Red Append-LogMessage $error[0] } } #Function to Get the Location where script is located function GetScriptDir { $Invocation = (Get-Variable MyInvocation -Scope 1).Value return Split-Path $Invocation.MyCommand.Path } #Extract Emails from each user in a Group function Get-GroupEmails() { try { [email protected]() $Site = Validate-SPSite $EmailGroupSite if($site -ne $null) { $Group = $site.RootWeb.sitegroups | Where {$_.Name -match $EmailGroupName} #$Group= $web.SiteGroups[$GroupName] if($Group -ne $null) { $Users = $Group.Users foreach ($User in $Users) { if( ($user.Email -ne "") -and ($user.Email -ne $null) ) { $Emails+=$user.Email } } $Emails = $Emails -split "," return $Emails } else { $Message = "E-mail Group Name:$EmailGroupName Not found!" Write-Host $Message -ForegroundColor red Append-LogMessage $Message $error[0] return $null } } } catch { $Message = "Error in Get-GroupEmails Function!" Write-Host $Message -ForegroundColor red Append-LogMessage $Message $error[0] #Continue executing code continue return $null } } #Function to Log details to Tracking List function LogTo-TrackingList($EmailBody) { $web = Validate-Web $TrackingSiteURL if($web -eq $null) { # Creating base file to append to, adding a new line for readability $Message = "Tracking Site URL is not valid!" Write-Host $Message -ForegroundColor Red Append-LogMessage $Message return } #Try to Get the "Tracking List" $TrackingList = $web.Lists | Where { $_.Title -match "Document Expiration Monitor - Tracking"} #If Tracking list doesnt exist, Create it if($TrackingList -eq $null) { #Create New Tracking List $TrackingListID = $Web.Lists.Add("DEMTracking","List to Track Document Expiration Monitor Mails",[Microsoft.SharePoint.SPListTemplateType]::GenericList) #Set Name for the List $TrackingList = $web.Lists[$TrackingListID] $TrackingList.Title="Document Expiration Monitor - Tracking" $TrackingList.update() #Rename the "Title Field" to Tracking ID $TitleField = $TrackingList.Fields.GetFieldByInternalName("Title") #Set Field Display Name and Update $TitleField.Title ="Tracking Date" $TitleField.Update() #Add Email Body Field $EmailBodyField = $TrackingList.Fields.Add("Email Body","Note",$true) $trackingList.Fields["Email Body"].RichText = $True $trackingList.Fields["Email Body"].RichTextMode = "FullHtml" $trackingList.Fields["Email Body"].update() } #Add New Item to Tracking List $item = $TrackingList.Items.Add() #Set the Tracking Date & Email Body field values $item["Email Body"] = $EmailBody #Set the Tracking Date field $item["Tracking Date"] = $TodaysDate $item.update() #Log details to Tracking List $web.dispose() } # Appends text to the log message function Append-LogMessage($text) { Add-Content $LogTextFileName $text"`r" -Encoding UTF8 } #EndRegion #Variables for Internal processing $ScriptPath = GetScriptdir #Get the Template HTM File with CSS $MailContent = Get-Content -Path "$ScriptPath\template.htm" -Encoding UTF7 | Out-String $CAMLDateFormat = "yyyy'-'MM'-'dd'T'HH':'mm':'ss'Z'" $DateFormat = "MM/dd/yyyy" $TodaysDate = Get-date $TodaysDateFormatted = (Get-date).ToString($CAMLDateFormat) $ExpirationDate = (Get-Date).AddDays($ExpiryDays) $ExpirationDateFormatted = (Get-Date).AddDays($ExpiryDays).ToString($CAMLDateFormat) $LogTextFileName = "$ScriptPath\DEM_LOG.txt" $Message ="" # Creating base file to append to, adding a new line for readability $Message = "Script Execution Started at $TodaysDate." >> $LogTextFileName Write-Host $Message -ForegroundColor DarkGreen Append-LogMessage $Message ############################################################################### ## PROCESS EACH DOCUMENT LIBRARY AND SEND EMAIL ## ############################################################################### ########## Process "Expiring Documents" ########### $MailContent+= "<h2> Document Expiry Monitor - Report as on : $(Get-Date) </h2>" $ExpiringContentHeader = "<h3 style='color: #333399;'> List of Expiring Documents: </h3> <table class='altrowstable' cellpadding='5px' cellspacing='5'><tr><th>Document Name </th><th>Created By</th><th>Created on</th><th> Expiry Date </th><th> URL </th></tr>" $MailContent += $ExpiringContentHeader $ExpiringContent="" $ExpiredContent="" # Loop through items and Get all Expiring Documents foreach ($ListURL in $LibraryURLs) { #Get the web $web = Validate-Web $ListURL if($web -eq $null) { #Skip the current list and proceed with the next from array continue } #Get the List $list = Validate-List $ListURL if($list -eq $null) { #Skip the current list and proceed with the next from array continue } #Check if the list has the "Expiration Date" Column if ($list.Fields.GetFieldByInternalName($ExpiryDateColumnInternalName) -eq $false) { $Message = "$ListURL does not contains Expiration Date Field specified!" Write-Host $Message -ForegroundColor red Append-LogMessage $Message #Skip the current list and proceed with the next from array continue } # Write out the dates we're looking for $Message = "Searching $ListURL for Expiring documents with Expiration Date between " + $TodaysDate + " - " + $ExpirationDate Write-Host $Message -ForegroundColor DarkGreen Append-LogMessage $Message # Get all Expiring Documents $ExpiringQuery = '<Where><And><Geq><FieldRef Name="' + $ExpiryDateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $TodaysDateFormatted + '</Value></Geq><Leq><FieldRef Name="' + $ExpiryDateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $ExpirationDateFormatted + '</Value></Leq></And></Where>' $SPExpiringQuery = new-object Microsoft.SharePoint.SPQuery $SPExpiringQuery.ViewAttributes = "Scope='Recursive'" $SPExpiringQuery.Query = $ExpiringQuery $ExpiringListItems = $List.GetItems($SPExpiringQuery) $Message = "Found " + $ExpiringListItems.Count + " Expired document(s)." Write-Host $Message -ForegroundColor DarkGreen Append-LogMessage $Message #If There are some expiring documents found if($ExpiringListItems.Count -gt 0) { foreach($Item in $ExpiringListItems) { $ExpiringContent+= "<tr> <td> $($item.Name) </td> <td> $($item.File.Author.Name) </td> <td> $($item.File.TimeCreated.ToString($DateFormat)) </td> <td> $($Item [$ExpiryDateColumnInternalName].ToString($DateFormat)) </td> <td> <a href='$($web.URL)/$($item.URL)'>$($web.URL)/$($item.URL)</a> </td> </tr>" } } } if($ExpiringContent.Length -eq 0) { $ExpiringContent+= "<tr> <td colspan=5> No Expiring Documents Found! </td> </tr>" } $ExpiringContent+= "</table><br/>" $MailContent += $ExpiringContent ########## Process "Expired Documents " ########### $ExpiredContentHeader = "<h3 style='color: #660033;'> List of Expired Documents: </h3> <table class='altrowstable' cellpadding='5px' cellspacing='5'><tr><th>Document Name </th><th>Created By</th><th>Created on</th><th> Expiry Date </th><th> URL </th></tr>" $MailContent += $ExpiredContentHeader $ExpiredContent="" # Loop through items and Get all Expired Documents foreach ($ListURL in $LibraryURLs) { if($web -eq $null) { #Skip the current list and proceed with the next from array continue } #Get the List $list = Validate-List $ListURL if($list -eq $null) { #Skip the current list and proceed with the next from array continue } #Check if the list has the "Expiration Date" Column if ($list.Fields.GetFieldByInternalName($ExpiryDateColumnInternalName) -eq $false) { $Message = "$ListURL does not contains Expiration Date Field specified!" Write-Host $Message -ForegroundColor red Append-LogMessage $Message #Skip the current list and proceed with the next from array continue } # Write out the dates we're looking for $Message = "Searching $ListURL for Expired documents with Expiration Date between " + $TodaysDate + " - " + $ExpirationDate Write-Host $Message -ForegroundColor DarkGreen Append-LogMessage $Message # Get all Expired Documents $ExpiredQuery = '<Where><Lt><FieldRef Name="' + $ExpiryDateColumnInternalName + '" /><Value Type="DateTime" IncludeTimeValue="True">' + $TodaysDateFormatted + '</Value></Lt></Where>' $SPExpiredQuery = new-object Microsoft.SharePoint.SPQuery $SPExpiredQuery.ViewAttributes = "Scope='Recursive'" $SPExpiredQuery.Query = $ExpiredQuery $ExpiredListItems = $List.GetItems($SPExpiredQuery) $Message = "Found " + $ExpiredListItems.Count + " Expired document(s)." Write-Host $Message -ForegroundColor DarkGreen Append-LogMessage $Message #If There are some expired documents found if($ExpiredListItems.Count -gt 0) { foreach($Item in $ExpiredListItems) { $ExpiredContent+= "<tr> <td> $($item.Name) </td> <td> $($item.File.Author.Name) </td> <td> $($item.File.TimeCreated.ToString($DateFormat)) </td> <td> $($Item [$ExpiryDateColumnInternalName].ToString($DateFormat)) </td> <td> <a href='$($web.URL)/$($item.URL)'>$($web.URL)/$($item.URL)</a> </td> </tr>" } } } if($ExpiredContent.Length -eq 0) { $ExpiredContent+= "<tr> <td colspan=5> No Expired Documents Found! </td> </tr>" } $ExpiredContent+= "</table><br/>" $MailContent += $ExpiredContent $MailContent += "</body></html>" #Email the Body $EmailAddresses = Get-GroupEmails if($EmailAddresses -ne $null) { Send-Email $MailContent $EmailAddresses } #Log to Tracking table LogTo-TrackingList $MailContent ############################################################################### ## CLEAN UP ## ############################################################################### # Log end of processing $Message = "Finished Processing Document Expiration Monitor Script by $(Get-Date)." Write-Host $Message -ForegroundColor DarkGreen Append-LogMessage $Message # Dispose of the web object $web.Dispose()
Template.htm File content:
<html> <head> <!-- CSS goes in the document HEAD or added to your external stylesheet --> <style type="text/css"> body{ font-family: Calibri; height: 12pt; } table.altrowstable { border-collapse: collapse; font-family: verdana,arial,sans-serif; font-size:11px; color:#333333; border-width: 1px; border-color: #a9c6c9; border: b1a0c7 0.5pt solid; } table.altrowstable th { border-width: 1px; padding: 5px; background-color:#8064a2; border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: white; font-size: 11pt; font-weight: 700; text-decoration: none; } table.altrowstable td { border: #b1a0c7 0.5pt solid; font-family: Calibri; height: 15pt; color: black; font-size: 11pt; font-weight: 400; text-decoration: none; } </style> </head> <body>
PowerShell Script in Action:
Tracking List:
No comments:
Please Login and comment to get your questions answered!