Export OneDrive for Business experience settings

A user's OneDrive for Business experience stores information to help the user find and navigate to content of interest to them. Most of this information can be accessed by end users using the corresponding in-product features listed in the following table:

Experience Data stored End user access
Shared items A list of documents shared with the user by others. It also presents a view of the documents the user has shared with others, which is a view of the permissions the user has set on the items in their OneDrive for Business account. From a user's OneDrive for Business, select Shared.
Discover view A list of relevant content they have access to, based on who they work with and what they're working on.
This depends on integration with the Office Graph, which admins can control in the Settings tab in the SharePoint admin center.
From a user's OneDrive for Business, select Discover.
Mobile Push Notifications Push notifications to users' OneDrive for Business and SharePoint Online mobile apps, if configured. This includes relevant activity such as new files shared with them.
Admins can manage these in Settings tab in the SharePoint admin center, under Mobile Push Notifications – OneDrive and Mobile Push Notifications – SharePoint.
View Notifications sent to the OneDrive for Business and SharePoint mobile apps.
Followed Sites and Content Users can follow sites, documents, or people.
Followed sites can be viewed using in product experiences.
Followed sites can be viewed and managed in the SharePoint Home experience. This experience shows Recent Sites as well.
From a user's OneDrive for Business account, they can select the Settings gear icon, select Site Settings, then select Newsfeed. On the right side, they can click the appropriate content type under I'm Following.
Newsfeed A list of user activities, including followed content, mentions, and profile changes. Users can access it using in-product experiences. From a user's OneDrive for Business account, they can select the Settings gear icon, select Site Settings, and then select Newsfeed.
Newsfeed settings can be managed as part of the user profile experience, that was previously described.
Access requests A list of requested access to content. From a user's OneDrive for Business account, they can select the Settings gear icon, select Site Settings, and then select Access Requests and Invitations

An admin can export these lists by using PowerShell Script and SharePoint Client-Side Object Model (CSOM) commands in this article. All of the needed CSOM assemblies are included in the SharePointPnPPowerShellOnline Microsoft PowerShell module.

This is a sample script and can be adapted to meet your organization’s needs. For example, an admin can extract the information for user1@contoso.com by using the following procedure.

  1. Assign yourself admin permissions to the user's OneDrive for Business account. This can be done in the Microsoft 365 admin center.

  2. Install the required Microsoft PowerShell modules:

    Install-Module SharePointPnPPowerShellOnline

    Install-Module CredentialManager

  3. Run the ExportODBLists PowerShell script below (or a customize version of the script):

    $ODBSite = "https://contoso-my.sharepoint.com/personal/user1_contoso_com"

    ExportODBLists.ps1 -siteUrl $ODBSite

The script will export data stored for the features described in the previous table to multiple CSV files. The admin can review or redact before providing them to the end user.

The script creates following CSV files (if the corresponding lists are found). The name of the CSV file corresponds to the title of the list.

File Description
(Reference, <id>).csv, where <id> is a sequence of letters and numbers List of items shared with the user
Sharing Links.csv List of sharing links generated by the user
userActivityFeedHiddenListF4387007-BE61-432F-8BDB-85E6B9679E4B.csv List of relevant activity
notificationSubscriptionHiddenList6D1E55DA-2564-4A22-A5F9-6C4FCAFF53DE.csv List of notifications sent to mobile apps, and app IDs for those devices
Social.csv List of followed content
MicroFeed.csv List of Newsfeed items
Access Requests.csv List of Access requests
SharePointHomeCacheList.csv Cached data from the user navigating to SharePoint Home. This is refreshed every time the user navigates to SharePoint Home.

ExportODBLists script

Copy the contents below and paste them into a text file. Save the file as ExportODBLists.ps1. The script can export all fields from the target lists using the exportAllFields parameter. The script can be modified to export all lists and to handle other sites as well.

Note

If you see an error about an assembly not being loaded, double-check the path to the latest version of the SharePointPnPPowerShellOnline PowerShell Module as defined in the Add-Type Path parameters. The path may be different on your computer or you may be using a different version of the module (the module version is part of the path).

#ExportODBLists
#Exports  ODB experience settings, stored in several SharePoint Lists

param([string]$siteUrl, [bool]$exportAllFields=$false, [bool]$useStoredCreds=$true, [string]$exportFolder)
Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\2.26.1805.0\Microsoft.SharePoint.Client.dll"
Add-Type -Path "C:\Program Files\WindowsPowerShell\Modules\SharePointPnPPowerShellOnline\2.26.1805.0\Microsoft.SharePoint.Client.Runtime.dll"

if (!$siteUrl)
{
    Write-Host "Please specify a OneDrive site using -siteUrl."
    return
}

