Use sqlcmd in a powercli loop script

[Pinball]

Hi there

I've been working on this for a while and finally managed to get all the stats i'm interested in via powercli, with the help of some great people. The second step was to write the results to SQL db for future use, also working. The last thing i need some advice on is how to use "SQLCMD" in conjunction with a powershell script that loops trough all the clusters.

Steps:

1. Main script requires a Cluster variable

2. I created a small pre-script (thanks LUCD) that loops trough all the clusters instead of running the script manually for each cluster.

3. Added the details to write the query results to SLQ Database.

Problem:

The script works fine for the first cluster however as i call sqlcmd i'm unable to get it to exit sqlcmd and be able to run the same loop for a new cluster.

Is it posible to exit SQLCMD without killing the Powercli session?

Attached is the 2 scripts i use, please can someone tell me if there is a better way to achieve the same thing or just where i'm going wrong.

The end result is to schedule the script to run weekly and for us to build a front end that makes use of the save data.

Thanks

Johan

[LucD]

Can you try the pre-script with

Get-Cluster | %{

   .\Get-Cluster-Usage-Stats.ps1 $_.Name

}

And try to end the tsql command with a semi-column

$SqlWrite = "INSERT INTO $tableclust (DATE, CLUSTER, HOSTCOUNT, VMCOUNT, VMON, VMOFF, VMTEMP, CLpCPU, CLpCORE, CLvCPU, CLpRAM, CLvRAMUSED, vNEWVM)

values ('$date', '$clus', '$HostCount','$VMCount','$VMCountpoweredon','$VMCountpoweredoff','$TemplateCount','$TpCPUSocket','$pCPUCore','$VCPU','$TotalClusterRAMGB','$TotalClusterRAMusageGB','$newvmcount');"

[Pinball]

Hi Luc

I tried the ";" as requested but no luck, the problem seems to be that there is no way to unload the SQLCMD from powershell and allow for the loop to continue.

If i had to change my approach and append every lop to a csv file and then upload the csv into SQL. What would i need to do to get the results written as part of a loop in the correct sequence?

Johan

[LucD]

Can you try adding the -Verbose switch on the Invoke-SqlCmd cmdlet ?

Perhaps that gives some more details on what happens

[Pinball]

Hi Luc

I'm constantly trying to evolve the attached script and need some help with line 56 & 57.

I'm trying to record the number of newly created and deleted vm's per cluster. I get no errors but the value is always 0.

$vmnewcount = ($clus | Get-VIEvent -Start $start -MaxSamples ([int]::MaxValue) | where {"VMCreatedEvent" -contains $_.GetType().Name} | measure-object).Count

$vmdelcount = ($clus | Get-VIEvent -Start $start -MaxSamples ([int]::MaxValue) | where {"VMRemovedEvent" -contains $_.GetType().Name} | measure-object).Count

Any ideas as to where i'm going wrong?

Johan

[LucD]

The left operand of the contains operator should be an array.

Try like this

$vmnewcount = ($clus | Get-VIEvent -Start $start -MaxSamples ([int]::MaxValue) | where {@("VMCreatedEvent") -contains $_.GetType().Name} | measure-object).Count

$vmdelcount = ($clus | Get-VIEvent -Start $start -MaxSamples ([int]::MaxValue) | where {@("VMRemovedEvent") -contains $_.GetType().Name} | measure-object).Count