MS365 DateAge LastModified

Details

Summary

New with version 4.0, comes the ability to scan your Microsoft 365 tenant and create reports from the data found in the Document Libraries and One Drive File Systems.

This report focuses on the when the files in the desired drive_categores were last modified by year. The report layout includes both Summary and the Detailed Reports.

Details

The query is designed to include all files that located in SharePoint and Teams Document Libraries along with the OneDrive drives. You can narrow down the report by modifying the where clause on line 32 to just include one or more of those drive_categories.

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

Code

 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')

 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')

Author

rlagger

Post date

Thursday, April 8, 2021 - 08:08

Last modified

Tuesday, March 29, 2022 - 08:15

Properties

Supported Version

4.1

4.0

Report Category

Microsoft 365