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')
 
Author
rlagger
Last modified
Monday, April 26, 2021 - 10:58
Properties
Supported Version
Report Category
Includes a Report Layout
Yes
Databases
Rating
  • 1
  • 2
  • 3
  • 4
  • 5
Total votes: 0
Downloads
AttachmentSize
MS365-FileExtension.zip6.54 KB
Sample Report
AttachmentSize
MS365 File Extension.pdf304.74 KB