Export all SPO user profile data where any properties blank or NULL

Raju Ram Bagra 41 Reputation points
2022-04-20T18:08:16.687+00:00

Below code working fine Export all SPO user profile data
However i need only data where any user profile properties blank or NULL

Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"

Function Export-SPOUserProfileProperties()

{
param

(
    [Parameter(Mandatory=$true)] [string] $SiteURL,
    [Parameter(Mandatory=$true)] [string] $CSVPath

)  

Try {

    #Setup Credentials to connect
    $Cred= Get-Credential
    $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)

    #Setup the context
    $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)
    $Ctx.Credentials = $Credentials


    #Delete the CSV report file if exists
    if (Test-Path $CSVPath) { Remove-Item $CSVPath }

    #Get all Users
    $Users = $Ctx.Web.SiteUsers
    $Ctx.Load($Users)
    $Ctx.ExecuteQuery()

           Write-host "Total Number of Profiles Found:"$Users.count -f Yellow

    #Get User Profile Manager
    $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)
    #Array to hold result
    $UserProfileData = @()
    Foreach ($User in $Users)

    {


    $date = (Get-Date).AddDays(-30).Date

Get-ADUser -Filter { WhenCreated -ge $date } -Properties WhenCreated,Department,CompanyName,GivenName,SurName,Countrz

 #Get-ADUser -Filter {Enabled -eq $TRUE} -SearchBase $OU -Properties Name,SamAccountName,Createdate | Where {($_.LastLogonDate -lt (Get-Date).AddDays(-30)) -and ($_.LastLogonDate -ne $NULL)} | Sort | Select Name,SamAccountName,LastLogonDate



        Write-host "Processing User Name:"$User.LoginName

        #Get the User Profile

        $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)

        $Ctx.Load($UserProfile)

        $Ctx.ExecuteQuery()

        if($UserProfile.Email -ne $Null)

        {

        #Send Data to object array

        $UserProfileData += New-Object PSObject -Property @{
        'User Account' = $UserProfile.UserProfileProperties["UserName"]
        'Full Name' = $UserProfile.UserProfileProperties["PreferredName"]
        'Test-Department' = $UserProfile.UserProfileProperties["Test-Department"]
        'E-mail' =  $UserProfile.UserProfileProperties["WorkEmail"]
        'Test-Country' = $UserProfile.UserProfileProperties["test--Country"]
        'Test-Company' = $UserProfile.UserProfileProperties["test--Company"]
        'Test-EmployeeName' = $UserProfile.UserProfileProperties["test-EmployeeName"]         

        }

        }

    }

    #Export the data to CSV
    $UserProfileData | Export-Csv $CSVPath -Append -NoTypeInformation
    write-host -f Green "User Profiles Data Exported Successfully to:" $CSVPath

}
Catch {
    write-host -f Red "Error Exporting User Profile Properties!" $_.Exception.Message
}

}

Call the function

$SiteURL="https://test-admin.sharepoint.com"
$CSVPath="C:\Temp\UserProfiles21.csv"
Export-SPOUserProfileProperties -SiteURL $SiteURL -CSVPath $CSVPath

SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
10,212 questions
SharePoint Server Development
SharePoint Server Development
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Development: The process of researching, productizing, and refining new or existing technologies.
1,594 questions
SharePoint Server Management
SharePoint Server Management
SharePoint Server: A family of Microsoft on-premises document management and storage systems.Management: The act or process of organizing, handling, directing or controlling something.
2,889 questions
{count} votes

