## Removing failed replica disks from Veeam proxies.

There’s no doubt that I’ve been a big fan of Veeam Backup and Replication.  For the most part in my day job this has been a set and forget type of application that just works (and even through the use of SureBackup it let’s me know that it works!).  That said, there are times when my jobs will fail.  Usually due to causes such as vCenter disconnecting or other environmental concerns.  When this happens, a lot of times I find that Veeam will have hot added a replica disk to the proxy in order to do it’s thing, yet since the disruption it hasn’t removed it.  This will cause all subsequent backups to fail, normally with errors around opening vddk disks and/or invalid snapshot configurations.

#### Easy way – just remove them – but where are they?

No problem right – just right-click on our proxy VM and remove those stray replica disks – that’ll fix it.  This seems like an easy fix for maybe a couple of replica disks on a couple of Veeam proxies…but what if you are using Veeam at scale?  We by no means are huge when it comes to Veeam but honestly, even with 6 Veeam proxies and 200 VMs it can get a little hectic and almost feel like you are playing a game of Where’s Waldo when you are trying to determine which proxy has hot-added which failed replica.  And I find it monotonous with 6, for those larger the Where’s Waldo book could be a few hundred pages 🙂

#### Enter PowerCLI Man!!!

Now before I get into explaining how to do this with PowerCLI we need to go over a few assumptions.  A few things that I’m assuming about the environment that Veeam is installed in.

1. You have used a standard naming convention for your VMs that contains at least a few characters that can uniquely identify your Veeam VMs from the ones you are backing up.
2. You haven’t done any crazy with adding existing disks or renaming them at all.  Meaning you don’t have a VM called VM1 with a mounted disk called VM2.vmdk.  All disks attached to your proxies are consistent in their naming as the VM is.
3. Veeam isn’t running any current jobs.  If Veeam is running this little command might just unmount a disk that Veeam is currently trying to replicate/backup.  Not good – so be sure your jobs are all stopped.

Phew!  Now that’s out of the way let’s have a look at a one-liner that will accomplish the task of removing all foreign disks from our Veeam proxies – essentially, this will mimic the person that has selfishly flipped through the book and circled all of the Waldos 🙂

Get-VM | Where-Object {$_.Name –like “*VBR*”} | Get-HardDisk | Where-Object {$_.Filename –notlike “*VBR*”} | Remove-HardDisk

