VMtoday

VMware News, Views, and How-To's from vExpert Josh Townsend

You are here: Home / VMware / PowerCLI / Obtaining VMware Guest Disk Free Space for NFS Sizing

Obtaining VMware Guest Disk Free Space for NFS Sizing

by 17 Comments

I am often asked about sizing storage vis-à-vis how much free space within a guest VMDK eats into the overall size of the volume.  The answer can be drastically whether we are dealing with thick-provisioned VMDK’s on FC or iSCSI LUN’s, or thin provisioned VMDK’s on NFS volumes.  The amount of free space present in guest VMDK’s also comes into effect when calculating the impact of dedupe on the volume.  Add in some flexible volumes on NetApp storage and the amount of provisioned storage in the design changes significantly.

There are several methods of obtaining the amount of free space in a guest OS, from third party systems management tools (some are vendor or OS specific) to custom scripting (some VB for your Windows hosts, etc.).  VirtualCenter also knows how much free space is within each guest VMDK, but the information is not readily displayed.

The first method of getting guest free space is using the VMware VI Toolkit for Windows.  A simple statement like what I show below will pull the info for you (see Hal Rottenberg’s post on the VMware Communities Forum):

?
Code below ↓
1
2
3
$hdCapacity = @{ N = "Capacity (bytes)"; E = { $_.Guest.Disks | % { $_.Capacity } } }
$hdFreeSpace = @{ N = "FreeSpace (bytes)"; E = { $_.Guest.Disks | % { $_.FreeSpace } } }
Get-VM | select Name, $hdCapacity, $hdFreeSpace

You can re-write the command into a single line and change the output to show Percentage Free space, for example.  The following came from https://communities.vmware.com/message/1046360.

?
Code below ↓
1
>Get-VM | Where { $_.PowerState -eq "PoweredOn" } | Get-VMGuest | Select VmName -ExpandProperty Disks | Select VmName, Path, @{ N="PercFree"; E={ [math]::Round( (100 * ( $_.FreeSpace / $_.Capacity ) ),0 ) } } | Sort PercFree

These examples are well and good, but there are a couple of catches – The guest must be powered up and VMware Tools must be installed and running inside each VM you want to pull statistics from.
Many people do not know that the guest disk capacity and free space statistics are also captured in the VirtualCenter database and is available for VM’s in any power state (On, Off, or Suspended), so long as VMware Tools has been installed and run at least once in the VM.  A SQL simple query will return the data too you (this could be more simple – I was pulling some additional statistics for a project I am working on):

?
Code below ↓
1
2
3
SELECT VPX_GUEST_DISK.VM_ID, VPX_GUEST_DISK.PATH, VPX_GUEST_DISK.CAPACITY, CONVERT(bigint, VPX_GUEST_DISK.CAPACITY) / 1048576 AS 'Capacity MB', VPX_GUEST_DISK.FREE_SPACE, CONVERT(bigint, VPX_GUEST_DISK.FREE_SPACE) / 1048576 AS 'Free Space MB',VPX_HOST.ID, VPX_HOST.DATACENTER_ID, VPX_HOST.DNS_NAME, VPX_VM.ID, VPX_VM.DATACENTER_ID, VPX_VM.FILE_NAME, VPX_VM.LOCAL_FILE_NAME, VPX_VM.POWER_STATE, VPX_VM.GUEST_OS, VPX_VM.GUEST_STATE, VPX_VM.MEM_SIZE_MB, VPX_VM.NUM_DISK, VPX_VM.DNS_NAME, VPX_VM.IS_TEMPLATE, VPX_VM.HOST_ID
FROM VPX_GUEST_DISK VPX_GUEST_DISK, VPX_HOST VPX_HOST, VPX_VM VPX_VM
WHERE VPX_VM.ID = VPX_GUEST_DISK.VM_ID AND VPX_HOST.ID = VPX_VM.HOST_ID

Of course, you can always combine the power of PowerShell and the raw SQL data to create formatted output.  Frank Hagen displays a method of obtaining SQL data through a PowerShell script and dumping the data to Excel for further manipulation on his blog.  I modified a few lines of Frank’s code for my purposes:

?
Code below ↓
001
002
003
004
005
006
007
008
009
010
011
012
013
014
015
016
017
018
019
020
021
022
023
024
025
026
027
028
029
030
031
032
033
034
035
036
037
038
039
040
041
042
043
044
045
046
047
048
049
050
051
052
053
054
055
056
057
058
059
060
061
062
063
064
065
066
067
068
069
070
071
072
073
074
075
076
077
078
079
080
081
082
083
084
085
086
087
088
089
090
091
092
093
094
095
096
097
098
099
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
################################################
# QuerySQL.ps1 # Frank W Hagen - 2008/07/15
# # FWHagen.wordpress.com
# Powershell script to query a SQL database # fwhagen.blog@gmail.com
# and write the output to an Excel file
#
# Usage:
# * Create a SQL query file by putting a valid SQL query in a text file in
# the subdirectory specified in $SQLQueryPath named <$TaskName>.sql
# * Set configuration variables in config section below
# * Run at command line: powershell -nologo .QuerySQL.ps1
# OR right-click this script and Open With -> Powershell.EXE
#
# If you get a security warning running the script, see the following post:
# https://fwhagen.wordpress.com/2007/10/29/running-local-powershell-scripts/
########################################################
 
# Function used for binding to Excel
function Invoke([object]$m, [string]$method, $parameters)
{ $m.PSBase.GetType().InvokeMember($method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters, [System.Globalization.CultureInfo]"en-US") }
 
############################################
### Configuration information for specific query #####
$TaskName = "VIGuestDiskFree" # Title and name of query file
 
#######################################################
### UPDATE THE FOLLOWING LINES FOR YOUR ENVIRONMENT ###
$SqlServer = ""; # SQL Server hosting VirtualCenter Database (include instance name if not using Default)
$SqlCatalog = ""; # Virtual Center Database name
$SQLUserID = ""; # SQL Server User ID if not using Integrated Security see lines 69 and 70
$SQLPassword = ""; # SQL Server User Password if not using Integrated Security see lines 69 and 70
### END OF CUSTOMER UPDATABLE FIELDS ###
#######################################################
$WriteOutXML = $False
$WriteOutCSV = $False
$WriteOutXLS = $True
#
# Environment Configuration
$TaskPath = "C:Scripts" # Root Directory for creating reports
$SQLQueryPath = "SQLQueries" # Subdirectory for finding the queryfile
######################################################
 
# Timestamp the output folder and files using ISOdate
$OutPath = ($TaskPath + (Get-Date -Format yyyyMMdd) + "-" + $TaskName + "")
$OutFileName = ( (Get-Date -Format yyyyMMdd) + "-" + $TaskName )
 
# Create the output folder #TODO: Fix Call to eliminate verbose results from system
if (!$(test-path ($OutPath)))
{
New-Item -itemType directory -Name ((Get-Date -Format yyyyMMdd) + "-" + $TaskName) > $null
 
if ($(test-path ($OutPath)))
{
Write-Host ($OutPath + " Created") -ForegroundColor "darkgreen"
}
else
{
Write-Host ($OutPath + " FAILED") -ForegroundColor "red"
}
}
 
# Get the T-SQL Query from .SQL file
$SqlQuery = Get-Content ($TaskPath + $SQLQueryPath + $TaskName + ".sql")
Write-Host ("Executing Queryfile: " + ($TaskName + ".sql") + " ") -ForegroundColor "darkgreen"
#Write-Host ($SqlQuery) -ForegroundColor "gray"
 
# Setup SQL Connection (using Integrated Security (your workstation login). Use standard connection string format for other)
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
#$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = True"
$SqlConnection.ConnectionString = "Server = $SqlServer; Database = $SqlCatalog; Integrated Security = False; User ID = $SQLUserID; Password = $SQLPassword;"
 
