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:

[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: