Wednesday, April 10, 2013

Annual Review of Documents Using PowerShell in SharePoint

In your SharePoint travels you may come across a requirement to have users review documents based on a timeframe.  For instance, the requirement may say something like:

"Users must review documents every 12 months to ensure content is correct and up to date. Etc, etc."
 
This can be accomplished in a number of different ways in SharePoint, but in this post we'll look at how to accomplish this using PowerShell.  This demo will show you how to query a document library for documents that fall within a date range and then notify the document's owner that they must review the document.

This script can be downloaded from the Microsoft TechNet Gallery.

You may ask yourself at this point, why not use a workflow.  This is a great question, as you could certainly use a workflow here.  However, consider an environment where you don't have any SharePoint administrators, or even any seasoned power users.  This is more common that some may think.  In these scenarios, you will most likely have seasoned server admins, who most likely have PowerShell experience. Using this method, a little bit of SharePoint knowledge can be applied to a PowerShell script to provide the intended goal.

So let's look at our requirements:
  1. Query a specific document library and find documents with a review date 30 days from the day the script is run.
  2. Email the document owner and notify them that the document must be reviewed.
Pretty simple!  At least for this demo.  Let's start with creating a document library.  It will be simple, just creating a out of the box library and adding two columns:
  • Review Date - This is the date the document must be reviewed by.
  • Document Owner - This is the owner of the document who will be emailed.
After creating, we just need to add a few documents:


We'll upload three documents and give two review dates 30 days out, and one 31 days out.  This will give us a good test as our script should find two documents.

Now let's create our PowerShell script.  We'll break this up into sections to make it easier to break down, but the entire script is at the bottom of the post ;)

Add in the SharePoint Snapin
if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}
 
First things first, we need to make sure the SharePoint snapin is loaded and if not, load it.  Without the snapin you won't be able to access anything in the SharePoint object model, so its important.

