Obtaining VMware Guest Disk Free Space for NFS Sizing

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):

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

>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):

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:

################################################
# 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!