Sharepoint 2013 PS to populate fields in list based on other items in list 1

[lahddah]

Does anyone know if I can run a PowerShell script to (maybe) create a 'temporary' SharePoint list, then use that list to create a new list? I've got script working to complete erase and regenerate a user list pulling from Active Directory. That part is fine. But I want to be able to populate the user's Manager Phone and Email addresses, as well. So, the script would need to look at the list, find user1's manager name, search the list for the manager's information, grab the phone and email, then finish populating user1 item. I may be overthinking it. If anyone knows, please let me know. Thanks!

Example

[ol]
   [li]user1Name[/li]
   [li]user1Phone[/li]
   [li]user1Email[/li]
   [li]user1ManagerName[/li]
   [li]user1ManagerPhone - (pulling from Manager's info)[/li]
   [li]user1ManagerEmail - (pulling from Manager's info)[/li]
[/ol]

Thank you, in advance!

~ lahddah

 

[blister911]

You could just get the manager information from AD when you're getting the user information.

What is the code you are using to get the user information?


Light travels faster than sound. That's why some people appear bright until you hear them speak.

 

[lahddah]

Thank you, blister911. I'm actually running an sql via crystal report & outputting to csv so that I can then import the csv into the SharePoint list on a nightly basis. The sql I'm running for the report is:

SELECT sAMAccountName, Title, Department, givenName, sn, Manager, distinguishedName
                       FROM 
                         'LDAP://DC=(###),DC=(company),DC=COM'
                       WHERE 
                         objectCategory ='Person'
                                    AND
                                      objectClass = 'User'

AD only has the manager name (as distinguishedName), but not phone number and email on each user entry. So I need to populate those fields in the SharePoint list, somehow. I don't know if it would be through the sql command in the report/csv or in the powershell command to import the csv.


~ lahddah

 

[blister911]

Your code doesn't match your output, with regards to which fields you need, so I went with what you provided as output.

I would do it like this:

## Set export location
$export_location = "C:\Temp\SharePoint_Users.csv"

## Get all users and necessary attributes
$users = Get-ADUser -Filter * -Properties OfficePhone, mail, Manager

## Initialize export array
$export = @()

foreach ($user in $users)
	{
	 ## write current user to the console to monitor progress (this can be remove if the script is automated
	 $user.name
	 
	 ## Create custom object for export
	 $output = New-Object PSObject
	 $output | add-member -memberType noteproperty -Name UserName -value $user.Name
	 $output | add-member -memberType noteproperty -Name UserPhone -value $user.OfficePhone
	 $output | add-member -memberType noteproperty -Name UserEmail -value $user.mail
	 
	 ## Find manager information if the user has a manager set
	 if ($user.manager)
		{
		 $manager = Get-ADUser -Filter * -Properties mail, OfficePhone | where {$_.distinguishedname -eq $user.manager}
		 $output | add-member -memberType noteproperty -Name MgrName -value $manager.Name
		 $output | add-member -memberType noteproperty -Name MgrPhone -value $manager.OfficePhone
		 $output | add-member -memberType noteproperty -Name MgrMail -value $manager.mail
		}
	 else
		{
		 $output | add-member -memberType noteproperty -Name MgrName -value ""
		 $output | add-member -memberType noteproperty -Name MgrPhone -value ""
		 $output | add-member -memberType noteproperty -Name MgrMail -value ""
		}
		
	 ## Copy object to export array
	 $export += $output
	}

## Export array to file
$export | Export-Csv $export_location -NoTypeInformation

Light travels faster than sound. That's why some people appear bright until you hear them speak.

 

[lahddah]

Thank you, blister911!

This was a great help! We do not have the Active Directory module installed on our SharePoint server, so I had to tweak your example a bit. This was why I needed the CSV import workaround. I pulled the AD information in using Crystal Reports & exporting to CSV. Then, using powershell, I import the CSV, load the manager information and then add the field values to the SharePoint list I'm using for many things I want to customize in SharePoint.

Here is your example how I used it & it seems to be working nicely. Thank you, again!

## Set location of raw file to be used

$FilePath = "http://[site]/SharedDocuments/SharePointUsersRaw.csv"

## Set the export location

$export_location = "C:\temp\SharePointUsers.csv"

$wc.DownloadFile($FilePath, $export_location)

## Get all users and necessary attributes

$users = Import-Csv $export_location -header("sAMAccountName", "DisplayName", "mobile", "telephoneNumber", "title", "Department", "PhysicalDeliveryOfficeName", "mail", "SN", "whenCreated", "Manager", "ManagerPhone", "ManagerEmail")

## Initialize export array
$export = @()

foreach($line in $users)
 {  
	 ## write current user to the console to monitor progress (this can be remove if the script is automated
	 
	$line.sAMAccountName
	 
	 ## Create custom object for export

	 $output = New-Object PSObject
	 $output | add-member -memberType noteproperty -Name sAMAccountName -value $line."sAMAccountName"   
	 $output | add-member -memberType noteproperty -Name DisplayName -value $line."DisplayName"
	 $output | add-member -memberType noteproperty -Name mobile -value $line."mobile"
	 $output | add-member -memberType noteproperty -Name telephoneNumber -value $line."telephoneNumber"   
	 $output | add-member -memberType noteproperty -Name title -value $line."title"
	 $output | add-member -memberType noteproperty -Name Department -value $line."Department"
	 $output | add-member -memberType noteproperty -Name PhysicalDeliveryOfficeName -value $line."PhysicalDeliveryOfficeName"   
	 $output | add-member -memberType noteproperty -Name mail -value $line."mail"
	 $output | add-member -memberType noteproperty -Name SN -value $line."SN"  


	 ## Find manager information if the user has a manager set

	 if ($line.manager)
		{
		 $manager = $users | where {$_."DisplayName" -eq $line."Manager"}
         $output | add-member -memberType noteproperty -Name MgrName -value $manager."DisplayName"
		 $output | add-member -memberType noteproperty -Name MgrPhone -value $manager."telephoneNumber"
		 $output | add-member -memberType noteproperty -Name MgrMail -value $manager."mail"
		}
	 else
		{
		 $output | add-member -memberType noteproperty -Name MgrName -value ""
		 $output | add-member -memberType noteproperty -Name MgrPhone -value ""
		 $output | add-member -memberType noteproperty -Name MgrMail -value ""
		}
		
	 ## Copy object to export array
	 $export += $output
	}

## Export array to file
$export | Export-Csv $export_location -NoTypeInformation

~ lahddah