There you are, you can see the line will first grab all VMs that contain the string ‘VBR’ – a unique string inside of all my Veeam proxies.  Then, it retrieves all the disks mounted to those VMs that do not contain the ‘VBR’ string, meaning they are foreign or from my replicas.  It then, simply removes them – with a prompt 🙂  To remove the prompt just add a –confirm:$false right at the end of the line. With that let’s call this post done. Although not a very technical post this is a simple one-liner that has saved me a ton of time over the years. It also has given me a good excuse to post some pictures of Waldo and complain about that third-grader that always seemed to get those library books before me and circle Waldo on EVERY PAGE! Thanks for the challenge Billy!!!! ## Kerberos authentication for the PowerShell plugin in vCO 5.5 The ability to have vCO kick off PowerShell scripts is pretty awesome! And the fact that you can kick these off contextually inside of the vSphere Web Client is even more awesome! Even more awesome than that, yes, that’s a lot of awesome is the new features offered with vCenter Orchestrator 5.5 – So, I’ve taken the plunge on one of my environments and upgraded. Since then I’ve been slowly migrating workflows over – one of which utilized the PowerShell plug-in. Now, since the appliance mode of vCO requires you to do a rip and replace rather than an upgrade (because I’m using the embedded database) I had to reinstall the PS plugin, therefore forcing me to reconfigure the Kerberos settings on vCO. During this I realized that things are a little bit different than when I first blogged about vCO and PowerShell here. Below is how I got it to work… First up is the WinRM setup on your PowerShell host. This process hasn’t changed from 5.1, however I’ll still include the steps and commands that need to be run below. Remember these are to be executed on the Windows box that you wish to run the PowerShell script from. • To create a winrm listener and open any required firewall ports • winrm quickconfig • To enable kerberos authentication • winrm set winrm/config/service/auth @{Kerberos=”true”} • Allow transfer of unencrypted data • winrm set winrm/config/service @{AllowUnencrypted=”true”} • Up the max memory per shell – I needed to do this to get things working • winrm set winrm/config/winrs @{MaxMemoryPerShellMB=”2048″} No on to the krb5.conf file – this is where things get a bit different. In vCO 5.1 we were required to edit the krb5.conf file located in /opt/vmo/jre/lib/security/ – well, if you go looking for that directory on 5.5 you won’t find it. Instead, we need to create our krb5.conf file in /usr/java/jre-vmware/lib/security/ As far as what goes in the file it is the same and is listed below…(obviosoly substituting your own domain for lab.local and your own dc for the kdc definition). [libdefaults] default_realm = LAB.LOCAL udp_preferences_limit = 1 [realms] LAB.LOCAL = { kdc = dc.LAB.LOCAL default_domain = LAB.LOCAL } [domain_realms] .lab.local=LAB.LOCAL lab.local=LAB.LOCAL After you have saved the file in the proper directory we need to modify the permissions. The following line should get you the proper permissions to get everything working. chmod 644 /usr/java/jre-vmware/lib/security/krb5.conf Just a few other notes! You might want to modify your /etc/hosts file and be sure that you are able to resolve the fqdn’s of both your dc and the PowerShell host you plan to use. Also, when adding the PowerShell host be sure to select Kerberos as your authentication type and enter in your credentials using the ‘[email protected]’ format. For now, that should get you automating like a champ! ## Some updates to the PowerCLI Shutdown VMs Script – Now w/ a virtualized vCenter option Over that past few months I've been getting quite a few emails and comments as it pertains to the scripts I have shared to shutdown and power on VMs in the event of power loss. I assume since the storm weather is kicking in people are realizing that they need to have some kind of protection in regards to gracefully getting the VMs down. Now I've had the (privilege?) of watching my script run multiple times in a production environment and it has gotten to the point where it is working flawlessly. One caveat to the script though – whichever Windows box is executing the PowerShell must stay powered on longer than your vCenter Server. That being said the script would be pretty useless if you had a virtualized vCenter Server running on the same cluster as your production VMs that your are shutting down – this is what all of the emails and comments that I've been getting have been based around. So, without further ado I've added a few updates to the script to better support those with a virtualized vCenter. A few explanations below… So first off (Line 50) I've added a variable called$vCenterVMName and assigned this a default value of NA.  This variable should hold the VM Name of your vCenter Server.  I've tried to keep just one version of the script supporting both virtualized and non-virtualized vCenter's thus is why the default to "NA".  So if you don't have a virtualized vCenter just leave the variable set to NA and the script will run as it always had, otherwise if you do, put its' VM name in here.

Secondly down on Lines 107 to 1118 I do a few things.  First, check to see if you have changed that default value and if you have we need to figure out which ESXi host vCenter is running on in order to shut it down last.  This is what's done here.

Then, in each of the shutdown loops (Line 125 and Line 162) I've added an if statement.  Basically it just checks to see if the VM it is processing is the vCenter Server.  If so, it doesn't process the shutdown.

Lastly I've done a couple more things.  On Line 186 I've added a check while shutting down the hosts.  If the host that is being processed is the host that vCenter is running on I want to leave it till the end, so it is skipped during this loop.  Then, the finally the block of code starting on Line 196, if needed, will send the shutdown command to the host that is running vCenter Server.

