galileofile-reporter
Microsoft 365: Filename extensions
Updated: Galileo File Reporter
25.2+ 25.2+
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') Downloads
| Attachment | Size |
|---|---|
| MS365-FileExtension.zip | 6.54 KB |
Sample Report
| Attachment | Size |
|---|---|
| MS365 File Extension.pdf | 304.74 KB |