get-list-of-vms-in-esx-host

Get the list of Virtual Machines on a ESXi Host with Get-VMHost | PowerCLI

Posted on by Sahan Fernando

Recently I needed to create a script to get a list of all virtual machines in a specific ESXi host. Surprisingly I had to search high and low on the internet to figure this out.

This is perplexing I mean its very straightforward to get the VMs in a datastore with 

Get-VM -Datastore <datastore_name>

you would think getting all the virtual machines on a ESXi host would be more important than that! Work on this VMware – give us a Get-VM -VMHost!

But I digress – this is how you get the list of virtual machines on a ESXi host… I mean I was ranting up there but it’s really not that difficult, just that there’s no proper documentation on it

Get-VMHost -Name <Host_name> | Get-VM

And that’s all there is to it. But it’s not that intuitive, you wouldn’t think piping through the host name to Get-VM would do anything right? Anyways this one-liner saved my bacon, and I hope it would be useful to you as well!

Till the next time – happy scripting!

Checking for empty returns from MySQL queries in Powershell

Posted on by Sahan Fernando

Recently during a project I had to some DB queries using powershell. While that is straightforward and easy enough (I will create a post on querying DBs with powershell later on), the problem I ran in to was checking for an empty return.

I wouldn’t blame you for thinking “but scriptigator this should be simple right?” – because I thought the same. Query the DB, check if its null, presto manifesto yes? Well… sort of – IF you’re using Invoke-MysqlQuery this will work

$query_result = Invoke-Mysql -Query "SELECT * FROM table_name"

if($query_result -eq $null){
    Write-Host "This will work if you're using invoke-mysql"
}

else{
    Write-Host "need to try something else"
}

The problem with this approach is that empty or the $null global variable in Powershell is not exactly the same as an SQL null value. So we will have to use a workaround to get around the issue. There are a couple of ways to do this :

1. Checking the row count of a query

If the query does not match any records, how many rows would it output? Should be 0 right? Let’s use that logic here

$query_result = Invoke-MysqlQuery "SELECT * FROM table_name"
$query_row_count = ($query_result).count
if($query_row_count -eq 0){
    Write-Host "This WILL work!"
}

And yes this will work!

2. Using DBNull

If you’re using SQL reader method to query the Database you can use the system provided value of DBNull or System.DBNull that makes life a little easier. ( I had trouble with this when using Invoke-MySQL – script life can’t get too easy now can it? )

//DBNull and System.DBNull are interchangeable

if($query_result -eq [DBNull]::Value){
    Write-Host "This will work if you're using sqlreader"
}


if(([DBNull]::Value).Equals($query_result)){
    Write-Host "This will work if you're using sqlreader"
}

Conclusion time…

So there it is – all the methods I found to check for empty returns in Powershell/PowerCLI. As you know I do not claim to be an all-knowing demigod in scripting so if you find any alternative methods to these please feel free to share!

Till the next time – happy scripting!