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

Question

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

Answer 1

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:

---

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.

---

Answer 2

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

Answer 3

Now this code getting this error

Answer 4

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

Answer 5

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."