How to get a detail list from SharePoint Online site retention policy result automatically

Question

Hello there,

I know there is a retention setting in O365 Security & Compliance that can use for archive or deleting specific files which are older than certain time. But is there a way to get a list of those files (such as file name, path, owner, etc.) before we delete or archive them? So we can get a list first when every time the archive or delete action are going to proceed for all SharePoint site.

I have tried to follow this article and use PoweShell to get that, but some how I just get GUID. https://www.sharepointdiary.com/2019/04/sharepoint-online-delete-files-older-than-30-days-using-powershell.html

I will appreciate if someone can help to let me know which article I can refer or what PowerShell I can use if there is a way to get a detail list for those files which match the retention policy, or if there is a way to get it via GUI (Audit log?)

Thank you.

Sincerely,

YL

Answer 1

@YL

Before you start or create the Retention Policy in the Microsoft 365 compliance, you can use the following script commands to view the original documents information.

Please run the following script in the SharePoint Online Management Shell as admin:

#the specific location path  
$SiteURL= "https://testlz.sharepoint.com/sites/Intern1"  
Connect-PnPOnline -Url $SiteURL -UseWebLogin  

#this script only view a signal Document Lib  
$List = Get-PnPList -Identity /doc  
write-host "Document Library Name : "$List.Title -ForegroundColor Green  

#loop through all files   
$ListItems = Get-PnPListItem -List $List | Where {$_["FileLeafRef"] -like "*.*"}  
$DocumentsData=@()  
ForEach($Item in $ListItems)  
{  
    #Collect Documents Data  
    $DocumentsData += New-Object PSObject -Property @{  
        FileURL = $Item.FieldValues['FileRef']  
        FileID = $Item.FieldValues['GUID']  
        FileName = $Item.FieldValues['FileLeafRef']  
        FileDate = $Item.FieldValues['Created']  
    }     
}  

$DocumentsData  

Then, go to Microsoft 365 compliance page, start your Retention Policy.

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

@YL
I have updated my Answer.

Please the following steps:

1.Before you start or create the Retention Policy in the Microsoft 365 compliance, you can use PowerShell command to get a Documents Information Report.
Please run the following script in the SharePoint Online Management Shell as admin:

$SiteURL= "https://testlz.sharepoint.com/sites/Intern1"  
Connect-PnPOnline -Url $SiteURL -UseWebLogin  
$List = Get-PnPList -Identity /doc  
$Query = "<View><Query><Where><Lt><FieldRef Name='Created' Type='DateTime'/><Value Type='DateTime'>  
          <Today OffsetDays='-5'/></Value></Lt></Where></Query></View>"  
$ListItems = Get-PnPListItem -List $List -Query $Query  
#Collect Documents Data  
$Docs=@()  
ForEach($Item in $ListItems)   
{  
    $DocsObject = New-Object -TypeName PSObject  
    $DocsObject | Add-Member -Name 'Library name' -MemberType Noteproperty -Value $List.Title   
    $DocsObject | Add-Member -Name 'FileID' -MemberType Noteproperty -Value $Item.FieldValues['GUID']                               
    $DocsObject | Add-Member -Name 'FileName' -MemberType Noteproperty -Value $Item.FieldValues['FileLeafRef']  
    $DocsObject | Add-Member -Name 'FileDate' -MemberType Noteproperty -Value $Item.FieldValues['Created']   
    $DocsObject | Add-Member -Name 'FileURL' -MemberType Noteproperty -Value $Item.FieldValues['FileRef']  

    $Docs += $DocsObject                           
}  
$Docs | export-csv "D:\report111.csv"   

Then, you can send an Email to notice owners in advance by using Microsoft Flow.
Recurrence [according to the custom value, run periodically] >> Get files(properties only) >> Filter array [get documents information as an array] >> Apply to each >> Condition [select documents that meet conditions. Note that Formula should be consistent with the PowerShell command ] >> (if yes)Send an email notification
Ps: lessOrEquals(formatDateTime(addDays(item()?['Created'], 5), 'MM/dd/yyyy'), utcNow('MM/dd/yyyy'))

Finally, go to Microsoft 365 compliance page, start your Retention Policy.

Thanks,
Echo Du

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

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.

Answer 2

Thank you, Echo Du. This script can help, but it seems not what I am looking for.

Because the purpose I wish to see those file is identifying which files older than specific time, such as older than 3 years, and then archive it.

But I would like to notify the file owner before we proceed the archive action.

That is why I am looking for a way to get those files path and owners before we proceed the retention policy.

If you have any idea about how can I do it, please kindly let me know, thank you so much. ^__^

Sincerely,

YL