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

Question

question

Raju-6114 asked Apr 20, '22 Raju-6114 published 2 days ago

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

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

office-sharepoint-online office-sharepoint-server-administration office-sharepoint-server-development

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TongZhangMSFT-7548 · Apr 21 at 01:32 AM

Hi @Raju-6114,

I am currently doing some research on this issue, will let you know as soon as possible.

1 ·

Answer 1 · 2022-04-21T08:41:12.863Z

TongZhangMSFT-7548 answered Apr 21, '22

Hi @Raju-6114,

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.

image.png (21.1 KiB)

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 2 · 2022-04-27T12:47:07.663Z

Raju-6114 answered Apr 27, '22

Hi @TongZhangMSFT-7548

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 3 · 2022-08-03T07:41:30.5Z

Raju-6114 answered Aug 3, '22 TongZhangMSFT-7548 commented Aug 3, '22

Now this code getting this error

4.png (13.9 KiB)

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TongZhangMSFT-7548 · Aug 03 at 09:07 AM

Hi @Raju-6114,

According to this error, please try to do a troubleshooting, check if the user name and password are entered correctly.

0 ·

Answer 4 · 2022-08-03T09:13:33.687Z

Raju-6114 answered Aug 3, '22 Raju-6114 published 2 days ago

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

· 5

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TongZhangMSFT-7548 · Aug 03 at 09:33 AM

Hi @Raju-6114,

Please try to use the following code to connect to SharePoint online:

 Connect-PnPOnline -url "tenant" -UseWebLogin

0 ·

Raju-6114 TongZhangMSFT-7548 · Aug 03 at 09:59 AM

Getting warning with no data export

0 ·

0.png (15.7 KiB)

TongZhangMSFT-7548 Raju-6114 · Aug 04 at 09:39 AM

Hi @Raju-6114,

Please refer to this document to connect to SharePoint Online using PowerShell with MFA.

https://www.sharepointdiary.com/2019/08/connect-sharepoint-online-powershell-using-mfa.html

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

0 ·

Show more comments

question