Summary with Quota and Owner

Summary with Quota and Owner

Details

Background

A customer needed to create a report for management that shows the file and folder counts, the users quota, and how much of their quota was being used. This customer wanted all empty directories to be ignored.

This custom query shows a summary of the folders in the path provided on line 7. The report summaries the file and folder counts, size used, the quota value set, and the quota remaining.

Note: This query is written for Active Directory and can be modified for use with eDirectory.

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.current_fs_scandata_ad AS sd
    WHERE sd.path_type = 2 AND
      sd.fullpath_hash IN (srs.path_hash('\\ad.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 = 1 AND
  s.scan_type = 1 AND
  s.is_valid = 'true' AND
  i.type = 1 

 

 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.current_fs_scandata_ad AS sd
    WHERE sd.path_type = 2 AND
      sd.fullpath_hash IN (srs.path_hash('\\serv1.acme.com\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 
Author
richlagger
Last modified
Tuesday, March 29, 2022 - 08:08
Properties
Supported Version
Report Category
Includes a Report Layout
Yes
Tags
Downloads
AttachmentSize
Summary with Quota and Owner.zip78.31 KB
Sample Report
AttachmentSize
Summary with Quota and Owner.pdf404.35 KB
Preview Images