Getting User Profile Properties out of Sharepoint and into a table

One of the shortcomings of Sharepoint 2007 is the lack of a tabular view of User Profile Properties.  This would be really useful, so I wrote a PowerShell script which gets specified profile properties for every user and writes them into a delimited file.

First up however, you need a list of the profile property names so you know what to select.  The following PowerShell script will display a table showing the internal name used by Sharepoint, and the property name displayed in the Sharepoint UI (Download Get-UserProfileConfigManager):

# Outputs a list of User profile names - both the internal name, and the name displayed in Sharepoint

# Function:          Get-UserProfileConfigManager
# Description:       return a UserProfileConfigManager object which is used for management of MOSS User Profiles
# Parameters:        SSPName          Shared Service Provider Name    

Function global:Get-UserProfileConfigManager($SSPName)
$ServerContext = [Microsoft.Office.Server.ServerContext]::GetContext($SSPName);
new-object Microsoft.Office.Server.UserProfiles.UserProfileConfigmanager($servercontext)

$cm.getProperties() | ft name,displayname

The output from this can be used to determine the names of the properties you want to use in the next script.  To use this script, update the  $arProperties list with the property names you need.  By default this saves the results to a file UserProfiles.csv in the directory from which you run the script.  You can then import this into Excel or whatever (Download Get-UserProfiles-To-CSV).

# Outputs a delimited file with specified user profile properties for each user in Sharepoint

# Create array of desired properties
$arProperties = 'UserName','FirstName','LastName','Title','WorkEmail','WorkPhone','Manager','AlternateContact','RoleDescription','PictureURL';
# Specify output file
$outfile = 'UserProfiles.csv';
#Specify delimiter character (i.e. not one that might appear in your user profile data)
$delim = '^';
# Specify Shared Service Provider that contains the user profiles.
$SSP = "SharedServices";


# Function:          Get-UserProfiles
# Description:       return a UserProfileManager object containing all user profiles
# Parameters:        SSPName          SSPName

Function global:Get-UserProfiles($SSPName)
	$ServerContext = [Microsoft.Office.Server.ServerContext]::GetContext($SSPName);
	$UPManager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($ServerContext);
	return $UPManager.GetEnumerator();
$profiles = Get-UserProfiles($SSP);

#Initialise Output file with headings
$header = [string]::join($delim,$arProperties);
Write-Output $header | Out-File $outfile

#Output the specified properties for each
$profiles | ForEach-Object {
	foreach($p in $arProperties){
		# Get the property name and add it to a new array, which will be used to construct the result string
		$arProfileProps += $_.Item($p);
	$results = [string]::join($delim,$arProfileProps);
	# Get rid of any newlines that may be in there.
	$CleanResults = $results.Replace("`n",'');
	Write-Output $CleanResults
	Remove-Variable -Name arProfileProps
} | Out-File -Append $outfile

The next stage of development would be to pipe the output of the first script into the second, instead of setting up a list of desired properties – it is probably more useful to just grab everything.

I’m sure this could be written much more gracefully, but I can’t work out how to iterate through the UserProfile.Item array/object.  Any suggestions gratefully received!

Download Sharepoint User Profile PowerShell scripts here (zip file).

Getting User Profile Properties out of Sharepoint and into a table

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s