###################################################
# vSphere ESXi Bulk Configuration Script
# Giuliano Bertello
# giuliano.bertello@gmail.com
# Version 2.0
# Last update: 02/11/2014
###################################################

cls

# Functions ===========================================================================================

Function FindStringGetPosition {
Param ($StringToFind, $ColumnToSearch)
	$RowMatch = 0
	$ColumnMatch = 0
	if ($ColumnToSearch -eq $null) { 
		$ColumnMax = ($Worksheet.UsedRange.Columns).Count
	}
	else { $ColumnMax = $ColumnToSearch }
	
	for($CurrentRow = 1 ; $CurrentRow -le $RowMax ; $CurrentRow++) {
		for ($CurrentColumn = 1; $CurrentColumn -le $ColumnMax; $CurrentColumn++) {
		if ($WorkSheet.Cells.Item($CurrentRow,$CurrentColumn).Value2 -eq $StringToFind ) { 
			$RowMatch = $CurrentRow
			$ColumnMatch = $CurrentColumn
			break
			}
		}
	}
 Return $RowMatch, $ColumnMatch	
}

Function RemoveAllSpaces {
Param ($InputString)

Return $InputString -Replace '\s+', ""
}

Function AddQuotes {
Param ($InputString)
Return '"'+$InputString+'"'
}

Function SplitString {
Param ($InputString,$Delimiter,[int]$Position)

Return ($InputString -split $DelimiGetter)[$Position]

}

Function GetVMnics {
Param ($InputString)
	$a = SplitString $InputString ',' 0
	$b = SplitString $InputString ',' 1
	$vSwitch_Mgmt_Uplinks = ($a, $b)
	Return $vSwitch_Mgmt_Uplinks
}

Function ToFQDN {
Param ($Hostname,$Suffix)
	$FQDN = RemoveAllSpaces ($Hostname+"."+$Suffix)
	Return $FQDN
}

Function AddLicense {
Param ([String]$SerialNumber)

	$si = Get-View ServiceInstance
	$LicManRef=$si.Content.LicenseManager
	$LicManView=Get-View $LicManRef
	$License = New-Object VMware.Vim.LicenseManagerLicenseInfo
	$License.LicenseKey = $SerialNumber
	$LicManView.AddLicense($License.LicenseKey,$null)
}

# The following functions by Damian Karlson https://damiankarlson.com/2010/09/06/vsphere-licensing-via-powercli/
Function Get-LicenseKey {
Param ($LicName)

    $licenses = $LicMgr.Licenses | Where {$_.Name -eq $LicName}
    foreach ($license in $licenses) {
            if ( (($license.Total - $license.Used) -ne "0") -or (($license.Total - $license.Used) -lt "0") )  {
                return $license.LicenseKey
                break
            }
    }
}

Function Get-VMHostId {
Param ($Name)
    $vmhost = Get-VMHost $Name | Get-View
    Return $vmhost.Config.Host.Value
}
 
function Set-LicenseKey {
Param ($VMHostId, $LicKey, $Name)
	$license = New-Object VMware.Vim.LicenseManagerLicenseInfo
	$license.LicenseKey = $LicKey
	$LicAssMgr.UpdateAssignedLicense($VMHostId, $license.LicenseKey, $Name)
}
 
function Get-License {
Param ($VMHostId)
	$details = @()
	$detail = "" |select LicenseKey,LicenseType,Host
	$license = $LicAssMgr.QueryAssignedLicenses($VMHostId)
	$license = $license.GetValue(0)
	$detail.LicenseKey = $license.AssignedLicense.LicenseKey
	$detail.LicenseType = $license.AssignedLicense.Name
	$detail.Host = $license.EntityDisplayName
	$details += $detail
	Return $details
}

# End Functions ========================================================================================

# Variables============================================================================================
$strPath="C:\Works\Dropbox\VMware\Documentation\PowerCLI\vSphereDeploymentSettings.xls"
$objExcel=New-Object -ComObject Excel.Application
$objExcel.Visible=$False
$TabName = 'HostNetworkConfig'
$WorkBook = $objExcel.Workbooks.Open($strPath)
$Worksheet = $workbook.sheets.item("HostNetworkConfig")
$RowMax = ($worksheet.UsedRange.Rows).Count
$ColumnMax = ($Worksheet.UsedRange.Columns).Count
$BeginGlobalSettingsString = "BeginGlobal"
$BeginNetworkSettingsString = "BeginNetwork"
$EndGlobalSettingsString = "EndGlobal"
$EndNetworkSettingsString = "EndNetwork"
$DefaultMgmtPortGroup = "Management Network"
$DefaultVMNetwork = "VM Network"
# End Variable ========================================================================================

# Main =================================================================================================

