Summary

Find sharing links across OneDrive for Business drives, surfacing cases where employees may be sharing company intellectual property with little regard for security.

Details

The report covers all sharing links found within discovered user OneDrive for Business drives. It does not include Direct Access permissions.

Code

SQL Server

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'

PostgreSQL

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'