Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Disk Space Report


Disk Space Report

Author
Message
Aditya Kota
Aditya Kota
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 473
Comments posted to this topic are about the item Disk Space Report
RJ Kelly
RJ Kelly
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 165
Thanks for posting this, I used to have something very similar that has one more for loop to read servers from a text file. Here is my stab at it the powershell way:

#drspacev2.ps1
#Iterate a list of servers (src:a text file in d:\ps_scripts\servers.txt) through the WMI interface in windows.
#Collect a list of stats on all local drives for each server and store data
#in a csv file pipe"|" delimited at c:\temp\freespace.csv
New-Item c:\temp\Freespace.csv -type file -force | out-null;
$srvr = Get-Content C:\Temp\Servers.txt; foreach($row in $srvr) {
Get-WMIObject Win32_LogicalDisk -filter "DriveType=3" -computer $row| Select SystemName,DeviceID,VolumeName,size,freespace | foreach{$_.DeviceID}{$row+"|"+$_.DeviceID+"|"+$_.size/1mb+"|"+$_.freespace/1mb+"|FileSpace"|add-content c:\temp\Freespace.csv}}



This will iterate a text file named "Servers.txt" and grab all the freespace for "FIXED" drives, for each server in the text file. I even took this another step further creating a FormatFile, importing the data, and creating a conditionalized "Freespace" report in SSRS. (Conditionalized meaning the cells are colored red, lt. yellow, and blue depending on the amount of free space.) If anyone is interested I can write this up. Thanks again for your script aditya!
LSCIV
LSCIV
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 154
Can the .vbs script query/display the drive letter instead of the drive name? Currently it's only displaying the drive name (optional in windows) which for most of our drives is null. thnx.

lc
Aditya Kota
Aditya Kota
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 473
Your Script Looks much more formatted!

Thanks again for clicking my first article!
Aditya Kota
Aditya Kota
Right there with Babe
Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)Right there with Babe (736 reputation)

Group: General Forum Members
Points: 736 Visits: 473
It can display the drive letter...

Chk this out for the Win32_Volume WMI Event

http://www.it-visions.de/Scripting/WMIReferenz.asp?C_Klasse=Win32_Volume
RJ Kelly
RJ Kelly
Old Hand
Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)Old Hand (322 reputation)

Group: General Forum Members
Points: 322 Visits: 165
There is also a very good utility to know what properties can be pulled. Check out the WMI browser from MS.
http://www.microsoft.com/downloads/details.aspx?familyid=6430F853-1120-48DB-8CC5-F2ABDC3ED314&displaylang=en
llgreen
llgreen
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 174
I have a similar vbscript to the one RJ provided that pulls a list of servers from a tbl I maintain on our sql boxes to track os and sql updates, ram, etc. The script loops through each server and returns the space infromation and loads it to a second tbl in the db with the server id so the space tbl can be joined to the server tbl. The script worked really well but my DBA has since become a powershell fanatic and replaced it.


Option Explicit

Dim oServerList, oSpaceTable, strServer, strServerID,strFreeSpace,strusedSpace, strDiskSize, strHDD, strTextFilePath, txt
Dim objWMIService, objItem, colItems,objFSO,objTextFile

'open our connections
set oServerList=createobject("adodb.recordset")
oServerList.open "SELECT * FROM [tblServer] WHERE localmonitor = 0" , "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Administration;Data Source=SQLDISTRIB;Application Name=DiskSpace Monitor"

set oSpaceTable=createobject("adodb.connection")
oSpaceTable.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Administration;Data Source=SQLDISTRIB;Application Name=DiskSpace Monitor"

' set the path for the error log file
strTextFilePath = "D:\CommonTools\Scripts\Log\errorlog.txt"
txt = "ServerID" & vbtab & "Server" & vbtab & "Error Description" & vbcrlf

' loop through or sql server tbl to get the server names we will need to go after.
Do While Not oServerList.EOF
'WScript.Echo oServerList("server").Value
strServer = oServerList("Server")
strServerID = oServerList("ServerID")
'msgbox strserver & " " & strServerID

' turn on our error handling
On Error Resume Next

Set objWMIService = GetObject("winmgmts:\\" & strServer & "\root\cimv2")

If Err.Number <> 0 Then
txt = txt & strServerID & vbtab & strServer & vbtab & Err.Description & vbcrlf

oServerList.MoveNext

Else

Set colItems = objWMIService.ExecQuery("Select * from Win32_LogicalDisk WHERE DriveType=3")
For Each objItem in colItems

'pctFreeSpace = INT((objItem.FreeSpace / objItem.Size) * 1000)/10
strDiskSize = Int(objItem.Size)
strFreeSpace = Int(objItem.FreeSpace)
strUsedSpace = Int(objItem.Size-objItem.FreeSpace)
strHDD = Replace(objItem.Name,":","")


oSpaceTable.execute "INSERT INTO tblServerSpace ([serverid],[hdd],[totalsize],[freespace],[usedspace]) VALUES ('"& strServerID & "','" & strHDD & "','" & strDiskSize & "','" & strFreeSpace & "','" & strusedSpace & "')"
Next

End If

oServerList.MoveNext

Loop


Set oServerList = Nothing


' write the error log to teh file.
SET objFSO = createobject("Scripting.FileSystemObject")
SET objTextFile = objFSO.CreateTextFile(strTextFilePath)
objTextFile.Write(txt)
objTextFile.Close
SET objTextFile = nothing

wscript.echo txt
wscript.quit

Iwas Bornready
Iwas Bornready
SSCrazy Eights
SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)SSCrazy Eights (10K reputation)

Group: General Forum Members
Points: 9972 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search