Solved

Find folder size on remote computers...

Posted on 2011-03-04
48
1,481 Views
Last Modified: 2012-05-11
I need a script that will read computer name and folder location from an excel spreadsheet and then go out and find the folder size of that specified folder and then write the computer name/folder name/ and folder size to a txt file or something.

What I need to do is scan some computers in my office to get an idea of how much disk space we need to start backing up our desktops/laptops to a NAS. So I need to look at user profile documents folders and email pst folders. And some machines are XP Pro and other are Window 7.

I am hoping that someone has had this same challenge before and can help with what they did to get this information.

I really appreciate the any help I can get with this.
0
Comment
Question by:CCG3
  • 25
  • 23
48 Comments
 
LVL 12

Expert Comment

by:prashanthd
ID: 35036729
Can we use Administrative shares to access remote systems?

example \\computername\C$\Scripts
0
 

Author Comment

by:CCG3
ID: 35036760
Yes. All Admin Shares are still in place.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35036917
Please try the following.

Excel Sheet should be in the following format

column1(computer) column2(Path of folder)

As First row from Excel will header info, it will check values from 2nd row.

Output will be in csv

computername, folder path, folder size in KB
On Error Resume Next

inputfile="C:\folderlist.xls" 'path of excel file
outputfile="C:\output.txt" 'path of output file to be written

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(inputfile)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set outputfile=objfso.OpenTextFile(outputfile,2)

intRow = 2

Do Until objExcel.Cells(intRow,1).Value = ""
    strcomputer=Trim(objExcel.Cells(intRow, 1).Value)
    folder=Trim(objExcel.Cells(intRow, 2).Value)
    
    folder_path="\\"&strcomputer&"\"& Replace(folder,":","$")
    
    Set objFolder=objfso.GetFolder(folder_path)
    
	outputfile.WriteLine strcomputer & "," & folder & "," Round(objFolder.Size/(1024))    
    
    intRow = intRow + 1
Loop

outputfile.Close
objExcel.Quit

Open in new window

0
 

Author Comment

by:CCG3
ID: 35037636
Thanks for the help. I seem to be having some issue though.

Should this be run as a bat file? just save as a .bat and run? When I do this I get an error on just about everything. "On Error Resume Next 'On' is not recongnized as an internal or external command, operable program or batch file." for example.
0
 

Author Comment

by:CCG3
ID: 35037887
when I run it as a .vbs I get the following error...

Line :21
Char: 56
Error: Expected end of statement.
Code: 800A0401
Source: Microsoft VBScript Compilation error.
0
 

Author Comment

by:CCG3
ID: 35037973
Ok so I added the & before round on line 21...

outputfile.WriteLine strcomputer & "," & folder & ","  & Round(objFolder.Size/(1024))

and now when I run it crashes Excel and won't finish. I am on Windows 7 running Office 2007. Should I change the xls file to be an xlsx file? think that would make a difference?
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35038204
Hi,

Can you create  a csv input file instead of excel as there may be some issues.

Please try the .xlsx format, if not you can create a csv file from excel, which we can use as input file.

0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35038328
If you can create an input csv file, try the following

csv format
computer name,folder path
On Error Resume Next

inputfile="C:\folderlist.txt" 'path of input outputfile="C:\output.txt" 'path of output file to be written

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set readfile=objfso.OpenTextFile(inputfile,1)
Set outputfile=objfso.OpenTextFile(outputfile,2)

Do Until readfile.AtEndOfStream

	strline=Split(readfile.ReadLine)
    strcomputer=Trim(strline(0))
    folder=Trim(strline(1))
    
    folder_path="\\"&strcomputer&"\"& Replace(folder,":","$")
    
    Set objFolder=objfso.GetFolder(folder_path)
    
	outputfile.WriteLine strcomputer & "," & folder & "," & Round(objFolder.Size/(1024))    
    
    intRow = intRow + 1
Loop

outputfile.Close
readfile.Close

Open in new window

0
 

