Export Microsoft OneDrive experience settings

Article
02/21/2023

A user's Microsoft OneDrive 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	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 account.	From a user's OneDrive, select Shared.
Mobile Push Notifications	Push notifications to users' OneDrive and SharePoint mobile apps, if configured. This includes relevant activity such as new files shared with them. Admins can manage these on the Settings page of the new SharePoint admin center.
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 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 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 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 PnP PowerShell 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.

Assign yourself admin permissions to the user's OneDrive account. This can be done in the Microsoft 365 admin center.
Install the required Microsoft PowerShell modules:

Install-Module SharePointPnPPowerShellOnline

Install-Module CredentialManager
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 OneDrive 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
     }
}

Share via

Export Microsoft OneDrive experience settings

ExportODBLists script

Additional resources