As you may of noticed I've added a some logging as well, nothing bit, just a record of what happened in a txt file rather than relying on Write-Host.  The script in its' entirety is below or you can download it here.  Watch for updates to the power on script to support the vritualized vCenter as well.  Just not sure how I'm going to tackle that one yet 🙂

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 ################################################################################# # Power off VMs (poweroffvms.ps1) # # This is a complete update to my previous shutdown script. No longer do we # loop through the esx hosts and shut down VMs per host. We now just shut them # all down up front. Also, no more $vmstoleaveon variable. I&#39;m assuming here # that your vCenter is not virtual. If it is you can most likely grab version # 1 of this script and take the code to leave the vCenter host till last. Maybe # someday I&#39;ll get around to updating it to merge the two...but for now, this is # it! # # This script does have a &#39;partner&#39; script that powers the VMs back on, you can # grab that script at http://blog.mwpreston.net/shares/ # # Created By: Mike Preston, 2012 - With a whole lot of help from Eric Wright # (@discoposse) # # Variables:$mysecret - a secret word to actually make the script run, stops # the script from running when double click DISASTER # $vcenter - The IP/DNS of your vCenter Server #$username/password - credentials for your vCenter Server # $filename - path to csv file to store powered on vms used for the poweronvms.ps1 script. #$cluster - Name of specific cluster to target within vCenter # $datacenter - Name of specific datacenter to target within vCenter # # # Usage: ./poweroffvms.ps1 &quot;keyword&quot; # Intended to be ran in the command section of the APC Powerchute Network # Shutdown program before the shutdown sequence has started. # ################################################################################# param($keyword)   Add-PSSnapin VMware.VimAutomation.Core #some variables $vcenter = &quot;vcenterServer&quot;$username = &quot;user&quot; $password = &quot;password&quot;$cluster = &quot;clusterName&quot; $datacenter = &quot;datacenterName&quot;$filename = &quot;c:\path\to\poweredonvms.csv&quot; $mysecret = &quot;kerfuffle&quot;$date = ( get-date ).ToString(&#39;yyyyMMdd&#39;) $logfile = New-Item -type file &quot;C:\ShutdownLog-$date.txt&quot;   #for use with a virtualized vCenter - bring it down last :) - if you don&#39;t need this functionality #simply leave the variable set to NA and the script will work as it always had $vCenterVMName = &quot;NA&quot; Write-Host &quot;A Powerchute network shutdown command has been sent to the vCenter Server.&quot; -Foregroundcolor yellow Write-Host &quot;This script will shutdown all of the VMs and hosts located in$datacenter&quot; -Foregroundcolor yellow Write-Host &quot;Upon completion, this server ($vcenter) will also be shutdown gracefully&quot; -Foregroundcolor yellow Write-Host &quot;&quot; Write-Host &quot;This script has also has a counterpart (powervmsbackon.ps1) which should&quot; -Foregroundcolor yellow Write-Host &quot;be setup to run during the startup of this machine ($vcenter). &quot; -Foregroundcolor yellow Write-Host &quot;This will ensure that your VMs are powered back on once power is restored!&quot; -Foregroundcolor yellow Sleep 5   Add-Content $logfile &quot;mwpreston.net PowerOff Script Engaged&quot; Add-Content$logfile &quot;&quot;   if ($keyword -ne$mysecret) { Write-Host &quot;You haven&#39;t passed the proper detonation sequence...ABORTING THE SCRIPT&quot; -ForegroundColor red Add-Content $logfile &quot;You haven&#39;t passed the proper detonation sequence...ABORTING THE SCRIPT&quot; exit } #connect to vcenter Write-Host &quot;Connecting to vCenter -$vcenter ....&quot; -nonewline Add-Content $logfile &quot;Connecting to vCenter -$vcenter&quot; $success = Connect-VIServer$vcenter -username $username -Password$password if ($success) { Write-Host &quot;Connected!&quot; -Foregroundcolor Green } else { Write-Host &quot;Something is wrong, Aborting script&quot; -Foregroundcolor Red Add-Content$logfile &quot;Something is wrong, Aborting script&quot; exit } Write-Host &quot;&quot; Add-Content $logfile &quot;&quot; #Get a list of all powered on VMs - used for powering back on.... Get-VM -Location$cluster | where-object {$_.PowerState -eq &quot;PoweredOn&quot; } | Select Name | Export-CSV$filename   #change DRS Automation level to partially automated... Write-Host &quot;Changing cluster DRS Automation Level to Partially Automated&quot; -Foregroundcolor green Get-Cluster $cluster | Set-Cluster -DrsAutomation PartiallyAutomated -confirm:$false #change the HA Level Write-Host &quot;&quot; Write-Host &quot;Disabling HA on the cluster...&quot; -Foregroundcolor green Write-Host &quot;&quot; Add-Content $logfile &quot;Disabling HA on the cluster...&quot; Add-Content$logfile &quot;&quot; Get-Cluster $cluster | Set-Cluster -HAEnabled:$false -confirm:$false #get VMs again (we will do this again instead of parsing the file in case a VM was powered in the nanosecond that it took to get here.... :) Write-Host &quot;&quot; Write-Host &quot;Retrieving a list of powered on guests....&quot; -Foregroundcolor Green Write-Host &quot;&quot; Add-Content$logfile &quot;Retrieving a list of powered on guests....&quot; Add-Content $logfile &quot;&quot;$poweredonguests = Get-VM -Location $cluster | where-object {$_.PowerState -eq &quot;PoweredOn&quot; }   Add-Content $logfile &quot;Checking to see if vCenter is virtualized&quot; #retrieve host info for vCenter if ($vcenterVMName -ne &quot;NA&quot;) { Add-Content $logfile &quot;vCenter is indeed virtualized, getting ESXi host hosting vCenter Server&quot;$vCenterHost = (Get-VM $vCenterVMName).Host.Name Add-Content$logfile &quot;$vCenterVMName currently running on$vCenterHost - will process this last&quot; } else { Add-Content $logfile &quot;vCenter is not virtualized&quot; } Add-Content$logfile &quot;Proceeding with VM PowerOff&quot; Add-Content $logfile &quot;&quot; #and now, let&#39;s start powering off some guests.... ForEach ($guest in $poweredonguests ) { if ($guest.Name -ne $vCenterVMName) { Write-Host &quot;Processing$guest ....&quot; -ForegroundColor Green Write-Host &quot;Checking for VMware tools install&quot; -Foregroundcolor Green $guestinfo = get-view -Id$guest.ID if ($guestinfo.config.Tools.ToolsVersion -eq 0) { Write-Host &quot;No VMware tools detected in$guest , hard power this one&quot; -ForegroundColor Yellow Add-Content $logfile &quot;$guest.Name - no VMware tools, hard power off&quot; Stop-VM $guest -confirm:$false } else { write-host &quot;VMware tools detected. I will attempt to gracefully shutdown $guest&quot; Add-Content$logfile &quot;$guest.Name - VMware tools installed, gracefull shutdown&quot;$vmshutdown = $guest | shutdown-VMGuest -Confirm:$false } } }   #Lets wait a minute or so for shutdowns to complete Write-Host &quot;&quot; Write-Host &quot;Giving VMs 2 minutes before resulting in hard poweroff&quot; Write-Host &quot;&quot; Add-Content $logfile &quot;&quot; Add-Content$logfile &quot;Waiting a couple minutes then hard powering off all remaining VMs&quot; Add-Content $logfile &quot;&quot; Sleep 120 #Now, let&#39;s go back through again to see if anything is still powered on and shut it down if it is Write-Host &quot;Beginning Phase 2 - anything left on....night night...&quot; -ForegroundColor red Write-Host &quot;&quot; #get our list of guests still powered on...$poweredonguests = Get-VM -Location $cluster | where-object {$_.PowerState -eq &quot;PoweredOn&quot; } ForEach ( $guest in$poweredonguests ) { if ($guest.Name -ne$vCenterVMName) { Write-Host &quot;Processing $guest ....&quot; -ForegroundColor Green #no checking for toosl, we just need to blast it down... write-host &quot;Shutting down$guest - I don&#39;t care, it just needs to be off...&quot; -ForegroundColor Yellow Add-Content $logfile &quot;$guest.Name - Hard Power Off&quot; Stop-VM $guest -confirm:$false } }   #wait 30 seconds Write-Host &quot;Waiting 30 seconds and then proceding with host power off&quot; Write-Host &quot;&quot; Add-Content $logfile &quot;&quot; Add-Content$logfile &quot;Processing power off of all hosts now&quot; Sleep 30   #and now its time to slam down the hosts - I&#39;ve chosen to go by datacenter here but you could put the cluster # There are some standalone hosts in the datacenter that I would also like to shutdown, those vms are set to # start and stop with the host, so i can just shut those hosts down and they will take care of the vm shutdown ;)   $esxhosts = Get-VMHost -Location$cluster foreach ($esxhost in$esxhosts) { if ($esxhost.Name -ne$vCenterHost) { #Shutem all down Write-Host &quot;Shutting down $esxhost&quot; -ForegroundColor Green Add-Content$logfile &quot;Shutting down $esxhost&quot;$esxhost | Foreach {Get-View $_.ID} | Foreach {$_.ShutdownHost_Task($TRUE)} } } #finally shut down the host which vCenter resides on if using that functionality if ($vcenterVMName -ne &quot;NA&quot;) { Add-Content $logfile &quot;&quot; Add-Content$logfile &quot;Processing $vcenterHost - shutting down &quot; Get-VMhost$vCenterHost | Foreach {Get-View $_.ID} | Foreach {$_.ShutdownHost_Task($TRUE)} } Add-Content$logfile &quot;&quot; Add-Content $logfile &quot;All done!&quot; #that&#39;s a wrap So there you have it! Hopefully this will help some of you out with your shutdown endeavors. Feel free to make any changes you need in order to make it work for your environment and let me know how it goes – keep those comments, concerns, thoughts and emails coming in! ## Move your templates to a new cluster – The PowerCLI Way Alright, here's a script I quickly pieced together to accomplish the task of moving some of our templates over to a new cluster. At the time I was currently in the process of deploying a new cluster on a new distributed virtual switch, thus this script only tackles your templates on a single host basis. Meaning I was walking each host over to the new switch one by one and then migrating virtual machine networking and all vmkernel interfaces on the host, then performing a vmotion on the VMs and templates and finally destroying the original host… Oh well, enough history (blabbing). The script is very simple as you can see below. Aside from your vCenter information it takes a couple of variables; source and destination host (Lines 7/8). The only quirkiness I found was with the Set-Template cmdlet – I could not find a way to specify the location of the VM when converting it, thus you can see on line 18 I have to move it back to my source host after the conversion to ensure i will have the connectivity on my migrated vMotion network. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23$vcenter ='IP of vCenter' $vcuser = 'vCenter User'$vcpass = 'vCenter Pass'   Connect-VIServer $vcenter -user$vcuser -pass $vcpass$sourcehost = get-vmhost 'Source Hostname/IP' $destinationhost = get-vmhost 'Target Hostname/IP' #get all templates on source host$templates = get-template -Location $sourcehost foreach ($template in $templates) { # convert template to VM Set-Template$template -ToVM -confirm:$False # ensure it is running on my source host Get-VM -Name$template | Move-VM -Destination $sourcehost # migrate vm to new cluster Get-VM -Name$template | Move-VM -Destination $destinationhost # convert back to a template Get-VM -Name$template | Set-VM -ToTemplate -Confirm:$false } So that's it! I told you it was a quickie! Again, I don't have a secret alias of PowerCLI Man so if you have any suggestions on how to improve up on any of this let me know… ## vSphere USB Passthru and Autoconnect Devices and PowerCLI Wait! I thought I had UPS plugged into my host and setup as passthru to my VM already! Why can't I see it now? What happened? Who moved that external drive I had connected to my Veeam console to seed an offsite backup? Ever find yourself asking yourself any of these questions…I certainly have! Due to circumstances out of my control I have a few hosts that tend to be "out in the wild". Available and insecure, readily accessible to the hundreds of people walking by it each day. At time to time either someone trips over a cord, some deliberately unplugs something, or equipment needs to be moved and gets plugged in to different ports upon reconnection. Not as many options in the GUI As is with most other products, using the GUI to configure something sometimes doesn't give you all the options that you need. Essentially when configuring USB Passthru to a VM from within either the vSphere Client or the vSphere Web Client you the device needs to be plugged in and it gets assigned to the VM based on the host USB port that it is connected to. Again in most cases this is fine but in my situation I needed to be able to have this device connected to the VM no matter what port it was connected to. Turned out after reading some documentation around the vSphere API as well as having a great discussion with Luc Dekens on the VMTN forums there is indeed a way to do exactly what I needed to do. Community to the rescue! So, in the API reference for the VirtualUSBUSBBackingInfo object it states the following  To identify the USB device, you specify an autoconnect pattern for the deviceName. The virtual machine can connect to the USB device if the ESX server can find a USB device described by the autoconnect pattern. The autoconnect pattern consists of name:value pairs. You can use any combination of the following fields. path – USB connection path on the host pid – idProduct field in the USB device descriptor vid – idVendor field in the USB device descriptor hostId – unique ID for the host speed – device speed (low, full, or high) Perfect, this is exactly what I was looking for. Basically I can have the USB device autoconnect to the VM by initially connecting by using ANY combination of the above parameters as the deviceName. First off, path is out of the question, it's what is going to changed when the device is plugged into a different port. So I decided to use pid, vid, and hostId. Therefore, if a device with the specified product id and vendor id is plugged into a host with the specified hostId it will automatically pass this through to the VM in which I assign it to! Awesome! One problem, I still don't have a clue what the pid, vid, and hostId are, nor do I know the PowerCLI syntax to add the device. PIDS and VIDS and More… So how do you find out the pid and vid of the device you want to add? Well, there's a KB for that…kinda, KB1648 mentions how to do it. Basically go to in the Windows registry HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Enum\USB\ and search around and you'll find it 🙂 In my case i was using an APC SmartUPS 1500 which had a pid and vid of 0003 and 051D respectively. The final piece of information we need is the hostId. By hostId the documentation really means the actual hardware id that is stored within the BIOS of your host. Accessing ExtensionData.Hardware.Systeminfo.Uuid will actually retrieve this for you. As you will see in the script below there is certainty some formatting issues that need to be resolved with it, but it most certainly works 🙂 Putting it all together Now that we have all the information we need it's time to fire up PowerCLI to get this task scripted (I needed to do it 40 times 🙂 ). I'm not going to lie here, I had no idea how to do this with PowerCLI but by using Onyx I got a great start. As you can see in the script below a few things happen. First you need to specify some credentials to and your vCenter location as well as the pid, vid, hostname of the host that will be connected to the device as well as the VM name in which you want to pass the device through. Lines 17 and 18 get some information in regards to the target VM and lines 21 and 22 get that hardware uuid from the host. Lines 24 through 36 is the reformatting of the uuid that I described earlier. You can see the format in the API reference. The rest of the script does the actually setup of the USB device, this is the code, minus a few changes here and there, that Onyx spit out for me. The Script 1. Add-PSSnapin VMware.VIMAutomation.Core 2. 3.$vcserver = "vcenterserver"
4. $vcusername = "username" 5.$vcpassword = "password"
6.
7. $myhost = "Host that USB is attached to" 8.$myvm = "VM which to pass USB through to"
9.
10. $ppid = "PID of USB Device" 11.$vvid = "VID of USB Device"
12.
13. Connect-VIserver $vcserver -user$vcusername -pass $vcpassword 14. 15. 16. #get id of VM 17.$vm = get-vm $myvm 18.$vmid = $vm.ID 19. 20. #get host uuid from BIOS 21.$vmhost = get-vmhost $myhost 22.$vmhostId =  $vmhost.ExtensionData.Hardware.SystemInfo.Uuid 23. 24. #reformat vmhostID to the proper format for autoconnect string 25.$vmhostd = $vmhostid.Replace("-","") 26.$section1 = $vmhostid.substring(0,16) 27.$section2 = $vmhostid.substring(16) 28.$newsec1 = (&{for ($i = 0;$i -lt $section1.length;$i += 2)
29.    {
30.      $section1.substring($i,2)
31.    }}) -join '\ '
32. $newsec2 = (&{for ($i = 0;$i -lt$section2.length;$i += 2) 33. { 34.$section2.substring($i,2) 35. }}) -join '\ ' 36.$hostId = "$newsec1-$newsec2"
37.
38.
39. #create usb device and add it to the VM.
40. $spec = New-Object VMware.Vim.VirtualMachineConfigSpec 41.$spec.deviceChange = New-Object VMware.Vim.VirtualDeviceConfigSpec[] (1)
42. $spec.deviceChange[0] = New-Object VMware.Vim.VirtualDeviceConfigSpec 43.$spec.deviceChange[0].operation = "add"
44. $spec.deviceChange[0].device = New-Object VMware.Vim.VirtualUSB 45.$spec.deviceChange[0].device.key = -100
46. $spec.deviceChange[0].device.backing = New-Object VMware.Vim.VirtualUSBUSBBackingInfo 47.$spec.deviceChange[0].device.backing.deviceName = "pid:$ppid vid:$vvid hostId:$hostId" 48.$spec.deviceChange[0].device.connectable = New-Object VMware.Vim.VirtualDeviceConnectInfo
49. $spec.deviceChange[0].device.connectable.startConnected =$true
50. $spec.deviceChange[0].device.connectable.allowGuestControl =$false
51. $spec.deviceChange[0].device.connectable.connected =$true
52. $spec.deviceChange[0].device.connected =$false
53.
54. $_this = Get-View -Id "$vmid"
55. $_this.ReconfigVM_Task($spec)
56.

