MS365 Filename Extension
MS365 Filename Extension
Details
Details
Summary
New with version 4.0 is the ability scan your Microsoft 365 tenent. The filename extension report helps you see what applications are being used, by way of the filename extensions used.
This report includes both the summary and detail sections you maybe used to from the built-in reports. The query can be filtered by drive_categories by modifying line 53.
Code
WITH q AS ( SELECT di.name AS file_name, LOWER(di.file_extension) AS file_extension, di.ms365_parent_id, di.scan_id, di.ms365_drive_id, di.size AS file_size, COUNT(*) OVER (PARTITION BY di.file_extension) AS file_extension_count, SUM(di.size) OVER (PARTITION BY LOWER(di.file_extension)) AS file_extension_size, ROW_NUMBER() OVER (PARTITION BY LOWER(di.file_extension) ORDER BY di.id) AS file_extension_rownum FROM ms365.drive_items AS di JOIN ms365.drive_scans AS ds ON ds.id = di.scan_id --JOIN ms365.user_drives AS ud ON ud.ms365_drive_id = di.ms365_drive_id WHERE 1=1 AND di.item_type = 1 AND ds.scan_state = 1 AND LEN(di.file_extension) > 0 ), p AS ( SELECT q.*, DENSE_RANK() OVER (ORDER BY q.file_extension_size DESC) AS file_extension_size_rank FROM q ), x as ( SELECT d.web_url as drive_path, RIGHT(pp.web_url, LEN(pp.web_url) - LEN(d.web_url)) AS parent_path, p.file_name, p.file_extension, p.file_size, p.file_extension_count, p.file_extension_size, p.file_extension_size_rank, p.file_extension_rownum, CASE WHEN d.drive_type = 'business' THEN 'OneDrive' WHEN gdm.id IS NOT NULL THEN 'Teams' ELSE 'SharePoint' END AS drive_category, DENSE_RANK() OVER (ORDER BY p.file_extension, pp.web_url) AS file_extension_group_rownum, ROW_NUMBER() OVER (PARTITION BY p.file_extension ORDER BY pp.web_url, p.file_name) AS file_extension_detail_rownum FROM p JOIN ms365.drives AS d ON d.ms365_id = p.ms365_drive_id LEFT JOIN ms365.drive_items AS pp ON pp.scan_id = p.scan_id AND pp.ms365_id = p.ms365_parent_id LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id --WHERE p.file_extension_rank <= 10 -- AND p.file_extension_rownum = 1 ) SELECT x.* FROM x WHERE x.drive_category in ('SharePoint','Teams','OneDrive')
WITH q AS ( SELECT di.name AS file_name, lower(di.file_extension) AS file_extension, di.ms365_parent_id, di.scan_id, di.ms365_drive_id, di.size AS file_size, COUNT(*) OVER (PARTITION BY di.file_extension) AS file_extension_count, SUM(di.size) OVER (PARTITION BY LOWER(di.file_extension)) AS file_extension_size, ROW_NUMBER() OVER (PARTITION BY LOWER(di.file_extension) ORDER BY di.id) AS file_extension_rownum FROM ms365.drive_items AS di JOIN ms365.drive_scans AS ds ON ds.id = di.scan_id --JOIN ms365.user_drives AS ud ON ud.ms365_drive_id = di.ms365_drive_id WHERE 1=1 AND di.item_type = 1 AND ds.scan_state = 1 AND length(di.file_extension) > 0 ), p AS ( SELECT q.*, DENSE_RANK() OVER (ORDER BY q.file_extension_size DESC) AS file_extension_size_rank FROM q ), x as ( SELECT d.web_url as drive_path, RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path, p.file_name, p.file_extension, p.file_size, p.file_extension_count, p.file_extension_size, p.file_extension_size_rank, p.file_extension_rownum, CASE WHEN d.drive_type = 'business' THEN 'OneDrive' WHEN gdm.id IS NOT NULL THEN 'Teams' ELSE 'SharePoint' END AS drive_category, DENSE_RANK() OVER (ORDER BY p.file_extension, pp.web_url) AS file_extension_group_rownum, ROW_NUMBER() OVER (PARTITION BY p.file_extension ORDER BY pp.web_url, p.file_name) AS file_extension_detail_rownum FROM p JOIN ms365.drives AS d ON d.ms365_id = p.ms365_drive_id LEFT JOIN ms365.drive_items AS pp ON pp.scan_id = p.scan_id AND pp.ms365_id = p.ms365_parent_id LEFT JOIN ms365.group_drives AS gdm ON gdm.ms365_drive_id = d.ms365_id --WHERE p.file_extension_rank <= 10 -- AND p.file_extension_rownum = 1 ) SELECT x.* FROM x WHERE x.drive_category in ('SharePoint','Teams','OneDrive')
Post date
Thursday, April 8, 2021 - 09:48
Last modified
Friday, April 26, 2024 - 12:48
Downloads
Attachment | Size |
---|---|
MS365-FileExtension.zip | 6.54 KB |
Sample Report
Attachment | Size |
---|---|
MS365 File Extension.pdf | 304.74 KB |