1

I was asked to create a phone list of employees into our company SharePoint website that automatically updates whenever new users are added into our active directory.

I've researched a few things such as UPS(user profile sync) but from my understanding, this pulls the whole profile of every user from the active directory where I am only wanting the first/last name and phone number.

1

I'm sure there are many ways to do this, but thought I'd share parts of my Powershell solution to do exactly that. Maybe it will give you some ideas even if you use different language. I wronte this for 2010, so I'm not using REST calls or anything newer that could be used in SP2013/2016

We have an online employee list that is populated through changes in AD - it's separate from the SharePoint user lists.

I created a custom list with the usual fields needed: first, last name, department, phone.... whatever you need. I also included a single-line text field for the SID of the AD user.

My powershell is scheduled to run every morning, so it will pick up any changes/deletions. It is not running on the SharePoint server so it uses basic CAML/list web services.

The important pieces: Note, there are < and > building the CAML that don't show up here, so I replaced them with ( and ) so this makes sense. I might have missed something, so double check if I have to many opening or closing { }

Import the AD module

 Import-Module ActiveDirectory -ErrorAction SilentlyContinue

I get the current list into a hash to make it easier to compare it to AD

    $WebUrl = "https://xxxx"
    $service = New-WebServiceProxy -Uri ($WebUrl + "/_vti_bin/lists.asmx?WSDL")  -Namespace SpWs  -UseDefaultCredential 
    #Employee Phone List           
    $listName = "{a guid}" # or even a list name   

 # Retrieve Current 'Employee' entries in the Employee Phone List
        $xmlDocToday = new-object System.Xml.XmlDocument
        $query = $xmlDocToday.CreateElement("Query")
        $selection = "(Where)(Eq)(FieldRef Name = 'PhoneType' /)(Value Type = 'Choice')Employee(/Value)(/Eq)(/Where)"           
        $query.set_InnerXml($selection)

        $viewFields = $xmlDocToday.CreateElement("ViewFields")
        $viewFields.set_InnerXml("(FieldRef Name='ID' /)(FieldRef Name='SID' /)(FieldRef Name='FirstName' /)(FieldRef Name='Department' /)(FieldRef Name='Account' /)(FieldRef Name='WorkPhone' /)(FieldRef Name='FullLastName' /)") 

        $queryOptions = $xmlDocToday.CreateElement("QueryOptions") 

        [System.Xml.XmlNode]$ndReturn = $service.GetListItems($listName, $null, $query, $viewFields, "1000", $queryOptions, $null)

 # Build hash table of all employees for lookup