So there you have it!  You can unplug and plug this USB device in to your hearts delight.  ESXi should pick up the device no matter what port it is plugged into and pass it on to your VM every time!  Certainly this isn't something that you will do everyday, but for those that have hosts sitting out in the open, may be a handy configuration to have set up in their environment.  As always, I'm not the greatest scripter in the world, so any comments, suggestions, improvements, concerns, thoughts are most definitely appreciated.

## My First vCenter Orchestrator Workflow – Part 5 – A little bit of SQL

Thus far in this series of posts we have installed and configured vCenter Orchestrator as well as setup and utilized a couple of plugins; the vCenter plug-in and the PowerShell plug-in.  Before the series ends I wanted to go through one more plug-in.  The SQL plug-in.  The SQL plug-in is used for, well, you guessed it; running SQL queries against a database.  If you remember in Part 4 we setup a workflow that took two input parameters; a host and a location code.  The script then went on to create a new port group on a vSwitch named locationcode-VM_Network.  The logic in itself works fine and the workflow validates, the only problem I see with it is that the user needs to enter the ‘location code’ manually as an input.  Now I know you are all very careful and take your time when doing things, but I’m not, and more than likely after doing this 40 or 50 times you can count on me keying in that location code wrong 🙂 – Enter the SQL plugin.