# Find Cell(Row,Column) position of tags begin/end global settings and network configuration
$BeginGlobalSettingsPosition = FindStringGetPosition $BeginGlobalSettingsString 1
$EndGlobalSettingsPosition = FindStringGetPosition $EndGlobalSettingsString 1
$BeginNetworkSettingsPosition = FindStringGetPosition $BeginNetworkSettingsString 1
$EndNetworkSettingsPosition = FindStringGetPosition $EndNetworkSettingsString 1

# Load the global settings into hash table array
$GS = @{"Attribute" = "Value"}
for($CurrentRow = $BeginGlobalSettingsPosition[0] + 1 ; $CurrentRow -le $EndGlobalSettingsPosition[0] - 1; $CurrentRow++) {
	$GS[$WorkSheet.Cells.Item($CurrentRow,1).Value2] = RemoveAllSpaces $WorkSheet.Cells.Item($CurrentRow,2).Value2
}

# Build FQDN for vCenter
$vCenterServer = ToFQDN $GS["vCenterServer"] $GS["DomainSuffix"]
# Connect to vCenter
Connect-VIServer -Server $vCenterServer -User $GS["DomainUser"] -Password $GS["DomainPassword"] -SaveCredentials

# Create datacenter
New-Datacenter -Location (Get-Folder -NoRecursion) -Name $GS["Datacenter"]
# Add Licenses
AddLicense $GS["vCenterLicense"]
AddLicense $GS["vSphereLicense"]

# Initialise the License Manager modules
$ServInst = Get-View ServiceInstance
$LicMgr = Get-View $ServInst.Content.LicenseManager
$LicAssMgr = Get-View $LicMgr.LicenseAssignmentManager
# Given the license serial number I extract the name, for example "VMware vSphere 5 Enterprise", rather than type it manually
$LicName = $LicMgr.Licenses | Where {$_.LicenseKey -eq $GS["vSphereLicense"]} | % {$_.Name}
$LicKey = Get-LicenseKey -LicName $LicName

# Add ESXi hosts to the datacenter and license them
for($CurrentRow = ($BeginNetworkSettingsPosition[0]+2) ; $CurrentRow -le ($EndNetworkSettingsPosition[0]-1) ; $CurrentRow++) {
	$ESXiHost = ToFQDN $WorkSheet.cells.item($CurrentRow,1).Value2 $GS["DomainSuffix"]
	Add-VMHost $ESXiHost -Location $GS["Datacenter"] -User root -Password $GS["RootPwd"] -Confirm:$false -Force:$true
	#Given -RunAsync doesn't wait for the host to be "Connected" here I wait until it is, otherwise Set-LicenseKey will fail
	While ( (Get-VMHost $ESXiHost).ConnectionState -eq "Disconnected") {}
	$VMHostId = Get-VMHostId $ESXiHost
	Set-LicenseKey -LicKey $LicKey -VMHostId $VMHostId -Name $null
}

