Generate CSV with Datastore Space Info from multiple VCs and Clusters

[faherne_CTI]

I'm looking to create a script that outputs a table of our hundreds of datastores, showing all the datastores:

• capacity utilisation (%)
• space provisioned (%)

The script needs to query multiple vCenters and would output something along the lines of:

vCenter Name | Cluster Name | Datastore Name | Datastore capacity (GB) | Datastore capacity (%) | Datastore Provisioned (%)

The script below explains what I want to do, but I don't know how to pull the data from both the get-Cluster and get-Datastore commands...

$report = Foreach($vc in $global:DefaultVIServers){

    foreach($dc in Get-Datacenter -Server $vc){

        Get-Cluster -Server $vc | Get-Datastore

        Select @{N='vCenter Name';E={$vc.Name}},

            @{N='Cluster';E={$_.Name}},

            @{N='Datastore Name';E={ds.Name}},

            @{N='Datastore Capacity (GB)';E={ds.CapacityinGB}},

            @{N='Datastore Capacity (%)';E={ds.CapacityinPC}},

            @{N='Datastore Provisioned (%)';E={ds.ProvisionedSpaceinPC}},

    }

}

$report | Export-CSV C:\Scripts\vCenterClusterDatastoreInfo.csv

[LucD]

Try like this.

Not sure what the purpose of Capacity% is, that would be always 100%.
Or do you mean something else?

