Export Microsoft OneDrive experience settings
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. |
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
}
}