Summary

This report combines file extensions into categories, with scope limited to current scans. It comes in Detailed and Summary styles, each with its own report layout.

Details

The report uses the srs.current_fs_scandata database view to limit scope to current scans. It also uses the Custom Query File System Report feature, which moves report-path management into the Report Designer UI instead of requiring direct query edits.

Code

Summary — SQL Server

WITH
    x(filename_extension, size, category) AS (SELECT sd.filename_extension,
          sd.size,
          CASE WHEN sd.filename_extension IN ('lan', 'ncp', 'nlm', 'nlk', 'vlm') THEN 'Novell NetWare Files'
               WHEN sd.filename_extension IN ('a', 'asm', 'ascx', 'atp', 'awk', 'bin', 'bpi', 'bsc', 'c', 'c--', 'c++', 'cache', 'class', 'cpp', 'cs', 'csm', 'cur', 'cxx',
                                              'dbg', 'dbp', 'dcp', 'dcu', 'def', 'des', 'dfm', 'disco', 'dlg', 'don', 'dpc', 'dump', 'dxp', 'eng', 'epx', 'exp', 'flt', 'fmt',
                                              'h', 'hdl', 'h++', 'h--', 'hpp', 'hrc', 'hxc', 'hxx', 'idb', 'idl', 'ilk', 'jar', 'java', 'jnl', 'map', 'mfcribbon-ms', 'obj',
                                              'o', 'pbi', 'pbl', 'pch', 'pdb', 'py', 'rc', 'rc2', 'resources', 'resx', 'sbr', 'sln', 'suo', 'svn-base', 'tdt', 'vap', 'vb',
                                              'vbs', 'vdg', 'vbp', 'vcxproj', 'vup', 'wsf', 'xsd') THEN 'Software Development Files' 
               WHEN sd.filename_extension IN ('000', 'cat', 'cpl', 'dll_debug', 'dl_', 'dll', 'drv', 'font', 'gadget', 'inf', 'job', 'lib', 'mft', 'mnu', 'pf', 'regtrans-ms',
	                             		     'rtp', 'sys', 'scr', 'spl', 'str', 'tsk', 'w32', 'wss') THEN 'System Files' 
               WHEN sd.filename_extension IN ('ppt', 'pptx', 'ppsx', 'pptm') THEN 'Presentation Files' 
               WHEN sd.filename_extension IN ('bundle', 'kdelnk', 'lnk', 'lnx', 'man', 'mk', 'mod', 'rpm', 'sh', 'run') THEN 'Linux Files' 
               WHEN sd.filename_extension IN ('crdownload','partial') THEN 'Incomplete Internet Downloads' 
               WHEN sd.filename_extension IN ('msi', 'msu') THEN 'Windows Application Installables' 
               WHEN sd.filename_extension IN ('xls', 'xlsx', 'xlxs', 'xlw', 'sdw', 'vor', 'pxl', 'csv', 'sxc', 'stc', 'odt', 'ott', 'swx') THEN 'Spreadsheet Files' 
               WHEN sd.filename_extension IN ('pst', 'ost', 'efx') THEN 'Outlook Files' 
               WHEN sd.filename_extension IN ('avhd', 'lock', 'nvram', 'vmdk', 'vmem', 'vmsd', 'vmsn', 'vmss', 'vmtm', 'vmx', 'vmxf', 'vsv', 'vhd', 'vhdx') THEN 'Virtualization Files' 
               WHEN sd.filename_extension IN ('iso', 'img') THEN 'File System Image Files' 
               WHEN sd.filename_extension IN ('temp', 'tmp', '~tmp') THEN 'Temporary Files' 
               WHEN sd.filename_extension IN ('text', 'txt') THEN 'Text Files' 
               WHEN sd.filename_extension IN ('cfg', 'ini', 'config', 'conf') THEN 'Configuration Files' 
               WHEN sd.filename_extension IN ('asp', 'aspx', 'cgi', 'css', 'fla', 'html', 'htm', 'js', 'php', 'mhtm', 'mhtml', 'mht', 'swf', 'xml', 'xslt', 'xsp') THEN 'Internet Files' 
               WHEN sd.filename_extension IN ('chm', 'hid', 'hlp') THEN 'Help Files' 
               WHEN sd.filename_extension IN ('4db', '4dd', '4dindy', '4dindx', '4dr', 'accdb', 'accde', 'acct', 'adt', 'apr', 'box', 'chml', 'daf', 'dat', 'db', 'dbf', 'egt', 'ess',
                                              'eap', 'fdb', 'fdb', 'fp', 'fp3', 'fp5', 'fp7', 'frm', 'gdb', 'gtable', 'idx', 'kexi', 'kexic', 'kexis', 'ldb', 'mda', 'mdb', 'adp', 'mde',
                                              'mdf', 'myd', 'myi', 'nsf', 'ntf', 'nv2', 'odb', 'ora', 'pdb', 'pdi', 'pdx', 'prc', 'sql', 'rec', 'rel', 'rin', 'sdb', 'sdf', 'udl', 'wadata',
                                              'waindx', 'wamodel', 'wajournal', 'wdb', 'wmdb') THEN 'Database Files' 
               WHEN sd.filename_extension IN ('doc', 'docx', 'epub', 'fb2', 'odf', 'pdf', 'pub', 'rtf', 'pages', 'odp', 'sxw', 'tex', 'wp', 'wp7', 'wpd') THEN 'Document Files'
               WHEN sd.filename_extension IN ('exe', 'com', 'bat', 'cmd', 'command') THEN 'Executables' 
               WHEN sd.filename_extension IN ('3gp', '3g2', 'asf', 'avi', 'drc', 'dvi', 'dvix', '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') THEN 'Video Files' 
               WHEN sd.filename_extension IN ('act', 'aiff', 'aac', 'amr', 'au', 'awb', 'dct', 'dss', 'dvf', 'flac', 'gsm', 'iklax', 'ivs', 'm4a', 'm4p', 'mmf', 'mp3', 'mpc', 'msv', 'opus',
                                              'raw', 'wav', 'wma', 'wv') THEN 'Music' 
               WHEN sd.filename_extension IN ('back', 'bak', 'backup', 'sav', 'save', 'old', 'bz2', 'zip', 'tar', 'tgz', 'bgz', '7z', '7zip', 'rar', 'ar', 'cpio', 'shar', 'lzip', 'gzip',
                                              'ace', 'arc', 'arj', 'cab', 'dmg', 'compress', 'gz') THEN 'Archive' 
               WHEN sd.filename_extension IN ('ai', 'bmp', 'bpg', 'cgm', 'exif', 'gif', 'hdr', 'ico', 'jfif', 'jpg', 'jpeg', 'png', 'ppm', 'pgm', 'pbm', 'pnm', 'psd', 'rif', 'svg', 'tif',
                                              'tiff', 'webp') THEN 'Graphic Files' 
               WHEN sd.filename_extension IN ('etl', 'evt', 'evtx', 'lgs', 'log', 'log1', 'tlog') THEN 'Log Files' 
               WHEN sd.filename_extension IS NULL THEN 'NONE' 
               ELSE 'Other Files' 
               END AS category,
               cp.target_path
        FROM srs.current_fs_scandata AS sd
        JOIN #tmp_cq_fs_paths as cp on cp.scan_id = sd.scan_id
        	AND cp.ns_left <= sd.ns_left
        	AND cp.ns_right >= sd.ns_right
        WHERE
          (sd.path_type = 1))