if ($useStoredCreds)
{
    Write-Host "Retrieving stored Windows credentials for $siteUrl."
    $cred = Get-StoredCredential -Target $siteUrl
    if (!$cred)
    {
        Write-Host "Didn't find stored credential for $siteUrl. Please provide credentials to connect."
        $cred = Get-Credential
    }
}
else
{
   $cred = Get-Credential
}

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($cred.UserName,$cred.Password)
$webURL = $siteUrl
$ctx = New-Object Microsoft.SharePoint.Client.ClientContext($webURL)
$ctx.Credentials = $credentials

#root folders of lists to export
$SWMRoot = "Reference " #starts with this string
$notificationsRoot = "notificationSubscriptionHiddenList6D1E55DA25644A22"
$activityFeedRoot = "userActivityFeedHiddenListF4387007BE61432F8BDB85E6"
$accessRequestsRoot = "Access Requests"
$microfeedRoot = "PublishedFeed"
$SPHomeCacheRoot = "SharePointHomeCacheList"
$sharingLinksRoot = "Sharing Links"
$socialRoot = "Social"

#list fields to eexport
$SWMFields = @("ID","Created","Modified","Title","RemoteItemPath","OwnerDisplayName","OwnerSipAddress","RemoteItemFileSystemObjectType",
                "RemoteItemCreatorDisplayName","RemoteItemCreatorSipAddress","RemoteItemCreatedDateTime",
                "RemoteItemModifierDisplayName","RemoteItemModifierSipAddress","RemoteItemModifiedDateTime",
                "SWMSharedByDisplayName","SWMSharedBySipAddress","SWMSharedByDateTime",
                "RemoteItemLastAccessedDateTime","RemoteItemServerRedirectedUrl","RemoteItemServerRedirectedEmbedUrl")
$accessRequestsFields = @("ID","Created","Modified","Title","RequestId","RequestedObjectTitle","RequestedObjectUrl","PermissionType","PermissionLevelRequested","RequestDate",
                            "RequestedByDisplayName","RequestedBy","ReqByUser",
                            "RequestedForDisplayName","RequestedFor","ReqForUser",
                            "ApprovedBy","AcceptedBy","Status","Expires","WelcomeEmailSubject","WelcomeEmailBody","ExtendedWelcomeEmailBody","Conversation")
$microfeedFields = @("ID","Created","Modified","Title","MicroBlogType","PostAuthor","RootPostOwnerID","RootPostUniqueID","ReplyCoungett","Order","ContentData")
$notificationsFields = @("ID","Created","Modified","Title","SubscriptionId","PoolName","SecondaryPoolName","AppType","NotificationHandle",
                            "SecondsToExpiry","DestinationType","SubmissionDateTime","ExpirationDateTime","Locale","DeviceId","HostName","NotificationCounter",
                            "SingleSignOutKey","NotificationScenarios","ComplianceAssetId","AppAuthor","AppEditor")
$SPHomeCacheFields = @("ID","Created","Modified","Author","Editor","Title","Value")
$sharingLinksFields = @("ID","Created","Modified","Title","SharingDocId","ComplianceAssetId","CurrentLink","AvailableLinks")
$socialFields = @("ID","Created","Modified","Author","Editor","Title","Url","Hidden","HasFeed","SocialProperties")
$activityFeedFields = @("ID","Created","Modified","Title","ActivityId","ItemId","PushNotificationsSent","EmailNotificationSent","IsActorActivity","IsRead","Order",
                        "ItemChildCount","FolderChildCount","ActivityEventType","ActivityEvent")


#get lists in the web
try{
    $lists = $ctx.web.Lists
    $ctx.load($lists)
    $ctx.executeQuery()
}
catch{
	write-host "$($_.Exception.Message)" -foregroundcolor red
}


