PowerShell command to generate OneDrive report for a user's on files included sub folder & Size

Question

PowerShell command to generate OneDrive report for a user's on files included sub folder & Size

JT 61

please refer to powerShell code below, i had tried to run and hit into error. I need some expertise to assist to look into this code on how to make it work. Really appreciate if someone able to assist on this

I had posted this question here, but I has been asked to repost it to SharePoint

here is the code

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"

$now=Get-Date -format "dd-MMM-yy,HH:mm:ss"

$fileFormat = Get-Date -format "dd-MMM-yy_HHmmss"

Write-Host "Script Start : '$($now)'" -ForegroundColor Yellow

$global:SourceCount = 0 ### To know the total count of the documents to be processed

$global:Processed = 0

$global:OutFilePath = "C:\Reports\files_" + $fileFormat + ".csv"

$header = "Date,Time,Type,Parent,Name,Path,FilesCount,FileSize(bytes),Remark"

Add-Content -Path $global:OutFilePath -Value "`n $header"

$username = "XXX@Piepel .com"

$password = "XXXXXXXXXXX"

$srcUrl = "https://XXXXXX-my.sharepoint.com/personal/SSSSXXXXYYYY_com"

$srcLibrary = "Documents"

$securePassword = ConvertTo-SecureString $password -AsPlainText -Force

$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)

function to create a log for the report in csv

function WriteLog

{

param (

[Parameter(Mandatory=$true)] $type, $folderName,$name,$path,$fileCount,$fileSize,$remark

)

$nowTime=Get-Date -format "dd-MMM-yy,HH:mm:ss"

$folderName = $folderName.replace(",","|") ### sometime folder / file name has comma so replace it with something

$name = $name.replace(",","|")

$path = $path.replace(",","|")

$lineContent = "$($nowTime),$($type),$($folderName),$($name),$($path),$($fileCount),$($fileSize),$($remark)"

Add-Content -Path $global:OutFilePath -Value "$lineContent"

$global:Processed = $global:Processed +1

}

function ScanFolders

{

param (

[Parameter(Mandatory=$true)] $srcfolder, $parentName

)

$remarkDetail = ""

$replacedUser=""

Write-Host "Total Count: $($global:SourceCount) Completed: $($global:Processed)" -ForegroundColor Cyan

Write-Host "Navigate to: " $srcfolder.ServerRelativeUrl -ForegroundColor Yellow

$folderItem = $srcfolder.ListItemAllFields

$srcContext.Load($f)

$srcContext.Load($folderItem)

$srcContext.ExecuteQuery()

$authorEmail = $folderItem["Author"].Email

$editorEmail = $folderItem["Editor"].Email

$filepath = $folderItem["FileDirRef"]

$fileSize = $fItem["File_x0020_Size"]

$fileName = $srcfolder.Name

$fileCol = $srcfolder.Files

$srcContext.Load($fileCol)

$srcContext.ExecuteQuery()

WriteLog "Folder" $parentName $fileName $filepath $fileCol.Count 0 $remarkDetail

foreach ($f in $fileCol)

{

$remarkDetail = ""

$replacedUser=""

$fItem = $f.ListItemAllFields

$srcContext.Load($f)

$srcContext.Load($fItem)

$srcContext.ExecuteQuery()

$authorEmail = $fItem["Author"].Email

$editorEmail = $fItem["Editor"].Email

$filepath = $fItem["FileDirRef"]

$fileSize = $fItem["File_x0020_Size"]

$fileName = $fItem["FileLeafRef"]

WriteLog "File" $srcfolder.Name $fileName $filepath 0 $fileSize $remarkDetail

}

$fL1FolderColl = $srcfolder.Folders

$srcContext.Load($fL1FolderColl);

$srcContext.ExecuteQuery();

foreach ($myFolder in $fL1FolderColl)

{

$srcContext.Load($myFolder)

$srcContext.ExecuteQuery()

ScanFolders $myFolder $srcfolder.Name

}

The script starts here to run ####

Write-Host "Authenticating ..." -ForegroundColor White

$srcContext = New-Object Microsoft.SharePoint.Client.ClientContext($srcUrl)

$srcContext.Credentials = $credentials

$srcWeb = $srcContext.Web

$srcList = $srcWeb.Lists.GetByTitle($srcLibrary)

$query = New-Object Microsoft.SharePoint.Client.CamlQuery

$listItems = $srcList.GetItems($query)

$srcContext.Load($srcList)

$srcContext.Load($listItems)

$srcContext.ExecuteQuery()

$global:SourceCount = $srcList.ItemCount

Write-Host "Total Count: $($global:SourceCount)" -ForegroundColor Cyan

foreach($item in $listItems)

{

if($item.FileSystemObjectType -eq "File")

{

$remarkDetail = ""

$replacedUser=""

$srcF = $item.File

$fItem = $srcF.ListItemAllFields

$srcContext.Load($srcF)

$srcContext.Load($fItem)

$srcContext.ExecuteQuery()

$authorEmail = $fItem["Author"].Email

$editorEmail = $fItem["Editor"].Email

$filepath = $fItem["FileDirRef"]

$fileSize = $fItem["File_x0020_Size"]

$fileName = $fItem["FileLeafRef"]

WriteLog "File" "Root" $fileName $filepath 0 $fileSize $remarkDetail

}

elseif ($item.FileSystemObjectType -eq "Folder")

{

$srcContext.Load($item)

$srcContext.ExecuteQuery()

$folder = $srcWeb.GetFolderByServerRelativeUrl($item.FieldValues["FileRef"].ToString())

$srcContext.Load($folder)

$srcContext.ExecuteQuery()

ScanFolders $folder "Root"

}

$now=Get-Date -format "dd-MMM-yy,HH:mm:ss"

Write-Host "Total Count: $($global:SourceCount) Completed: $($global:Processed)" -ForegroundColor Cyan

Write-Host "END Start : '$($now)'" -ForegroundColor Yellow

===============

the error I had encounter is

Attached below is the error encountered.

PS C:\reports> & '.\All item usage.ps1'
Script Start : '26-Jan-21,09:24:51'
Authenticating ...
Exception calling "ExecuteQuery" with "0" argument(s): "List 'Documents' does not exist at site with URL
'https://XXXXXXX.sharepoint.com/personal/XXXXXXXX_com'.";
At C:\reports\All item usage.ps1:207 char:1

$srcContext.ExecuteQuery()
~~~~~~~~~~~~~~~~~~~~~~~~~~
CategoryInfo : NotSpecified: (:) [], MethodInvocationException
FullyQualifiedErrorId : ServerException

Total Count:
The collection has not been initialized. It has not been requested or the request has not been executed. It may need
to be explicitly requested.
At C:\reports\All item usage.ps1:213 char:9

foreach($item in $listItems)
~~~~~
CategoryInfo : OperationStopped: (:) [], CollectionNotInitializedException
FullyQualifiedErrorId : Microsoft.SharePoint.Client.CollectionNotInitializedException

Total Count: Completed: 0
END Start : '26-Jan-21,09:24:52'
PS C:\reports>

JT 61 Reputation points

2021-02-05T00:51:05.69+00:00

let me re-post this to Microsoft service request. Initially they seem to be reluctant to help before I Posted the question in this forum, saying that this is beyond their scope to look into the powershell.

Thank you for your follow up. Will re-open a queue if need help again

Accepted answer

0 additional answers

Your answer

JT 61 Reputation points

2021-02-05T00:51:05.69+00:00

let me re-post this to Microsoft service request. Initially they seem to be reluctant to help before I Posted the question in this forum, saying that this is beyond their scope to look into the powershell.

Thank you for your follow up. Will re-open a queue if need help again

Answer 1

MichaelHan-MSFT 18,126

Hi @JT ,

From the error :

Exception calling "ExecuteQuery" with "0" argument(s): "List 'Documents' does not exist at site with URL
'https://XXXXXXX.sharepoint.com/personal/XXXXXXXX_com'.";;

Looks like the library title 'Documents' is not correct, please make sure you use the correct list title.

You could try the below to check all the list title in the onedrive:

$lists=$srcWeb.Lists  
$srcContext.Load($lists)  
$srcContext.executeQuery()  
foreach($list in $lists){  
$list.title  
}

If an Answer is helpful, please click "Accept Answer" and upvote it.
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.

JT 61 Reputation points

2021-01-28T08:47:16.39+00:00

Dear Michael,

I am new to this. May I know how to execute the line you had provided to target on one user's oneDrive ?

You can refer to attachment screenshot provided

You can refer to the attached screenshot, and the URL of the user's OneDrive

https://XXXXXX.sharepoint.com/personal/XXXX_XXXX_com/Documents/Forms/All.aspx

$srcLibrary = "Documents"

It seem that it is matching using ==> $srcLibrary = "Documents" ?

MichaelHan-MSFT 18,126

Hi @JT ,

Just run the below script and check the result:

$username = "******@XXXXX.com"  
$password = "XXXXXXXXXXX"  
$srcUrl = "https://XXXXXX-my.sharepoint.com/personal/SSSSXXXXYYYY_com";  
$securePassword = ConvertTo-SecureString $password -AsPlainText -Force  
$credentials = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($username, $securePassword)  
$srcContext = New-Object Microsoft.SharePoint.Client.ClientContext($srcUrl)  
$srcContext.Credentials = $credentials  
$srcWeb = $srcContext.Web  
 $lists=$srcWeb.Lists  
 $srcContext.Load($lists)  
 $srcContext.executeQuery()  
 foreach($list in $lists){  
 $list.title  
 }

JT 61 Reputation points

2021-01-28T09:26:38.677+00:00

Hi Michael,

Below is the result executed for two user. Looks same. Which one should I use to check all the files/folders in user's OneDrive ?

PS C:\reports> & '.\All item usage - Copy.ps1'
appfiles
Form Templates
List Template Gallery
Master Page Gallery
Social
Solution Gallery
Style Library
TaxonomyHiddenList
Theme Gallery
User Information List
Web Part Gallery

PS C:\reports> & '.\All item usage - JG.ps1'
appfiles
Form Templates
List Template Gallery
Master Page Gallery
Social
Solution Gallery
Style Library
TaxonomyHiddenList
Theme Gallery
User Information List
Web Part Gallery
PS C:\reports>
MichaelHan-MSFT 18,126 Reputation points

2021-01-29T03:13:17.313+00:00

Hi ,

None of the result should you use. From the result, looks like you do not have the "Documents" list in user's onedrive. This is weried.

In user's onedrive, click site contents in the seetings. Could you see the Documents list?
JT 61 Reputation points

2021-02-03T02:38:41.097+00:00

Hi Sorry for late reply.

You can refer to below 4 screenshot for the settings. A bit different from yours. Seem to be there is a "Documents exist". Need your further advise
MichaelHan-MSFT 18,126 Reputation points

2021-02-04T02:35:03.603+00:00

Hi @JT ,

From the screenshots, yes the "Documents" List does exist in the user's onedrive. However, it's strange that you could not get it from the powershell. I would suggest you create a service request with microsoft to check what's going on the backend.

Share via

PowerShell command to generate OneDrive report for a user's on files included sub folder & Size

function to create a log for the report in csv

$srcContext.Load($f)

$fileSize = $fItem["File_x0020_Size"]

$srcContext.Load($f)

The script starts here to run ####

0 additional answers

Your answer