SELECT
	x.target_path,
    x.category,
    Sum(x.size) AS cat_size,
    count(*) AS file_count,
    srs.byte_string(CAST(Sum(x.size) AS BIGINT)) AS cat_size_string
FROM
    x
GROUP BY
    x.target_path, x.category

Summary — PostgreSQL

WITH
    x(filename_extension, size, category) AS (SELECT sd.filename_extension,
          sd.size,
          CASE WHEN sd.filename_extension IN ('lan', 'ncp', 'nlm', 'nlk', 'vlm') THEN 'Novell NetWare Files'
               WHEN sd.filename_extension IN ('a', 'asm', 'ascx', 'atp', 'awk', 'bin', 'bpi', 'bsc', 'c', 'c--', 'c++', 'cache', 'class', 'cpp', 'cs', 'csm', 'cur', 'cxx',
                                              'dbg', 'dbp', 'dcp', 'dcu', 'def', 'des', 'dfm', 'disco', 'dlg', 'don', 'dpc', 'dump', 'dxp', 'eng', 'epx', 'exp', 'flt', 'fmt',
                                              'h', 'hdl', 'h++', 'h--', 'hpp', 'hrc', 'hxc', 'hxx', 'idb', 'idl', 'ilk', 'jar', 'java', 'jnl', 'map', 'mfcribbon-ms', 'obj',
                                              'o', 'pbi', 'pbl', 'pch', 'pdb', 'py', 'rc', 'rc2', 'resources', 'resx', 'sbr', 'sln', 'suo', 'svn-base', 'tdt', 'vap', 'vb',
                                              'vbs', 'vdg', 'vbp', 'vcxproj', 'vup', 'wsf', 'xsd') THEN 'Software Development Files' 
               WHEN sd.filename_extension IN ('000', 'cat', 'cpl', 'dll_debug', 'dl_', 'dll', 'drv', 'font', 'gadget', 'inf', 'job', 'lib', 'mft', 'mnu', 'pf', 'regtrans-ms',
	                             		     'rtp', 'sys', 'scr', 'spl', 'str', 'tsk', 'w32', 'wss') THEN 'System Files' 
               WHEN sd.filename_extension IN ('ppt', 'pptx', 'ppsx', 'pptm') THEN 'Presentation Files' 
               WHEN sd.filename_extension IN ('bundle', 'kdelnk', 'lnk', 'lnx', 'man', 'mk', 'mod', 'rpm', 'sh', 'run') THEN 'Linux Files' 
               WHEN sd.filename_extension IN ('crdownload','partial') THEN 'Incomplete Internet Downloads' 
               WHEN sd.filename_extension IN ('msi', 'msu') THEN 'Windows Application Installables' 
               WHEN sd.filename_extension IN ('xls', 'xlsx', 'xlxs', 'xlw', 'sdw', 'vor', 'pxl', 'csv', 'sxc', 'stc', 'odt', 'ott', 'swx') THEN 'Spreadsheet Files' 
               WHEN sd.filename_extension IN ('pst', 'ost', 'efx') THEN 'Outlook Files' 
               WHEN sd.filename_extension IN ('avhd', 'lock', 'nvram', 'vmdk', 'vmem', 'vmsd', 'vmsn', 'vmss', 'vmtm', 'vmx', 'vmxf', 'vsv', 'vhd', 'vhdx') THEN 'Virtualization Files' 
               WHEN sd.filename_extension IN ('iso', 'img') THEN 'File System Image Files' 
               WHEN sd.filename_extension IN ('temp', 'tmp', '~tmp') THEN 'Temporary Files' 
               WHEN sd.filename_extension IN ('text', 'txt') THEN 'Text Files' 
               WHEN sd.filename_extension IN ('cfg', 'ini', 'config', 'conf') THEN 'Configuration Files' 
               WHEN sd.filename_extension IN ('asp', 'aspx', 'cgi', 'css', 'fla', 'html', 'htm', 'js', 'php', 'mhtm', 'mhtml', 'mht', 'swf', 'xml', 'xslt', 'xsp') THEN 'Internet Files' 
               WHEN sd.filename_extension IN ('chm', 'hid', 'hlp') THEN 'Help Files' 
               WHEN sd.filename_extension IN ('4db', '4dd', '4dindy', '4dindx', '4dr', 'accdb', 'accde', 'acct', 'adt', 'apr', 'box', 'chml', 'daf', 'dat', 'db', 'dbf', 'egt', 'ess',
                                              'eap', 'fdb', 'fdb', 'fp', 'fp3', 'fp5', 'fp7', 'frm', 'gdb', 'gtable', 'idx', 'kexi', 'kexic', 'kexis', 'ldb', 'mda', 'mdb', 'adp', 'mde',
                                              'mdf', 'myd', 'myi', 'nsf', 'ntf', 'nv2', 'odb', 'ora', 'pdb', 'pdi', 'pdx', 'prc', 'sql', 'rec', 'rel', 'rin', 'sdb', 'sdf', 'udl', 'wadata',
                                              'waindx', 'wamodel', 'wajournal', 'wdb', 'wmdb') THEN 'Database Files' 
               WHEN sd.filename_extension IN ('doc', 'docx', 'epub', 'fb2', 'odf', 'pdf', 'pub', 'rtf', 'pages', 'odp', 'sxw', 'tex', 'wp', 'wp7', 'wpd') THEN 'Document Files'
               WHEN sd.filename_extension IN ('exe', 'com', 'bat', 'cmd', 'command') THEN 'Executables' 
               WHEN sd.filename_extension IN ('3gp', '3g2', 'asf', 'avi', 'drc', 'dvi', 'dvix', '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') THEN 'Video Files' 
               WHEN sd.filename_extension IN ('act', 'aiff', 'aac', 'amr', 'au', 'awb', 'dct', 'dss', 'dvf', 'flac', 'gsm', 'iklax', 'ivs', 'm4a', 'm4p', 'mmf', 'mp3', 'mpc', 'msv', 'opus',
                                              'raw', 'wav', 'wma', 'wv') THEN 'Music' 
               WHEN sd.filename_extension IN ('back', 'bak', 'backup', 'sav', 'save', 'old', 'bz2', 'zip', 'tar', 'tgz', 'bgz', '7z', '7zip', 'rar', 'ar', 'cpio', 'shar', 'lzip', 'gzip',
                                              'ace', 'arc', 'arj', 'cab', 'dmg', 'compress', 'gz') THEN 'Archive' 
               WHEN sd.filename_extension IN ('ai', 'bmp', 'bpg', 'cgm', 'exif', 'gif', 'hdr', 'ico', 'jfif', 'jpg', 'jpeg', 'png', 'ppm', 'pgm', 'pbm', 'pnm', 'psd', 'rif', 'svg', 'tif',
                                              'tiff', 'webp') THEN 'Graphic Files' 
               WHEN sd.filename_extension IN ('etl', 'evt', 'evtx', 'lgs', 'log', 'log1', 'tlog') THEN 'Log Files' 
               WHEN sd.filename_extension IS NULL THEN 'NONE' 
               ELSE 'Other Files' 
               END AS category,
               cp.target_path
        FROM srs.current_fs_scandata AS sd
        JOIN tmp_cq_fs_paths as cp on cp.scan_id = sd.scan_id
        	AND cp.ns_left <= sd.ns_left
        	AND cp.ns_right >= sd.ns_right
        WHERE
          (sd.path_type = 1))
