VMware Cloud Community
MrSiddiqui
Enthusiast
Enthusiast
‎10-24-2018 02:09 AM

Script modification to ADD MAC Address against respective NICs_ vCloud Director

Dear Friends,

Need some help to modify\update below script to add a MAC Address Column in front of each NICs IP.

It is a simple script which connects to vCloud Director, asks for VAPP and provides details abut it like VM info, Org Networks etc etc.

Attached please find the CSV generated by the script and also sample what i am looking forward to add in it.

Below is the the script which i am using so far to update my CMDB.

===========================================================

function exportTRS{
try {
# $date = (Get-Date).ToString("s").Replace(":","-")
 
$excel = New-Object -ComObject Excel.Application
 
$excel.Visible = $false
 
$workbook = $excel.Workbooks.Add()
 
$sheet = $workbook.ActiveSheet
$sheet.Name = 'AS Built'
 
$range1 = $sheet.range("1:1")
$range1.Cells.Interior.ColorIndex =1 
$range1.Cells.font.bold = $true
$range1.Cells.Font.ColorIndex = 46
$range1.Cells.HorizontalAlignment = -4108
 
$sheet.Cells.Item(1,1) = 'OrgVDC Name'
$sheet.Cells.Item(1,2) = 'vApp Name'
$sheet.Cells.Item(1,3) = 'Server Name'
$sheet.Cells.Item(1,4) = 'eth0'
$sheet.Cells.Item(1,5) = 'eth1'
$sheet.Cells.Item(1,6) = 'eth2'
$sheet.Cells.Item(1,7) = 'vCPU'
$sheet.Cells.Item(1,8) = 'Memory'
$sheet.Cells.Item(1,9) = 'Storage'
$sheet.Cells.Item(1,10) = 'Guest OS Name'
 
 
$range3 = $sheet.range("2:2")
$range3.Cells.Interior.ColorIndex = 56
$range3.Cells.Font.ColorIndex = 2
$range3.Cells.HorizontalAlignment = -4108
 
$sheet.Cells.Item(2,4) = 'Prod'
$sheet.Cells.Item(2,5) = 'Nic2'
$sheet.Cells.Item(2,6) = 'Nic3'
 
 
$range2 = $sheet.range("50:50")
$range2.Cells.Interior.ColorIndex =1 
$range2.Cells.font.bold = $true
$range2.Cells.Font.ColorIndex = 46
$range2.Cells.HorizontalAlignment = -4108
$sheet.Cells.Item(50,1) = 'OrgVDCs networks connected'
$sheet.Cells.Item(50,2) = 'Gateway'
$sheet.Cells.Item(50,3) = 'Subnet Mask'
 
 
#$column = 1
$row = 3
$nume_VAPP = read-host "Please Enter the Name of the VAPP"
write-host "Getting vApp Properties for $nume_VAPP"
$vapp = Get-CIVApp -Name $nume_VAPP
$orgtemp = $vapp.Org.Name
$name = $nume_VAPP
 
$date = (Get-Date).ToString("d")
 
write-host "Searching for organization $orgtemp"
$Org = Search-Cloud -QueryType Organization -Name "$orgtemp";
write-host "Searching for organization VDC with $($Org.id)"
$aOvdcs = Search-Cloud -QueryType AdminOrgVdc -Filter "org==$($Org.id)";
$aVApps = Search-Cloud -QueryType AdminVApp -Filter "Name==$name";
$VappOrgID = $aVApps.PropertyList.Vdc
$OrgName = Get-OrgVDC -ID $VappOrgID
 
Write-host "Getting VM information"
$vms = Get-CIVM -VApp $nume_VAPP
#$vms = search-cloud -querytype Adminvm -Filter "container==$($vapp.id)"
 
foreach ($vm in $vms ){
$hardware = $vm.ExtensionData.GetVirtualHardwareSection()
$diskMB = (($hardware.Item | where {$_.resourcetype.value -eq "17"}) | %{$_.hostresource[0].anyattr[0]."#text"} | 
 
Measure-Object -Sum).sum
$diskGB = ($diskMB/1024)
$column = 1
$adapters = Get-CINetworkAdapter -VM $vm
$sortadapters = $adapters | Sort-Object
$sheet.cells.Item($row,$column) = $OrgName.Name
$column++
$sheet.cells.Item($row,$column) = $vm.VApp.Name
$column++
$sheet.cells.Item($row,$column) = $vm.Name
$column++
$sheet.cells.Item($row,$column) = $sortadapters[0].IPAddress.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $sortadapters[1].IPAddress.IPAddressToString
$column++
        $sheet.cells.Item($row,$column) = $sortadapters[2].IPAddress.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $vm.CpuCount
$column++
$sheet.cells.Item($row,$column) = $vm.memoryGB
$column++
$sheet.cells.Item($row,$column) = $diskGB
$column++
$sheet.cells.Item($row,$column) = $vm.GuestOSFullName
$row++
}
 
 
$net = Get-CIVAppNetwork -VApp $nume_VAPP
$row = 51
$column = 1
$sheet.cells.Item($row,$column) = $net[0].Name
$column++
$sheet.cells.Item($row,$column) = $net[0].Gateway.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $net[0].Netmask.IPAddressToString
$column = 1
$row++
$sheet.cells.Item($row,$column) = $net[1].Name
$column++
$sheet.cells.Item($row,$column) = $net[1].Gateway.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $net[1].Netmask.IPAddressToString
$column = 1
$row++
$sheet.cells.Item($row,$column) = $net[2].Name
$column++
$sheet.cells.Item($row,$column) = $net[2].Gateway.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $net[2].Netmask.IPAddressToString
$column = 1
 
$objRange = $sheet.UsedRange
[void] $objRange.EntireColumn.Autofit()
 
$filename = "C:\Work\ASB_$name.xlsx"
$workbook.SaveAs($filename)
$excel.Quit()
}
catch {
  Write-Warning $_.Exception.Message
}
}
connect-ciserver XXXXXXXX -User XXXXXXX -Password XXXXXXXX
exportTRS
function exportTRS{
try {
# $date = (Get-Date).ToString("s").Replace(":","-")
 
$excel = New-Object -ComObject Excel.Application
 
$excel.Visible = $false
 
$workbook = $excel.Workbooks.Add()
 
$sheet = $workbook.ActiveSheet
$sheet.Name = 'AS Built'
 
$range1 = $sheet.range("1:1")
$range1.Cells.Interior.ColorIndex =1 
$range1.Cells.font.bold = $true
$range1.Cells.Font.ColorIndex = 46
$range1.Cells.HorizontalAlignment = -4108
 
$sheet.Cells.Item(1,1) = 'OrgVDC Name'
$sheet.Cells.Item(1,2) = 'vApp Name'
$sheet.Cells.Item(1,3) = 'Server Name'
$sheet.Cells.Item(1,4) = 'eth0'
$sheet.Cells.Item(1,5) = 'eth1'
$sheet.Cells.Item(1,6) = 'eth2'
$sheet.Cells.Item(1,7) = 'vCPU'
$sheet.Cells.Item(1,8) = 'Memory'
$sheet.Cells.Item(1,9) = 'Storage'
$sheet.Cells.Item(1,10) = 'Guest OS Name'
 
 
$range3 = $sheet.range("2:2")
$range3.Cells.Interior.ColorIndex = 56
$range3.Cells.Font.ColorIndex = 2
$range3.Cells.HorizontalAlignment = -4108
 
$sheet.Cells.Item(2,4) = 'Prod'
$sheet.Cells.Item(2,5) = 'Nic2'
$sheet.Cells.Item(2,6) = 'Nic3'
 
 
$range2 = $sheet.range("50:50")
$range2.Cells.Interior.ColorIndex =1 
$range2.Cells.font.bold = $true
$range2.Cells.Font.ColorIndex = 46
$range2.Cells.HorizontalAlignment = -4108
$sheet.Cells.Item(50,1) = 'OrgVDCs networks connected'
$sheet.Cells.Item(50,2) = 'Gateway'
$sheet.Cells.Item(50,3) = 'Subnet Mask'
 
 
#$column = 1
$row = 3
$nume_VAPP = read-host "Please Enter the Name of the VAPP"
write-host "Getting vApp Properties for $nume_VAPP"
$vapp = Get-CIVApp -Name $nume_VAPP
$orgtemp = $vapp.Org.Name
$name = $nume_VAPP
 
$date = (Get-Date).ToString("d")
 
write-host "Searching for organization $orgtemp"
$Org = Search-Cloud -QueryType Organization -Name "$orgtemp";
write-host "Searching for organization VDC with $($Org.id)"
$aOvdcs = Search-Cloud -QueryType AdminOrgVdc -Filter "org==$($Org.id)";
$aVApps = Search-Cloud -QueryType AdminVApp -Filter "Name==$name";
$VappOrgID = $aVApps.PropertyList.Vdc
$OrgName = Get-OrgVDC -ID $VappOrgID
 
Write-host "Getting VM information"
$vms = Get-CIVM -VApp $nume_VAPP
#$vms = search-cloud -querytype Adminvm -Filter "container==$($vapp.id)"
 
foreach ($vm in $vms ){
$hardware = $vm.ExtensionData.GetVirtualHardwareSection()
$diskMB = (($hardware.Item | where {$_.resourcetype.value -eq "17"}) | %{$_.hostresource[0].anyattr[0]."#text"} | 
 
Measure-Object -Sum).sum
$diskGB = ($diskMB/1024)
$column = 1
$adapters = Get-CINetworkAdapter -VM $vm
$sortadapters = $adapters | Sort-Object
$sheet.cells.Item($row,$column) = $OrgName.Name
$column++
$sheet.cells.Item($row,$column) = $vm.VApp.Name
$column++
$sheet.cells.Item($row,$column) = $vm.Name
$column++
$sheet.cells.Item($row,$column) = $sortadapters[0].IPAddress.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $sortadapters[1].IPAddress.IPAddressToString
$column++
        $sheet.cells.Item($row,$column) = $sortadapters[2].IPAddress.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $vm.CpuCount
$column++
$sheet.cells.Item($row,$column) = $vm.memoryGB
$column++
$sheet.cells.Item($row,$column) = $diskGB
$column++
$sheet.cells.Item($row,$column) = $vm.GuestOSFullName
$row++
}
 
 
$net = Get-CIVAppNetwork -VApp $nume_VAPP
$row = 51
$column = 1
$sheet.cells.Item($row,$column) = $net[0].Name
$column++
$sheet.cells.Item($row,$column) = $net[0].Gateway.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $net[0].Netmask.IPAddressToString
$column = 1
$row++
$sheet.cells.Item($row,$column) = $net[1].Name
$column++
$sheet.cells.Item($row,$column) = $net[1].Gateway.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $net[1].Netmask.IPAddressToString
$column = 1
$row++
$sheet.cells.Item($row,$column) = $net[2].Name
$column++
$sheet.cells.Item($row,$column) = $net[2].Gateway.IPAddressToString
$column++
$sheet.cells.Item($row,$column) = $net[2].Netmask.IPAddressToString
$column = 1
 
$objRange = $sheet.UsedRange
[void] $objRange.EntireColumn.Autofit()
 
$filename = "C:\Work\ASB_$name.xlsx"
$workbook.SaveAs($filename)
$excel.Quit()
}
catch {
  Write-Warning $_.Exception.Message
}
}
connect-ciserver XXXXXXXX -User XXXXXXX -Password XXXXXXXX
exportTRS
Preview file
10 KB
0 Kudos
1 Reply
MrSiddiqui
Enthusiast
Enthusiast
‎10-27-2018 09:48 PM

Hi Guys,

Can anyone guide me how to make it work.

Thanks

0 Kudos