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 to monitor Document Expiration based on a Date column:
[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 https://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 [string]::IsNullOrEmpty()) -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:
