Track Server Disk Space Trends for Free

By | October 19, 2009

Well maybe not entirely free. I am making the assumption you have access to the Microsoft Office Suite of Applications which under most scenarios, this is the case.

When trying to make a case for more disk space on your servers, every consultant, teacher or colleague always says “Make sure you have a graph to show the trends of your disk usage.” Well a lot of times that is easier said than done. How are you supposed to do this if you don’t have any monitoring software. Here’s a method I used to trend my disk space usage using vbscript, Microsoft Access and Microsoft Excel.

 

First thing first. We need to make a list of the servers which we wish to track disk usage. The file look similar to this:

———-COPY EVERYTHING BELOW THIS LINE———-

Servername1
Servername2
Servername3
Servername4

———-COPY EVERYTHING ABOVE THIS LINE———-

Replace the text with the names or IP Addresses of your servers

Save this to C:\scripts as servernames.txt

 

Then we need to create a Microsoft Access Database

Create your database at C:\scripts as diskspace.accdb

Create a table called DiskSpace

In this table create the following columns

Field Name Data Type
ID AutoNumber
Server Text
Drive Letter Text
Partition Format Text
TotalSpace Number
FreeSpace Number
DiskType Text
RDate Date/Time

 

Now we need to create a System DSN which the script will access to write the information into the database.
For this, go to Control Panel –> Administrative Tools and open Data Sources (ODBC)
Click on the System Tab and click Add
Create new System DSN and choose Add.
Choose Microsoft Access Driver (*.mdb, *.accdb) and click finish
Under Data Source Name: Type Diskspace
Description can be left blank
Under Database, press Select and browse to C:\Scripts and choose diskspace.accdb
Press OK on the Select Database window
Press OK on ODBC Microsoft Access Setup
Press OK on ODBC Source Administrator

And now we need the script

 

———-COPY EVERYTHING BELOW THIS LINE———-

On Error Resume Next
Dim objConnection
Dim strConnectionString
Dim strSQL
Dim objCommand
Dim objTextFile
Const ForReading = 1

dt = Replace(Date,”/”,”-“)
strConnectionString = “Provider=MSDASQL;DSN=Diskspace;UID=;PWD=;”

Set objFSO = CreateObject(“Scripting.FileSystemObject”)
Set objTextFile = objFSO.OpenTextFile(“C:\Servers.txt”, ForReading)
Do Until objTextFile.AtEndOfStream
strComputer = objTextFile.Readline

Set objWMIService = GetObject(“winmgmts:” _
& “{impersonationLevel=impersonate}!\\” _
& strComputer & “\root\cimv2”)
Set colDisks = objWMIService.ExecQuery _
(“Select * from Win32_LogicalDisk”)

For Each objDisk in colDisks
If objDisk.DriveType = 3 Then
strSQL = “INSERT INTO DiskSpace ( Server, DriveLetter, PartitionFormat, TotalSpace, FreeSpace, DiskType, RDate ) VALUES (‘”& strComputer & “‘, ‘” & objDisk.DeviceId & “‘, ‘” & objDisk.FileSystem & “‘, ‘” & objdisk.size /1074000000 & “‘, ‘” & objDisk.FreeSpace /1074000000 & “‘, ‘” & objDisk.DriveType & “‘, ‘” & dt & “‘);”
Set objConnection = CreateObject(“ADODB.Connection”)
objConnection.ConnectionString = strConnectionString
objConnection.Open
Set objCommand = CreateObject(“ADODB.Command”)
With objCommand
.ActiveConnection = objConnection
.CommandText = strSQL
End With
objCommand.Execute
Set objCommand = Nothing
objConnection.Close
Set objConnection = Nothing

End If
Next

Loop

———-COPY EVERYTHING ABOVE THIS LINE———-

Make sure no word wrapping has taken place.

Save this as UpdateDS.vbs in C:\Scripts

 

Now all you have to do is run the script and it will populate the database tables with the disk space information.

Either you can remember to run this script at the intervals you wish or you can run this as a scheduled task. If you do run it as a scheduled task, make sure you run it with administrative credentials on the remote machines otherwise it will not return any results.

Now to view the information you need to create a Microsoft Excel Pivot Chart.

Open Microsoft Excel

Go to the data tab and select “From Access”
Navigate to C:\Scripts and select DiskSpace.accdb and choose open
In the select table dialog, choose DiskSpace (Table)
In the Import Data Window, Choose PivotChart and PivotTable.
Press OK.

In the field list,
drag Server and DriveLetter into Report Filter
drag RDate into Axis Fields (Categories)
drag FreeSpace and TotalSpace into Values

This will give you the basis of what you need to start to see your data. From here you will want to format, label or filter your graph how you wish in order to get the display results you are trying to get across.

Here is an example of what the chart might look like:

dspc

As you can see I chose a line graph to show total disk space and free disk space trend over the first 4 months of the year.

 

There you have it. Sure it might take a little work but this is a great way to anticipate when you will run out of disk space and a great tool to better explain to the people hanging out the money that you need to buy more disk space.

 

NOTE: This does not work on x64 operating systems. You can run this against x64 systems but you cannot execute the script from an x64 machine due to the lack of a Microsoft Access x64 driver. THIS MUST BE RAN FROM 32 BIT WINDOWS.

Also, these instructions were created for a Windows XP Pro machine using Microsoft Office 2007. If using different versions of Windows and Microsoft Office, you may need to modify some of these steps.

 

 

THIS INFORMATION IS PROVIDED AS IS WITH NO WARRANTIES EXPRESSED OR IMPLIED.

Leave a Reply

Your email address will not be published. Required fields are marked *