Creating a custom list with UPS information
Requirement
Our induction solution required an automated welcome e-mail to be sent to a user whenever their account was added to SharePoint. This would trigger a series of events that were part of the induction process, including the tracking of when a user completed a quiz. If not, a reminder would be sent to them after 7 days.
Solution
You will need the following for this solution to work:
- SharePoint 2010 User Profile Service successfully configured and populated with user information.
- SharePoint search is configured correctly and is crawling the User Profile Service.
- A new custom property is created in the User Profile Service Application called ‘StartDate’ which pulls the ‘whenCreated’ attribute from Active Directory. This gives me the account creation date of each user.
- A PowerShell script that extracts data from the User Profile Service and populates a custom SharePoint list.
- A Batch file that runs every night to run the PowersShell script.
This post assumes that you have 1, 2 and 3 configured.
Create the Custom List
Create a custom list in SharePoint with the columns you wish to populate from the User Profile Service.
- Browse to your site collection
- Site Settings -> More Options -> Lists -> Custom List
- Create the columns, in this case I create “DisplayName”, “User”, and “StartDate”.
PowerShell Script
Change the site name and attribute names in the following script to suit your requirements. See lines in bold.
# This script pulls all User Profiles from the User Profile Service Application in SharePoint.
# It reads the AccountName and StartDate of the user and writes them to a SharePoint list.
# Created by: Andrew Toh on 2/12/2011.
Add-PSSnapin Microsoft.SharePoint.PowerShell
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.Office.Server.UserProfiles")
[void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint")
# SharEPoint Site URL
$site = new-object Microsoft.SharePoint.SPSite(http://spsite);
$ServiceContext = [Microsoft.SharePoint.SPServiceContext]::GetContext($site);
$ProfileManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServiceContext)
$AllProfiles = $ProfileManager.GetEnumerator()
foreach($profile in $AllProfiles)
{
#Select the values to extract from the User Profile Service.
$AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value
$DisplayName = $profile.DisplayName
#Use this format for Custom properties
$hiredate = $profile["StartDate"].value
#Update SP User List
$spAssignment = Start-SPAssignment
$mylist = (Get-SPWeb -identity http://spsite -AssignmentCollection $spAssignment).Lists["myListname"]
$newItem = $mylist.Items.Add()
$newItem["User"] = $AccountName
$newItem["StartDate"] = $hiredate
$newItem["DisplayName"] = $DisplayName
$newItem.Update()
Stop-SPAssignment $spAssignment
}
Write-Output "Finished"
$site.Dispose()
Create a batch file that runs the above powershell script and add it to your task scheduler as a job.
- Open your text editor and paste the following line in, changing the path and script:
- powershell -command “&C:\path\to\script\scriptname.ps1”
- Save the file as a .bat file and close.
- Open Task Scheduler and create a new job, set it to run the .bat file.
Note:
- Ensure that you get your schedules right, i.e. UPS sync must run first, followed by search crawl, then script. Otherwise, your users information will not be updated on schedule.
The result
The Magic
Add workflows to the list, this would be triggered every time the script adds a new user to the list. Perfect for something like welcome e-mails and induction quizzes.
Is there a way to exclude certain users and groups from the output?
Great script!
Yes, just add an IF statement in the for loop, e.g.
foreach($profile in $AllProfiles)
{
#Select the values to extract from the User Profile Service.
$AccountName = $profile[[Microsoft.Office.Server.UserProfiles.PropertyConstants]::AccountName].Value
$DisplayName = $profile.DisplayName
IF ($DisplayName -ne “Test User” -or $DisplayName -ne “Test User 2”)
{
rest of the script
}
Hi,
Nice post.
Is it possible to populate a “Person or Group” column with “AccountName” or “DisplayName” user profile property using the above script?
I created a text column and it is working, but not with “Person or Group”.