So the setup is as follows; I have a database containing a table with the following columns; locationcode and deployed (boolean).  What I would like to do is have an input parameter that would allow me to select the location code from a list of non-deployed locations in a drop-down box (rather than manually enter this information in), and in turn pass the location code to my PowerShell script.  Once the script has ran I’d like to update the deployed column for that specific location, ensuring it isn’t displayed in the list again in turn ensuring I can’t deploy the same location twice.  Make sense?  I know it’s a lot to follow but the concepts of setting it up are pretty much the same no matter what you are looking to do.

Alright – enough background – Let’s get into it.  I’m not going to go through the installation of the SQL Plug-in – it’s exactly the same installation process as the Powershell plug-in which we covered in Part 4.  Similar to how we setup our Powershell host we need to setup our SQL server database connection inside of vCO.  To do this fire up your vCO client and navigate through the workflow tree to Library->SQL->Configuration and select the ‘Add a database’ workflow, right click and Start Workflow.  There are a few parameters as you can see that we need to pass to the workflow in order for it to successfully run.  First, give this connection a name and select your desired Database Type – in my case MS SQL.  Also here we need to pass a Connection URL.  Now if you don’t know anything about jdbc connection urls no fear, it’s not that difficult.  Simply enter it in the the following format…

jdbc:jtds:sqlserver://[SERVERNAME]:[SERVERPORT]/[DATABASENAME]

