Below I’m sharing some options – how to find files or documents in SharePoint, Teams or OneDrive that are older than some specific date. Why you might need that? To avoid content deletion as a result of retention policies in action.

If an organization is mature enough – it has data lifecycle policies established. If so – these polices must be applied to information stored in Microsoft 365 via retention policies. Retention policies are configured under Compliance center, but in particular applied to documents stored in SharePoint and OneDrive. Policies might dictate to retain documents or delete documents. Let say your organization is implementing retention policy that is configured to delete documents if 5 years passed after the file was last modified. That literally means all your files modified more than 5 years ago will be deleted and you will not even notice it. So – what if you want to know – which documents in your OneDrive or SharePoint site are older than 5 years?

Search in SharePoint with query parameters (GUI)

At any level of your site hierarchy – root level, library, folder etc. – you can refine your search results specifying properties values, e.g. document author or document created date or document last modified date. For last modified date the property is “LastModifiedTime”, e.g. here I’m in the SharePoint site document library:

If I put in search box query “LastModifiedTime<2023-07-15” I’ll get only documents older than July 15 2023:

There is property “LastModifiedTime”, and there is also property “LastModifiedTimeForRetention” you can use to detect documents your retention policy works against.

When you issue query with just “LastModifiedTimeForRetention<2023-06-15” you get as results all kind of SharePoint content – including pages, libraries, folders etc. If your concern is to avoid specific documents deletion as a result of retention policy – you’d probably be interested in finding documents only and do not want folders (as retention policy applies to all files in all document libraries), e.g.

• If you need only Microsoft Word documents older than some specific date (e.g. June 15, 2021), you might use query: “*.docx LastModifiedTimeForRetention<2021-06-15”
• For Microsoft Word and Excel documents older than June 15, 2021 – you’ might ‘d use query: “(*.docx OR *.xlsx) LastModifiedTimeForRetention<2021-06-15”

If you need only Microsoft Word documents authored by some specific User and older than some specific date, you might use query:
“*.docx author:Patti LastModifiedTimeForRetention<2021-01-01”

Search in Teams

You can successfully use refinements to search for the same in Teams. But you’d select “Files” tab for better experience:

Microsoft is constantly updating this product, so your experience might be different. Note also that when you search in teams – you search through all sites you have access to.

Search for old documents in OneDrive

You can use the same technique – putting “LastModifiedTime<2023-07-15” in search bar in OneDrive. In some ways it’s even better, as you can

• search for files in all sites (not only your personal OD site)
• select multiple file types you are interested in

Search with Graph API

The same query you can use to search content with Microsoft Graph API. Here is the code example:

$query = "LastModifiedTimeForRetention<2021-01-01"
$apiUrl = "https://graph.microsoft.com/beta/search/query"
$body = @"
{ 
  "requests": [
    {
      "entityTypes": [
        "driveItem"
      ],
      "query": {
        "queryString": "$query"
      }
    }
  ]
}
"@

$res = Invoke-RestMethod -Headers $Headers -Uri $apiUrl -Body $Body -Method Post -ContentType 'application/json'
$res.value[0].searchTerms
$res.value[0].hitsContainers[0].hits

Video tutorial

How to fetch all SharePoint documents older than some amount of time:

References

• Microsoft SharePoint Search Query Syntax
• Retention policies in SharePoint