Summary

This is a file-extension report that displays fullpath, filename, size, owner, scan_target, and file_extension from the srs.current_fs_scandata database view and is limited to current scan data.

Details

Although this query is written to report on media-based file extensions, the list on line 16 can be modified to include any set of extensions you need.

Code

SELECT
    sd.fullpath AS fullpath1,
    sd.name,
    srs.byte_string(sd.size),
    sd.size,
    sd.owner_display_name,
    sd.scan_target,
    sd.filename_extension,
    Sum(sd.size) OVER (PARTITION BY sd.filename_extension, sd.scan_target) AS extension_size_by_target,
    srs.byte_string(CAST(Sum(sd.size) OVER (PARTITION BY sd.filename_extension, sd.scan_target) AS BIGINT)) AS extension_size_by_target_string,
    Sum(sd.size) OVER (PARTITION BY sd.filename_extension) AS extension_total_size,
    srs.byte_string(CAST(Sum(sd.size) OVER (PARTITION BY sd.filename_extension) AS BIGINT)) AS extension_total_size_string
FROM
    srs.active_fs_scandata_ad AS sd
WHERE
    sd.filename_extension IN ('3gp','3g2','asf','avi','drc','flv','mkv','mng','mp4','m2v','m4p','m4v','mov','mpg','mp2','mpeg','mpe','mpv','mv4','mxf','ogg','ogv','qt','rm', 'rmvb', 'roq', 'svi', 'webm', 'wmv', 'yuv','act','aiff','aac','amr','au','awb','dct','dss','dvf','flac','gsm','iklax','ivs','m4a','m4p','mmf','mp3','mpc','msv','opus','raw','wav','wma','wv')