Detach LUN by naa ID

The supported way to remove a LUN from VMware is to unmount and detach before removing your zoning.  Problem is the unmount is easy as they provided a GUI option to do that one.  But the detach has to be done on each hosts adapter, there is no easy button.  PowerCLI to the rescue!  This script will run through each host in a specified cluster and detach it.

$LunIDs = “naa.xxxxxxxxxxxxxx”
$Clustername = “clustername”
function Detach-Disk {
$lunUuid=(Get-ScsiLun-VmHost $VMHost|where {$_.CanonicalName-eq$CanonicalName}).ExtensionData.Uuid
$ClusterHosts = Get-Cluster $Clustername | Get-VMHost
Foreach($VMHost in $ClusterHosts)
Write-Host”Detaching”$LUNid”from”$VMHost-ForegroundColor “Yellow”
Detach-Disk -VMHost $VMHost -CanonicalName $LUNid


PowerBI report for VMware using streamed dataset

Been “experimenting” with PowerBI lately and gotta admit, I kinda like it!  I am not a data sciency kinda guy but I can appreciate a nice dashboard that can supply data at a glance.  During my experimenting I have used PowerBI to plug into all sorts of data, from Citrix, to AD to CSV files and databases.  Today I will show you what I did around VMware using a PowerBI streamed dataset (API).  At time of writing, this feature is in preview, but it works so gets my vote for production deployment! 🙂

My first thought was to plug directly into the vCenter database.  All my vCenters are appliances that are using the built-in PostgreSQL DB.  PowerBI does have a connector for this DB so it is an option.  However, upon further reading it looks like I needed to modify some config files on the appliance and install some powerbi software to make the connector work.  All doable, but VMware does not officially support the changes needed to vCenter to allow remote database access.  As a result I was hesitant to mess with production vCenter.  No doubt it would work and everything would be fine but I had to take of my cowboy hat and be a good little architect (my boss might read this! 🙂 ) and find a supported path.  I tried several different methods to access the data I wanted.  For this post we will used the API method.

Streamed datasets in powerBI is a fancy name for API post.  Basically we are collecting data and using a REST post operation to post the data into PowerBI.  For this to work you will need a PowerBI Pro subscription.  Streamed datasets, from what I can tell, are only available on the cloud version of PowerBI, so the desktop app and free edition wont work for this method.

To get started login to PowerBI (  In the top right corner, under your pretty photo, will be a Create button.  Click that and select streaming dataset.


Select API and click next.


Next you will see a screen like this.  You need to fill out each field that you plan to post data to.  Fill in the info and click create.


Next you will be presented with a screen that displays the info you need to post data.  Select the powershell tab and copy the code output.  Will will use this later.


Now we need to create a powershell script that will connect to vCenter and pull the data we need.  This script will pull the data from vCenter using PowerCLI and then do a post to PowerBI.  (squirrel* why are all these product names starting with power? Is it supposed to make me feel powerful?)  Anywho, here is the script I wrote to get this done:

$vcenter = "vcenter host name"
$cluster = "cluster name"

Import-Module VMware.VimAutomation.Core
Connect-VIServer -Server $vcenter

#This line passes creds to the proxy for auth.  If you dont have a PITA proxy, comment out.
[System.Net.WebRequest]::DefaultWebProxy.Credentials = [System.Net.CredentialCache]::DefaultCredentials

$date = Get-Date
$datastore = Get-Cluster -Name $cluster | Get-Datastore | Where-Object {$_.Type -eq 'VMFS' -and $_.Extensiondata.Summary.MultipleHostAccess}

$hostinfo = @()
        ForEach ($vmhost in (Get-Cluster -Name $cluster | Get-VMHost))
            $HostView = $VMhost | Get-View
                        $HostSummary = "" | Select HostName, ClusterName, MemorySizeGB, CPUSockets, CPUCores, Version
                        $HostSummary.HostName = $VMhost.Name
                        $HostSummary.MemorySizeGB = $HostView.hardware.memorysize / 1024Mb
                        $HostSummary.CPUSockets = $HostView.hardware.cpuinfo.numCpuPackages
                        $HostSummary.CPUCores = $HostView.hardware.cpuinfo.numCpuCores
                        $HostSummary.Version = $HostView.Config.Product.Build
                        $hostinfo += $HostSummary

$vminfo = @()
            foreach($vm in (Get-Cluster -Name $cluster | Get-VM))
                $VMView = $vm | Get-View
                $VMSummary = "" | Select ClusterName,HostName,VMName,VMSockets,VMCores,CPUSockets,CPUCores,VMMem
                $VMSummary.VMName = $vm.Name
                $VMSummary.VMSockets = $VMView.Config.Hardware.NumCpu
                $VMSummary.VMCores = $VMView.Config.Hardware.NumCoresPerSocket
                $VMSummary.VMMem = $VMView.Config.Hardware.MemoryMB

                $vminfo += $VMSummary

$TotalStorage = ($datastore | Measure-Object -Property CapacityMB -Sum).Sum / 1024
$AvailableStorage = ($datastore | Measure-Object -Property FreeSpaceMB -Sum).Sum / 1024
$NumofVMs = $vminfo.Count
$NumofVMCPUs = ($vminfo | Measure-Object -Property "VMSockets" -Sum).Sum
$NumofHostCPUs = ($hostinfo | Measure-Object -Property "CPUCores" -Sum).Sum
$HostVM2coreRatio = $NumofVMCPUs / $NumofHostCPUs
$TotalHostRAM = ($hostinfo | Measure-Object -Property "MemorySizeGB" -Sum).Sum / 1024
$TotalVMRAM = ($vminfo | Measure-Object -Property "VMMem" -Sum).Sum / 1024 / 1024
$NumOfHosts = $hostinfo.count
$NumOfHostsSockets = ($hostinfo | Measure-Object -Property "CPUSockets" -Sum).Sum

## This section is where you paste the code output by powerBI

$endpoint = ""
$payload = @{
"Date" = $date
"Total Storage" = $TotalStorage
"Available Storage" = $AvailableStorage
"NumofVMs" = $NumofVMs
"NumofVMCPUs" = $NumofVMCPUs
"NumofHostCPUs" = $NumofHostCPUs
"HostVM2coreRatio" = $HostVM2coreRatio
"TotalHostRAM" = $TotalHostRAM
"TotalVMRAM" = $TotalVMRAM
"NumOfHosts" = $NumOfHosts
"NumOfHostsSockets" = $NumOfHostsSockets
Invoke-RestMethod -Method Post -Uri "$endpoint" -Body (ConvertTo-Json @($payload))


Now that we have data in the dataset you can create the report. Click the create button in powerBI (the one under your pretty picture) and click report.  It will ask your to select a dataset, select the one we just created.

In the fields selection area you will see all the datapoints we setup in the dataset.  Each one should contain the data that we just posted via the powershell script.


I am not going to get into how to use powerBI during this post as there are plenty of google-able blogs that have already been written on the topic.  But for a quick example, this is what my initial report looks like.


The sky is the limit here when it comes to how you present the data and build your report.  PowerBI is a really neat (and currently cheap) tool that Microsoft offers to build good looking dashboards and reports.  This example is just what I started with in an attempt to play with streamed datasets.  You can add/remove as many data points as you want to build this report out as you wish.  You can also use this method on other stuff outside of just VMware.  VMware is just the product I choose to test with.

Some limitations to this method.

  1. once data post into the dataset it cannot be removed.  Streamed datasets are a preview feature at time of posting so this may not be true in the future.
  2. data is not real-time.  Only refreshes when the powershell script runs.
  3. PowerBI cannot manipulate data, only report on it.  If it can I have not found it yet.  This means you cannot do math on two sets of data to come up with a third datapoint.  Thats why you see math being done via powershell and then posting the result to powerBI for reporting.  pCPU to vCPU ratio is a good example of this.


Clear SEL Logs for ESXi Hosts

I recently had a problem where VMware was reporting a memory module failure for all of our UCS blades.  After working with Cisco and VMware we came to the conclusion that the alerts were false and were caused SEL logs being full on the hosts.  To clear these logs VMware support requested that I run “localcli hardware ipmi sel clear” on every host and then reset the management agents.  I have about 100 hosts that needed this done so going one by one was not something I wanted to do.  PowerCLI to the rescue!

You will need the latest PowerCLI installed for this to run as is.  PowerCLI changed to module based in the latest versions.  You will also need to install Posh-SSH from the PowerShell Gallery.  If unsure how to do that, consult the manual (google)!

#Script to clear the SEL logs of each host in a cluster.
#Requires Posh-ssh to be installed from the powershell gallery
#Requires Latest PowerCLI to be installed (Module based, not snapin based)

Import-Module -Name VMware.VimAutomation.Core

Connect-VIServer vcenter

$cluster = "ClusterName"
$esxilogin = Get-Credential -Credential "root"

$esx_all = Get-Cluster $cluster| Get-VMHost

foreach ($esx in $esx_all){

$sshService = Get-VmHostService -VMHost $esx.Name | Where { $_.Key -eq “TSM-SSH”}
Start-VMHostService -HostService $sshService -Confirm:$false
# Connect with ssh and execute the commands
New-SSHSession -ComputerName $esx.Name -AcceptKey -Credential $esxilogin
Invoke-SSHCommand -SessionId 0 -Command "localcli hardware ipmi sel clear; nohup /sbin/ restart > foo.out 2> foo.err < /dev/null &"
#pause to allow management agents to fire back up
Start-Sleep -Seconds 75
Remove-SSHSession -SessionId 0
Stop-VMHostService -HostService $sshService -Confirm:$false

Adding Multiple Disks to VMs with PowerCLI

I don’t know about all of you, but it seems like I get a request to build a new SQL server every couple weeks.  Whether or not we should have that many SQL servers is a different matter, but we do, so I got tired of building the same thing over and over.  You could just build a VM template just for SQL deployments or you could do it using Powershell like I am writing about.  Our SQL DBAs have a certain configuration they want to be laid down on each new SQL server we pump out.  The problem.. its 12 disks.  yes, 12.  So I have to add 12 disks to the VM, then initialize them, then format them and mount to the correct location.  It takes a while and its boring so I want to get it over with ASAP and move on to more youtu… err work..

Before we start, assumptions here are that you already have a VM deployed from a template and its running.  For this script, I have added a 2nd SCSI controller (Paravirtual) to the VM for all the SQL Disks.

Adding disks to the VM

This will add the disks I need to the VM.  If you looking at this and thinking; you’re not using RDMs for SQL?  You’re using Thin provisioning for SQL?  You’re not using different Datastores for each disk?  Are you crazy?.  The answer, yes, yes I am.  But let us stay on topic and not get into the weeds for this post.

$VM = get-vm -name yourvmname

New-HardDisk -CapacityGB 50 -StorageFormat Thin -Controller "SCSI controller 0" 
New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 250 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"
New-HardDisk -VM $VM -CapacityGB 50 -StorageFormat Thin -Controller "SCSI Controller 1"


Do all the “stuff” inside the VM

The script is pretty easy to read and is pretty self-explanatory.  Basically, it is doing all the steps I would have done by hand before.

#Bring Disks Online
get-disk | where operationalstatus -eq Offline | Set-Disk -IsOffline:$false

#Find RAW online disks and init with GPT
get-disk | where PartitionStyle -eq RAW | Initialize-Disk -PartitionStyle GPT

#Make mount point dirs
mkdir D:\DBDataFiles01
mkdir D:\DBDataFiles02
mkdir D:\DBDataFiles03
mkdir D:\DBDataFiles04
mkdir D:\DBLogFiles01
mkdir D:\DBLogFiles02
mkdir D:\DBLogFiles03
mkdir D:\DBLogFiles04
mkdir D:\TempDB01
mkdir D:\TempDB02
mkdir D:\TempDB03
mkdir D:\TempDB04

$i = get-disk -Number 2
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles01"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles01 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 3
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles02"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles02 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 4
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles03"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles03 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 5
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBDataFiles04"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBDataFiles04 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 6
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles01"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles01 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 7
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles02"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles02 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 8
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles03"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles03 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 9
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\DBLogFiles04"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel DBLogFiles04 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 10
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB01"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB01 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 11
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB02"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB02 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 12
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB03"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB03 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

$i = get-disk -Number 13
New-Partition -DiskNumber $i.Number -UseMaximumSize
Add-PartitionAccessPath -DiskNumber $i.Number -PartitionNumber 2 –AccessPath "D:\TempDB04"
Get-Partition –Disknumber $i.Number –PartitionNumber 2 | Format-Volume –FileSystem NTFS –NewFileSystemLabel TempDB04 –Confirm:$false
Get-Partition -DiskNumber $i.Number -PartitionNumber 2 | Set-Partition -NoDefaultDriveLetter:$True

A couple interesting notes.  The “Set-Partition -NoDefaultDriveLetter:$True” needs to be run or the first time you reboot the VM all your disks will get a drive letter assigned to them.  Since we are using mount point here, we don’t need a drive letter.

Make sure the disk numbers match up to what is shown in disk management or via get-disk if your on core with no GUI.

I realize there are probably different ways to code this to make it shorter.  If that is something you want to improve upon then knock yourself out.  This was the quick and easy way I threw it together to GSD (get stuff done).

Set DNS and Hostname on all ESXi Hosts in a Cluster

This script can be used to configure DNS settings on each host in the cluster.  It will set the DNS Server addresses and will also set the hostname to match what was used when adding it to vcenter.  If you added the host to vcenter by IP then you will want to comment that section out.

As with any VMWare script, you will need the latest version of VMWare PowerCLI installed to run this.

$Cluster = "Clustername"
$ESXHosts = Get-Cluster $Cluster | Get-VMHost

ForEach ($ESXHost in $ESXHosts){

#Set DNS Servers
Get-VMHost | Get-VMHostNetwork | Set-VMHostNetwork -DnsAddress [],[]

#Set DNS domain and search domains
Get-VMHost | Get-VMHostNetwork | Set-VMHostNetwork -Domain domain -SearchDomain,,

#Get hostname from vcenter, set Hostname
$hostnamearray = $".")
$hostname = $hostnamearray[0]
Get-VMHostNetwork -VMHost $ESXHost | Set-VMHostNetwork -HostName $hostname