SELECT
	x.target_path,
    x.category,
    Sum(x.size) AS cat_size,
    count(*) AS file_count,
    srs.byte_string(CAST(Sum(x.size) AS BIGINT)) AS cat_size_string
FROM
    x
GROUP BY
    x.target_path, x.category

Detailed — SQL Server

WITH
    x AS (SELECT sd.filename_extension,
          sd.size,
          CASE WHEN sd.filename_extension IN ('lan', 'ncp', 'nlm', 'nlk', 'vlm') THEN 'Novell NetWare Files'
               WHEN sd.filename_extension IN ('a', 'asm', 'ascx', 'atp', 'awk', 'bin', 'bpi', 'bsc', 'c', 'c--', 'c++', 'cache', 'class', 'cpp', 'cs', 'csm', 'cur', 'cxx', 
                                              'dbg', 'dbp', 'dcp', 'dcu', 'def', 'des', 'dfm', 'disco', 'dlg', 'don', 'dpc', 'dump', 'dxp', 'eng', 'epx', 'exp', 'flt', 'fmt',
                                              'h', 'hdl', 'h++', 'h--', 'hpp', 'hrc', 'hxc', 'hxx', 'idb', 'idl', 'ilk', 'jar', 'java', 'jnl', 'map', 'mfcribbon-ms', 'obj',
                                              'o', 'pbi', 'pbl', 'pch', 'pdb', 'py', 'rc', 'rc2', 'resources', 'resx', 'sbr', 'sln', 'suo', 'svn-base', 'tdt', 'vap', 'vb',
                                              'vbs', 'vdg', 'vbp', 'vcxproj', 'vup', 'wsf', 'xsd') THEN 'Software Development Files' 
               WHEN sd.filename_extension IN ('000', 'cat', 'cpl', 'dll_debug', 'dl_', 'dll', 'drv', 'font', 'gadget', 'inf', 'job', 'lib', 'mft', 'mnu', 'pf', 'regtrans-ms',
                                              'rtp', 'sys', 'scr', 'spl', 'str', 'tsk', 'w32', 'wss') THEN 'System Files' 
               WHEN sd.filename_extension IN ('ppt', 'pptx', 'ppsx', 'pptm') THEN 'Presentation Files' 
               WHEN sd.filename_extension IN ('bundle', 'kdelnk', 'lnk', 'lnx', 'man', 'mk', 'mod', 'rpm', 'sh', 'run') THEN 'Linux Files' 
               WHEN sd.filename_extension IN ('crdownload','partial') THEN 'Incomplete Internet Downloads' 
               WHEN sd.filename_extension IN ('msi', 'msu') THEN 'Windows Application Installables' 
               WHEN sd.filename_extension IN ('xls', 'xlsx', 'xlxs', 'xlw', 'sdw', 'vor', 'pxl', 'csv', 'sxc', 'stc', 'odt', 'ott', 'swx') THEN 'Spreadsheet Files' 
               WHEN sd.filename_extension IN ('pst', 'ost', 'efx') THEN 'Outlook Files' 
               WHEN sd.filename_extension IN ('avhd', 'lock', 'nvram', 'vmdk', 'vmem', 'vmsd', 'vmsn', 'vmss', 'vmtm', 'vmx', 'vmxf', 'vsv', 'vhd', 'vhdx') THEN 'Virtualization Files' 
               WHEN sd.filename_extension IN ('iso', 'img') THEN 'File System Image Files' 
               WHEN sd.filename_extension IN ('temp', 'tmp', '~tmp') THEN 'Temporary Files' 
               WHEN sd.filename_extension IN ('text', 'txt') THEN 'Text Files' 
               WHEN sd.filename_extension IN ('cfg', 'ini', 'config', 'conf') THEN 'Configuration Files' 
               WHEN sd.filename_extension IN ('asp', 'aspx', 'cgi', 'css', 'fla', 'html', 'htm', 'js', 'php', 'mhtm', 'mhtml', 'mht', 'swf', 'xml', 'xslt', 'xsp') THEN 'Internet Files' 
               WHEN sd.filename_extension IN ('chm', 'hid', 'hlp') THEN 'Help Files' 
               WHEN sd.filename_extension IN ('4db', '4dd', '4dindy', '4dindx', '4dr', 'accdb', 'accde', 'acct', 'adt', 'apr', 'box', 'chml', 'daf', 'dat', 'db', 'dbf', 'egt',
                                              'ess', 'eap', 'fdb', 'fdb', 'fp', 'fp3', 'fp5', 'fp7', 'frm', 'gdb', 'gtable', 'idx', 'kexi', 'kexic', 'kexis', 'ldb', 'mda', 'mdb',
                                              'adp', 'mde', 'mdf', 'myd', 'myi', 'nsf', 'ntf', 'nv2', 'odb', 'ora', 'pdb', 'pdi', 'pdx', 'prc', 'sql', 'rec', 'rel', 'rin', 'sdb',
                                              'sdf', 'udl', 'wadata', 'waindx', 'wamodel', 'wajournal', 'wdb', 'wmdb') THEN 'Database Files' 
               WHEN sd.filename_extension IN ('doc', 'docx', 'epub', 'fb2', 'odf', 'pdf', 'pub', 'rtf', 'pages', 'odp', 'sxw', 'tex', 'wp', 'wp7', 'wpd') THEN 'Document Files'
               WHEN sd.filename_extension IN ('exe', 'com', 'bat', 'cmd', 'command') THEN 'Executables' 
               WHEN sd.filename_extension IN ('3gp', '3g2', 'asf', 'avi', 'drc', 'dvi', 'dvix', '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') THEN 'Video Files' 
               WHEN sd.filename_extension IN ('act', 'aiff', 'aac', 'amr', 'au', 'awb', 'dct', 'dss', 'dvf', 'flac', 'gsm', 'iklax', 'ivs', 'm4a', 'm4p', 'mmf', 'mp3', 'mpc', 'msv',
                                              'opus', 'raw', 'wav', 'wma', 'wv') THEN 'Music' 
               WHEN sd.filename_extension IN ('back', 'bak', 'backup', 'sav', 'save', 'old', 'bz2', 'zip', 'tar', 'tgz', 'bgz', '7z', '7zip', 'rar', 'ar', 'cpio', 'shar', 'lzip',
                                              'gzip', 'ace', 'arc', 'arj', 'cab', 'dmg', 'compress', 'gz') THEN 'Archive' 
               WHEN sd.filename_extension IN ('ai', 'bmp', 'bpg', 'cgm', 'exif', 'gif', 'hdr', 'ico', 'jfif', 'jpg', 'jpeg', 'png', 'ppm', 'pgm', 'pbm', 'pnm', 'psd', 'rif', 'svg',
                                              'tif', 'tiff', 'webp') THEN 'Graphic Files' 
               WHEN sd.filename_extension IN ('etl', 'evt', 'evtx', 'lgs', 'log', 'log1', 'tlog') THEN 'Log Files' 
               WHEN sd.filename_extension IS NULL THEN 'NONE' 
               ELSE 'Other Files' 
               END AS category,
               cp.target_path
        FROM srs.current_fs_scandata AS sd
        JOIN #tmp_cq_fs_paths as cp on cp.scan_id = sd.scan_id
          AND cp.ns_left <= sd.ns_left
          AND cp.ns_right >= sd.ns_right
        WHERE sd.path_type = 1),
    y AS (
        SELECT x.target_path, 
        	x.category,
        	x.filename_extension,
        	Sum(x.size) AS extension_size,
        	Count(x.filename_extension) AS extension_count
        FROM x
        GROUP BY x.target_path, x.category, x.filename_extension
    )