#After all hosts have been added and licensed, I load the network settings from the spreadsheet into an array and execute the host configuration
$NS = @("Host Network Settings")
for($CurrentRow = $BeginNetworkSettingsPosition[0]+2 ; $CurrentRow -le $EndNetworkSettingsPosition[0]-- ; $CurrentRow++) {
	for ($CurrentColumn = $BeginNetworkSettingsPosition[1]; $CurrentColumn -le $ColumnMax; $CurrentColumn++) {
 		$NS += RemoveAllSpaces $WorkSheet.cells.item($CurrentRow,$CurrentColumn).Value2
	}
	# Build host FQDN
	$ESXiHost = ToFQDN $NS[1] $GS["DomainSuffix"]
	$VMHostObj = Get-VMHost $ESXiHost
	
	# Configure DNS settings
	$DNS_Servers = ($GS["DNS1"], $GS["DNS2"])
	Get-VMHostNetwork $ESXiHost  | Set-VMHostNetwork -DomainName $GS["DomainSuffix"] -DnsAddress $DNS_Servers -DnsFromDhcp:$false -SearchDomain $GS["DomainSuffix"]
	
	# Configure NTP settings
	$NTP_ServersList = ($GS["NTPServer1"], $GS["NTPServer2"])
	Get-VMHost -Name $ESXiHost | Get-VMHostService | Where {$_.key -eq 'ntpd'} | Stop-VMHostService -Confirm:$false
 	$CurrentNTPServerList = Get-VMHostNtpServer -VMHost $ESXiHost
 	Remove-VMHostNtpServer -VMHost $ESXiHost -NtpServer $CurrentNTPServerList -Confirm:$false
 	Add-VMHostNtpServer -VMHost $ESXiHost -NtpServer $NTP_ServersList -Confirm:$false
	# Start ntpd service
	Get-VMHost -Name $ESXiHost | Get-VMHostService | Where {$_.key -eq 'ntpd'} | Start-VMHostService -Confirm:$false
	Set-VMHostService -HostService (Get-VMHostservice -VMHost $ESXiHost | Where-Object {$_.key -eq "ntpd"}) -Policy "Automatic"
	
	# From this point it's all Network Configuration
	
	#If enabled, disable IPv6 , reboot required!
	if (Get-VMHostNetworkAdapter -VMHost $ESXiHost -VMKernel | Select IPv6Enabled) { Get-VMHostNetworkAdapter -VMHost $ESXiHost -VMKernel | Set-VMHostNetworkAdapter -IPv6Enabled:$false -Confirm:$false } 
	#If still present, remove default port group "VM Network"
	if (Get-VirtualPortGroup -VMHost $ESXiHost | Where {$_.Name -eq $DefaultVMNetwork}) { Get-VirtualPortGroup -VMHost $ESXiHost -Name $DefaultVMNetwork | Remove-VirtualPortGroup -Confirm:$false }
		
	# Management Network
	#Add second vmnic uplink to the existing vSwitch0, vmnic0 being the first
	$Uplinks = GetVMnics $GS["Mgmt_Uplinks"]
	Get-VirtualSwitch -VMHost $ESXiHost -Name $GS["Mgmt_vSwitch"] | Set-VirtualSwitch -Nic $Uplinks -Mtu $GS["MTUDefault"] -Confirm:$false
	$VMHostObj | Get-VirtualPortGroup -Name $DefaultMgmtPortGroup | Set-VirtualPortGroup -Vlanid $GS["Mgmt_VLAN"] -Name $GS["Mgmt_Label"]
		
	# vMotion
	$Uplinks = GetVMnics $GS["vMotion_Uplinks"]
	$vSwitch1 = New-VirtualSwitch -VMHost $ESXiHost -Name $GS["vMotion_vSwitch"] -NumPorts $GS["vSwitchPorts"] -Nic $Uplinks -Mtu $GS["MTUJumbo"] -Confirm:$false
	New-VMHostNetworkAdapter -VMHost $ESXiHost -VirtualSwitch $GS["vMotion_vSwitch"] -PortGroup $GS["vMotion_Label"] -IP $NS[4] -SubnetMask $NS[5] -Mtu $GS["MTUJumbo"] -VMotionEnabled:$true -Confirm:$false
	$VMHostObj | Get-VirtualPortGroup -Name $GS["vMotion_Label"] | Set-VirtualPortGroup -Vlanid $GS["vMotion_VLAN"]
	Get-NicTeamingPolicy -VirtualSwitch $vSwitch1 | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceIP -Confirm:$false
	# FT: in my design I am using the same vSwitch for FT and vMotion, using different VLANS
	$Uplinks = GetVMnics $GS["FT_Uplinks"]
	New-VMHostNetworkAdapter -VMHost $ESXiHost -VirtualSwitch $GS["FT_vSwitch"] -PortGroup $GS["FT_Label"] -IP $NS[6] -SubnetMask $NS[7] -Mtu $GS["MTUJumbo"] -FaultToleranceLoggingEnabled:$true -Confirm:$false
	$VMHostObj | Get-VirtualPortGroup -Name $GS["FT_Label"] | Set-VirtualPortGroup -Vlanid $GS["FT_VLAN"]

	# Virtual machine traffic
	$Uplinks = GetVMnics $GS["VMTraffic_Uplinks"]
	$vSwitch2 = New-VirtualSwitch -VMHost $ESXiHost -Name $GS["VMTraffic_vSwitch"] -NumPorts $GS["vSwitchPorts"] -Nic $Uplinks -Mtu $GS["MTUDefault"] -Confirm:$false
	Get-NicTeamingPolicy -VirtualSwitch $vSwitch2 | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceIP -Confirm:$false
	New-VirtualPortGroup -VirtualSwitch $vSwitch2 -Name $GS["VMTraffic_Label"] -VLanID $GS["VMTraffic_VLAN"] -Confirm:$false

	# iSCSI
	$Uplinks = GetVMnics $GS["iSCSI_Uplinks"]
	$vSwitch4 = New-VirtualSwitch -VMHost $ESXiHost -Name $GS["iSCSI_vSwitch"] -NumPorts $GS["vSwitchPorts"] -Nic $Uplinks -Mtu $GS["MTUJumbo"] -Confirm:$false
	Get-NicTeamingPolicy -VirtualSwitch $vSwitch4 | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceSrcId -Confirm:$false
	New-VMHostNetworkAdapter -VMHost $ESXiHost -VirtualSwitch $GS["iSCSI_vSwitch"] -PortGroup $GS["iSCSI1_Label"] -IP $NS[8] -SubnetMask $NS[9] -Mtu $GS["MTUJumbo"] -Confirm:$false
	New-VMHostNetworkAdapter -VMHost $ESXiHost -VirtualSwitch $GS["iSCSI_vSwitch"] -PortGroup $GS["iSCSI2_Label"] -IP $NS[10] -SubnetMask $NS[11] -Mtu $GS["MTUJumbo"] -Confirm:$false
	# Set vmkernel port labels and vlan id
	$VMHostObj | Get-VirtualPortGroup -Name $GS["iSCSI1_Label"] | Set-VirtualPortGroup -Vlanid $GS["iSCSI1_VLAN"]
	$VMHostObj | Get-VirtualPortGroup -Name $GS["iSCSI2_Label"] | Set-VirtualPortGroup -Vlanid $GS["iSCSI2_VLAN"]
	Get-VirtualPortGroup -VMHost $ESXiHost -VirtualSwitch $GS["iSCSI_vSwitch"] -Name $GS["iSCSI1_Label"] | Get-NicTeamingPolicy | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceSrcId -MakeNicActive $Uplinks[0] -MakeNicUnused $Uplinks[1] -Confirm:$false
	Get-VirtualPortGroup -VMHost $ESXiHost -VirtualSwitch $GS["iSCSI_vSwitch"] -Name $GS["iSCSI2_Label"] | Get-NicTeamingPolicy | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceSrcId -MakeNicActive $Uplinks[1] -MakeNicUnused $Uplinks[0] -Confirm:$false
	
	#Add iSCSI Software Adapter
 	Get-VMHostStorage -VMHost $ESXiHost | Set-VMHostStorage -SoftwareIScsiEnabled:$true -Confirm:$false
	#iSCSI PortBinding
	Get-VMHostNetworkAdapter -VMKernel
	$portname = Get-VMHost -Name $ESXiHost | Get-VMHostNetworkAdapter | Where {$_.PortGroupName -match "iSCSI-*"} | %{$_.DeviceName}
	$vmhba = Get-VMHostHba -VMHost $ESXiHost -Type iscsi | %{$_.Device}
	$esxcli = Get-EsxCli -VMHost $ESXiHost
	$esxcli.iscsi.networkportal.add($vmhba, $false, $portname[0])
	$esxcli.iscsi.networkportal.add($vmhba, $false, $portname[1])
	Get-VMHostStorage -VMHost $ESXiHost -RescanVmfs -RescanAllHba

	# NFS
	$Uplinks = GetVMnics $GS["NFS_Uplinks"]
	$vSwitch3 = New-VirtualSwitch -VMHost $ESXiHost -Name $GS["NFS_vSwitch"] -NumPorts $GS["vSwitchPorts"] -Nic $Uplinks -Mtu $GS["MTUJumbo"] -Confirm:$false
	Get-NicTeamingPolicy -VirtualSwitch $vSwitch3 | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceSrcId -Confirm:$false
	New-VMHostNetworkAdapter -VMHost $ESXiHost -VirtualSwitch $GS["NFS_vSwitch"] -PortGroup $GS["NFS1_Label"] -IP $NS[12] -SubnetMask $NS[13] -Mtu $GS["MTUJumbo"] -Confirm:$false
	New-VMHostNetworkAdapter -VMHost $ESXiHost -VirtualSwitch $GS["NFS_vSwitch"] -PortGroup $GS["NFS2_Label"] -IP $NS[14] -SubnetMask $NS[15] -Mtu $GS["MTUJumbo"] -Confirm:$false
	# set vmkernel port labels and vlan id
	$VMHostObj | Get-VirtualPortGroup -Name $GS["NFS1_Label"] | Set-VirtualPortGroup -Vlanid $GS["NFS1_VLAN"]
	$VMHostObj | Get-VirtualPortGroup -Name $GS["NFS2_Label"] | Set-VirtualPortGroup -Vlanid $GS["NFS2_VLAN"]
	Get-VirtualPortGroup -VMHost $ESXiHost -VirtualSwitch $GS["NFS_vSwitch"] -Name $GS["NFS1_Label"] | Get-NicTeamingPolicy | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceSrcId -MakeNicActive $Uplinks[0] -MakeNicUnused $Uplinks[1] -Confirm:$false
	Get-VirtualPortGroup -VMHost $ESXiHost -VirtualSwitch $GS["NFS_vSwitch"] -Name $GS["NFS2_Label"] | Get-NicTeamingPolicy | Set-NicTeamingPolicy -LoadBalancingPolicy LoadBalanceSrcId -MakeNicActive $Uplinks[1] -MakeNicUnused $Uplinks[0] -Confirm:$false
	
}

$objExcel.Quit()
Disconnect-VIServer -Server $vCenterServer -Confirm:$false