Variables
$webUrl = "http://sp2010"
$listDisplayName = "Annual Review Documents"
$dateColumnInternalName = "Review_x0020_Date"
$ownerColumnInternalName = "Document_x0020_Owner"
$reviewDateDaysOut = 30
$startDateRangeDays = 29
$endDateRangeDays = 31
$smtpServer = "191.168.1.1"
$emailFromAddress = "annualreview@dev.com"
$emailSubjectLine = "Annual Review Documents Notification"
# DO NOT CHANGE THE FOLLOWING VARIABLES!
$dateToStringFormat = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
$logTextFileName = ".\log-" + (Get-date).ToString($dateToStringFormat) + ".txt" -replace " ","-" -replace ":","-"
$reviewDate = (Get-date).AddDays($reviewDateDaysOut).ToShortDateString()
$viewFilter = "?FilterField1=" + $dateColumnInternalName + "&FilterValue1=" + $reviewDate 
 Let's look at each of these variables:
  • $webUrl - This is the URL to site where the library is located.
  • $listDisplayName - This is the friendly name of the library we are searching.
  • $dateColumnInternalName - This is the internal name of the library column that holds the review date.
  • $ownerColumnInternalName - This is the internal name of the library column that holds the document owner.
  • $reviewDateDaysOut - This is how many days in the future we want to search the library for.  In our script we set this to 30, so the day the script runs, we are looking for documents which expire 30 days from now.
  • $startDateRangeDays - Since we are searching a date range (I'll explain later), we need to specify a start and end range.  Using 30 for how many days into the future to search, we'll set this to 29, one day prior.
  • $endDateRangeDays - Set this to one day after your desired target days.  For our example it will be 31, one day after.
  • $smtpServer - This is the IP address of your email server that will be used to send the notification emails.
  • $emailFromAddress - This is the email address that will display in the From field.
  • $emailSubjectLine - This is the email subject line.
The next set of variables should not be changed, which is why we've included the line "DO NOT CHANGE THE FOLLOWING VARIABLES!".  Let's look at these:
  • $dateToStringFormat - This is a format that we apply to all the dates used in this script.  This will put dates into a format that SharePoint likes for querying (I'll explain later).
  • $logTextFileName - Like any good script we'll do some logging of what we find and processed.  This line creates a log file name with a date/time stamp.  It will remove spaces and colons so the file name is valid for Windows.
  • $reviewDate - This is a date that will be included in the notification email.  It simply gets today's date and adds the $reviewDateDaysOut number to it.
  • $viewFilter = This is a querystring that will be appended to a link to the library we are searching.  We'll provide a link to the library in the email so a user can view the library filtered to the date we are searching for.
Now let's take a moment to explain all the items I said I would explain later.  These all deal with dates and why we search for a date range versus just searching for documents that expire on the exact date we're looking for.  This is because SharePoint stores a date and time for date fields.  Even if you have marked that field as Date Only, in the backend there is still a time associated with that date.  So simply searching for a date without a time will not return anything.  To get around this, we search for one day prior and one day after the target date.

Now the date format string will put the date time into a format that SharePoint likes and will make your CAML query successful.  Whew!  Not that we've gone into that, back to the script!

Functions
# Sends an email to the specified address
function SendEmail($ownerEmail, $docLibraryViewUrl, $documentName) {
 try {
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

  $msg = EmailStructure $ownerEmail $docLibraryViewUrl
 $documentUrl
    $smtp.Send($msg)
  $emailSentMessage = "Email sent to " + $ownerEmail
  AppendLogMessage $emailSentMessage
 }
 catch {
  Write-Host "Error sending email, check log file!" -ForegroundColor Red
  AppendLogMessage $error[0]
 }
}


# Builds the email message that will be sent
function EmailStructure($to, $docLibraryViewUrl
) {

   $msg = new-object Net.Mail.MailMessage
 
   $msg.IsBodyHtml = $true
   $msg.From = $emailFromAddress
   $msg.To.Add($to)
   $msg.Subject = $emailSubjectLine
   $msg.Body = "Review items for $listDisplayNameThe following document ($documentName) has a review date of $reviewDate.

Click here to review all documents which have a review date on $reviewDate."

  return $msg
}

# Appends text to the log message
function AppendLogMessage($text) {
 Add-Content $logTextFileName $text"`r" -Encoding UTF8
}
 
We have a few functions to assist with email and logging:
  • SendEmail - This function will build the email message and send the email.
  • EmailStructure - The SendEmail function calls this function to build the actual email message.
  • AppendLogMessage - This function handles adding lines to the log file.  We set the encoding here as well to ensure we get English in the file!  If you need another encoding you can change it here.
Create the Log File
# Creating base file to append to, adding a new line for readability
"Log file created." >> $logTextFileName
AppendLogMessage " "
 
This simply creates the log file we'll be appending to.  We use the  $logTextFileName variable to create it, then we just add a single line with nothing in it to get the line break our AppendLogMessage adds.

Get Reference to Site and Library
# Get the access to SP lib, open it
$web = Get-SPWeb $webUrl
$docLib = $web.Lists[$listDisplayName]
 
Next we just grab a reference of the site and library.

Set the Date Range for CAML Query
# Set the date range
$reviewStartDate = (Get-date).AddDays($startDateRangeDays).ToString($dateToStringFormat)
$reviewEndDate = (Get-date).AddDays($endDateRangeDays).ToString($dateToStringFormat)

# Write out the dates we're looking for
$dateRangeText = "INFO: Searching for documents with review dates between " + $reviewStartDate + " - " + $reviewEndDate
Write-Host $dateRangeText -ForegroundColor Green
AppendLogMessage $dateRangeText
 
 Here we are just creating two new variables to store our date range values.  We'll use the variables we setup early to calculate these dates.  Then we log the date range we're searching for to the log file and write that same message out to the PowerShell window.

Query the Library
# Build the SPQuery
$camlQuery = '' + $reviewStartDate + '' + $reviewEndDate + ''
$spQuery = new-object Microsoft.SharePoint.SPQuery
$spQuery.Query = $camlQuery
$spListItems = $docLib.GetItems($spQuery)

# Write out the number of documents found
$docFoundText = "INFO: Found " + $spListItems.Count + " document(s)."
Write-Host $docFoundText -ForegroundColor Green
AppendLogMessage $docFoundText
 
 Next we'll build our CAML query to search using the date range variables.  As you can see in the query, we search for dates using the GT (greater than) and the LT (less than) tags.  Once we have the SPQuery object built, we call the GetItems method to search the library.  We'll log how many items we found and show that in the PowerShell window.

Process the Documents
# Log start of processing
$beginProcessingText = "INFO: Start processing documents."
Write-Host $beginProcessingText -ForegroundColor Green
AppendLogMessage $beginProcessingText

# Loop through items and write out info
foreach ($item in $spListItems) {

 # Get the SPUser object from the column
 $spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField($ownerColumnInternalName);
 $spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item[$ownerColumnInternalName].ToString());
 $user = $spFieldUserValue.User;

 # Get the user email to test for null/empty
 $userEmail = $user.Email
 # If the User's email is present send the email, otherwise log the error
 if ($userEmail) {
  # Build URL for item
  $docLibraryViewUrl = $web.Url + "/" + $docLib.DefaultView.Url
  $ownerDocEmail = "INFO: Document '" + $item.Name + "' has owner " + $userEmail
  AppendLogMessage $ownerDocEmail
  SendEmail $userEmail $docLibraryViewUrl $item.Name
 }
 else {
  $emptyDocEmail = "ERROR: No email is set for '" + $item.Name + "' with document owner: " + $user.DisplayName
  Write-Host $emptyDocEmail -ForegroundColor Red
  AppendLogMessage $emptyDocEmail
 }
}
 
Once we have run our query we need to process the results.  We do this using a simple foreach loop.  If no items are returned these will just be ignored.  First we have to get the SPUser object out using our $ownerColumnInternalName variable.  There is a blog post here that explains this piece of the script.

Once we have an SPUser object pulled out, we need to get the email address out.  Then we test if the email address is null.  If the email address is present we build a URL for the library then call the SendEmail function passing the email address, library default view URL (which is appended with the filter querystring), and the name of the document.  We also log this as we go.

If no email has been set for the owner, we log that and move on.

Cleanup
# Log end of processing
$endProcessingText = "INFO: Finished processing documents."
Write-Host $endProcessingText -ForegroundColor Green
AppendLogMessage $endProcessingText

# Dispose of the web object
$web.Dispose()
 
At the end we log that we're done processing and we dispose of the web object we created at the very beginning.

That's it!  Its a long blog post, but the script is not too complicated.  Let's run this and see what happens.

First, we'll create a folder to hold the script and log files.  In this example we create a folder called "Annual-Review-Notifications" located on the C: drive.  We'll save a version of this script in that folder called "Document-Review-Task":


If you were to use this on your server, you would most likely create a folder for each script and name them something that relates to the library name so its easier to idenitfy.

Now let's launch a PowerShell window:


As you can see, this is a base PowerShell window that is pointing to the newly created folder.  Running the script we'll the output from the script in the window:

 
Oh noes!  We got an error!  Well this was to be expected as I do not have email addresses setup in my development environment ;)  Who wants to see a perfectly run script anyways?  Ignoring the error we'll see that the script kicked off and notified us that is was searching in a certain date range.  It then notifies how many documents it found and that its begun processing them.  It notifies that it could not find an email for the document owner then it finishes.