Author Comment

by:CCG3
ID: 35038904
thank you again for your help with this! Ok that one runs and but doesn't do anything.

I was able to get the first example to run (my problem was I had the computer name included in the folder name).

However, even though it runs and produces output to the txt file, it still shows an Excel crash. So i wonder if it is happening with it finds an empty line in Excel or when it tries to close it.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35038978
can you post sample input file format?
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35039083
I have tested the script with  .xls input file and it worked without any issues, there may be an issue with your input file (excel and .csv).
0
 

Author Comment

by:CCG3
ID: 35039162

Here are the only 2 columns that I have. Computer name is A and file location is B.

A                             B
User-PC            c$\Users\User\Documents\1750
User-PC            c$\Users\User\Documents\Dell
User-PC            c$\Users\User\Documents\Flash
User-PC            c$\Users\User\Documents\CGTEST
User-PC            c$\Users\mkenny\Documents\Firewall
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35039228
The script actuall replaces ":" with "$" as I thought the folder path will be like c:\foldername.

I had made changes, to the first one i.e with excel plese try.

Also I had given someecho statements, so you willknow if the excelfiles is being read and output is being written


On Error Resume Next

inputfile="C:\folderlist.xls" 'path of excel file
outputfile="C:\output.txt" 'path of output file to be written

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(inputfile)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set outputfile=objfso.OpenTextFile(outputfile,2)

intRow = 1

Do Until objExcel.Cells(intRow,1).Value = ""
    strcomputer=Trim(objExcel.Cells(intRow, 1).Value)
    folder=Trim(objExcel.Cells(intRow, 2).Value)
    
    folder_path="\\"&strcomputer&"\"& folder
    
    WScript.Echo folder_path
    
    Set objFolder=objfso.GetFolder(folder_path)
    
	outputfile.WriteLine strcomputer & "," & folder & "," & Round(objFolder.Size/(1024)) 
	WScript.Echo strcomputer & "," & folder & "," & Round(objFolder.Size/(1024))    
    
    intRow = intRow + 1
Loop

outputfile.Close
objExcel.Quit

Open in new window

0
 

Author Comment

by:CCG3
ID: 35039451
Ok since you said that now I see where you were replacing the : and $ now.

that seems to work better but when I try to look at a C$\Users\UserrName\Documents it doesn't write anything. It shows that it read it but it doesn't write anything to the output file for that line.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35041429
can you comment the 'on error resume next and try, we can troubleshoot based on the error.
0
 

Author Comment

by:CCG3
ID: 35041473
Hmmm.. Permission Denied.

I can open this folder from the run command but it fails in the script. I can see the My Documents fine on an XP machine. I wonder why Windows 7 is blocking me.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35041669
Do you have the requisite privileges on all the computers?

Permission Denied line no 21 ?
0
 

Author Comment

by:CCG3
ID: 35056421
Yes I have Network Admin rights and can open this location and add/del anything that I need to.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35056491
can you check from a system other than Windows 7 ?
0
 

Author Comment

by:CCG3
ID: 35056655
I think this is happening because the Windows 7 folder structure has some folders like 'My Music' that is just a shortcut and if you try to open them you get the Access Denied error. Is there anyway that we can not look at these shortcuts in the Documents folder?

I know that if I specify any folder in the Documents folder I can get the size of it. However I need to be able to look at the entire Documents folder and find out the size because I am not always going to know what is in there.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35056795
On error resume next - will skip the folders where permission is denied and move on to the next folder.

Made some changes for error trapping, this will display permission denied folders also
On Error Resume Next

inputfile="C:\folderlist.xls" 'path of excel file
outputfile="C:\output.txt" 'path of output file to be written

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(inputfile)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set outputfile=objfso.OpenTextFile(outputfile,2)

intRow = 1

