Summary

This report focuses on when files in your Microsoft 365 tenant were last modified, grouped by year. Scans data found in SharePoint document libraries and OneDrive for Business. The report layout includes both Summary and Detailed views.

Details

The query covers files in SharePoint document libraries, Teams drives, and OneDrive drives. You can narrow the report by modifying the WHERE clause on line 32 to include only one or more of those drive categories.

The corresponding zip file contains both SQL queries and the custom report layout.

Code

SQL Server

with q as (SELECT
    d.web_url AS drive_path,
    RIGHT(pp.web_url, LEN(pp.web_url) - LEN(d.web_url)) AS parent_path,
    di.name AS file_name,
    di.modify_time,
    DATEPART(year, di.modify_time) AS modify_year,
    di.create_time,
    DATEPART(year, di.create_time) AS create_year,
    di.size AS file_size,
    CASE
        WHEN udm.id IS NOT NULL THEN 'OneDrive'
        WHEN gdm.id IS NOT NULL THEN 'Teams'
        ELSE 'SharePoint'
    END AS drive_category,
    SUM(di.size) OVER (PARTITION BY DATEPART(year, di.modify_time)) AS modify_year_size,
    COUNT(*) OVER (PARTITION BY DATEPART(year, di.modify_time)) AS modify_year_count,
    ROW_NUMBER() OVER (PARTITION BY DATEPART(year, di.modify_time)  ORDER BY di.modify_time) AS modify_year_rownum,
    SUM(di.size) OVER (PARTITION BY DATEPART(year, di.create_time)) AS create_year_size,
    COUNT(*) OVER (PARTITION BY DATEPART(year, di.create_time)) AS create_year_count,
    ROW_NUMBER() OVER (PARTITION BY DATEPART(year, di.create_time) ORDER BY di.create_time) AS create_year_rownum
FROM ms365.drive_scans AS ds
JOIN ms365.drives AS d ON d.id = ds.drive_id
JOIN ms365.drive_items AS di ON di.ms365_drive_id = d.ms365_id
JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = ds.id
LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id
LEFT JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id
WHERE ds.scan_state = 1
    AND di.item_type = 1
)
select *
from q
where q.drive_category IN ('SharePoint','Teams','OneDrive')

PostgreSQL

with q as (SELECT
    d.web_url AS drive_path,
    RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path,
    di.name AS file_name,
    di.modify_time,
    date_part('year', di.modify_time)::int AS modify_year,
    di.create_time,
    date_part('year', di.create_time)::int AS create_year,
    di.size AS file_size,
    CASE
        WHEN udm.id IS NOT NULL THEN 'OneDrive'
        WHEN gdm.id IS NOT NULL THEN 'Teams'
        ELSE 'SharePoint'
    END AS drive_category,
    SUM(di.size) OVER (PARTITION BY date_part('year', di.modify_time))::bigint AS modify_year_size,
    COUNT(*) OVER (PARTITION BY date_part('year', di.modify_time))::bigint AS modify_year_count,
    ROW_NUMBER() OVER (PARTITION BY date_part('year', di.modify_time))::bigint AS modify_year_rownum,
    SUM(di.size) OVER (PARTITION BY date_part('year', di.create_time))::bigint AS create_year_size,
    COUNT(*) OVER (PARTITION BY date_part('year', di.create_time))::bigint AS create_year_count,
    ROW_NUMBER() OVER (PARTITION BY date_part('year', di.create_time))::bigint AS create_year_rownum
FROM ms365.drive_scans AS ds
JOIN ms365.drives AS d ON d.id = ds.drive_id
JOIN ms365.drive_items AS di ON di.ms365_drive_id = d.ms365_id
JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = ds.id
LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id
LEFT JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id
WHERE ds.scan_state = 1
  AND di.item_type = 1
)
select *
from q
where q.drive_category IN ('SharePoint', 'Teams', 'OneDrive')