Best way to read data from AD and insert in a list in Office 365

Question

What is the best way to read data such as name, email, designation, department, etc. from AD and insert in a SharePoint list in Office 365?

Is there some kind of event available which will fire when a new record is inserted in AD. So let's say is Person A is inserted, that code will run and insert data in list?

OR

We can make some kind of job that will read AD, let's say every one hour and sync it with our list?

OR

Configure User Profile Service that will sync all data with AD at regular intervals. And then we can have some kind of job which will read all user data from UPS and sync with list?

Speaking of job, what kind of job can we write in SharePoint online? Because in case of on-premise we can create timer job but I guess here it is not possible.

Answer 1

You would setup DirSync from your AD to the cloud. That will make the data available in SPO.

https://support.office.com/en-us/article/Set-up-directory-synchronization-for-Office-365-1b3b5318-6977-42ed-b5c7-96fa74b08846

[edit:]

If you want to go the job route, you have to consider that options for running jobs in O365 are rather limited. You need to have a place where you can run them, and there isn't much support in O365 to run regular jobs. So you need to execute them on a host you own. Since you want to sync from your AD you would most likely be a server onprem. You could use CSOM or rest to upload the data, but I would try to prevent this since there is already a working user sync in place, and I would just try to utilize that one as much as possible.

Possible option I can think of are a list with a person field in it, or creating a linked list to the user info list and enrich that list with some extra fields for you to manipulate. But this would not fill the list on changes in your AD... For that you could use an external job that pushes that information up there... But I am very hesitant to rebuild a user sync since there is already one in O365 and having more than one version of the "truth" will lead to customer questions

Ok... This stuff needs to run onprem since you need to access your AD to retrieve the users.

Try this pseudo Powershell to get the new users:

$timestamp= ((Get-Date).AddDays(-1)).Date Get-ADUser -Filter {whenCreated -ge $timestamp} -Properties whenCreated

I would save the last run and use that timestamp.

Then use CSOM or REST to add those entries to the list.

Answer 2

You can retrieve User Profile Properties using CSOM with PowerShell and export to csv:

#Specify tenant admin and URL
$User = "[email protected]"
#Configure Site URL and User
$SiteURL = "https://tenant.sharepoint.com/sites/site"    
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
    $Password = Read-Host -Prompt "Please enter your password" -AsSecureString
    $Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($User,$Password)
    #Bind to Site Collection
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Context.Credentials = $Creds
    #Identify users in the Site Collection
    $Users = $Context.Web.SiteUsers
    $Context.Load($Users)
    $Context.ExecuteQuery()
    #Create People Manager object to retrieve profile data
    $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
    Foreach ($User in $Users)
        {
        $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)
        $Context.Load($UserProfile)
        $Context.ExecuteQuery()
        If ($UserProfile.Email -ne $null)
            {
            Write-Host "User:" $User.LoginName -ForegroundColor Green
            $UserProfile.UserProfileProperties
            Write-Host ""
            }  
        }

Detailed script from Office 365 – Retrieve User Profile Properties using CSOM with PowerShell

Then export the csv file to SharePoint online list using PowerShell command:

SharePoint Online: Creating a list and import data from CSV using PowerShell