Summary

This filename extension report scans your Microsoft 365 tenant to help you see what applications are being used, based on the filename extensions present.

Details

The report includes both the summary and detail sections familiar from the built-in reports. To filter the query by drive category, modify line 53.

Code

SQL Server

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

PostgreSQL

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