Extension Report by Category

Details

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. The scope 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.

Note: The layout for this report has been updated to handle the chart scripting differently. As it was giving an error for some people.

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', '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', 'hpp', 'hrc', 'hxc', 'hxx', 'idb', 'idl', 'ilk', 'jar', 'java', 'jnl', 'map', 'mfcribbon-ms', 'obj', 'o', 'pbi', 'pbl', 'pch', 'pdb', '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', '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') 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', 'css', 'fla', 'html', 'htm', 'js', 'php', 'mhtm', 'mhtml', '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', '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', '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 ('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 '\\winfns.mfds.tech\HQShare\%' 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(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', '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', 'hpp', 'hrc', 'hxc', 'hxx', 'idb', 'idl', 'ilk', 'jar', 'java', 'jnl', 'map', 'mfcribbon-ms', 'obj', 'o', 'pbi', 'pbl', 'pch', 'pdb', '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', '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') 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', 'css', 'fla', 'html', 'htm', 'js', 'php', 'mhtm', 'mhtml', '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', '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', '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 ('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 '\\winfns.mfds.tech\HQShare\%' 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
 
 

Comments

This is a great report Richard. For our environment, I have changed the path statement to '\\%' which allows us to have a high level overview of the entire estate.

Thanks
Chris

mohinidutt12's picture

Welcome to the world of Independent Delhi escorts, all present to make your day a memorable one. Say bye-bye to stress and unnecessary anxieties, by loosing yourself in the arms of these hotties and pampering yourself till the hilt. With no strings attached, you can dive in the pool of these beautifies and enjoy a gala time with these babes. Escort in Delhi #delhi #callgirl #models #escort #delhimodel #girl #delhiescort #buties

Finding a trusted and reliable router customer support is indeed a laborious task that causes customers to take uncalled steps. So, how is it possible to strike a right customer service? The answer is Cisco customer support. You would be able to avail every possible solution of your errors if you visit Cisco customer support.

https://www.quickhelps.co/phone-number/asus/

https://www.quickhelps.co/phone-number/netgear/

https://www.quickhelps.co/phone-number/d-link/

https://www.quickhelps.co/phone-number/linksys/

https://www.quickhelps.co/phone-number/tp-link/

https://www.quickhelps.co/phone-number/cisco/

https://www.quickhelps.co/phone-number/belkin/

Paperless” is a staple in many headlines about business processes, and yet, the stories inevitably admit “paperless” is an ambitious goal. However, you don’t have to think too hard to see where digital processes and documents have replaced paper. That’s because digital processes meet customer expectations for accuracy, speed, and responsiveness. As such, you will see less and less paper in the coming years – call it the “less paper office.”

https://www.quickhelps.co/phone-number/canon/

https://www.quickhelps.co/phone-number/brother/

https://www.quickhelps.co/phone-number/hp/

https://www.quickhelps.co/phone-number/epson/

https://www.quickhelps.co/phone-number/xerox/

https://www.quickhelps.co/phone-number/lexmark/

AOL email has amazing features for all users who love it. +1 (888) 597-3962 This is a reliable and secure email address. If any user has an issue with AOL email then they can connect on AOL Customer Service Phone Number which is available for 24 hours every day.

https://www.quickhelps.co/phone-number/quickbooks/

https://www.quickhelps.co/phone-number/quicken/

https://www.quickhelps.co/phone-number/sage/

https://www.quickhelps.co/phone-number/aol/

https://www.quickhelps.co/phone-number/outlook/

Author
richlagger
Last modified
Friday, July 13, 2018 - 16:37
Properties
Minimum Version
Report Category
Includes a Report Layout
Yes
Databases
Rating
  • 1
  • 2
  • 3
  • 4
  • 5
Total votes: 3
Sample Report
Preview Images