Avatar of keonh
keonh asked on

Disk Space Usage for list of server shares

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!!!!
Microsoft Legacy OSVB Script

Avatar of undefined
Last Comment
Bill Prew

8/22/2022 - Mon
Bill Prew

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

~bp
arnold

Use wmi to access the remote servers to get the storage information the presentation is completely up to you.
https://www.experts-exchange.com/OS/Microsoft_Operating_Systems/Server/2003_Server/Q_22082577.html

http://www.vistax64.com/powershell/57617-powershell-wmi-scripting-disk-usage.html

http://blogs.technet.com/b/heyscriptingguy/archive/2009/03/04/how-do-i-migrate-my-vbscript-wmi-queries-to-windows-powershell.aspx

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.
ASKER
keonh

@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.
diskspacestats.jpg
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
arnold

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."
Bill Prew

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.

~bp
' 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)
objSharesFile.Close
 
' Start Excel, create a new worksheet
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False
objExcel.Workbooks.Add
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
Next
 
' 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
      Next
      
      ' 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
      Next
      
      ' 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
Next
 
' Save excel file, close excel
objExcel.DisplayAlerts = False
objExcel.ActiveWorkbook.SaveAs cExcelFile, cExcel7
objExcel.ActiveWorkbook.Close False
objExcel.Application.Quit
 
' Wrap up
Set objSheet = Nothing
Set objExcel = Nothing

Open in new window

ASKER
keonh

@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.

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Bill Prew

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
keonh

Thanks bp ... Genius is an understatement.
Bill Prew

Thanks for the points, and compliment, glad to have provided some useful info for you.

~bp