We'll also see a log file has been created in the folder with a timestamp in its name:


Opening the log file we'll see the same messages from the PowerShell window:


If your emails are setup correct, :/, you'll see a line that indicates an email was sent to a certain email address.

And that's it!  In most cases you'll want to set this up as a scheduled task and just let it run on the server each night.  You can easily copy this script and run one for each library where you need to send notifications.

Here is the full script:
###############################################################################
##  ADD IN SHAREPOINT SNAP IN IF NOT ALREADY LOADED ##
###############################################################################

if ((Get-PSSnapin "Microsoft.SharePoint.PowerShell" -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin "Microsoft.SharePoint.PowerShell"
}

###############################################################################
##  VARIABLES ##
###############################################################################

$webUrl = "http://sp2010"
$listDisplayName = "Annual Review Documents"
$dateColumnInternalName = "Review_x0020_Date"
$ownerColumnInternalName = "Document_x0020_Owner"
$reviewDateDaysOut = 30
$startDateRangeDays = 29
$endDateRangeDays = 31
$smtpServer = "191.168.1.1"

$emailFromAddress = "annualreview@dev.com"
$emailSubjectLine = "Annual Review Documents Notification"
# DO NOT CHANGE THE FOLLOWING VARIABLES!
$dateToStringFormat = "yyyy'-'MM'-'dd HH':'mm':'ss'Z'"
$logTextFileName = ".\log-" + (Get-date).ToString($dateToStringFormat) + ".txt" -replace " ","-" -replace ":","-"
$reviewDate = (Get-date).AddDays($reviewDateDaysOut).ToShortDateString()
$viewFilter = "?FilterField1=" + $dateColumnInternalName + "&FilterValue1=" + $reviewDate

###############################################################################
##  FUNCTIONS ##
###############################################################################

# Sends an email to the specified address
function SendEmail($ownerEmail, $docLibraryViewUrl, $documentName) {
 try {
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)

  $msg = EmailStructure $ownerEmail $docLibraryViewUrl
 $documentUrl
    $smtp.Send($msg)
  $emailSentMessage = "Email sent to " + $ownerEmail
  AppendLogMessage $emailSentMessage
 }
 catch {
  Write-Host "Error sending email, check log file!" -ForegroundColor Red
  AppendLogMessage $error[0]
 }
}
# Builds the email message that will be sent
function EmailStructure($to, $docLibraryViewUrl
) {

   $msg = new-object Net.Mail.MailMessage
 
   $msg.IsBodyHtml = $true
   $msg.From = $emailFromAddress
   $msg.To.Add($to)
   $msg.Subject = $emailSubjectLine
   $msg.Body = "Review items for $listDisplayNameThe following document ($documentName) has a review date of $reviewDate.

Click here to review all documents which have a review date on $reviewDate."

  return $msg
}

