Sharing Links by User Drive

Details

Summary

This report capitalizes on the ability of version 4.0 to scan your OneDrive for Business storage. Individuals within your company may be sharing out your companies IP with little regard for security. This report is limited to the list of all sharing links located with discovered User OneDrive for Business drives, and does not include Direct Access permissions.

Code

 SELECT
    t.default_name AS tenant_default_name,
    t.display_name AS tenant_display_name,
    t.tenant_name,
    u.display_name AS user_name,
    u.upn AS user_upn,
    u.ms365_id AS user_ms365_id,
    d.web_url AS drive_path,
    RIGHT(pp.web_url, LEN(pp.web_url) - LEN(d.web_url)) AS parent_path,
    di.name AS item_name,
    di.item_type,
    p.expire_time,
    p.has_password,
    p.is_inherited,
    p.link_prevents_download,
    p.link_scope,
    p.link_type,
    p.roles,
    CASE
        WHEN p.link_scope = 'anonymous' THEN '[Anonymous]'
        WHEN smg.display_name IS NOT NULL THEN smg.display_name
        WHEN smu.display_name IS NOT NULL THEN CONCAT(smu.display_name, ' (', smu.upn, ')')
        ELSE sm.display_name
    END AS link_trustee,
    sm.ms365_id AS link_ms365_id
FROM ms365.users AS u
JOIN ms365.tenants AS t ON t.id = u.tenant_id
JOIN ms365.user_drives AS ud ON ud.ms365_user_id = u.ms365_id AND ud.tenant_id = t.id
JOIN ms365.drives AS d ON d.ms365_id = ud.ms365_drive_id AND ud.tenant_id = t.id
JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id
JOIN ms365.drive_items AS di ON di.ms365_drive_id = ud.ms365_drive_id AND di.scan_id = ds.id
LEFT JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = ds.id
JOIN ms365.permissions AS p ON p.drive_item_id = di.id
LEFT JOIN ms365.sharing_link_members AS sm ON sm.permission_id = p.id
LEFT JOIN ms365.users AS smu ON smu.ms365_id = sm.ms365_id AND smu.tenant_id = t.id
LEFT JOIN ms365.groups AS smg ON smg.ms365_id = sm.ms365_id AND smg.tenant_id = t.id
WHERE ds.scan_state = 1
  AND (sm.id IS NOT NULL OR p.link_scope='anonymous')
  AND p.is_inherited = 'false'

 SELECT
    t.default_name AS tenant_default_name,
    t.display_name AS tenant_display_name,
    t.tenant_name,
    u.display_name AS user_name,
    u.upn AS user_upn,
    u.ms365_id AS user_ms365_id,
    d.web_url AS drive_path,
    RIGHT(pp.web_url, LENGTH(pp.web_url) - LENGTH(d.web_url)) AS parent_path,
    di.name AS item_name,
    di.item_type,
    p.expire_time,
    p.has_password,
    p.is_inherited,
    p.link_prevents_download,
    p.link_scope,
    p.link_type,
    p.roles,
    CASE
        WHEN p.link_scope = 'anonymous' THEN '[Anonymous]'
        WHEN smg.display_name IS NOT NULL THEN smg.display_name
        WHEN smu.display_name IS NOT NULL THEN CONCAT(smu.display_name, ' (', smu.upn, ')')
        ELSE sm.display_name
    END AS link_trustee,
    sm.ms365_id AS link_ms365_id
FROM ms365.users AS u
JOIN ms365.tenants AS t ON t.id = u.tenant_id
JOIN ms365.user_drives AS ud ON ud.ms365_user_id = u.ms365_id AND ud.tenant_id = t.id
JOIN ms365.drives AS d ON d.ms365_id = ud.ms365_drive_id AND ud.tenant_id = t.id
JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id
JOIN ms365.drive_items AS di ON di.ms365_drive_id = ud.ms365_drive_id AND di.scan_id = ds.id
LEFT JOIN ms365.drive_items AS pp ON pp.ms365_id = di.ms365_parent_id AND pp.scan_id = ds.id
JOIN ms365.permissions AS p ON p.drive_item_id = di.id
LEFT JOIN ms365.sharing_link_members AS sm ON sm.permission_id = p.id
LEFT JOIN ms365.users AS smu ON smu.ms365_id = sm.ms365_id AND smu.tenant_id = t.id
LEFT JOIN ms365.groups AS smg ON smg.ms365_id = sm.ms365_id AND smg.tenant_id = t.id
WHERE ds.scan_state = 1
  AND (sm.id IS NOT NULL OR p.link_scope='anonymous')
  AND p.is_inherited = 'false'

Author

rlagger

Post date

Friday, January 15, 2021 - 10:34

Last modified

Friday, April 26, 2024 - 12:48

Properties

Supported Version

4.1

4.0

Report Category

Microsoft 365