Accepted answer
  1. Tong Zhang_MSFT 9,141 Reputation points
    2022-04-21T08:41:12.863+00:00

    Hi @Raju Ram Bagra ,

    Based on my research and testing, you can try adding a filter before exporting the csv file (as below):

    $UserProfileData |Where-Object E-mail -eq ''|Export-Csv $CSVPath -Append -NoTypeInformation   
    

    It will export all SharePoint Online user profile data where the properties Null.

    Full Script:

    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"  
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"  
    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"  
      
    Function Export-SPOUserProfileProperties()  
      
    {  
    param  
      
     (  
         [Parameter(Mandatory=$true)] [string] $SiteURL,  
         [Parameter(Mandatory=$true)] [string] $CSVPath  
     )    
     Try {  
         #Setup Credentials to connect  
         $Cred= Get-Credential  
         $Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)  
         #Setup the context  
         $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($SiteURL)  
         $Ctx.Credentials = $Credentials  
             
         #Delete the CSV report file if exists  
         if (Test-Path $CSVPath) { Remove-Item $CSVPath }  
         #Get all Users  
         $Users = $Ctx.Web.SiteUsers  
         $Ctx.Load($Users)  
         $Ctx.ExecuteQuery()  
                Write-host "Total Number of Profiles Found:"$Users.count -f Yellow  
         #Get User Profile Manager  
         $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)  
         #Array to hold result  
         $UserProfileData = @()  
         Foreach ($User in $Users)  
         {  
         $date = (Get-Date).AddDays(-30).Date  
    #Get-ADUser -Filter { WhenCreated -ge $date } -Properties WhenCreated,Department,CompanyName,GivenName,SurName,Countrz  
      #Get-ADUser -Filter {Enabled -eq $TRUE} -SearchBase $OU -Properties Name,SamAccountName,Createdate | Where {($_.LastLogonDate -lt (Get-Date).AddDays(-30)) -and ($_.LastLogonDate -ne $NULL)} | Sort | Select Name,SamAccountName,LastLogonDate  
                 
             Write-host "Processing User Name:"$User.LoginName  
             #Get the User Profile  
             $UserProfile = $PeopleManager.GetPropertiesFor($User.LoginName)   
             $Ctx.Load($UserProfile)  
             $Ctx.ExecuteQuery()  
               
             if($UserProfile.UserProfileProperties -ne $Null)  
             {  
                 #Send Data to object array  
                 $UserProfileData += New-Object PSObject -Property @{  
                 'User Account' = $UserProfile.UserProfileProperties["UserName"]  
                  'E-mail' =  $UserProfile.UserProfileProperties["WorkEmail"]  
                 }   
             }  
               
      
         }  
         #Export the data to CSV $UserProfileData   
         $UserProfileData |Where-Object E-mail -eq ''|Export-Csv $CSVPath -Append -NoTypeInformation   
        #$UserProfileData   
           
         write-host -f Green "User Profiles Data Exported Successfully to:" $CSVPath}  
       
     Catch {  
         write-host -f Red "Error Exporting User Profile Properties!" $_.Exception.Message  
     }  
    }  
      
      
    #Call the function  
      
    $SiteURL="https://xxxxx-admin.sharepoint.com"  
    $CSVPath="C:\Temp\UserProfiles21.csv"  
    Export-SPOUserProfileProperties -SiteURL $SiteURL -CSVPath $CSVPath  
    

    My test result:
    195058-image.png


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


    0 comments No comments

4 additional answers

Sort by: Most helpful
  1. Raju Ram Bagra 41 Reputation points
    2022-04-27T12:47:07.663+00:00

    Hi @Tong Zhang_MSFT

    Thanks for the support, it's working fine.

    I am asking next level query for same script as in below code i am going to Sync. SPO user profile 4 fields( only Blank/NULL )with AAD for only one user.

    How i can use this result sheet and sync. all fields with AAD (only Blank/NULL)

    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll"
    Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll"
    Add-Type -Path "C:\Program Files\Common Files\microsoft shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.UserProfiles.dll"
    cls

    Function Sync-UserProfileProperty()
    {
    Param
    (
    [Parameter(Mandatory=$true)] [string] $AdminSiteUrl,
    [Parameter(Mandatory=$true)] [string] $UserPrincipalName
    )
    #Get Credentials to connect
    $Cred = Get-Credential
    Try {
    #Connect to AzureAD
    Connect-AzureAD -Credential $Cred | Out-Null
    #Get the User Property from AzureAD
    $user = Get-AzureADUser -Filter "UserPrincipalName eq '$UserPrincipalName'" | Select Department,CompanyName,GivenName,SurName,Country

            #To get extended property value, use: $AzureADUser.ExtensionProperty.Item($ADPropertyName)  
            #Setup the context  
            $Ctx = New-Object Microsoft.SharePoint.Client.ClientContext($AdminSiteUrl)  
            $Ctx.Credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Cred.Username, $Cred.Password)  
            #Get User Profile Manager  
            $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Ctx)  
            #Sync User Profile Property from AD  
            $UserAccount = "i:0#.f|membership|$UserPrincipalName"  
            $PeopleManager.SetSingleValueProfileProperty($UserAccount, $SPOPropertyName, $ADUserPropertyValue)  
            $PeopleManager.SetSingleValueProfileProperty($UserAccount, "test-EmployeeName", $user.GivenName + " " + $user.Surname) ;  
            $PeopleManager.SetSingleValueProfileProperty($UserAccount, "test-Company", $user.CompanyName) ;  
            $PeopleManager.SetSingleValueProfileProperty($UserAccount, "test-Department", $user.Department) ;  
            $PeopleManager.SetSingleValueProfileProperty($UserAccount, "test-Country", $user.Country) ;  
            $Ctx.ExecuteQuery()  
            Write-host "User Profile Property has been Synched for: $UserPrincipalName" -f Green  
        }  
        Catch  
        {  
            write-host -f Red "Error Synching User Profile Property!" $_.Exception.Message  
        }  
    

    $Counter = 1
    ForEach($User in $AllUsers)
    {
    Write-host "`Updating User Profile Property for: $($User.UserPrincipalName)" -f Yellow

        #Get the User Property value from Azure AD         
        $ADUserPropertyValue = $User | Select -ExpandProperty $ADPropertyName  
    
        #Check if the AD Property is not Null  
        If (!([string]::IsNullOrEmpty($ADUserPropertyValue)))  
        {  
            #Get existing User Profile Property from SharePoint  
            $UserAccount = "i:0#.f|membership|$($User.UserPrincipalName)"  
            $UserProfile = Get-PnPUserProfileProperty -Account $UserAccount  
            $UserProfileProperty = $UserProfile.UserProfileProperties[$SPOPropertyName]  
    
            #Check if the Existing SharePoint User Profile Property is Null  
            If (([string]::IsNullOrEmpty($UserProfileProperty)))  
            {  
                Set-PnPUserProfileProperty -Account $UserAccount -PropertyName $SPOPropertyName -Value $ADUserPropertyValue  
                Write-host "`Updated User Profile Property for: $($User.UserPrincipalName)" -f Green  
            }  
            Else  
            {  
                Write-host "`Existing Value of the Property in SharePoint is Not Null! Skipping..." -f Yellow  
            }  
        }  
        else  
        {  
            Write-host "`AD Value of the Property is Null! Skipping..." -f Yellow  
        }  
        $Counter++  
        Write-Progress -Activity "Updating User Profile Data..." -Status "Updating User Profile $Counter of $($AllUsers.Count)" -PercentComplete (($Counter / $AllUsers.Count)  * 100)  
    }  
    

    }
    Catch {
    write-host -f Red "Error Updating User Profile Property!" $_.Exception.Message
    }

    Config parameters

    $AdminSiteUrl = "https://test-admin.sharepoint.com"
    $UserPrincipalName = "test@test .com"

    Call the function to sync a Property from Azure AD to SharePoint Online

    Sync-UserProfileProperty -AdminSiteUrl $AdminSiteURL -UserPrincipalName $UserPrincipalName

    0 comments No comments

  2. Raju Ram Bagra 41 Reputation points
    2022-08-03T07:41:30.5+00:00

    227584-4.png

    Now this code getting this error


  3. Raju Ram Bagra 41 Reputation points
    2022-08-03T09:13:33.687+00:00

    Yes, Tested multi time with different system and user name and password correct
    Is that issue with MFA connection ? successfully get 1 user data then always getting issue


  4. Raju Ram Bagra 41 Reputation points
    2022-12-23T11:09:54.747+00:00

    Passwords always correct but getting issue

    $UserProfileData |Where-Object E-mail -eq ''|Export-Csv $CSVPath -Append -NoTypeInformation
    cmdlet Get-Credential at command pipeline position 1
    Supply values for the following parameters:

    Error Exporting User Profile Properties! Exception calling "ExecuteQuery" with "0" argument(s): "The sign-in name or password does not match one in the Microsoft account system."

    0 comments No comments