Script Total Free Drive space to excel file
thanks fresh,
I found another way to do it. Four days ago I didnt even know what a variable was. Using google and several scripting sites (especially this one) I have come up with a script that returns total and free drive space of all available drives on all servers, sends it to an excel file, naming it todays date, and creates it in the CWD. It also colors space under 500MB red and under 1 GB yellow. Its probly sloppy as I am not a programmer alot of troubleshooting lines have been REMd out. Im still working on it to do other things, but it works. Its a good script for a begginer to learn from too. Im posting it here because I wished someone had done it for me. I hopes it helps someone.
on error resume next
Function FormatThisDate(dtmDate)
Dim strDay
Dim strMonth
Dim strYear
strDay = Day(dtmDate)
strMonth = Month(dtmDate)
strYear = Year(dtmDate)
If Len(strDay) = 1 Then
strDay = "0" & strDay
End If
If Len(strMonth) = 1 Then
strMonth = "0" & strMonth
End If
FormatThisDate = strYear & "-" & strMonth & "-" & strDay
End Function
************CREATE DICTIONARY**********************
Set Computers = CreateObject("Scripting.Dictionary")
Computers.Add "Enter server name here", "enter server path here"
Computers.Add "Enter server name here", "enter server path here"
***************************************************
set XlObj = Wscript.CreateObject("Excel.Application")
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Global Variables
XlSheet = 1
Drives to process
MasterString = "enter drive letter here"
Gig
UpperQuota = 0.50
yellowQuota = 1.00
system variable
Row = 1
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Set objFSO = CreateObject ("Scripting.FileSystemObject")
currentdir = ObjFSO.GetAbsolutePathName ("") & "\"
QuotaFile = currentdir & FormatThisDate(Now()) & ".xls"
Bind to Excel object.
On Error Resume Next
Err.Clear
Set xlobj = CreateObject("Excel.Application")
If Err.Number <> 0 Then
Err.Clear
Wscript.Echo "Excel application not found."
Wscript.Quit
End If
On Error GoTo 0
Create a new workbook.
xlobj.Workbooks.Add
Set objSheet = xlobj.ActiveWorkbook.Worksheets(xlsheet)
objSheet.Name = "User Groups"
XlObj.Sheets(XlSheet).Activate
objSheet.Cells(Row, 1).Value = "Server"
objSheet.Cells(Row, 1).Font.Bold = True
objSheet.Cells(Row, 1).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 2).Value = "Drive"
objSheet.Cells(Row, 2).Font.Bold = True
objSheet.Cells(Row, 2).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 3).Value = " Total "
objSheet.Cells(Row, 3).Font.Bold = True
objSheet.Cells(Row, 3).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 4).Value = " Free "
objSheet.Cells(Row, 4).Font.Bold = True
objSheet.Cells(Row, 4).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 5).Value = "Priv"
objSheet.Cells(Row, 5).Font.Bold = True
objSheet.Cells(Row, 5).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 6).Value = "Pub"
objSheet.Cells(Row, 6).Font.Bold = True
objSheet.Cells(Row, 6).Interior.Color = &H00C0C0C0
Row = Row + 2
For Each Computer In Computers.keys
Wscript.Echo Computer
Set objWMIService = GetObject("winmgmts://" & Computer)
Set colLogicalDisk = objWMIService.InstancesOf("Win32_LogicalDisk")
For Each objLogicalDisk In colLogicalDisk
DriveName = objLogicalDisk.Name
Wscript.Echo Computer & "-" & DriveName
if (Instr(MasterString, DriveName) > 0) and (objLogicalDisk.FreeSpace <> vbNull) then
FreeGB = Round((objLogicalDisk.FreeSpace / (1024*1024*1024)),2)
FreeGBColor = FreeGB
If FreeGB < 1.0 Then
FreeGB = (FreeGB * 100) & "MB"
Else
FreeGB = FreeGB & "GB"
end if
objSheet.Cells(Row, 1).Value = Computer
end if
objSheet.Cells(Row, 1).Font.Size = 8
objSheet.Cells(Row, 1).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 1).Font.Bold = True
objSheet.Cells(Row, 2).Value = " " & DriveName
objSheet.Cells(Row, 2).Interior.Color = &H00C0C0C0
objSheet.Cells(Row, 2).Font.Bold = True
if (FreeGBColor >= yellowQuota) then
wscript.echo FreeGBColor & " is >= " & yellowQuota
objSheet.Cells(Row,4).Interior.Color = &H00C0C0C0
objSheet.Cells(Row,4).Font.Color = &H0FF00000
objSheet.Cells(Row,4).Font.Bold = True
end if
if (FreeGBColor >= Upperquota) and (FreeGBColor < yellowQuota) then
wscript.echo FreeGBColor & " is <= " & yellowQuota & " and > " & UpperQuota
objSheet.Cells(Row,4).Interior.Color = &H00C0C0C0
objSheet.Cells(Row,4).Font.ColorIndex = 6
objSheet.Cells(Row,4).Font.Bold = True
end if
if (FreeGBColor < Upperquota) then
Wscript.Echo "Current Drive is below quota."
objSheet.Cells(Row,4).Interior.Color = &H00C0C0C0
objSheet.Cells(Row,4).Font.Color = &H000000FF
objSheet.Cells(Row,4).Font.Bold = True
end if
objSheet.Cells(Row,4).Value = FreeGB
TotalSize = Round((objLogicalDisk.Size / (1024*1024*1024)),2)
If TotalSize < 1.0 Then
TotalSize = (TotalSize * 100) & "MB"
Else
TotalSize = TotalSize & "GB"
end if
objSheet.Cells(Row,3).Value = TotalSize
objSheet.Cells(Row,3).Font.Bold = True
Row = Row + 1
Wscript.Echo Computer & " " & objLogicalDisk.DeviceID & " " & FreeGB
else
Wscript.Echo "Instr didnt get called! for " & Computer & " " & DriveName
end if
next
---------------------Mapping network drives-------------------------------------
if (computers.Item(Computer) <> "NULL") then
wscript.echo computers.Item(Computer)
wscript.echo "Attempting to insert data into row " & Row
objSheet.Cells(Row,1).Value = computers.Item(Computer)
Else
wscript.echo "No Mapping required"
end if
--------------------------------------------------------------------------------
Row = Row + 2
next
Save the spreadsheet and close the workbook.
xlobj.ActiveWorkbook.SaveAs QuotaFile
xlobj.ActiveWorkbook.Close
Quit Excel.
xlobj.Application.Quit
Wscript.Quit 0
