Summary

This report finds files stored in Teams chat locations within Microsoft 365.

Details

Microsoft 365 storage includes SharePoint document libraries, OneDrive for Business, and Teams drives. This report narrows the scope to files stored in Teams chat locations.

Code

SQL Server

WITH q AS (
    SELECT
        d.web_url AS drive_path,
        di.id AS item_id,
        di.ms365_id,
        di.scan_id,
        di.web_url,
        di.item_type,
        di.modify_time AS item_modify_time,
        di.create_time AS item_create_time,
        RIGHT(pp.web_url, LEN(pp.web_url) - LEN(d.web_url)) AS parent_path,
        di.name AS item_name,
        di.size AS item_size,
        udm.ms365_user_id
    FROM ms365.drives AS d
    JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state=1
    JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id
    JOIN ms365.drive_items AS pp ON pp.ms365_drive_id = d.ms365_id AND pp.ms365_parent_id = '' AND pp.scan_id = ds.id
    JOIN ms365.drive_items AS di ON di.ms365_parent_id = pp.ms365_id AND pp.scan_id = di.scan_id
    WHERE di.item_type = 2 AND di.name='Microsoft Teams Chat Files'
    
    UNION ALL
    
    SELECT
        d.web_url AS drive_path,
        di.id AS item_id,
        di.ms365_id,
        di.scan_id,
        di.web_url,
        di.item_type,
        di.modify_time AS item_modify_time,
        di.create_time AS item_create_time,
        RIGHT(q.web_url, LEN(q.web_url) - LEN(d.web_url)) AS parent_path,
        di.name AS item_name,
        di.size AS item_size,
        q.ms365_user_id
    FROM ms365.drives AS d
    JOIN ms365.drive_items AS di ON di.ms365_drive_id = d.ms365_id
    JOIN q ON di.ms365_parent_id = q.ms365_id AND di.scan_id = q.scan_id
),
p AS (
    SELECT
        q.item_id,
        q.drive_path,
        q.parent_path,
        q.item_name,
        q.item_size,
        q.item_modify_time,
        q.item_create_time,
        q.ms365_user_id,
        u.display_name AS user_display_name,
        u.upn AS user_principal_name,
        COUNT(*) OVER (PARTITION BY q.item_type, q.drive_path) AS items_count,
        SUM(q.item_size) OVER (PARTITION BY q.item_type, q.drive_path) AS items_size,
        ROW_NUMBER() OVER (PARTITION BY q.item_type, q.drive_path ORDER BY q.item_type) AS items_rownum
    FROM q
    JOIN ms365.users AS u ON u.ms365_id = q.ms365_user_id
    WHERE q.item_type = 1
),
sh AS (
    SELECT
        q.item_id,
        count(*) AS sharing_link_count
    FROM q
    JOIN ms365.permissions AS p ON p.drive_item_id = q.item_id
    WHERE p.roles <> 'owner' AND p.roles <> ''
    GROUP BY q.item_id
),
r AS (
    SELECT
        p.*,
        DENSE_RANK() OVER (ORDER BY p.items_size DESC) AS items_size_rank,
        DENSE_RANK() OVER (ORDER BY p.items_count DESC) AS items_count_rank
    FROM p
),
s AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY r.items_rownum, r.items_size_rank ASC, r.drive_path) AS items_size_rank_rownum,
        ROW_NUMBER() OVER (ORDER BY r.items_rownum, r.items_count_rank ASC, r.drive_path) AS items_count_rank_rownum,
        r.*
    FROM r
)
SELECT
    s.*,
    COALESCE(sh.sharing_link_count, 0) AS share_count
FROM s
LEFT JOIN sh ON sh.item_id = s.item_id

PostgreSQL

WITH RECURSIVE q AS (
    SELECT
        d.web_url AS drive_path,
        di.id AS item_id,
        di.ms365_id,
        di.scan_id,
        di.web_url,
        di.item_type,
        di.modify_time AS item_modify_time,
        di.create_time AS item_create_time,
        RIGHT(pp.web_url, length(pp.web_url) - length(d.web_url)) AS parent_path,
        di.name AS item_name,
        di.size AS item_size,
        udm.ms365_user_id
    FROM ms365.drives AS d
    JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id AND ds.scan_state=1
    JOIN ms365.user_drives AS udm ON udm.ms365_drive_id = d.ms365_id
    JOIN ms365.drive_items AS pp ON pp.ms365_drive_id = d.ms365_id AND pp.ms365_parent_id = '' AND pp.scan_id = ds.id
    JOIN ms365.drive_items AS di ON di.ms365_parent_id = pp.ms365_id AND pp.scan_id = di.scan_id
    WHERE di.item_type = 2 AND di.name='Microsoft Teams Chat Files'
    
    UNION ALL
    
    SELECT
        d.web_url AS drive_path,
        di.id AS item_id,
        di.ms365_id,
        di.scan_id,
        di.web_url,
        di.item_type,
        di.modify_time AS item_modify_time,
        di.create_time AS item_create_time,
        RIGHT(q.web_url, length(q.web_url) - length(d.web_url)) AS parent_path,
        di.name AS item_name,
        di.size AS item_size,
        q.ms365_user_id
    FROM ms365.drives AS d
    JOIN ms365.drive_items AS di ON di.ms365_drive_id = d.ms365_id
    JOIN q ON di.ms365_parent_id = q.ms365_id AND di.scan_id = q.scan_id
),
p AS (
    SELECT
        q.item_id,
        q.drive_path,
        q.parent_path,
        q.item_name,
        q.item_size,
        q.item_modify_time,
        q.item_create_time,
        q.ms365_user_id,
        u.display_name AS user_display_name,
        u.upn AS user_principal_name,
        COUNT(*) OVER (PARTITION BY q.item_type, q.drive_path) AS items_count,
        SUM(q.item_size) OVER (PARTITION BY q.item_type, q.drive_path) AS items_size,
        ROW_NUMBER() OVER (PARTITION BY q.item_type, q.drive_path) AS items_rownum
    FROM q
    JOIN ms365.users AS u ON u.ms365_id = q.ms365_user_id
    WHERE q.item_type = 1
),
sh AS (
    SELECT
        q.item_id,
        count(*) AS sharing_link_count
    FROM q
    JOIN ms365.permissions AS p ON p.drive_item_id = q.item_id
    WHERE p.roles <> 'owner' AND p.roles <> ''
    GROUP BY q.item_id
),
r AS (
    SELECT
        p.*,
        DENSE_RANK() OVER (ORDER BY p.items_size DESC) AS items_size_rank,
        DENSE_RANK() OVER (ORDER BY p.items_count DESC) AS items_count_rank
    FROM p
),
s AS (
    SELECT
        ROW_NUMBER() OVER (ORDER BY r.items_rownum, r.items_size_rank ASC, r.drive_path) AS items_size_rank_rownum,
        ROW_NUMBER() OVER (ORDER BY r.items_rownum, r.items_count_rank ASC, r.drive_path) AS items_count_rank_rownum,
        r.*
    FROM r
)
SELECT
    s.*,
    COALESCE(sh.sharing_link_count, 0) AS share_count
FROM s
LEFT JOIN sh ON sh.item_id = s.item_id