# Setup SQL Command
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
# Setup .NET SQLAdapter to execute and fill .NET Dataset
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$DataTable = New-Object System.Data.DataTable
 
# Execute and Get Row Count
$nRecs = $SqlAdapter.Fill($DataSet)
 
Write-Host ($nRecs.ToString() + " Records retrieved.") -ForegroundColor "Blue"
$SqlConnection.Close();
 
if ($nRecs -gt 0)
{
# Make copy of successful query in output directory for traceability
if ($(test-path ($OutPath + $OutFileName + ".sql")))
{
del ($OutPath + $OutFileName + ".sql")
}
Copy-Item ($TaskPath + $SQLQueryPath + $TaskName + ".sql") -destination ($OutPath + $OutFileName + ".sql")
 
# Very simple to export XML
if($WriteOutXML)
{
Write-Host "Creating XML File..." -ForegroundColor "darkgreen"
if ($(test-path ($OutPath + $OutFileName + ".xml")))
{
del ($OutPath + $OutFileName + ".xml")
}
 
$DataSet.Tables[0].WriteXML($OutPath + $OutFileName + ".xml");
}
 
# Very simple to export CSV
if($WriteOutCSV)
{
Write-Host "Creating CSV File..." -ForegroundColor "darkgreen"
if ($(test-path ($OutPath + $OutFileName + ".csv")))
{
del ($OutPath + $OutFileName + ".csv")
}
 
$DataSet.Tables[0] | Export-Csv ($OutPath + $OutFileName + ".csv")
}
 
# Very hard to export XSL - This method writes the data to an object array and pastes the array directly into Excel (Thanks go to a few sources on the Internet for this method)
if($WriteOutXLS)
{
Write-Host "Creating Excel File..." -ForegroundColor "darkgreen"
if ($(test-path ($OutPath + $OutFileName + ".xls")))
{
del ($OutPath + $OutFileName + ".xls")
}
 
$sheetIndex = 0;
$oExcel = New-Object -COM Excel.Application
$oExcel.Visible = $false
$oBooks = $oExcel.Workbooks
$oCulture= [System.Globalization.CultureInfo]"en-US"
$oBook=$oBooks.psbase.gettype().InvokeMember("Add",[Reflection.BindingFlags]::InvokeMethod,$null,$oBooks,$null,$oCulture)
#$oSheet = $oBook.Worksheets.Item(1)
 
$DataTable = $DataSet.Tables[0];
$nDr = $DataTable.Rows.Count + 1
$nDc = $DataTable.Columns.Count + 1
 
# Create the object array
$rawData = new-object 'object[,]' $nDr,$nDc
 
# Write the field names in the first row
for ($col = 0; $col -lt $DataTable.Columns.Count; $col++)
{
$rawData[0, $col] = $DataTable.Columns[$col].ColumnName;
}
 
# Copy the dataset to the object array
for ($col = 0; $col -lt $DataTable.Columns.Count; $col++)
{
for ($row = 0; $row -lt $DataTable.Rows.Count; $row++)
{
$rawData[($row + 1), $col] = $DataTable.Rows[$row][$col];
}
}
 
# Calculate the final column letter
$finalColLetter = "";
$colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
$colCharsetLen = $colCharset.Length;
if ($DataTable.Columns.Count -gt $colCharsetLen)
{
$finalColLetter = $colCharset.Substring((($DataTable.Columns.Count - 1) / ($colCharsetLen - 1)), 1);
}
$finalColLetter += $colCharset.Substring(($DataTable.Columns.Count - 1) % $colCharsetLen, 1);
 
### Export it all to Excel #####
Write-Host "Writing to Excel..." -ForegroundColor "darkgreen"
 
# Create a new Sheet
$excelSheet = $oBook.Worksheets.Item(1)
 
#$excelSheet.name = $DataTable.TableName; #TODO: Be nice to figure out how to make this work (not critical)
 
# Create the entire range on the worksheet and dump the data into it
$excelRange = "A1:" + $finalColLetter + "" + ($DataTable.Rows.Count + 1)
$excelSheet.Range($excelRange).FormulaLocal = $rawData;
 
# Mark the first row as BOLD #TODO: Be nice to figure out how to make this work (not critical)
#$excelSheet.Rows[1].Font.Bold = $True;
#$excelSheet.Cells.Item(1,1).Font.Bold = $True;
 
# Save the Excel file and we're done
Invoke $oBook SaveAs ($OutPath + $OutFileName + ".xls") > $null
Invoke $oBook Close 0 >$null
$oExcel.Quit()
}
}
 
Write-Host ("Complete")

I dropped the PowerShell code into a file named VIGuestDiskFree.ps1 in C:Scripts on my Vista laptop, and the SQL query from earlier in this post to c:scriptssqlqueriesVIGuestDiskFree.sql.  Simply run the PowerShell script from a PowerShell command line.  The Excel output file  will be created in a date-stamped folder in C:Scripts.  Once you have this data you can go about the business of formatting the data as an Excel Table and summing the Free Space column to find out your storage savings with thin provisioning and other storage efficiency technologies.  I have attached a .zip (scripts) containing the scripts in this post (formatting is a bit off in WordPress).

How do you report on your storage utilization and storage efficiency efforts? Post a comment to share your methods!

Reader Interactions

Comments

  1. Joshua Townsend says

    Thanks for the pingback, Scott. You are correct on there being a vCenter plugin for obtaining free disk space – Rich Garsthagen wrote a plug-in called VCPlus that accomplishes this (https://www.run-virtual.com/?page_id=145). I used it for a while a few years back and had mixed results (some VM’s were just not being reported on). Rich and I emailed back and forth a few times trying to figure it out but I dropped off as new projects demanded time. VCPlus also offers a few other neat options such as showing snapshot status and syncing the DNS name with the VC display name for each guest. Rich also has a sample script for the VI Perl Toolkit called VMDiskFree that can report on guest free space.

    Josh

    Reply

  2. Joshua Townsend says

    Thanks for the pingback, Scott. You are correct on there being a vCenter plugin for obtaining free disk space – Rich Garsthagen wrote a plug-in called VCPlus that accomplishes this (https://www.run-virtual.com/?page_id=145). I used it for a while a few years back and had mixed results (some VM’s were just not being reported on). Rich and I emailed back and forth a few times trying to figure it out but I dropped off as new projects demanded time. VCPlus also offers a few other neat options such as showing snapshot status and syncing the DNS name with the VC display name for each guest. Rich also has a sample script for the VI Perl Toolkit called VMDiskFree that can report on guest free space.

    Josh

    Reply

  3. Brendan says

    Any idea what VMware vCenter and SQL platforms/releases are associated with SQL query above?

    We’re trying to run against vCenter 4.1, SQL 2008 and getting syntax errors. Our presumption is that the DB schema may be different thus throwing an error.

    Reply

    • Joshua Townsend says

      Brendan – I don’t know off the top of my head, but your timing couldn’t be better. I was just about to try this query against my 4.1/SQL 2008 setup. I’ll let you know what I discover.

      Reply

  4. Brendan says

    Any idea what VMware vCenter and SQL platforms/releases are associated with SQL query above?

    We’re trying to run against vCenter 4.1, SQL 2008 and getting syntax errors. Our presumption is that the DB schema may be different thus throwing an error.

    Reply

    • Joshua Townsend says

      Brendan – I don’t know off the top of my head, but your timing couldn’t be better. I was just about to try this query against my 4.1/SQL 2008 setup. I’ll let you know what I discover.

      Reply

  5. Brendan says

    Had one of our SQL query gurus take a look and we had to make the following adjustments for this platform.

    SELECT VPX_GUEST_DISK.VM_ID, VPX_GUEST_DISK.PATH, VPX_GUEST_DISK.CAPACITY,
    CONVERT(bigint, VPX_GUEST_DISK.CAPACITY) / 1048576 AS ‘CAPACITY_MB’, VPX_GUEST_DISK.FREE_SPACE,
    CONVERT(bigint, VPX_GUEST_DISK.FREE_SPACE) / 1048576 AS ‘FREE_MB’, VPX_HOST.ID,
    VPX_HOST.DATACENTER_ID, VPX_HOST.DNS_NAME, VPX_VM.ID, VPX_VM.DATACENTER_ID, VPX_VM.FILE_NAME,
    VPX_VM.LOCAL_FILE_NAME, VPX_VM.POWER_STATE, VPX_VM.GUEST_OS, VPX_VM.GUEST_STATE, VPX_VM.MEM_SIZE_MB,
    VPX_VM.NUM_DISK, VPX_VM.DNS_NAME, VPX_VM.IS_TEMPLATE, VPX_VM.HOST_ID
    FROM [database_name].[dbo].VPX_GUEST_DISK, [database_name].[dbo].VPX_HOST, [database_name].[dbo].VPX_VM
    WHERE [database_name].[dbo].VPX_VM.ID = [database_name].[dbo].VPX_GUEST_DISK.VM_ID AND [database_name].[dbo].VPX_HOST.ID = [database_name].[dbo].VPX_VM.HOST_ID

    Reply

  6. Brendan says

    Had one of our SQL query gurus take a look and we had to make the following adjustments for this platform.

    SELECT VPX_GUEST_DISK.VM_ID, VPX_GUEST_DISK.PATH, VPX_GUEST_DISK.CAPACITY,
    CONVERT(bigint, VPX_GUEST_DISK.CAPACITY) / 1048576 AS ‘CAPACITY_MB’, VPX_GUEST_DISK.FREE_SPACE,
    CONVERT(bigint, VPX_GUEST_DISK.FREE_SPACE) / 1048576 AS ‘FREE_MB’, VPX_HOST.ID,
    VPX_HOST.DATACENTER_ID, VPX_HOST.DNS_NAME, VPX_VM.ID, VPX_VM.DATACENTER_ID, VPX_VM.FILE_NAME,
    VPX_VM.LOCAL_FILE_NAME, VPX_VM.POWER_STATE, VPX_VM.GUEST_OS, VPX_VM.GUEST_STATE, VPX_VM.MEM_SIZE_MB,
    VPX_VM.NUM_DISK, VPX_VM.DNS_NAME, VPX_VM.IS_TEMPLATE, VPX_VM.HOST_ID
    FROM [database_name].[dbo].VPX_GUEST_DISK, [database_name].[dbo].VPX_HOST, [database_name].[dbo].VPX_VM
    WHERE [database_name].[dbo].VPX_VM.ID = [database_name].[dbo].VPX_GUEST_DISK.VM_ID AND [database_name].[dbo].VPX_HOST.ID = [database_name].[dbo].VPX_VM.HOST_ID

    Reply

  7. Richard says

    Is there a change that needs to be made to the function used for binding Excel to support 2010?
    # Function used for binding to Excel
    function Invoke([object]$m, [string]$method, $parameters)
    { $m.PSBase.GetType().InvokeMember($method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters, [System.Globalization.CultureInfo]”en-US”) }

    Reply

  8. Richard says

    Is there a change that needs to be made to the function used for binding Excel to support 2010?
    # Function used for binding to Excel
    function Invoke([object]$m, [string]$method, $parameters)
    { $m.PSBase.GetType().InvokeMember($method, [Reflection.BindingFlags]::InvokeMethod, $null, $m, $parameters, [System.Globalization.CultureInfo]”en-US”) }

    Reply

Trackbacks

Drop a comment below:

This site uses Akismet to reduce spam. Learn how your comment data is processed.