Monthly Automation - Get vCPU's, GB of Memory, and Used Storage

[tsquare]

Hello all!

I was referred to using PowerCLI to achieve a task, however vCloud Automation Center may be able to complete this as well.

On a monthly basis I have a requirement to pull the vCPU's, GB of Memory and used storage for all VM's in a resource pool, seperated by sub resource pool.

For example there may be a Resource Pool called Finance and Sub Resource pools called Great Plains, and File servers.  I need to report separately on Great Plain and the File Servers so they can determine how much of the infrastructure is set aside for each component.  After gathering each of these I would like to push those results into a SQL database to archive the history, ideally I would like like the resource pool name, server names and counts all injected into this SQL database.

Now would PowerCLI possibly be the best mechanism for this or should I look into vCloud Automation Center so that I can pull this into a separate database.

Any help on this would be greatly appreciated.

[LucD]

This is perfectly possible with PowerCLI :smileycool:

You can gather the VMs in each of these resourcepools with the Get-VM cmdlet.

Something like

$vmsFinance = Get-ResourcePool -Name Finance | Get-VM

You didn't specify if you want to collect the assigned resources, or the actual usage.

The allocated resources are mostly available in the VirtualMachine objects returned by Get-VM.

If you want to collect the actual usage, you will need to use the Get-Stat cmdlet.

You could have a look at my Statistics posts to get an idea what is possible.

And of course there are many examples of both available in this community.

[tsquare]

Ah sorry for that.  Yes I need to collect allocated vCPU's and allocated GB of Memory, however I do need the used storage vs the assigned storage.

I will review your Statistics post to determine which pieces I can use out of there.  At quick glance I see you have it exporting to CSV, do you have any methods to get that data into SQL directly?  I am sure I could gather a import utility for CSV into SQL.

[LucD]

That is perfectly possible, see for example Use PowerShell to Collect Server Data and Write to SQL

That post shows one way of writing date to a SQL table.

[tsquare]

Actually I am making quite a bit of progress so far!  Current status, I can pull the vm's in the top level resource pool Finance and inject them into SQL using the following.  however the results of the get-resourcepool -name Finance | get-vm displays the Num CPUs with a space and I am having a hard time getting a variable to work for that.  The other thing I would like is the resource pool displayed in the query so the top level columns would be; ResourcePool, Name, PowerState (not needed but ok), MemoryGB, and NumCPUs

[LucD]

Perhaps you could include some of the code you have, so we can have a look ?

[tsquare]

Add-PSSnapin VMware.VimAutomation.Core
Add-PSSnapin SqlServerCmdletSnapin100
Add-PSSnapin SqlServerProviderSnapin100

$db_server = "127.0.0.1\VIM_SQLEXP"
$db = "Billing"
$table = "vmdata"
$username = "import"
$pwd = "myPassWord"

# First, clear existing table
$sql_query_del = "DELETE FROM $table"
Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query_del

# Get vms and resources add to DB
Get-resourcepool -name finance | get-vm | foreach {
    $name = $_.Name
    $PowerState = $_.PowerState
    $NumCPUs = $_.'Num CPUs'
    $MemoryGB = $_.MemoryGB   
    Write-Host " Name : $Name       PowerState : $PowerState       MemoryGB : $MemoryGB       Num CPUs : $NumCPUs"

    $sql_query = "INSERT INTO $table (Name, PowerState, MemoryGB, NumCPUs) VALUES ('$Name', '$PowerState', '$MemoryGB', '$NumCPUs')"
    Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query

}

[tsquare]

Also started a thread here, in response to the powershell var issue I am facing currently.

powershell - PowerCLI and SQL import issue with var - Stack Overflow

[RvdNieuwendijk]

I answered your question on StackOverflow.

[tsquare]

Thank you!  That did the trick for the var issue.  Now I just need to get the rest of this squared away.

[LucD]

You could adapt your central loop something like this

foreach($rp in Get-resourcepool){

    get-vm -Location $rp | foreach {

        $name = $_.Name

        $PowerState = $_.PowerState

        $NumCPUs = $_.NumCPU

        $MemoryGB = $_.MemoryGB

        Write-Host " ResourcePool : $($rp.Name)  Name : $Name       PowerState : $PowerState       MemoryGB : $MemoryGB       Num CPUs : $NumCPUs"

    $sql_query = "INSERT INTO $table (ResourcePool, Name, PowerState, MemoryGB, NumCPUs) VALUES ('$($rp.Name)', '$Name', '$PowerState', '$MemoryGB', '$NumCPUs')"

    Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query

}

[tsquare]

where do you suggests this starts based upon the original?

[tsquare]

Unexpected token 'in' in expression or statement.

At C:\users\default\Desktop\VM2.ps1:16 char:58

+ Get-resourcepool -name finance| get-vm | foreach($rp in <<<<  Get-resourcepool){

    + CategoryInfo          : ParserError: (in:String) [], ParseException

    + FullyQualifiedErrorId : UnexpectedToken

[kunaludapi]

Replace the line of the code with below

foreach($rp in (Get-resourcepool)) {

[tsquare]

This is what I currently have, and the error is 'in', again I am looking to list all sub resource pools of finance, currently I have 3 sub resource pools.

Get-resourcepool -name finance | get-vm | foreach($rp in (Get-resourcepool)){

    get-vm -Location $rp | foreach {

        $name = $_.Name

        $PowerState = $_.PowerState

        $NumCPUs = $_.NumCPU

        $MemoryGB = $_.MemoryGB

        Write-Host " ResourcePool : $($rp.Name)  Name : $Name       PowerState : $PowerState       MemoryGB : $MemoryGB       Num CPUs : $NumCPUs"

    $sql_query = "INSERT INTO $table (ResourcePool, Name, PowerState, MemoryGB, NumCPUs) VALUES ('$($rp.Name)', '$Name', '$PowerState', '$MemoryGB', '$NumCPUs')"

    Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query

[kunaludapi]

Remove "Get-resourcepool -name finance | get-vm |" from first line then run your code.

[LucD]

Like this

Add-PSSnapin VMware.VimAutomation.Core

Add-PSSnapin SqlServerCmdletSnapin100

Add-PSSnapin SqlServerProviderSnapin100

$db_server = "127.0.0.1\VIM_SQLEXP"

$db = "Billing"

$table = "vmdata"

$username = "import"

$pwd = "myPassWord"

# First, clear existing table

$sql_query_del = "DELETE FROM $table"

Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query_del

# Get vms and resources add to DB

foreach($rp in Get-resourcepool){

    get-vm -Location $rp | foreach {

        $name = $_.Name

        $PowerState = $_.PowerState

        $NumCPUs = $_.NumCPU

        $MemoryGB = $_.MemoryGB 

        Write-Host " ResourcePool : $($rp.Name)  Name : $Name       PowerState : $PowerState       MemoryGB : $MemoryGB       Num CPUs : $NumCPUs"

        $sql_query = "INSERT INTO $table (ResourcePool, Name, PowerState, MemoryGB, NumCPUs) VALUES ('$($rp.Name)', '$Name', '$PowerState', '$MemoryGB', '$NumCPUs')"

        Invoke-Sqlcmd -ServerInstance $db_server -Database $db -Username $username -Password $pwd -Query $sql_query

    }

}