Do Until objExcel.Cells(intRow,1).Value = ""
    strcomputer=Trim(objExcel.Cells(intRow, 1).Value)
    folder=Trim(objExcel.Cells(intRow, 2).Value)
    
    
    folder_path="\\"&strcomputer&"\"& folder
    
    WScript.Echo folder_path
    
    Err.Clear
    fsize=Round(objFolder.Size/(1024))
    
    If Err.number=0 Then
        outputfile.WriteLine strcomputer & "," & folder & "," &  
        WScript.Echo strcomputer & "," & folder & "," & Round(objFolder.Size/(1024))    
    Else
        WScript.Echo strcomputer & "," & folder & "- Permission denied"
    End If
    
    intRow = intRow + 1
Loop

outputfile.Close
objExcel.Quit

Open in new window

0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35056802
Ignore earlier code, try this
On Error Resume Next

inputfile="C:\folderlist.xls" 'path of excel file
outputfile="C:\output.txt" 'path of output file to be written

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(inputfile)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set outputfile=objfso.OpenTextFile(outputfile,2)

intRow = 1

Do Until objExcel.Cells(intRow,1).Value = ""
    strcomputer=Trim(objExcel.Cells(intRow, 1).Value)
    folder=Trim(objExcel.Cells(intRow, 2).Value)
    
    
    folder_path="\\"&strcomputer&"\"& folder
    
    WScript.Echo folder_path
    
    Err.Clear
    fsize=Round(objFolder.Size/(1024))
    
    If Err.number=0 Then
        outputfile.WriteLine strcomputer & "," & folder & "," &  fsize
        WScript.Echo strcomputer & "," & folder & "," & fsize   
    Else
        WScript.Echo strcomputer & "," & folder & "- Permission denied"
    End If
    
    intRow = intRow + 1
Loop

outputfile.Close
objExcel.Quit

Open in new window

0
 

Author Comment

by:CCG3
ID: 35057098
Shows everything as Permission Denied now with no output.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35058804
Would like to get the error number and description, change the following in code  

If Err.number=0 Then
        outputfile.WriteLine strcomputer & "," & folder & "," &  fsize
        WScript.Echo strcomputer & "," & folder & "," & fsize  
    Else
        WScript.Echo strcomputer & "," & folder & " - " & Err.number & " - " & vbcrlf & err.description
    End If
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:CCG3
ID: 35058841


\\computername, c$\Users\Username\Documents - 424 - Object required.


0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35059042
what is the value assigned to strcomputer?

strcomputer value should be computername and not \\computername

Above you can see that strcomputer is returning \\computername

What is the value being displayed for WScript.Echo folder_path
0
 

Author Comment

by:CCG3
ID: 35059167


I just entered computername and path. According to the pop up messages the computer name and path are correct. I have been using the same xls file the entire time.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35059357
hmmm....missed out the following line before err.clear when modifying

Add the following code before err.clear

 Set objFolder=objfso.GetFolder(folder_path)
0
 

Author Comment

by:CCG3
ID: 35059859


Ok everything looks good now but we are back to Permission Denied but now it is giving Error number 70, Permission Denied.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35059910
ok...is it for all the subfolders or only some?
0
 

Author Comment

by:CCG3
ID: 35061144
I can get to all subfolders if I key them in the list for example...

c$\Users\Username\Documents\1705
c$\Users\Username\Documents\CG Folder

those 2 examples work just fine. However I am not always going to know what the folder names are in each user's Documents folder. So I want to try and look at everything like this...

c$\Users\Username\Documents

And this gives the error 70 Permission Denied. But I can go to this same path through Windows Explorer and I can check the size, add, and delete folders.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35065359
Can you run the script from command with elevated privileges, at times Windows 7 requires this eventhoug logged as an administrator

Right click and hit 'run as administrator' on command prompt
0
 

Author Comment

by:CCG3
ID: 35068985
Since this is a .VBS i don't have the option to run as administrator (batch files do though). But I did log in as the network admin and tried and had the same results.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35069062
you can run .vbs from command prompt

Change directory to script file path and run following

cscript filename.vbs

http://www.blogsdna.com/2168/windows-7-how-to-open-elevated-command-prompt-with-administrator-privileges.htm
0
 

Author Comment

by:CCG3
ID: 35070457
ran command prompt as administrator, and ran the .vbs file and got the same Error 70 Permission Denied error.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35072386
0
 

Author Comment

by:CCG3
ID: 35095819
Thanks for the info. So it looks like this is limitation of Vista and win 7? There isn't any way to get around it?
0
 

Author Comment

by:CCG3
ID: 35096143
I wonder if I can do this in PowerShell instead of VBS and not have this issue.
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35096302
Hi,

We can try powershell also will work on it,meanwhile can you try this utility to get folder size of one of the folders where we are getting permission denied error

http://technet.microsoft.com/en-us/sysinternals/bb896651.aspx
0
 

Author Comment

by:CCG3
ID: 35097053
OK that running manually form a command line seems to help. Now to figure out how to just get the folder size instead of all the Totals Information, output that to the txt file, and how to incorporate this in the code.

And I would rather do this and stay with the VBS since most of my users are XP and may not have Power Shell installed and certainly wouldn't have PS 2.0 installed.
0
 
LVL 12

Accepted Solution

by:
prashanthd earned 500 total points
ID: 35107262
The following Vbscript uses du.exe to get the size info, ensure du.exe is in the same path as .vbs file.
On Error Resume Next

inputfile="C:\folderlist.xls" 'path of excel file
outputfile="C:\output.txt" 'path of output file to be written

Set objExcel = CreateObject("Excel.Application")
Set objWorkbook = objExcel.Workbooks.Open(inputfile)
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set outputfile=objfso.OpenTextFile(outputfile,2)

intRow = 1

Do Until objExcel.Cells(intRow,1).Value = ""
    strcomputer=Trim(objExcel.Cells(intRow, 1).Value)
    folder=Trim(objExcel.Cells(intRow, 2).Value)
    folder_path="\\"&strcomputer&"\"& folder
    WScript.Echo folder_path
    Set objShell = CreateObject("Wscript.Shell")
    command = "du.exe " & folder_path
    
    Set objExec = objShell.Exec(Command) 
    Do While Not objExec.StdOut.AtEndOfStream
        strText = Trim(objExec.StdOut.ReadLine())
        ' Test or display strText
        WScript.Echo strText  
        If InStr(strText,"Size:         ")>0 Then
            fsize=Replace(strText,"Size:         ","")
            fsize=Replace(fsize," bytes","")
            fsizedbl=CDbl(fsize)
            WScript.Echo "File size " & fsizedbl
            outputfile.WriteLine strcomputer & "," & folder & "," &  fsize
            Exit Do
        End If 
    Loop
    
    intRow = intRow + 1
Loop

outputfile.Close
objExcel.Quit

Open in new window

0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35107268
Change line no 31 to following

 outputfile.WriteLine strcomputer & "," & folder & "," &  fsizedbl
0
 

Author Comment

by:CCG3
ID: 35110158
Wow! That looks really good! Can we change the output number to megabytes? is that =((fsize /1024)/1024)? Or something like that?
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35110645
can do that.... can you ask this in a new post :)
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35110666
add following after line 29

fsizedbl=round(fsizedbl/(1024*1024),2)
0
 

Author Comment

by:CCG3
ID: 35110675
Yup! thanks!
0
 

Author Comment

by:CCG3
ID: 35112237
Hey parashanthd, thanks so much for your help! I really appreciate it!! You are a life saver!
0
 
LVL 12

Expert Comment

by:prashanthd
ID: 35113812
Finally we got this working :), you are welcome...

regards
Prashanth
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

This script checks a path to see if a folder exists. If the folder does exist you will get output "The folder has previously been created. No action taken" If not it will create the folder. Then adds one user modify permission to the folder. It …
Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn the basics of while and for loops in Python.  while loops are used for testing while, or until, a condition is met: The structure of a while loop is as follows:     while <condition>:         do something         repeate: The break statement m…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now