Locate All GW Archive Folders Within The Desired Path

Details

Summary

I need way of locating, within a particular path, all of the GW Archive folders matching the 'ofxxxarc' pattern where the xxx is equal to the FID of the GW user. I also need the fullpath where the archive is located, and the space consumed by the archive.

You will need to change the path in line 8 to match your environment. Note: Case-Sensitive.

Code
 WITH x(fullpath, name, size, size_string, name_count) AS (SELECT sd.fullpath, 
   sd.name, 
   Sum(sd.size) OVER (PARTITION BY sd.size) AS size, 
   srs.byte_string(CAST(Sum(sd.size) OVER (PARTITION BY sd.size) AS BIGINT)) AS size_string,
   Count(sd.name) OVER (PARTITION BY sd.name) AS name_count
FROM srs.scan_data AS sd
   INNER JOIN srs.scans AS s ON sd.scan_id = s.id 
WHERE (sd.fullpath LIKE '\\CCC4\SHARE\%' ESCAPE '#') AND 
   (sd.name LIKE 'of___arc') AND 
   (sd.path_type = 2) AND 
   (s.progress_status = 4) AND 
   (s.scan_type = 1)) 
SELECT x.* 
FROM x 
WHERE x.name_count >= 1 
 
Author
richlagger
Last modified
Monday, July 23, 2018 - 15:01
Properties
Minimum Version
Report Category
Includes a Report Layout
No
Databases
Tags
Rating
  • 1
  • 2
  • 3
  • 4
  • 5
Total votes: 0
Downloads
AttachmentSize
LocateGWArchiveFolders.zip481 bytes