Update to the OneDrive for Business Admin Tool


Over the past several months, I've slowly been adding features to the OneDrive for Business Admin Tool (you can read about the previous updates and features here, here, and here).  Earlier this week, one of my peers asked if I knew of an easy way for a customer to search OneDrive for Business sites and delete all files of a certain type (say, video files).

A way?  Meh.  An easy way?  Definitely not.  This prompted the proverbial "Hold my beer" while I put my mediocre CSOM scripting ability to the task.  Since my OneDrive for Business admin tool already had the foundation for iterating through OneDrive sites, I thought it was the perfect launchpad to start such an endeavor.  And 11:00 PM seemed like the perfect time to start.

The magic is accomplished by layering in a small function and a query to get the files matching a particular pattern.

First, the function declaration:

function DeleteFilePattern($Pattern, $OD4BSite)
{
 If ($Confirm)
 {
 $FilesToDelete = @()
 foreach ($obj in $allItems)
 {
 If ($obj["FileRef"] -match $Pattern)
 {
 #$obj.DeleteObject()
 $FilesToDelete += $obj
 #$ClientContextSource.ExecuteQuery()
 If ($LogFile) { LogWrite -Function "DeleteFilePattern" -OD4BSite $OD4BSite -Note "File $($obj["FileRef"]) deleted." }
 }
 }
 $FilesToDelete.DeleteObject()
 $ClientContextSource.ExecuteQuery()
 }
 Else
 {
 foreach ($obj in $allItems)
 {
 If ($obj["FileRef"] -match $Pattern)
 {
 write-host "File $($obj["FileRef"]) matches. Specify Confirm switch to delete.";
 If ($LogFile) { LogWrite -Function "DeleteFilePattern-LogOnly" -OD4BSite $OD4BSite -Note "File $($obj["FileRef"]) would have been deleted." }
 }
 }
 }
}

Then building the collection of files to pass to the function:

If ($DeleteFilePattern)
 {
 $Library = $ClientContextSource.Web.Lists.GetByTitle("Documents")
 $ClientContextSource.Load($Library)
 $ClientContextSource.ExecuteQuery()
 $CamlQuery = New-Object Microsoft.SharePoint.Client.CamlQuery
 $CamlQuery.ViewXml = "<View Scope='RecursiveAll' />"
 $AllItems = $Library.GetItems($camlQuery)
 $ClientContextSource.Load($AllItems)
 $ClientContextSource.ExecuteQuery()
 
 If ($Confirm) { DeleteFilePattern -Pattern $DeleteFilePattern -Confirm }
 Else { DeleteFilePattern -Pattern $DeleteFilePattern }
 }

I tried a lot of things here, such diving into CAML (and discovering a lot of cool CAML query builder tools along the way) to specify file sets.  However, even after building a CAML query that resulted in the correct file filter (say, "docx") and testing it in the tools, I found that in the real world, my results were less than adequate.  Some of the fields that I was using to filter and build my query weren't available in SharePoint Online, so the net result was getting a collection back of all the files.  In the end, I just created a collection of all the files in the library using a very generic query ($CamlQuery.ViewXml = "<View Scope='RecursiveAll'/>") and then used standard regular expression pattern matching later on to sort out what I needed.  In the end, I think it resulted in something better, since people are much more familiar with creating regexes than CAML queries and the regular expressions have much better pattern matching capabilities.

You'll notice I highlighted two commented-out lines in magenta.  I kept those in as a reminder to blog about something that I had learned at about 1 am.   Since this is CSOM ("Client Side" code), everything is queued up on the client and then sent to the server when you perform an ExecuteQuery().  When I had those lines in and would perform a delete, I would receive the error "Collection was modified; enumeration operation may not execute."  This means I was modifying a collection that I was actively looping through.  Barf.

What I did instead is saved the pending deletes to the new object $FilesToDelete (in spiffy green).  That way, I could keep track of the things to delete matching the pattern, and then issue the delete for all objects together using:

 $FilesToDelete.DeleteObject()
 $ClientContextSource.ExecuteQuery()

Before I move to the next user's OneDrive for Business site collection.

Here it is in action (DeleteFilePattern with no confirmation):

$cred = Get-Credential
$tenant = "m365x306657"
.\OneDriveForBusinessAdmin.ps1 -DeleteFilePattern "\.xlsx" -Tenant $tenant -Credential $cred -GrantPermission

Then, I put some files in a user's OneDrive for Business site:

I just created a bunch of small text files and renamed them to:

"test file with .mpeg in the name but not the extension.txt"
testmp3.mp3
testmp4.mp4
testmpeg.mpeg
testmpg.mpg

My creativity apparently knows no bounds.

The goal was to make sure the pattern matching captures the right things.  In this case, I submitted a pattern of "\.mp4$|\.mpeg$|\.mpg$" (match files ending in .mp4, .mpeg, or .mpg).  Obviously, I want to make sure that I don't capture the files .mp3 (which I didn't specify in the filter) or files with .mpeg in the middle of the name but not at the end of the file (extension).

We can check the log file that was generated for results:

And then we can flip back to OneDrive for Business and verify that only the files matching the regex pattern were deleted:

If you just can't live without these and loads of other great features, head over to the Technet Gallery and download it--stat!

Comments (2)

  1. Jere Vekka says:

    How can I change to go thru only specific group folder for example? What format is needed to InputFile?

    1. You could try specifying the folder path as part of the filename in the regex:

      For example, to match files ending in “.xlsx” in a folder or subfolder called “monitored”:

      -DeleteFilePattern “/monitored/\S*(\.xlsx$)”

      That would capture:
      /monitored/subfolder/asdf.xlsx
      /monitored/file.xlsx
      /folder1/monitored/asdf.xlsx

      but not:
      /monitored/subfolder1/asdf.xlsx/anotherfolder

Skip to main content