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 http://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:
# http://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!

Comments

  1. 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 (http://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

  2. 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 (http://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

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

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

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

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

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

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

  7. 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”) }

  8. 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”) }

Trackbacks

  1. […] Townsend has a good post at VMtoday.com about using PowerShell and SQL queries to determine the amount of free space within guest VMs. As he states in his post, this can often […]

  2. […] Townsend has a good post at VMtoday.com about using PowerShell and SQL queries to determine the amount of free space within guest VMs. As he states in his post, this can often […]

  3. […] wrote about a method for determining guest free disk space using a PowerShell script a couple weeks ago.  Scott Lowe picked up the post on his blog last […]

  4. […] wrote about a method for determining guest free disk space using a PowerShell script a couple weeks ago.  Scott Lowe picked up the post on his blog last […]

Drop a comment below:

%d bloggers like this: