none
Retrieve Onedrive for business URL for users mentioned in a CSV file using PowerShell

    Question

  • Hello,

    I have a few PowerShell scripts on how to retrieve the ODFB URLs for users in my tenant, however, I have an extremely long list of users, and those scripts take more than 7hrs for what I tried...

    Is it possible to get the users' ODFB URL only for a subset of users that would be in a CSV file? With the "Import-Csv" cmdlet somewhere in the script?

    Any help appreciated.

    Thanks.


    FrenchSpeaker - MCTS

    Saturday, April 30, 2016 5:14 PM

Answers

  • What's the format of the CSV file though? If you have the username there (in a column called UserPrincipalName), you can just add the following lines:

    $csv = Import-Csv 'C:\test.csv'
    $csvlist = $csv.UserPrincipalName -join "|"
    $users = $Users.LoginName | ? {$_ -match $csvlist}
    

    You can add them right after the "Create People Manager object to retrieve profile data" section. The rest can remain the same.

    Sunday, May 1, 2016 6:08 PM
  • Oh right, sorry, you're supposed to edit out one more line. As we are overwriting the $users variable now, use this for the cycle:

    Foreach ($User in $Users)
        {
        $UserProfile = $PeopleManager.GetPropertiesFor($User)

    The rest can remain the same.

    Monday, May 2, 2016 12:29 PM

All replies

  • Well, not knowing what the scripts look like, all I can tell you is that it should be possible. Here's an example script based on CSOM's PeopleManager:

    Add-Type –Path “C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\ISAPI\Microsoft.SharePoint.Client.dll”
    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”
    
    $MySiteURL= “https://tenant-my.sharepoint.com”
    $credentials = Get-Credential
    
    #$SecurePass = ConvertTo-SecureString -string $GlobalAdminPassword -AsPlainText -Force
    $Context = New-Object Microsoft.SharePoint.Client.ClientContext($MySiteURL)
    $Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($credentials.UserName,$credentials.Password)
    $Context.Credentials = $Creds
    $Users = $Context.Web.SiteUsers
    $Context.Load($Users)
    $Context.ExecuteQuery()
    
    $peopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
    $members = New-Object System.Collections.ArrayList
    $i = 0
    
    $users = $Users.LoginName | ? {$_ -match "vasil|huku|pesho"}
    
    foreach ($User in $Users) { 
        $userProfile = $peopleManager.GetPropertiesFor($user)
        $Context.Load($userProfile)
        $Context.ExecuteQuery()
    
        if ($userProfile.Email -ne $null -and $userProfile.UserProfileProperties.PersonalSpace -ne “”) {
            $obj = New-Object PSObject
    
            Add-Member -InputObject $obj -MemberType NoteProperty -Name "EmailAddress" -Value $userProfile.Email
            Add-Member -InputObject $obj -MemberType NoteProperty -Name "ODFBSite" -Value $userProfile.UserProfileProperties.PersonalSpace
    
            $members.Add($obj) > $Null
         
            $i++
        }
    }
    
    $members

    Line 20 is where I do a regex match against a list of predefined usernames. You can adapt it as needed (i.e. feed the names form a CSV file, etc)
    Saturday, April 30, 2016 6:29 PM
  • Hi Vasil,

    Thanks for the answer.
    I'm not an expert, but my understanding is that this part loads every ODFB in the "-my" site collection:

    $Users = $Context.Web.SiteUsers
    $Context.Load($Users)
    $Context.ExecuteQuery()

    You do a "match", but where should the CSV file be inserted instead of direct names?

    Thanks


    FrenchSpeaker - MCTS

    Saturday, April 30, 2016 6:57 PM
  • Again, I'm giving you an example here, I have no way of knowing what method you are using if you don't share the script :)

    As I don't have any idea of what you have in the CSV, so I cannot just randomly insert it. Assuming it contains the username, line 20 in the code above is where you can insert a "|" delimited list to match against the full user list.

    Saturday, April 30, 2016 8:53 PM
  • Hi Vasil,
    Sorry for not sharing the script :-(

    Here is what I use

    #Specify tenant admin and URL
    $User = "<admin>@<TenantName>.onmicrosoft.com"
    
    #Configure Site URL and User
    $SiteURL = "https://<TenantName>-my.sharepoint.com"
    
    #Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
    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"
    
    $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 ""
            }  
        }
    
    
    #Create People Manager object to retrieve profile data
    $Output = "C:\users\<LocalAcct>\desktop\\Output.csv"
    $Headings = "OneDrive URL"
    $Headings -join "," | Out-File -Encoding default -FilePath $Output
    
    $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)
            {
            $UPP = $UserProfile.UserProfileProperties
            $Properties = $UserProfile.PersonalUrl
            $Properties -join "," | Out-File -Encoding default -Append -FilePath $Output
            }  
        }
    

    It gives me an Output file formatted like this:

    OneDrive URL
    https://<TenantName>-my.sharepoint.com/personal/user1_domain_co_uk/
    https://<TenantName>-my.sharepoint.com/personal/user2_domain_co_uk/

    So I'd like to integrate a CSV file somewhere in the script to get only the ODFB URL for those CSV users, and not go through my tenant to give all ODFB (takes several hours in my situation).

    So if I can get the ODFB urls against a CSV file mentioned, then I can match them together, and see who is & and who's not been provisioned in a "breakdown" way.

    Thank you.


    FrenchSpeaker - MCTS

    Sunday, May 1, 2016 12:57 PM
  • What's the format of the CSV file though? If you have the username there (in a column called UserPrincipalName), you can just add the following lines:

    $csv = Import-Csv 'C:\test.csv'
    $csvlist = $csv.UserPrincipalName -join "|"
    $users = $Users.LoginName | ? {$_ -match $csvlist}
    

    You can add them right after the "Create People Manager object to retrieve profile data" section. The rest can remain the same.

    Sunday, May 1, 2016 6:08 PM
  • Vasil,

    Yes, my CSV file is formatted like this:
    ----------------------------------------------
    UserPrincipalName
    John.Smith123@mydomain.co.uk
    Jane.Doe456@mydomain.co.uk

    I inserted your script above to look like this

    #Specify tenant admin and URL
    $User = "<Admin>@<TenantName>.onmicrosoft.com"
    
    #Configure Site URL and User
    $SiteURL = "https://<TenantName>-my.sharepoint.com"
    
    #Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
    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"
    
    $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)
    
    $csv = Import-Csv "C:\users\<LocalAcct>\Desktop\MyCSVFile.csv"
    $csvlist = $csv.UserPrincipalName -join "|"
    $users = $Users.LoginName | ? {$_ -match $csvlist}
    
    
    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 ""
            }  
        }
    
    
    #Create People Manager object to retrieve profile data
    $Output = "C:\users\<LocalAcct>\desktop\\Output.csv"
    $Headings = "OneDrive URL"
    $Headings -join "," | Out-File -Encoding default -FilePath $Output
    
    $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)
            {
            $UPP = $UserProfile.UserProfileProperties
            $Properties = $UserProfile.PersonalUrl
            $Properties -join "," | Out-File -Encoding default -Append -FilePath $Output
            }  
        }

    At least the script doesn't go through all the ODFB users, but I got an error:
    ==============================================

    Exception calling "ExecuteQuery" with "0" argument(s): "Account name cannot be null or empty.
    Parameter name: strAcct"
    At C:\Users\<LocalAcct>\Desktop\<MyPSscript>.ps1:69 char:5
    +     $Context.ExecuteQuery()
    +     ~~~~~~~~~~~~~~~~~~~~~~~
        + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
        + FullyQualifiedErrorId : ServerException

    Help appreciated.

    Thank you.


    FrenchSpeaker - MCTS

    Monday, May 2, 2016 10:46 AM
  • Oh right, sorry, you're supposed to edit out one more line. As we are overwriting the $users variable now, use this for the cycle:

    Foreach ($User in $Users)
        {
        $UserProfile = $PeopleManager.GetPropertiesFor($User)

    The rest can remain the same.

    Monday, May 2, 2016 12:29 PM
  • Thanks alooooot Vasil, the script now works beautifully (^_^)

    FrenchSpeaker - MCTS

    Monday, May 2, 2016 1:47 PM
  • You're welcome. You can share the full script here or on some blog so that others with the same requirements can 'borrow' it :)

    And don't forget to mark it as answer :)

    Monday, May 2, 2016 5:43 PM
  • Here is the full script fully working

    <#
    THIS SCRIPT WILL EXPORT TO A CSV FILE LIKE THIS:
    =================================================
    UserPrincipalName,OneDrive URL
    John.Doe@mydomain.com,https://<TenantName>-my.sharepoint.com/personal/John_doe_mydomain_com/
    john.smith123@mydomain.com,https://<TenantName>-my.sharepoint.com/personal/john_smith123_mydomain_com/
    
    #>
    
    
    #Specify tenant admin and URL
    $User = "<admin>@<TenantName>.onmicrosoft.com"
    
    #Configure Site URL and User
    $SiteURL = "https://<TenantName>-my.sharepoint.com"
    
    #Add references to SharePoint client assemblies and authenticate to Office 365 site - required for CSOM
    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"
    
    $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)
    
    $csv = Import-Csv "C:\<My_CSV_File.csv>"
    $csvlist = $csv.UserPrincipalName -join "|"
    $users = $Users.LoginName | ? {$_ -match $csvlist}
    
    
    Foreach ($User in $Users)
        {
        $UserProfile = $PeopleManager.GetPropertiesFor($User)
        $Context.Load($UserProfile)
        $Context.ExecuteQuery()
        If ($UserProfile.Email -ne $null)
            {
            Write-Host "User:" $User.UserPrincipalName -ForegroundColor Green
            $UserProfile.UserProfileProperties
            Write-Host ""
            }  
        }
    
    
    #Create People Manager object to retrieve profile data
    $Output = "C:<My_Output_Location>\ODFBurl.csv"
    $Headings = "UserPrincipalName","OneDrive URL"
    $Headings -join "," | Out-File -Encoding default -FilePath $Output
    
    $PeopleManager = New-Object Microsoft.SharePoint.Client.UserProfiles.PeopleManager($Context)
    Foreach ($User in $Users)
        {
        $UserProfile = $PeopleManager.GetPropertiesFor($User)
        $Context.Load($UserProfile)
        $Context.ExecuteQuery()
        If ($UserProfile.Email -ne $null)
            {
            $UPP = $UserProfile.UserProfileProperties
            $Properties = $UPP.'SPS-UserPrincipalName',$UserProfile.PersonalUrl
            $Properties -join "," | Out-File -Encoding default -Append -FilePath $Output
            }  
        }

    Voila!


    FrenchSpeaker - MCTS

    Monday, May 2, 2016 6:06 PM
  • This was very helpful worked great! Thanks

    rcusa

    Monday, November 13, 2017 1:44 PM