So, for a SQL Server with named DC.lab.local running on the default port 1433 and  database named ServerDeploy you would use the following…

jdbc:jtds:sqlserver://dc.lab.local:1433/ServerDeploy

After clicking Next we are once again presented with our Shared/Per User session mode – again, I chose shared to use one simple set of credentials rather than a per user authentication.  When you are ready click ‘Submit’ to add your new database to vCO’s inventory.  One thing to note here is that this step is not necessary   If we wanted we could perform all of this at run time inside code, however for tutorial purposes and learning purposes it’s sometimes easier to do it this way.

Alright, nuff config!  It’s time now to get started on our task at hand; Querying the database for non-deployed servers and pushing the result as a drop-down box as an input parameter to our workflow that we created in Part 4.  First off there is a simple change we need to make to our existing workflow.  Here’s a tip – don’t feel like buffalo-ing your original workflow, simply right click on it and  select ‘Duplicate Workflow’ to copy it.  OK, first off we need a new attribute.  We originally have locationcode setup an input parameter of type string – and we still need this, however the result we get back from our database will be an array of strings.  So, on the General tab of your workflow create a new attribute called  databaseParameter of type SQL:Database and assign it the value of the Database we created earlier (this should allow you to browse the inventory to do so).  Once you are done that simply Save & Close and continue on with any validation errors.