# Appends text to the log message
function AppendLogMessage($text) {
 Add-Content $logTextFileName $text"`r" -Encoding UTF8
}

###############################################################################
##  CREATE LOG FILE ##
###############################################################################

# Creating base file to append to, adding a new line for readability
"Log file created." >> $logTextFileName
AppendLogMessage " "

###############################################################################
##  WEB AND DOCUMENT LIBRARY ##
###############################################################################

# Get the access to SP lib, open it
$web = Get-SPWeb $webUrl
$docLib = $web.Lists[$listDisplayName]

###############################################################################
##  SET DATE RANGE ##
###############################################################################

# Set the date range
$reviewStartDate = (Get-date).AddDays($startDateRangeDays).ToString($dateToStringFormat)
$reviewEndDate = (Get-date).AddDays($endDateRangeDays).ToString($dateToStringFormat)
# Write out the dates we're looking for
$dateRangeText = "INFO: Searching for documents with review dates between " + $reviewStartDate + " - " + $reviewEndDate
Write-Host $dateRangeText -ForegroundColor Green
AppendLogMessage $dateRangeText

###############################################################################
##  QUERY THE DOCUMENT LIBRARY ##
###############################################################################

# Build the SPQuery
$camlQuery = '' + $reviewStartDate + '' + $reviewEndDate + ''
$spQuery = new-object Microsoft.SharePoint.SPQuery
$spQuery.Query = $camlQuery
$spListItems = $docLib.GetItems($spQuery)
# Write out the number of documents found
$docFoundText = "INFO: Found " + $spListItems.Count + " document(s)."
Write-Host $docFoundText -ForegroundColor Green
AppendLogMessage $docFoundText

###############################################################################
##  PROCESS DOCUMENTS AND SEND EMAILS ##
###############################################################################

# Log start of processing
$beginProcessingText = "INFO: Start processing documents."
Write-Host $beginProcessingText -ForegroundColor Green
AppendLogMessage $beginProcessingText
# Loop through items and write out info
foreach ($item in $spListItems) {

 # Get the SPUser object from the column
 $spFieldUser = [Microsoft.SharePoint.SPFieldUser]$item.Fields.GetField($ownerColumnInternalName);
 $spFieldUserValue = [Microsoft.SharePoint.SPFieldUserValue]$spFieldUser.GetFieldValue($item[$ownerColumnInternalName].ToString());
 $user = $spFieldUserValue.User;

 # Get the user email to test for null/empty
 $userEmail = user.Email
 # If the User's email is present send the email, otherwise log the error
 if ($userEmail) {
  # Build URL for item
  $docLibraryViewUrl = $web.Url + "/" + $docLib.DefaultView.Url
  $ownerDocEmail = "INFO: Document '" + $item.Name + "' has owner " + $userEmail
  AppendLogMessage $ownerDocEmail
  SendEmail $userEmail $docLibraryViewUrl $item.Name
 }
 else {
  $emptyDocEmail = "ERROR: No email is set for '" + $item.Name + "' with document owner: " + $user.DisplayName
  Write-Host $emptyDocEmail -ForegroundColor Red
  AppendLogMessage $emptyDocEmail
 }
}

###############################################################################
##  CLEAN UP ##
###############################################################################
# Log end of processing
$endProcessingText = "INFO: Finished processing documents."
Write-Host $endProcessingText -ForegroundColor Green
AppendLogMessage $endProcessingText
# Dispose of the web object
$web.Dispose()
This script can be downloaded from the Microsoft TechNet Gallery.

Enjoy!

3 comments:

  1. Hi Brandon

    Great blog ,nice post presented in very detailed way ,thanks for sharing .





    Sharepoint Developers

    ReplyDelete
  2. Hi Brandon,

    I landed on your page after lot of efforts in research to find a simple way to send email reminders to user for review of list items.

    Your process is very simple and understandable. But I am pretty much struck at some points.

    Errors
    1. Get-SPWeb : The term 'Get-SPWeb' is not recognized as the name of a cmdlet, function, script file.... at $web = Get-SPWeb $webUrl
    (I understand that is because Microsoft.SharePoint.PowerShell snapin)

    Questions:
    I am trying this on SharePoint 2007 - Will this work on SP 2007?
    Should I have to run this on the Server SharePoint is on or can I run from my PC. In the office network.

    I am getting some more errors where as I think if this is fixed rest will all be fixed as they are all related to same issue.

    Could you please help me with this....

    Thank you

    ReplyDelete
  3. Hi Brandon,

    Great blog, and works well.

    Quick question, how would you recommend tweaking the script to email owners for the documents due to expire in the say next 90 days.

    I want to schedule tasks for:

    90 Days
    60 Days
    30 Days
    Expired (ie review date passed)

    As opposed to just sending one email for a specific date?

    Thanks
    Matt

    ReplyDelete