Summary

Summarize file and folder counts per user, along with quota allocated and quota consumed, for the folders under a given path.

Details

This query summarizes the folders under the path provided on line 7, showing file and folder counts, size used, quota allocated, and quota remaining. Empty directories are excluded.

Code

WITH root_path(fullpath, path_type, scan_id, path_depth) AS (SELECT sd.fullpath,
      sd.path_type,
      sd.scan_id,
      sd.path_depth
    FROM srs.active_fs_scandata_ad AS sd
    WHERE sd.path_type = 2 AND
      sd.fullpath_hash IN (srs.path_hash('\\serv1.acme.com\Shares\Home')))
SELECT rp.fullpath AS root_path,
  sd.fullpath,
  sdd.file_count,
  sdd.directory_count,
  sd.size,
  srs.byte_string(sd.size) AS size_string,
  srs.ad_account_name(ad.domain, ad.name, sd.owner_id) AS owner,
  sdd.directory_quota,
  CASE WHEN sdd.directory_quota IS NULL THEN 'N/A'
    WHEN sdd.directory_quota = -1 THEN 'NONE'
    ELSE srs.byte_string(sdd.directory_quota) END AS quota_string,
  CASE WHEN sdd.directory_quota = -1 THEN NULL
    ELSE (sdd.directory_quota - sd.size) END AS remaining_quota,
  CASE WHEN sdd.directory_quota = -1 THEN '*UNLIMITED'
    ELSE srs.byte_string(sdd.directory_quota - sd.size)
  END AS remaining_quota_string,
  CASE WHEN sdd.directory_quota IS NULL THEN 0
    WHEN sdd.directory_quota = 0 THEN 0 WHEN sdd.directory_quota = -1 THEN 0
    ELSE (sd.size * 100) / sdd.directory_quota END AS quota_percent_used
FROM srs.scan_directory_data AS sdd
  INNER JOIN srs.scan_data AS sd ON sd.id = sdd.scan_data_id
  INNER JOIN srs.scans AS s ON s.id = sd.scan_id
  LEFT JOIN srs.ad_objects AS ad ON ad.sid = sd.owner_id
  INNER JOIN srs.scan_targets AS st ON st.id = s.scan_target_id
  INNER JOIN srs.identity_systems AS i ON i.id = st.identity_system_id
  INNER JOIN root_path AS rp ON rp.scan_id = sd.scan_id
WHERE sd.path_depth = rp.path_depth + 1 AND
  sdd.file_count > 0 AND
  sd.path_depth = 2 AND
  s.scan_type = 1 AND
  s.is_valid = 'true' AND
  i.type = 1