MS365 Teams Chat Files

Details

Details

Summary

Version 4.0 introduced the ability to scan and report on Microsoft 365 storage repositories. These include SharePoint document libraries, OneDrive for Business drives, and Team drives.

This report focuses on files that are located in Team chat file locations.

Code
 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
 
 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
 
Author
rlagger
Last modified
Friday, April 9, 2021 - 08:48
Properties
Supported Version
Report Category
Includes a Report Layout
Yes
Databases
Rating
  • 1
  • 2
  • 3
  • 4
  • 5
Total votes: 0
Downloads
AttachmentSize
MS365-TeamsChatFiles.zip6.32 KB
Sample Report
AttachmentSize
MS365-TeamsChatFiles.pdf461.01 KB