T-SQL Tuesday has rolled up on us yet again. This month it’s Rob Sewell’s (b/t) turn to host and picked a subject Lets get all Posh!.

I’m a little confused, to be honest. Has anyone else heard of PoSH? No? I didn’t think so.

Well, after much research (it was rather hard to find information on the subject) I discovered that Powershell (or PoSH for short) is some kind of scripting language. Not terribly useful either.

Ok. Yea. I’m kidding. Not like I haven’t done PoSH posts before. In this particular case, I actually had a need that was perfect for PoSH, and since I had a good excuse, a scripting I went.

This isn’t exactly a SQL Server script. Honestly, it’s not in any way shape or form about SQL Server. But if you are playing with Azure then you are probably creating Azure VMs. And if you are creating Azure VMs you have probably forgotten to shut one of them down. And then you come back and realize Hey, how did I blow through $50 dollars in a week?

So here is a nice little script that will shut down (actually deallocate so no cost) every VM you have. There are also options to turn on all of your VMs, or to restrict it to just VMs within a given resource group.

Unfortunately, it’s still a manual script. I haven’t figured out how to get the login task to work without manual intervention. (FYI Certificates will not work in this case if you are using a personal account.) If one of you PoSH experts can figure it out I’d LOVE to hear it. In the mean time here is my script. Special note to Start-AzureRmVM and Stop-AzureRmVM that do the heavy lifting. Hopefully, I have the whole thing documented well enough that everyone can follow along.

##########################################################
####### Turn all VMs On or Off
##########################################################


## Install the Azure Resource Manager modules from the PowerShell Gallery
#Install-Module AzureRM

## This is only really required if you aren't already logged in. Unfortunately there
## appears to be a problem using regular MS accounts as credentials for
## Login-AzureRmAccount so you have to go through the window & log in manually.
## Log into Azure
Login-AzureRmAccount

$NewStatus = "Off"  ## Values = On, Off, or Flip
                    ## If an values other than Running (on) or VM deallocated (off) then
                    ## deallocate (stop).
$RGName = ""        ## Optional. If not "" then restrict to just VMs in the Resource Group.

IF ($RGName -ne "") {
    ## Get only those VMs for the RG requested.
    $VMs = Get-AzureRmVM -Status -ResourceGroupName $RGName }
ELSE {
    ## Get all VMs regardless of RG.
    $VMs = Get-AzureRmVM -Status }

## Flip through each VM
foreach ($VM in $VMs)
{
    ## If the VM is deallocated and we want to turn it on or flip it
    ## turn it on.
    IF ($VM.PowerState -eq "VM deallocated" -and 
            ($NewStatus -eq "On" -Or $NewStatus -eq "Flip")){
        "Start " + $VM.Name
        Start-AzureRmVM -ResourceGroupName $VM.ResourceGroupName -Name $VM.Name}
    ## If the VM is not deallocated and we want to turn it off or flip it
    ## turn it off.  Note: This means that if the VM is in stopped but not deallocated
    ## it will be deallocated. There are almost certainly allocations that cause an 
    ## error & those can be added to an exclusion list if need be.
    ELSEIF ($VM.PowerState -ne "VM deallocated" -and
            ($NewStatus -eq "Off" -Or $NewStatus -eq "Flip")){
        "Stop " + $VM.Name
        Stop-AzureRmVM -ResourceGroupName $VM.ResourceGroupName -Name $VM.Name -Force}
}    

## List current status
Get-AzureRmVM -Status | SELECT Name, PowerState