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