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

Post date

Friday, March 27, 2015 - 15:37

Last modified

Monday, July 23, 2018 - 15:01

Properties

Supported Version

2.6.0

Report Category

File System

Includes a Report Layout