# if account is empty, use 'generic'
     $eHash = @{}
    foreach ($row in $ndReturn.data.row) {

        $employee = New-Object PSObject
        $employee | Add-Member -type NoteProperty -name ID -value $row.ows_ID
        $employee | Add-Member -type NoteProperty -name SID -value $row.ows_SID
        $employee | Add-Member -type NoteProperty -name Account -value $row.ows_Account
        $employee | Add-Member -type NoteProperty -name FirstName -value $row.ows_FirstName
        $employee | Add-Member -type NoteProperty -name Department -value $row.ows_Department 
        $employee | Add-Member -type NoteProperty -name WorkPhone -value $row.ows_WorkPhone
        $employee | Add-Member -type NoteProperty -name FullLastName -value $row.ows_FullLastName 
        # we have records without accounts. use the ID as the unique.
        if($row.ows_SID) {
            $eHash.Add($row.ows_SID, $employee)
         } else {
            $eHash.Add($row.ows_ID, $employee)
         }


Then I call the AD module to get the AD people I need, and start looping through them. If I can find a SID in my hash, I might need to update, else I insert. I do this through a CAML structure. The replace statements to take care of any & should look like .Replace("&", "&")

get-aduser -filter * -Properties Enabled,ObjectClass,SamAccountName,GivenName,Surname,OfficePhone,Office,Department,DisplayName | 
Where-object -FilterScript {($_.Enabled -eq 'True')   -and ($_.Surname -ne $null) -and ($_.SamAccountName -match "^[^\.]*$")} |

foreach-object {

    # see if SID is in the hash table

        # you have to change SID to a string to match it to hash table key
        $sidstring = $_.SID.ToString()

          if(-not $eHash.ContainsKey($sidstring)) {
              write-host "new entry for " $_.SamAccountName  $_.SID
              $totalChanges += 1
             $i += 1
             $caml += "(Method ID='$i' Cmd='New')"
             $caml += "(Field Name='Title')" + $_.Surname.substring(0,1) + "(/Field)"
             $caml += "(Field Name='Account')" + $_.SamAccountName +  "(/Field)"
             $caml += "(Field Name='FirstName')" + $_.GivenName + "(/Field)"
          #   $caml += "(Field Name='Department')" + $_.Office + "(/Field)"
             $caml += "(Field Name='Department')" + $_.Department.Replace("&", "&") + "(/Field)"
             $caml += "(Field Name='WorkPhone')" + $_.OfficePhone + "(/Field)"
             $caml += "(Field Name='PhoneType')" + "Employee" + "(/Field)"
             $caml += "(Field Name='FullName')" + $_.GivenName + " " + $_.Surname.substring(0,1) + "(/Field)"
             $caml += "(Field Name='FullLastName')" + $_.Surname + "(/Field)"
             if($nickname -ne $_.GivenName) {
                  $caml += "(Field Name='Nickname')" + $nickname + "(/Field)"
             }
             $caml += "(Field Name='SID')" + $sidstring + "(/Field)"
             $caml += "(/Method)"


# done with loop through AD - write changes if any to SharePoint List


# if there are items to insert
if(($i -gt 0) -or ($u -gt 0) -or ($d -gt 0)) {

    $xmlDoc = new-object System.Xml.XmlDocument
    $batchelement = $xmlDoc.createElement("Batch")
    $batchelement.setattribute("OnError", "Continue")            
    $batchelement.setattribute("ListVersion", "1") 
    $batchelement.InnerXML = $caml 

    write-host $i Total Inserts
    write-host $u Total Updates
    write-host $d Total Deletions
  [System.Xml.XmlNode]$ndReturn = $service.UpdateListItems($listName, $batchelement)

} else {

    write-host "There are no additions or updates or deletions to Employee Phone List"
}

There's lots of other stuff in there as well, another section to get disabled users and delete them out of the list.

Like I said, probably other cool ways to do this, but I wanted to learn more Powershell and how to talk to AD so I did it this way.

  • I appreciate the effort you put into this and sharing it with me i should have mentioned my minimal experience with sharepoint as I was only introduced to it yesterday. As well I was hoping to stay away from any coding since it is not on my small list of talents, but I will take what you have given me and try my best to learn. Thank you, – Ryan Reeves Jun 2 '17 at 20:33
  • No problem. It's going to be pretty hard without coding, and SharePoint does have a pretty steep learning curve. What I did means you have to figure out CAML, list web services, powershell, how to call AD in powershell....., if that's the case then you might want to investigate how to 'surface' the user list in SharePoint xxx/_layouts/15/people.aspx?MembershipGroupId=0 (or /14 if you are on SP2010) will show you all people in a site collection, but it doesn't show you the phone numbers. – Robin in Or Jun 2 '17 at 21:29
  • To get the phone numbers, you can go to the settings->list settings options in xxx/_layouts/15/people.aspx?MembershipGroupId=0 , and create a new view that includes the information you need. Good luck. – Robin in Or Jun 2 '17 at 21:39
  • I'm not seeing the settings -> List settings option is this maybe only for admins? – Ryan Reeves Jun 5 '17 at 13:40
  • Could be - the user list is at the site collection level, so if you aren't an admin of the site you might need someone else to create a new view for you. Relatively innocuous thing so they should be able to do that for you. – Robin in Or Jun 5 '17 at 14:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.