$report = Foreach($vc in $global:DefaultVIServers){

    foreach($cluster in Get-Cluster -Server $vc){

        Get-VMHost -Location $cluster | Get-Datastore |

        Select @{N='vCenter Name';E={$vc.Name}},

            @{N='Cluster';E={$cluster.Name}},

            @{N='Datastore Name';E={$_.Name}},

            @{N='Datastore Capacity (GB)';E={$_.CapacityGB}},

            @{N='Datastore Provisioned (%)';E={

                "{0:P}" -f (($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB)}}

    }

}

$report | Export-CSV C:\Scripts\vCenterClusterDatastoreInfo.csv

[faherne_CTI]

Hi Luc,

Wow, I really got muddled trying to explain what I needed for "Capacity %".

I was looking for the "Datastore overprovisioned/underprovisioned %" figure for each datastore to identify any that are heavily overprovisioned.

Apologies,

Fin

[LucD · Accepted solution]

No problem, try like this

$report = Foreach($vc in $global:DefaultVIServers){

    foreach($cluster in Get-Cluster -Server $vc){

        Get-VMHost -Location $cluster | Get-Datastore |

        Select @{N='vCenter Name';E={$vc.Name}},

            @{N='Cluster';E={$cluster.Name}},

            @{N='Datastore Name';E={$_.Name}},

            @{N='Datastore Capacity (GB)';E={[math]::Round($_.CapacityGB,0)}},

            @{N='Datastore Commit (GB)';E={[math]::Round(($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB,0)}},

            @{N='Datastore Commit (%)';E={

                "{0:P}" -f (($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/$_.ExtensionData.Summary.Capacity)}

            },

            @{N='Datastore Provisioned (%)';E={

                "{0:P}" -f (($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB)}}

            }

}

$report | Export-CSV C:\Scripts\vCenterClusterDatastoreInfo.csv

[faherne_CTI]

Hi LucD,

Thanks again for the update to the script, but the Datastore Provisioned (%) isn't giving me the figures I'm looking for.

I'm looking to identify the total space provisioned (as opposed to the actual space consumed) by all the thin and thick provisioned VMs as a percentage to the full capacity of the datastore.

[LucD]

Isn't that what the "Datastore Commit (GB)" is showing?

In "Datastore Commit (%)" you can see the eventual over-commit percentage.

The "Datastore Provisioned (%)" is the actual physical disk space that is used (Thick & Thin)

Unless I completely misunderstood your question.
Perhaps a short sample would help in that case.

[faherne_CTI]

Yes, an example might be best:

Datastore Name:            DS01

DS01 Total capacity:     500GB

VMs located on DS01:  VMThick01, VMThick02, VMThick03, VMThin01, VMThin02, VMThin03                        

VMName:               ProvisionedSize:    VMSizeonDS:

VMThick01             100GB                      100GB

VMThick02             100GB                      100GB   

VMThick03             100GB                      100GB

VMThin01              100GB                      10GB

VMThin02              100GB                      20GB

VMThin03              100GB                      30GB

TOTAL                   600GB                      360GB  

DS01 Aggregated Figures:

Total Capacity Provisioned (%):          120% => (600/500)*(100/1)

Total Capacity Utilised (%):                  72%   => (360/500)*(100/1)

[LucD]

What you have named 'Total Capacity Provisioned (%)' is the same as 'Datastore Commit (%)' in the script.

And 'Total Capacity Utilised (%)' is 'Datastore Provisioned (%)'.

Or do you see numbers in there that do not reflect that?

[faherne_CTI]

Hi Luc,

Here's a snippet of the output table. The first 4 columns are all good (except for row 5), but the values in the Data Provisioned column just don't add up. Some of the figures are just odd. We have primarily a thin-provisioned environment, yet no datastore is overprovisioned - This is a mature environment and I would expect most datastores to be overprovisioned, some quite heavily.

Datastore Capacity (GB)	Datastore Commit (GB)	Datastore Commit (%)	Datastore Provisioned (%)
100	81	80.71%	22.00%
2411	2000	82.97%	70.98%
100	80	79.97%	21.03%
2411	2001	83.00%	83.00%
100	114	114.02%	34.52%
2363	1856	78.52%	75.58%
2363	1856	78.52%	75.46%
2363	1806	76.42%	72.34%
532	0	0.00%	0.00%
2363	1764	74.65%	70.79%
2363	1780	75.32%	72.47%

[faherne_CTI]

I'll test on a different group of vCenters and see if the results are different.

[LucD]

I added a column 'Datastore Provisioned (GB)'.

That is the capacity minus the freespace, which should tell you what is provisioned.

Does that number correspond with what you see in the Web Client for a datastore?

$report = Foreach($vc in $global:DefaultVIServers){

    foreach($cluster in Get-Cluster -Server $vc){

        Get-VMHost -Location $cluster | Get-Datastore |

        Select @{N='vCenter Name';E={$vc.Name}},

            @{N='Cluster';E={$cluster.Name}},

            @{N='Datastore Name';E={$_.Name}},

            @{N='Datastore Capacity (GB)';E={[math]::Round($_.CapacityGB,0)}},

            @{N='Datastore Commit (GB)';E={[math]::Round(($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB,0)}},

            @{N='Datastore Commit (%)';E={

                "{0:P}" -f (($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/$_.ExtensionData.Summary.Capacity)}

            },

            @{N='Datastore Provisioned (GB)';E={$_.CapacityGB - $_.FreeSpaceGB}}

            },

            @{N='Datastore Provisioned (%)';E={

                "{0:P}" -f (($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB)}}

            }

}

$report | Export-CSV C:\Scripts\vCenterClusterDatastoreInfo.csv

[faherne_CTI]

Hi Luc,

Firstly I'd like to put my hand up and apologise for an earlier mistake - Out of all of our vCenters that I chose to test the scripts on, I chose the VDI vCenter, where it happens that all Clusters (10+) have thick provisioned VMs. That really threw a spanner in the works.

The second point of confusion is our understanding of the terms "Datastore Commited" and "Datastore Provisioned". My understanding is that:

"Datastore Commit" = Space currently used by VMs on disk/datastore

"Datastore Provisioned" = The maximum size possible for all VMs if fully populated to disk/datastore.

I've changed the labels on the table to "Datastore Used" and "Datasstore Provsioned" to reflect this, and the numbers all add up.

Your earlier scripts were correct. I settled on the final format:

Set-PowerCLIConfiguration -InvalidCertificateAction ignore -confirm:$false

Connect-VIServer -Server (Get-Content C:\Scripts\vC-List.txt) > $null

$report = Foreach($vc in $global:DefaultVIServers){

    foreach($cluster in Get-Cluster -Server $vc){

        Get-VMHost -Location $cluster | Get-Datastore |

        Select @{N='vCenter Name';E={$vc.Name}},

            @{N='Cluster';E={$cluster.Name}},

            @{N='Datastore Name';E={$_.Name}},

            @{N='Datastore Capacity (GB)';E={[math]::Round($_.CapacityGB,0)}},

            @{N='Datastore Space Used (GB)';E={$_.CapacityGB - $_.FreeSpaceGB}},

            @{N='Datastore Space Used (%)';E={"{0:P}" -f (($_.CapacityGB - $_.FreeSpaceGB)/$_.CapacityGB)}},

            @{N='Datastore Provisioned (GB)';E={[math]::Round(($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/1GB,0)}},

            @{N='Datastore Provisioned (%)';E={"{0:P}" -f (($_.ExtensionData.Summary.Capacity - $_.ExtensionData.Summary.FreeSpace + $_.ExtensionData.Summary.Uncommitted)/$_.ExtensionData.Summary.Capacity)}}  

            }

}

$report | Export-CSV D:\Scripts\DatastoreCapacityInfo.csv

[LucD]

No problem, glad the issue got resolved.