Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Disk Space Usage for list of server shares

Posted on 2010-11-11
Medium Priority
Last Modified: 2012-06-27
Hello Fellow Experts,

I'm looking for a VB script that will display Disk usage stats from a text file with a list of \\server\shares with the usage column color coded. Red, Yellow or Green based on usage percentage. Red = 90% - 100%, Yellow = 70% - 89%, Green = Below 69%

Here's the layout that I would like.

Server share, Usage (how much space is used in percentage), Total Drive Space, Used Drive Space,  Free Space.

I would like this to export to csv or txt file.  

Thanks in advance!!!!
Question by:keonh
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
LVL 57

Expert Comment

by:Bill Prew
ID: 34114155
Hmmm, there isn't anyway to include field color information in a CSV file.  What were you thinking in that regard?

LVL 79

Expert Comment

ID: 34114208
Use wmi to access the remote servers to get the storage information the presentation is completely up to you.

isolating to the shares, you may have to get a list of the shares to see on which drives they are and then compare the remaining space on those.

The other option is to define. The shares within the file server manager with quotas and then use the quota settings to generate event log events for warning.

Author Comment

ID: 34114900
@Billprew - I was thinking to export the csv/txt into excel to get the colors for that usage column. See image.

@Arnold - Thanks ...  I'll look at those links.
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 79

Expert Comment

ID: 34115446
Note the share is actually in most cases represents the available space on the drive on which it resides.  With the newer windows 2003 R2 and higher, there is a way to set a per share quota in terms of the total size of the space allocated to the share and then a per user quota as well.

The other option using WMI you might be able to query a local system that has the shares mapped.
The distinction deals with the "drive type."
LVL 57

Expert Comment

by:Bill Prew
ID: 34117501
I'll take a shot at this.  Here's a VBS script that should be close.  Create a list of the shares to report on in a text file.  Edit the constants near the top of the script as needed.

' Define constants
Const cSharesFile = "c:\temp\EE26608584\shares.txt"
Const cExcelFile = "c:\temp\EE26608584\usage.xlsx"
Const cHeaders = "Server Share;Usage;Total Space;Used Space;Free Space"
Const cExcel7 = 51
' Read list of shares into arrary
Set objFSO = CreateObject("Scripting.FilesystemObject")
Set objSharesFile = objFSO.OpenTextFile(cSharesFile, 1)
arrPaths = Split(objSharesFile.ReadAll, vbNewLine)
' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
Set objSheet = objExcel.ActiveWorkbook.Worksheets(1)
' Initialize row and column indexes
intCol = 1
intRow = 1
' Write header row
For Each strHeader In Split(cHeaders, ";")
   objSheet.Cells(intRow, intCol).Value = strHeader
   intCol = intCol + 1
' Process each share
For Each strPath in arrPaths
   If strPath <> "" Then
      intRow = intRow + 1
      arrTokens = Split(strPath, "\")
      strShare = arrTokens(UBound(arrTokens))
      strServer = arrTokens(UBound(arrTokens) - 1)
      strFull = "" 
      numSize = ""
      numFree = ""
      ' Enumerate share
      Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strServer & "\root\cimv2")
      Set colShares = objWMI.ExecQuery("Select Path from Win32_Share where name = '" & strShare & "'")
      ' Get drive share is on
      For Each objShare In colShares
         strFull = objShare.Path
         strDrive = Split(objShare.Path, ":")(0)
         Exit For
      ' Get space info for drive
      Set objWMI = GetObject("winmgmts:{impersonationLevel=impersonate}!\\" & strServer & "\root\cimv2")
      Set colItems = objWMI.ExecQuery("Select FreeSpace,Size from Win32_LogicalDisk where Name='" & strDrive & ":'")
      For Each objItem in colItems
         numSize = (objItem.Size / 1073741824)
         numFree = (objItem.FreeSpace / 1073741824)
         numUsed = numSize - numFree
         numPercent = numUsed / numSize
         Exit For
      ' Store data in Excel
      objSheet.Cells(intRow, 1).Value = strPath
      objSheet.Cells(intRow, 2).Value = numPercent
      objSheet.Cells(intRow, 3).Value = numSize
      objSheet.Cells(intRow, 4).Value = numUsed
      objSheet.Cells(intRow, 5).Value = numFree
   End If
' Save excel file, close excel
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs cExcelFile, cExcel7
objExcel.ActiveWorkbook.Close False
' Wrap up
Set objSheet = Nothing
Set objExcel = Nothing

Open in new window


Author Comment

ID: 34123486
@Bilprew - Nice work!! Thanks!! Just a few mods...

a.  Can you format the "Usage" to %
b.  Can you format Total, Used & Free to GB or MB depending on size.
c.  Modify the output filename to include date & time. Ex. 11_12_2010_1:10PM_Usage.xlsx
d.  Add conditional formatting based on Red = 90% to 100% - Yellow = 70% - 89% - Green = 1% to 69%

If you can't get item D, I can do it manually.  Thanks again.

Side note: I'm not a programmer but I was wondering if it's possible to get the same results using an EXE with a customizable refresh rate.  Just a thought.

LVL 57

Accepted Solution

Bill Prew earned 2000 total points
ID: 34126004
a.  Can you format the "Usage" to %

Yes, add the second line here after the existing comment toward the bottom:

' Save excel file, close excel
objSheet.Columns("B:B").NumberFormat = "0.00%"

b.  Can you format Total, Used & Free to GB or MB depending on size.

The only way to do that would be to treat these fields as character fields, do the scaling you desire in the VBS script, and then put the result in the cells.  But that will mean you can't do ANY math against those values, since they will be character cells, and also will have different scales.  That feels like a mistake to me, since it would be hard to follow the values when the went from say 999 MB to 1 GB.

c.  Modify the output filename to include date & time. Ex. 11_12_2010_1:10PM_Usage.xlsx

Change this line:

Const cExcelFile = "c:\temp\EE26608584\usage.xlsx"


cExcelFile = "c:\temp\EE26608584\" & Replace(FormatDateTime(Now, 2), "/", "-") & "_" & Replace(FormatDateTime(Now, 4), ":", "-") & "_usage.xlsx"

d.  Add conditional formatting based on Red = 90% to 100% - Yellow = 70% - 89% - Green = 1% to 69%

Don't think I can bite that off.


Author Comment

ID: 34173626
Thanks bp ... Genius is an understatement.
LVL 57

Expert Comment

by:Bill Prew
ID: 34173644
Thanks for the points, and compliment, glad to have provided some useful info for you.


Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
This video Micro Tutorial explains how to clone a hard drive using a commercial software product for Windows systems called Casper from Future Systems Solutions (FSS). Cloning makes an exact, complete copy of one hard disk drive (HDD) onto another d…
With the advent of Windows 10, Microsoft is pushing a Get Windows 10 icon into the notification area (system tray) of qualifying computers. There are many reasons for wanting to remove this icon. This two-part Experts Exchange video Micro Tutorial s…

705 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