#identify the lists to export
$listsToExport = @()
foreach($list in $lists)
{
    $ctx.load($list)
    $ctx.load($list.RootFolder)
    $ctx.executeQuery()
    $listTitle = [string]$list.Title
    $listRoot = $list.RootFolder.Name
    
    Write-host ("Processing List: " + $list.Title + " with " + $list.ItemCount + " items").ToUpper() -ForegroundColor Yellow
    Write-host (">> List Root Folder: " + $listRoot) -ForegroundColor Yellow

    if ($listRoot.StartsWith($SWMRoot,"CurrentCultureIgnoreCase") -and $list.ItemCount -ge 1)
    {
        Write-Host ">> Found: Shared With Me List" -ForegroundColor Green
        $listDetails = @{listType = "Shared With Me List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $SWMFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $notificationsRoot)
    {
        Write-Host ">> Found: Notifications List" -ForegroundColor Green
        $listDetails = @{listType = "Notifications List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $notificationsFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $activityFeedRoot)
    {
        Write-Host ">> Found: User Activity Feed List" -ForegroundColor Green
        $listDetails = @{listType = "User Activity Feed List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $activityFeedFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $accessRequestsRoot)
    {
        Write-Host ">> Found: Access Requests List" -ForegroundColor Green
        $listDetails = @{listType = "Access Requests List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $accessRequestsFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $microfeedRoot)
    {
        Write-Host ">> Found: MicroFeed List" -ForegroundColor Green
        $listDetails = @{listType = "Microfeed List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $microfeedFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $SPHomeCacheRoot)
    {
        Write-Host ">> Found: SharePoint Home Cache List" -ForegroundColor Green
        $listDetails = @{listType = "SharePoint Home Cache List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $SPHomeCacheFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $sharingLinksRoot)
    {
        Write-Host ">> Found: Sharing Links List" -ForegroundColor Green
        $listDetails = @{listType = "Sharing Links List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $sharingLinksFields}
        $listsToExport += $listDetails
    }
    elseif ($listRoot -eq $socialRoot)
    {
        Write-Host ">> Found: Social List" -ForegroundColor Green
        $listDetails = @{listType = "Social List"; listTitle = $listTitle; listRoot = $listRoot; listFields = $socialFields}
        $listsToExport += $listDetails
    }

}

#export list function
function exportList
{
    Param ([string] $listTitle, [string[]]$listFields, [string]$exportFile)

    Write-Host ("Exporting List: " + $listTitle).ToUpper() -ForegroundColor Green
    Write-Host (">> File location: $exportFile") -ForegroundColor Green

    #Get the list items
	$list = $lists.GetByTitle($listTitle)
	$listItems = $list.GetItems([Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery())
    $fieldColl = $list.Fields
	$ctx.load($listItems)
    $ctx.load($fieldColl)
	$ctx.executeQuery()
    
    if ($listFields) #if you're passing a specific set of fields, in a specific order, process those
    {
         #Array to Hold List Items 
         $listItemCollection = @() 
         #Fetch each list item value to export to excel
         foreach($item in $listItems)
         {
            $exportItem = New-Object PSObject 

            Foreach ($field in $listFields)
            {
                    if($NULL -ne $item[$field])
                    {
                        #Expand the value of Person or Lookup fields
                        $fieldType = $item[$field].GetType().name
                        if (($fieldType -eq "FieldLookupValue") -or ($fieldType -eq "FieldUserValue"))
                        {
                            $fieldValue = $item[$field].LookupValue
                        }
                        elseif ($fieldType -eq "FieldUrlValue")
                        {
                            $fieldValue = $item[$field].Url
                        }
                        else
                        {
                            $fieldValue = $item[$field]   
                        }
                    }
                    $exportItem | Add-Member -MemberType NoteProperty -name $field -value $fieldValue
            }
            #Add the object with above properties to the Array
            $listItemCollection += $exportItem
         }
        #Export the result Array to CSV file
        $listItemCollection | Export-CSV $exportFile -NoTypeInformation
    }
    else #export all fields for the list
    {
         #Array to Hold List Items 
         $listItemCollection = @() 
         #Fetch each list item value to export to excel
         foreach($item in $listItems)
         {
            $exportItem = New-Object PSObject 
            Foreach($field in $fieldColl)
            {
                    if($NULL -ne $item[$field.InternalName])
                    {
                        #Expand the value of Person or Lookup fields
                        $fieldType = $item[$field.InternalName].GetType().name
                        if (($fieldType -eq "FieldLookupValue") -or ($fieldType -eq "FieldUserValue"))
                        {
                            $fieldValue = $item[$field.InternalName].LookupValue
                        }
                        elseif ($fieldType -eq "FieldUrlValue")
                        {
                            $fieldValue = $item[$field].Url
                        }
                        else
                        {
                            $fieldValue = $item[$field.InternalName]   
                        }
                    }
                    $exportItem | Add-Member -MemberType NoteProperty -name $field.InternalName -value $fieldValue
            }
            #Add the object with above properties to the Array
            $listItemCollection += $exportItem
         }
        #Export the result Array to CSV file
        $listItemCollection | Export-CSV $exportFile -NoTypeInformation
    }
    
}

#export the lists
foreach ($list in $listsToExport)
{
     #if we have a valid folder for export, use it, otherwise export to the current directory
     if ($exportFolder -and (Test-Path $exportFolder -PathType Container))
     {
         $filepath = Join-Path -Path $exportFolder -ChildPath ($list["listTitle"] + ".csv")
     }
     else
     {
         $filepath = ($list["listTitle"] + ".csv")
     }

     #export the lists
     if ($exportAllFields)
     {
         exportList -listTitle $list["listTitle"] -exportFile $filepath
     }
     else
     {
         exportList -listTitle $list["listTitle"] -listFields $list["listFields"] -exportFile $filepath
     }
}