Duplicates in OneDrive for Business (users)

Details

Summary

New with version 4.0 is ability to scan your Microsoft 365 tenant for SharePoint Online document libraries, OneDrive for Business drives, and Teams document libraries.  This query helps you located duplicate files across user OneDrives.

The purpose for including some queries to provide examples that can be used to make queries that better suit the needs of the administrators.

Code
 WITH
    paths AS (SELECT di.name,
          di.size,
          di.created_by,
          di.modified_by,
          count(*) OVER (PARTITION BY di.file_hash) AS count,
          di.item_type,
          di.web_url,
          srs.bytes_to_hex_string(di.file_hash) as file_hash
        FROM ms365.drive_items AS di
          JOIN ms365.drives AS d ON d.ms365_id = di.ms365_drive_id
          JOIN ms365.drive_scans AS ds ON ds.drive_id = d.id
        WHERE (d.drive_type = 'business') AND
          (ds.scan_status = 2))
SELECT
    p.name AS filename,
    p.size,
    srs.byte_string(p.size) AS size_string,
    CASE WHEN uc.display_name IS NOT NULL THEN uc.display_name ELSE gc.display_name END AS created_by,
    CASE WHEN um.display_name IS NOT NULL THEN um.display_name ELSE gm.display_name END AS modified_by,
    p.web_url,
    p.file_hash,
    p.count
FROM
    paths AS p
    JOIN ms365.drive_item_types AS dit
        ON dit.item_type = p.item_type
    LEFT OUTER JOIN ms365.users AS uc
        ON uc.ms365_id = p.created_by
    LEFT OUTER JOIN ms365.users AS um
        ON um.ms365_id = p.modified_by
    LEFT OUTER JOIN ms365.groups AS gc
        ON gc.ms365_id = p.created_by
    LEFT OUTER JOIN ms365.groups AS gm
        ON gm.ms365_id = p.modified_by
WHERE
    (p.file_hash IS NOT NULL) AND
    (p.count >= 2) AND
    (dit.item_type_name = 'file')
--ORDER BY
   -- p.count DESC, p.file_hash, filename 
Author
rlagger
Last modified
Thursday, January 7, 2021 - 14:14
Properties
Supported Version
Report Category
Includes a Report Layout
Yes
Databases
Tags
Rating
  • 1
  • 2
  • 3
  • 4
  • 5
Total votes: 0
Downloads
Sample Report