File Extensions by Category
File Extensions by Category
Details
Details
Summary
This report combines file extensions in to categories. The report uses the srs.current_fs_scandata database view which limits the scope to current scans only. This recipes comes in two styles, either Detailed or Summary. Each version of the report comes with a report layout.
Version 3.6 - 4.0
The report is further limited by the sd.fullpath LIKE portion of the sql where clause. If you remove it, the report will run across all current scan_data.
Line 32, contains the desired path to be reported against.
Version 4.1
The 4.1 version of the report utilizes the Custom Query File System Report technology, which moves the adding and removing of report paths to the Report Designer user interface, instead of modifying the query directly.
Code
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
FROM srs.current_fs_scandata AS sd
WHERE (sd.fullpath LIKE '\\qt-m1.qt.cctec.org\data\%' ESCAPE '#') AND
(sd.path_type = 1)),
y(category, filename_extension, extension_size, extension_count) AS (SELECT x.category,
x.filename_extension,
Sum(x.size) AS extension_size,
Count(x.filename_extension) AS extension_count
FROM x
GROUP BY 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
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
FROM srs.current_fs_scandata AS sd
WHERE (sd.fullpath LIKE '\\qt-m1.qt.cctec.org\data\%' ESCAPE '#') AND
(sd.path_type = 1))
SELECT
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.category
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
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
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
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
Post date
Thursday, January 14, 2021 - 16:36
Last modified
Friday, April 26, 2024 - 12:48
Downloads
Sample Report
| Attachment | Size |
|---|---|
 Extension Report by Category _Detailed.pdf | 276.54 KB |
| Extension Report by Category _Summary.pdf | 201.54 KB |
Preview Images
