locked
vbscript help RRS feed

  • Question

  • Hello,

    I'm trying to inventory the PST and OST files on the C: drives of my users. I'm fairly green at vbScript and would really appreciate some tips/hints on improving my scripting.

    The following script takes around 20 minutes to finish and sometimes (I still can't figure out why) doesn't catch all the PST files. I'm pretty sure it's something to do with my objRSSQL.Open and .Close statements but I have tried so many things I'm just stumped at this point.

    I have removed some unrelated parts just for the sake of brevity so you might see some references in the first part of the script that don't make sense.

    Any help will be appreciated.
    On Error Resume Next
    
    '----------------------------------------------------------------------
    'Setup Variables To Be Used
    '----------------------------------------------------------------------
    Dim     strSQLQuery, strDoUpdate, strIP, strComputer, strSQLInsert, IPConfig, strComputerName, strUser, _
    strMyDocsSize, strPath
    Dim     objConn, objRSSQL, objComputer, objWMIService, objBIOS, objFolder
    Dim    colIPConfigSet, colBIOS
    
    
    '----------------------------------------------------------------------
    'Create Object Collections
    '----------------------------------------------------------------------
    strComputer = "."
    Set wshShell = CreateObject("WScript.Shell")
    Set wshNetwork = CreateObject("WScript.Network")
    Set objADSInfo = CreateObject("ADSystemInfo")
    Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")
    
    strUser = objADSInfo.Username
    strComputerName = wshNetwork.ComputerName
    
    ' Create SQL Connection
    Set objConn = CreateObject("ADODB.Connection")
        objConn.Open "Driver={SQL Server};Server=sqlsvr.domain.net;Database=Inventory;User ID=UserName;Password=YeahRight;"
    Set objRSSQL = CreateObject("ADODB.Recordset")
     
    DoQuery()
    
    If strDoUpdate2 < 1 Then
        DoPST()
    End If
    
    'Wscript.Echo strUser & vbCrLf & strIP & vbCrLf & strPath & vbCrLf & strMyDocsSize
    Wscript.Quit 0
    '----------------------------------------------------------------------
    'Functions and Subroutines executed only by the lines above
    '----------------------------------------------------------------------
    
    Function DoQuery()
    '----------------------------------------------------------------------
    'DoQuery checks to see if the Username already exists
    '----------------------------------------------------------------------
        strSQLQuery2 = "select count(colUsername) as NUM2 from tblPsts where colUsername like '" & strUser & "' and colMachineName like '" & strComputerName & "'"
        objRSSQL.Open strSQLQuery2, objConn
        strDoUpdate2 = objRSSQL("NUM2")
        'objRSSQL.Close
        'Wscript.Echo "DoQuery is: " & strDoUpdate & vbcrlf & strDoUpdate2
    End Function
    '----------------------------------------------------------------------
    
    Function DoPST()
        '----------------------------------------------------------------------
        'DoCV rounds Bytes to appropriate abbreviated total
        '----------------------------------------------------------------------
    
        Set colFiles = objWMIService. _
            ExecQuery("Select * From CIM_DataFile Where Drive = 'c:' and (Extension = 'pst' or Extension = 'ost')")
            For Each objFile in colFiles
                objRSSQL.Close
                strSQLInsert2 = "insert into tblPsts (colPst, colSize, colUsername, colMachineName, colADate) values ('" & objFile.Name & "','" & objFile.FileSize  & "','" & strUser  & "','" & strComputerName  & "','" & objFile.LastAccessed & "')"
                'Wscript.Echo "Drive: " & objFile.Drive & vbcrlf & "Path: " & objFile.Path & vbcrlf & "Name: " & objFile.Name
                objRSSQL.Open strSQLInsert2, objConn
            Next
    End Function
        '----------------------------------------------------------------------
       
    Function DoCV()
        '----------------------------------------------------------------------
        'DoCV rounds Bytes to appropriate abbreviated total
        '----------------------------------------------------------------------
    
        Do While InStr(strSize,",") 'Remove commas from size
            CommaLocate = InStr(strSize,",")
            strSize = Mid(strSize,1,CommaLocate - 1) & _
                Mid(strSize,CommaLocate + 1,Len(strSize) - CommaLocate)
        Loop
       
        strSuffix = " Bytes"
        If strSize >= 1024 Then strSuffix = " KB" End If
        If strSize >= 1048576 Then strSuffix = " MB"  End If
        If strSize >= 1073741824 Then strSuffix = " GB" End If
        If strSize >= 1099511627776 Then strSuffix = " TB" End If
       
        Select Case strSuffix
            Case " KB" strSize = Round(strSize / 1024, 1)
            Case " MB" strSize = Round(strSize / 1048576, 1)
            Case " GB" strSize = Round(strSize / 1073741824, 1)
            Case " TB" strSize = Round(strSize / 1099511627776, 1)
        End Select
    End Function
    '----------------------------------------------------------------------
    
    
    Thursday, October 15, 2009 2:52 PM

Answers

  • Here is a JScript script, findFiles.js, that can find files on all local fixed drives. It uses Cmd.exe to find files matching a pattern, outputs the files to a temporary file, and then uses FileSystemObject properties and methods to output CSV data related to each file: The file's path, it's name, its size, and its last modified date.

    // findFiles.js - written by Bill Stewart
    // Outputs a list of files on local fixed drives in CSV format.
    
    var SYSTEM_FOLDER = 1;
    var TEMP_FOLDER   = 2;
    
    var FSO = new ActiveXObject("Scripting.FileSystemObject");
    var WshShell = new ActiveXObject("WScript.Shell");
    
    // Returns the script host executable
    function scriptHost() {
      return WScript.FullName.substr(WScript.Path.length + 1).toLowerCase();
    }
    
    // Returns n as a hex value
    function hex(n) {
      return n < 0 ? (n + Math.pow(2, 32)).toString(0x10) : n.toString(0x10);
    }
    
    // Returns a temporary filename in the temp directory
    function getTempName() {
      do
        var result = FSO.BuildPath(FSO.GetSpecialFolder(TEMP_FOLDER), FSO.GetTempName());
      while (FSO.FileExists(result));
      return result;
    }
    
    // Executes a command and redirects the command's output to a temporary file;
    // returns the name of the temporary file
    function runCommand(theCommand) {
      var outFileName = getTempName();
      var cmdLine = FSO.BuildPath(FSO.GetSpecialFolder(SYSTEM_FOLDER), "cmd.exe") +
        " /c " + theCommand + " > " + outFileName;
      WshShell.Run(cmdLine, 0, true);
      return outFileName;
    }
    
    // Returns a VT_DATE as a string in the format yyyy-mm-dd hh:mm:ss
    function vbDateToString(vbDate) {
      function lz(n) {
        return n < 10 ? "0" + n.toString() : n.toString();
      }
      var theDate = new Date(vbDate);
      return theDate.getFullYear().toString() + "-" +
        lz(theDate.getMonth() + 1) + "-" +
        lz(theDate.getDate()) + " " +
        lz(theDate.getHours()) + ":"  +
        lz(theDate.getMinutes()) + ":" +
        lz(theDate.getSeconds());
    }
    
    // Outputs CSV data for its arguments; if an argument is a string, it is
    // enclosed in double quotes; otherwise, it's returned without quotes
    function getCSV(/* ... */) {
      var result = '';
      for (var n = 0; n < arguments.length; n++) {
        var arg = arguments[n];
        if (typeof arg == "string")
          result += (result == '') ? ('"' + arg + '"') : (',"' + arg + '"');
        else
          result += (result == '') ? (arg) : (',' + arg);
      }
      return result;
    }
    
    function main() {
      var args = WScript.Arguments;
    
      if ((args.length == 0) || (args.Named.Exists("?"))) {
        WScript.Echo("Usage: findFiles.js <pattern> [...]");
        return 0;
      }
    
      if (scriptHost() != "cscript.exe") {
        WScript.Echo("You must run this script with the CScript host.");
        return 1;
      }
    
      var argList = new Enumerator(args.Unnamed);
      var driveList = new Enumerator(FSO.Drives);
    
      // Iterate the collection of unnamed command-line arguments
      for (; ! argList.atEnd(); argList.moveNext()) {
        var toFind = argList.item();
        // Iterate the Drives collection
        for (; ! driveList.atEnd(); driveList.moveNext()) {
          var drive = driveList.item();
          if (drive.DriveType == 2) {  // 2 = fixed drive
            // Search using the dir command
            var tempFile = runCommand("dir " + drive.DriveLetter + ":\\" + toFind + " /a:-d /b /s");
            // Open the temporary file
            var tempTS = FSO.OpenTextFile(tempFile, 1);
            // Iterate the lines in the temporary file
            while (! tempTS.AtEndOfStream) {
              // Each line of the temporary file is a file's name
              var file = FSO.GetFile(tempTS.ReadLine());
              // Output CSV data for each file
              WScript.Echo(getCSV(file.ParentFolder.Path,
                                  file.Name,
                                  file.Size,
                                  vbDateToString(file.DateLastModified)));
            }
            // Close and delete the temporary file
            tempTS.Close();
            if (FSO.FileExists(tempFile))
              FSO.DeleteFile(tempFile);
          }
        }
        // Return to the beginning of the Drives collection
        driveList.moveFirst();
      }
    }
    
    WScript.Quit(main());

    To use the script, run it with CScript and specify one or more patterns on its command line; e.g.

    cscript findFiles.js *.pst *.ost

    Since it outputs CSV data, you can redirect its output to a CSV file; e.g.

    cscript findFiles.js *.pst *.ost > outlookData.csv

    Note that this script is JScript, not VBScript, so you must save it with a .js extension.

    HTH,

    Bill
    • Proposed as answer by Bill_Stewart Wednesday, October 21, 2009 5:11 PM
    • Marked as answer by IamMred Friday, January 15, 2010 8:16 AM
    Thursday, October 15, 2009 3:57 PM

All replies

  • Here is a JScript script, findFiles.js, that can find files on all local fixed drives. It uses Cmd.exe to find files matching a pattern, outputs the files to a temporary file, and then uses FileSystemObject properties and methods to output CSV data related to each file: The file's path, it's name, its size, and its last modified date.

    // findFiles.js - written by Bill Stewart
    // Outputs a list of files on local fixed drives in CSV format.
    
    var SYSTEM_FOLDER = 1;
    var TEMP_FOLDER   = 2;
    
    var FSO = new ActiveXObject("Scripting.FileSystemObject");
    var WshShell = new ActiveXObject("WScript.Shell");
    
    // Returns the script host executable
    function scriptHost() {
      return WScript.FullName.substr(WScript.Path.length + 1).toLowerCase();
    }
    
    // Returns n as a hex value
    function hex(n) {
      return n < 0 ? (n + Math.pow(2, 32)).toString(0x10) : n.toString(0x10);
    }
    
    // Returns a temporary filename in the temp directory
    function getTempName() {
      do
        var result = FSO.BuildPath(FSO.GetSpecialFolder(TEMP_FOLDER), FSO.GetTempName());
      while (FSO.FileExists(result));
      return result;
    }
    
    // Executes a command and redirects the command's output to a temporary file;
    // returns the name of the temporary file
    function runCommand(theCommand) {
      var outFileName = getTempName();
      var cmdLine = FSO.BuildPath(FSO.GetSpecialFolder(SYSTEM_FOLDER), "cmd.exe") +
        " /c " + theCommand + " > " + outFileName;
      WshShell.Run(cmdLine, 0, true);
      return outFileName;
    }
    
    // Returns a VT_DATE as a string in the format yyyy-mm-dd hh:mm:ss
    function vbDateToString(vbDate) {
      function lz(n) {
        return n < 10 ? "0" + n.toString() : n.toString();
      }
      var theDate = new Date(vbDate);
      return theDate.getFullYear().toString() + "-" +
        lz(theDate.getMonth() + 1) + "-" +
        lz(theDate.getDate()) + " " +
        lz(theDate.getHours()) + ":"  +
        lz(theDate.getMinutes()) + ":" +
        lz(theDate.getSeconds());
    }
    
    // Outputs CSV data for its arguments; if an argument is a string, it is
    // enclosed in double quotes; otherwise, it's returned without quotes
    function getCSV(/* ... */) {
      var result = '';
      for (var n = 0; n < arguments.length; n++) {
        var arg = arguments[n];
        if (typeof arg == "string")
          result += (result == '') ? ('"' + arg + '"') : (',"' + arg + '"');
        else
          result += (result == '') ? (arg) : (',' + arg);
      }
      return result;
    }
    
    function main() {
      var args = WScript.Arguments;
    
      if ((args.length == 0) || (args.Named.Exists("?"))) {
        WScript.Echo("Usage: findFiles.js <pattern> [...]");
        return 0;
      }
    
      if (scriptHost() != "cscript.exe") {
        WScript.Echo("You must run this script with the CScript host.");
        return 1;
      }
    
      var argList = new Enumerator(args.Unnamed);
      var driveList = new Enumerator(FSO.Drives);
    
      // Iterate the collection of unnamed command-line arguments
      for (; ! argList.atEnd(); argList.moveNext()) {
        var toFind = argList.item();
        // Iterate the Drives collection
        for (; ! driveList.atEnd(); driveList.moveNext()) {
          var drive = driveList.item();
          if (drive.DriveType == 2) {  // 2 = fixed drive
            // Search using the dir command
            var tempFile = runCommand("dir " + drive.DriveLetter + ":\\" + toFind + " /a:-d /b /s");
            // Open the temporary file
            var tempTS = FSO.OpenTextFile(tempFile, 1);
            // Iterate the lines in the temporary file
            while (! tempTS.AtEndOfStream) {
              // Each line of the temporary file is a file's name
              var file = FSO.GetFile(tempTS.ReadLine());
              // Output CSV data for each file
              WScript.Echo(getCSV(file.ParentFolder.Path,
                                  file.Name,
                                  file.Size,
                                  vbDateToString(file.DateLastModified)));
            }
            // Close and delete the temporary file
            tempTS.Close();
            if (FSO.FileExists(tempFile))
              FSO.DeleteFile(tempFile);
          }
        }
        // Return to the beginning of the Drives collection
        driveList.moveFirst();
      }
    }
    
    WScript.Quit(main());

    To use the script, run it with CScript and specify one or more patterns on its command line; e.g.

    cscript findFiles.js *.pst *.ost

    Since it outputs CSV data, you can redirect its output to a CSV file; e.g.

    cscript findFiles.js *.pst *.ost > outlookData.csv

    Note that this script is JScript, not VBScript, so you must save it with a .js extension.

    HTH,

    Bill
    • Proposed as answer by Bill_Stewart Wednesday, October 21, 2009 5:11 PM
    • Marked as answer by IamMred Friday, January 15, 2010 8:16 AM
    Thursday, October 15, 2009 3:57 PM
  • Is there a reason to use jscript instead of vbscript? iow, I have only just gotten above 'moron' level in vbscript and don't really know the first thing about jscript.
    Thursday, October 15, 2009 6:13 PM
  • Is there a reason to use jscript instead of vbscript? iow, I have only just gotten above 'moron' level in vbscript and don't really know the first thing about jscript.
    I usually use JScript instead of VBScript because it is, in my opinion, more powerful and flexible. But, you don't need to know anything about JScript to use the script I posted. Just save it as findFiles.js and use it from the command prompt.

    Bill
    Thursday, October 15, 2009 6:19 PM
  • Heh... well, I certainly do appreciate your help but I would have to modify it to put the info into a SQL table. I could figure that out but I'm already sick of being a newb with vbScript and don't really feel like starting from 0 again.

    Whoever said ignorance is bliss didn't have a computer.
    Thursday, October 15, 2009 7:56 PM
  • Heh... well, I certainly do appreciate your help but I would have to modify it to put the info into a SQL table. I could figure that out but I'm already sick of being a newb with vbScript and don't really feel like starting from 0 again.
    Hi,

    The script I posted writes its results in CSV format, which you can import into your SQL database.

    Bill
    Thursday, October 15, 2009 8:01 PM
  • Thanks Bill,

    I do appreciate your responses. I'm going to be doing this in a login script for hundreds of users. I was writing to SQL because I don't want to have to mess with importing/parsing individual files for each user. No worries, I will get it working.

    /x
    Thursday, October 15, 2009 9:04 PM