none
File Server - File size\type search and save results to file RRS feed

  • Question

  • I already have a vb script to do what I want on our file server, but it is very inefficient and slow.  I was thinking that a powershell script may be more suitable now but I don't know anything about scripting in PS.  So far the vb code that I have works, and I am not the one who wrote it but I can manipulate it to do what I want it to.  The only problem is, when I scan the shared network locations it stops on some files that are password protected and I don't know how to get around it.  If someone else knows of a PS script to go through the file system and get all files of a certain type or size (right now, preferably size) and save the file name, size, path, owner and dates created\modified please point me to it and I can work with that.  If not, could I get some help with the current script that I have to somehow get around the password protected files?  They belong in a users' HOME directory so I can't do anything with them.  Here is my code:   

    'Script for scanning file folders for certain types of files and those of a certain size of larger'
    'Note: Script must be placed locally on whichever machine the script is running on'
    
    '***********VARIABLES FOR USE IN SCRIPT***********'
    'objStartFolder - notes the location of the folder you wish to begin your scan in'
    objStartFolder = "\\FileServer\DriveLetter\SharedFolder"
    'excelFileName - notes the location where you want the output spreadsheet to be saved to'
    excelFileName = "c:\temp\Results_Shared.xls"
    '**********END OF VARIABLES**********'
    
    Set objFSO = CreateObject("Scripting.FileSystemObject")
    
    fileName = objFSO.GetFileName(path)
    'beginning row and column for actual data (not headers)'
    excelRow = 3 
    excelCol = 1
    
    
    'Create Excel Spreadsheet'
      Set objExcel = CreateObject("Excel.Application")
      Set objWorkbook = objExcel.Workbooks.Add()
      CreateExcelHeaders()
    'Loop to go through original folder'            
    Set objFolder = objFSO.GetFolder(objStartFolder)
    Set colFiles = objFolder.Files
    For Each objFile in colFiles
          Call Output(excelRow) 'If a subfolder is met, output procedure recursively called'
    Next
    
    ShowSubfolders objFSO.GetFolder(objStartFolder)
    'Autofit the spreadsheet columns'
    ExcelAutofit()
    'Save Spreadsheet'
    objWorkbook.SaveAs(excelFileName)
    objExcel.Quit
    '*****END OF MAIN SCRIPT*****'
    
    '*****BEGIN PROCEDURES*****'
    Sub ShowSubFolders(Folder)
    'Loop to go through each subfolder'
        For Each Subfolder in Folder.SubFolders
            Set objFolder = objFSO.GetFolder(Subfolder.Path)
            Set colFiles = objFolder.Files
            For Each objFile in colFiles
                    Call Output(excelRow)
            Next 
            ShowSubFolders Subfolder     
        Next
    End Sub
    
    Sub Output(excelRow)
        'convert filesize to readable format (MB)'
        fileSize = objFile.Size/1048576 
        fileSize = FormatNumber(fileSize, 2)
    'list of file extensions currently automatically included in spreadsheet report:'
    '.wav, .mp3, .mpeg, .avi, .aac, .m4a, .m4p, .mov, .qt, .qtm'         
        If fileSize > 100 	then'OR objFile.Type="Movie Clip" OR objFile.Type="MP3 Format Sound" _ '
    		'OR objFile.Type="MOV File" OR objFile.Type="M4P File" _'
    		'OR objFile.Type="M4A File" OR objFile.Type="Video Clip" _'
    		'OR objFile.Type="AAC File" OR objFile.Type="Wave Sound" _'
    		'OR objFile.Type="QT File" OR objFile.Type="QTM File"' 
            'export data to Excel'
            objExcel.Visible = True
            objExcel.Cells(excelRow,1).Value = objFile.Name
            objExcel.Cells(excelRow,2).Value = objFile.Type
            objExcel.Cells(excelRow,3).Value = fileSize & " MB"
            objExcel.Cells(excelRow,4).Value = FindOwner(objFile.Path)
            objExcel.Cells(excelRow,5).Value = objFile.Path
            objExcel.Cells(excelRow,6).Value = objFile.DateCreated
            objExcel.Cells(excelRow,7).Value = objFile.DateLastAccessed
            excelRow = excelRow + 1 'Used to move active cell for data input'
        end if
    End Sub
    
    'Procedure used to find the owner of a file'
    Function FindOwner(FName)  
        On Error Resume Next
        strComputer = "."
        Set objWMIService = GetObject("winmgmts:" _
            & "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
    
        Set colItems = objWMIService.ExecQuery _
            ("ASSOCIATORS OF {Win32_LogicalFileSecuritySetting='" & FName & "'}" _ 
                & " WHERE AssocClass=Win32_LogicalFileOwner ResultRole=Owner")
       
        For Each objItem in colItems
            FindOwner = objItem.AccountName
        Next    
    End Function
    
    Sub CreateExcelHeaders
      'create headers for spreadsheet'
      Set objRange = objExcel.Range("A1","G1")
      objRange.Font.Bold = true
      objExcel.Cells(1, 1).Value = "File Name"
      objExcel.Cells(1, 2).Value = "File Type"
      objExcel.Cells(1, 3).Value = "Size"
      objExcel.Cells(1, 4).Value = "Owner"
      objExcel.Cells(1, 5).Value = "Path"
      objExcel.Cells(1, 6).Value = "Date Created"
      objExcel.Cells(1, 7).Value = "Date Modified"
    
    End Sub
    
    Sub ExcelAutofit
          'autofit cells'
      Set objRange = objExcel.Range("A1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()
    
      Set objRange = objExcel.Range("B1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()
    
      Set objRange = objExcel.Range("C1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()
    
      Set objRange = objExcel.Range("D1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()
      
      Set objRange = objExcel.Range("E1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()
        
      Set objRange = objExcel.Range("F1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()   
      
      Set objRange = objExcel.Range("G1")
      objRange.Activate
      Set objRange = objExcel.ActiveCell.EntireColumn
      objRange.Autofit()  
    End Sub
    
    
        
    


    David Hood

    Monday, December 29, 2014 5:30 PM

Answers

  • Accessing Excel through automation is bvery slow no matter what tool you use.  Scanning a disk is very slow for all tools.

    Since Vista all system have a search service that catalogues all major file itmes like size, extension, name and other attributes.  A search of a 1+Tb  volume can return in less that a second if you query the search service.

    You can easily batch the result into Excel by writ4ing to a CSV and opening in Excel. Use a template to apply formats.

    Example.  See how fast this returns results.

    #The following will find all log files in a system that are larger than 10Mb
    
    $query="SELECT System.ItemName, system.ItemPathDisplay, System.ItemTypeText,System.Size,System.ItemType FROM SystemIndex where system.itemtype='.log' AND system.size > $(10Mb)"
    
    $conn=New-Object -ComObject adodb.connection
    $conn.open('Provider=Search.CollatorDSO;Extended Properties="Application=Windows";')
    
    $rs=New-Object -ComObject adodb.recordset
    $rs.open($query, $conn)
    
    do{
        $p=[ordered]@{
            Name = $rs.Fields.Item('System.ItemName').Value
            Type = $rs.Fields.Item('System.ITemType').Value
            Size = $rs.Fields.Item('System.Size').Value
        }
        New-Object PsObject -Property $p
        $rs.MoveNext()
    }Until($rs.EOF) 


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, December 29, 2014 7:33 PM
    • Marked as answer by Bill_StewartModerator Sunday, January 25, 2015 5:03 PM
    Monday, December 29, 2014 7:32 PM

All replies

  • Accessing Excel through automation is bvery slow no matter what tool you use.  Scanning a disk is very slow for all tools.

    Since Vista all system have a search service that catalogues all major file itmes like size, extension, name and other attributes.  A search of a 1+Tb  volume can return in less that a second if you query the search service.

    You can easily batch the result into Excel by writ4ing to a CSV and opening in Excel. Use a template to apply formats.

    Example.  See how fast this returns results.

    #The following will find all log files in a system that are larger than 10Mb
    
    $query="SELECT System.ItemName, system.ItemPathDisplay, System.ItemTypeText,System.Size,System.ItemType FROM SystemIndex where system.itemtype='.log' AND system.size > $(10Mb)"
    
    $conn=New-Object -ComObject adodb.connection
    $conn.open('Provider=Search.CollatorDSO;Extended Properties="Application=Windows";')
    
    $rs=New-Object -ComObject adodb.recordset
    $rs.open($query, $conn)
    
    do{
        $p=[ordered]@{
            Name = $rs.Fields.Item('System.ItemName').Value
            Type = $rs.Fields.Item('System.ITemType').Value
            Size = $rs.Fields.Item('System.Size').Value
        }
        New-Object PsObject -Property $p
        $rs.MoveNext()
    }Until($rs.EOF) 


    ¯\_(ツ)_/¯


    • Edited by jrv Monday, December 29, 2014 7:33 PM
    • Marked as answer by Bill_StewartModerator Sunday, January 25, 2015 5:03 PM
    Monday, December 29, 2014 7:32 PM
  • This is murderously fast. It returns all results on my 1Yb system in less that .5 seconds.

    $query=@"
    SELECT System.ItemName, system.ItemPathDisplay, System.ItemTypeText,System.Size,System.ItemType 
    FROM SystemIndex 
    WHERE 
        system.size > $(10Mb)
        AND
        CONTAINS(system.itemtype,'"jpg" OR "jpeg" OR "mpeg" OR "avi" OR "mov"')
    "@
    
    $conn=New-Object -ComObject adodb.connection
    $conn.open('Provider=Search.CollatorDSO;Extended Properties="Application=Windows";')
    
    $rs=New-Object -ComObject adodb.recordset
    $rs.open($query, $conn)
    
    do{
        $p=[ordered]@{
            Name = $rs.Fields.Item('System.ItemName').Value
            Type = $rs.Fields.Item('System.ItemType').Value
            FileType = $rs.Fields.Item('System.ItemTypeText').Value
            Size = $rs.Fields.Item('System.Size').Value
        }
        New-Object PsObject -Property $p
        $rs.MoveNext()
    }Until($rs.EOF) 
    


    ¯\_(ツ)_/¯

    Monday, December 29, 2014 7:53 PM
  • I tried this code and returned errors.  I'll re-iterate that I know very little about powershell.  I know PS is what I want to use for this project and I don't want to waste anyone's time for this, but I am trying to learn.  Too many projects, not enough time.  :)

     Unable to find type [ordered]: make sure that the assembly containing this type is loaded.
    At line:17 char:17
    +     $p=[ordered] <<<< @{
        + CategoryInfo          : InvalidOperation: (ordered:String) [], RuntimeException
        + FullyQualifiedErrorId : TypeNotFound

    New-Object : Cannot validate argument on parameter 'Property'. The argument is null or empty. Supply an argument that is not null or empty and then try the command again.
    At line:23 char:34
    +     New-Object PsObject -Property <<<<  $p
        + CategoryInfo          : InvalidData: (:) [New-Object], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationError,Microsoft.PowerShell.Commands.NewObjectCommand

     

    David Hood

    Wednesday, December 31, 2014 7:47 PM
  • Upgrade to at least PowerShell v3. PowerShell v2 and older do not support [ordered].

    -- Bill Stewart [Bill_Stewart]

    Wednesday, December 31, 2014 7:49 PM
    Moderator
  • Actually doing that right now.  That was my first thought.  I was running V2.0.  

    David Hood

    Wednesday, December 31, 2014 7:56 PM