So here comes the real magic!   We need to take that database attribute and pass it to a newly created action which will in turn spit out an array of string attributes (our locations in our database).   Again, you could do the following all in script embedded within your workflow, but you never know when you are going to need to reuse something so I’ve decided to create a new action to do so.    To create an new action be sure you are on ‘Design’ view from within your client and click on the actions tab in the left hand side menu.  Where you place your action doesn’t really matter, I chose to right click com.vmware.library.sql and create my action inside that module.  Just remember where you put it and what you named it:).

OK, you should now be in the Action editor.  This is where we are going to place the code that does all the querying of the database.  As I said earlier we need to pass this Action a database parameter and it will return an array of string.   The setup of input parameters and return types, along with all the other work we are going to do is all done on the scripting tab.  First off define your return type of an Array/string.  Secondly add an input parameter of type SQL:Database.  You can see all of this outlined in the capture below…

Hey!  That was easy!  Now it’s time for a little scripting.  vCO script is nothing but Javascript which calls the rich set of API’s that vSphere provides, along with the functions and properties of all the plug-ins provided in vCO.  The script I used is displayed below…

1 2 3 4 5 6 7 8 9 10 var resultingArray = new Array();   var query = "SELECT locationcode FROM Locations WHERE deployed = 0"; var resultingActiveRecords = databaseParameter.readCustomQuery(query); for each (var resultRecord in resultingActiveRecords) { var locationcode = resultRecord.getProperty("locationcode"); resultingArray.push(locationcode); }   return resultingArray;