SELECT
    y.*,
    Sum(y.extension_size) OVER (PARTITION BY y.category) AS cat_size,
    srs.byte_string(CAST(Sum(y.extension_size) OVER (PARTITION BY y.category) AS BIGINT)) AS cat_size_string,
    srs.byte_string(CAST(Sum(y.extension_size) OVER (PARTITION BY y.filename_extension) AS BIGINT)) AS ext_size_string,
    Count(y.extension_size) OVER (PARTITION BY y.category) AS cat_ext_count,
    Sum(y.extension_count) OVER (PARTITION BY y.category) AS cat_file_count
FROM
    y

Detailed — PostgreSQL

WITH
    x AS (SELECT sd.filename_extension,
          sd.size,
          CASE WHEN sd.filename_extension IN ('lan', 'ncp', 'nlm', 'nlk', 'vlm') THEN 'Novell NetWare Files'
               WHEN sd.filename_extension IN ('a', 'asm', 'ascx', 'atp', 'awk', 'bin', 'bpi', 'bsc', 'c', 'c--', 'c++', 'cache', 'class', 'cpp', 'cs', 'csm', 'cur', 'cxx', 
                                              'dbg', 'dbp', 'dcp', 'dcu', 'def', 'des', 'dfm', 'disco', 'dlg', 'don', 'dpc', 'dump', 'dxp', 'eng', 'epx', 'exp', 'flt', 'fmt',
                                              'h', 'hdl', 'h++', 'h--', 'hpp', 'hrc', 'hxc', 'hxx', 'idb', 'idl', 'ilk', 'jar', 'java', 'jnl', 'map', 'mfcribbon-ms', 'obj',
                                              'o', 'pbi', 'pbl', 'pch', 'pdb', 'py', 'rc', 'rc2', 'resources', 'resx', 'sbr', 'sln', 'suo', 'svn-base', 'tdt', 'vap', 'vb',
                                              'vbs', 'vdg', 'vbp', 'vcxproj', 'vup', 'wsf', 'xsd') THEN 'Software Development Files' 
               WHEN sd.filename_extension IN ('000', 'cat', 'cpl', 'dll_debug', 'dl_', 'dll', 'drv', 'font', 'gadget', 'inf', 'job', 'lib', 'mft', 'mnu', 'pf', 'regtrans-ms',
                                              'rtp', 'sys', 'scr', 'spl', 'str', 'tsk', 'w32', 'wss') THEN 'System Files' 
               WHEN sd.filename_extension IN ('ppt', 'pptx', 'ppsx', 'pptm') THEN 'Presentation Files' 
               WHEN sd.filename_extension IN ('bundle', 'kdelnk', 'lnk', 'lnx', 'man', 'mk', 'mod', 'rpm', 'sh', 'run') THEN 'Linux Files' 
               WHEN sd.filename_extension IN ('crdownload','partial') THEN 'Incomplete Internet Downloads' 
               WHEN sd.filename_extension IN ('msi', 'msu') THEN 'Windows Application Installables' 
               WHEN sd.filename_extension IN ('xls', 'xlsx', 'xlxs', 'xlw', 'sdw', 'vor', 'pxl', 'csv', 'sxc', 'stc', 'odt', 'ott', 'swx') THEN 'Spreadsheet Files' 
               WHEN sd.filename_extension IN ('pst', 'ost', 'efx') THEN 'Outlook Files' 
               WHEN sd.filename_extension IN ('avhd', 'lock', 'nvram', 'vmdk', 'vmem', 'vmsd', 'vmsn', 'vmss', 'vmtm', 'vmx', 'vmxf', 'vsv', 'vhd', 'vhdx') THEN 'Virtualization Files' 
               WHEN sd.filename_extension IN ('iso', 'img') THEN 'File System Image Files' 
               WHEN sd.filename_extension IN ('temp', 'tmp', '~tmp') THEN 'Temporary Files' 
               WHEN sd.filename_extension IN ('text', 'txt') THEN 'Text Files' 
               WHEN sd.filename_extension IN ('cfg', 'ini', 'config', 'conf') THEN 'Configuration Files' 
               WHEN sd.filename_extension IN ('asp', 'aspx', 'cgi', 'css', 'fla', 'html', 'htm', 'js', 'php', 'mhtm', 'mhtml', 'mht', 'swf', 'xml', 'xslt', 'xsp') THEN 'Internet Files' 
               WHEN sd.filename_extension IN ('chm', 'hid', 'hlp') THEN 'Help Files' 
               WHEN sd.filename_extension IN ('4db', '4dd', '4dindy', '4dindx', '4dr', 'accdb', 'accde', 'acct', 'adt', 'apr', 'box', 'chml', 'daf', 'dat', 'db', 'dbf', 'egt',
                                              'ess', 'eap', 'fdb', 'fdb', 'fp', 'fp3', 'fp5', 'fp7', 'frm', 'gdb', 'gtable', 'idx', 'kexi', 'kexic', 'kexis', 'ldb', 'mda', 'mdb',
                                              'adp', 'mde', 'mdf', 'myd', 'myi', 'nsf', 'ntf', 'nv2', 'odb', 'ora', 'pdb', 'pdi', 'pdx', 'prc', 'sql', 'rec', 'rel', 'rin', 'sdb',
                                              'sdf', 'udl', 'wadata', 'waindx', 'wamodel', 'wajournal', 'wdb', 'wmdb') THEN 'Database Files' 
               WHEN sd.filename_extension IN ('doc', 'docx', 'epub', 'fb2', 'odf', 'pdf', 'pub', 'rtf', 'pages', 'odp', 'sxw', 'tex', 'wp', 'wp7', 'wpd') THEN 'Document Files'
               WHEN sd.filename_extension IN ('exe', 'com', 'bat', 'cmd', 'command') THEN 'Executables' 
               WHEN sd.filename_extension IN ('3gp', '3g2', 'asf', 'avi', 'drc', 'dvi', 'dvix', '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') THEN 'Video Files' 
               WHEN sd.filename_extension IN ('act', 'aiff', 'aac', 'amr', 'au', 'awb', 'dct', 'dss', 'dvf', 'flac', 'gsm', 'iklax', 'ivs', 'm4a', 'm4p', 'mmf', 'mp3', 'mpc', 'msv',
                                              'opus', 'raw', 'wav', 'wma', 'wv') THEN 'Music' 
               WHEN sd.filename_extension IN ('back', 'bak', 'backup', 'sav', 'save', 'old', 'bz2', 'zip', 'tar', 'tgz', 'bgz', '7z', '7zip', 'rar', 'ar', 'cpio', 'shar', 'lzip',
                                              'gzip', 'ace', 'arc', 'arj', 'cab', 'dmg', 'compress', 'gz') THEN 'Archive' 
               WHEN sd.filename_extension IN ('ai', 'bmp', 'bpg', 'cgm', 'exif', 'gif', 'hdr', 'ico', 'jfif', 'jpg', 'jpeg', 'png', 'ppm', 'pgm', 'pbm', 'pnm', 'psd', 'rif', 'svg',
                                              'tif', 'tiff', 'webp') THEN 'Graphic Files' 
               WHEN sd.filename_extension IN ('etl', 'evt', 'evtx', 'lgs', 'log', 'log1', 'tlog') THEN 'Log Files' 
               WHEN sd.filename_extension IS NULL THEN 'NONE' 
               ELSE 'Other Files' 
               END AS category,
               cp.target_path
        FROM srs.current_fs_scandata AS sd
        JOIN tmp_cq_fs_paths as cp on cp.scan_id = sd.scan_id
          AND cp.ns_left <= sd.ns_left
          AND cp.ns_right >= sd.ns_right
        WHERE sd.path_type = 1),
    y AS (
        SELECT x.target_path, 
        	x.category,
        	x.filename_extension,
        	Sum(x.size) AS extension_size,
        	Count(x.filename_extension) AS extension_count
        FROM x
        GROUP BY x.target_path, x.category, x.filename_extension
    )
SELECT
    y.*,
    Sum(y.extension_size) OVER (PARTITION BY y.category) AS cat_size,
    srs.byte_string(CAST(Sum(y.extension_size) OVER (PARTITION BY y.category) AS BIGINT)) AS cat_size_string,
    srs.byte_string(CAST(Sum(y.extension_size) OVER (PARTITION BY y.filename_extension) AS BIGINT)) AS ext_size_string,
    Count(y.extension_size) OVER (PARTITION BY y.category) AS cat_ext_count,
    Sum(y.extension_count) OVER (PARTITION BY y.category) AS cat_file_count
FROM
    y