Simply paste this into the script pane of your action.  As you can see it’s a pretty simple script.  Creates and array, queries our database, pushes the locationcode column value of each record returned into that array and finally returns the array.  So – time to Save and Close and head back to our workflow.

So at this point we are left with 2 tasks.  The first being the creation of the drop-down box as an input.  To do this we will need to change the way our original input parameter, locationcode, is displayed.  This is done on the Presentation tab of our script editor.  Be sure you have selected locationcode in the Presentation tree that the top of the screen and select the icon to add a new property to it.  There are many several different properties listed but the one we are after is called Predefined list of elements.  Under the value field of our new property select the icon to assign an action.  In the popup find/filter for our action we created earlier, assign the actions parameter to our database parameter and click Apply.

There done right…well, kinda, you could run the workflow now and it would populate our input and it would go ahead and run the PowerCLI script and the VM Network would be created, however if you remember it was my intent to go back at the end of the workflow and update our database to ensure that the same locationcode could not be selected twice.  To do this we will need to drag a new Scriptable task element to run after we invoke our PowerCLI script.  Inside this scriptable task we will need to import a couple of local attributes in order to accomplish the sql we need to do, the locationcode, and the databaseParameter.

As for the script it’s going to look very similar to the syntax that we placed inside of our action with the exception of and executeCustomQuery function in place of the readCustomQuery and the actual query itself is different.  The following is what I used…

1 2 var query = "UPDATE locations SET deployed= 1 WHERE locationcode= '" + locationcode+ "'"; databaseParameter.executeCustomQuery(query);

And now at last we are done!!  Go ahead and run your workflow, either from within the vCO client or the Web Client and you should now see a drop-down selection for the locationcode.  Once it’s selected once the script will run, create the VM Network, then update our database to ensure that the selected locationcode is not shown again in the drop-down.

So that my friends is the end of the line for me on creating my first vCenter Orchestrator Workflow but it is most definitely not the end of the line with vCO.  With access to a huge set of vSphere API’s along with all of the functionality and properties provided by its’ plugins, throw in some sweet integration with the vSphere Web Client I’m beginning to see my usage of vCO ramp up within my current role.  This series has hardly even grazed the surface in terms of vCO’s functionality so I urge you all to go out there and learn as much as you can about this product.  I’ll do one more post in the series and outline some of the resources that I’ve found along the creation of this workflow so expect to see that soon.

My first vCenter Orchestrator Workflow