rxjoseph
Enthusiast
Enthusiast

How to fetch a Lookup Table to add data to CSV

Hi LucD

Purpose information extracted for VM migration

I have a script to extract information from a list of VMs from a text file and populate the output to CSV 

This works fine

This is the output

VMNameMemoryGBNumCpuSource_ClusterSource_vCenterVM-SizeTarget_Cluster
aaaa84CLUSTER233xxxxxxxx170#
bbbb84CLUSTER234xxxxxxxx810#
cccc42CLUSTER235xxxxxxxx180#
dddd144CLUSTER236xxxxxxxx80#

 

However I have one field which is Target_Cluster and i am struggling to populate the Target_cluster from the lookup table

 

Lookup Table from a CSV

I have to match the source cluster and update the target cluster 

 

Source_cluster    TargetC_Cluster
CLUSTER233       CLUSTER502
CLUSTER234       CLUSTER506
CLUSTER235       CLUSTER504
CLUSTER236       CLUSTER503
CLUSTER237       CLUSTER500
CLUSTER238       CLUSTER501
CLUSTER239       CLUSTER500
CLUSTER240       CLUSTER502
CLUSTER500      CLUSTER500
CLUSTER501        CLUSTER501
CLUSTER502      CLUSTER502
CLUSTER503       CLUSTER503

 

Original Script

 


###############################################################
## Collect VM information for NSX-T Onboarding ###
###############################################################


# Clear-Host

""
Write-host " Start Collecting VM information for ... " -ForegroundColor Yellow

""
Write-Host "PLEASE WAIT..." -ForegroundColor Yellow
""

$Report =@()

 

$CollectVMlist = Get-Content -path $TEXTfile

foreach ($VMlist in $CollectVMlist) {

Try {

$report += get-cluster |get-vm $VMlist -ErrorAction Stop| Select-object @{N='VMName' ; E={$_.name}},


@{N='MemoryGB' ; E={[math]::Round($_.MemoryGB)}},


@{N='NumCpu' ; E={$_.NumCpu}},


@{N='Source-Cluster';E={(Get-Cluster -VM $_).Name}},


@{N="Source-vCenter"; E={$_.Uid.Split(':@')[1]}},


@{N='VM-Size';E={(Get-HardDisk -VM $_ | Measure-Object -Sum CapacityGB).Sum}},


@{N = ”Target-Cluster”; E = { '#' } },

}


Catch {
""
Write-Host "Unable to find VM" $VMlist "in any of the vCenters. (check for Typo or spaces and rerun the report )" -ForegroundColor Yellow

}
""

}


#####################
### Export Report ###
#####################

Write-Host "Exporting report.... Please wait..." -ForegroundColor Yellow
""
$report | Export-Csv $ReportPath -NoTypeInformation -UseCulture

""
Write-Host "You can download the report from the following location" -ForegroundColor Green

""

Write-host "$ReportPath" -ForegroundColor Yellow

 

 

 

Labels (1)
Tags (1)
0 Kudos
4 Replies
LucD
Leadership
Leadership

You mean something like this?

$data = @'
Source_cluster,TargetC_Cluster
CLUSTER233,CLUSTER502
CLUSTER234,CLUSTER506
CLUSTER235,CLUSTER504
CLUSTER236,CLUSTER503
CLUSTER237,CLUSTER500
CLUSTER238,CLUSTER501
CLUSTER239,CLUSTER500
CLUSTER240,CLUSTER502
CLUSTER500,CLUSTER500
CLUSTER501,CLUSTER501
CLUSTER502,CLUSTER502
CLUSTER503,CLUSTER503
'@

$tab = @{}
ConvertFrom-Csv -InputObject $data -PipelineVariable row |
ForEach-Object -Process {
  $tab.Add($row.Source_cluster,$row.TargetC_Cluster)
}

# Test
$tab['Cluster234']


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
LucD
Leadership
Leadership

You can use an external CSV, just use the Import-Csv in that case.


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos
rxjoseph
Enthusiast
Enthusiast

Hi LucD

Yes that matches the list, how do i populate this to the existing CSV ?

or Do i need to create a new CSV and populate the results ?

Please advise

Many thanks

RXJ

 

 

 

Tags (1)
0 Kudos
LucD
Leadership
Leadership

If you created the $tab, you could do

@{N = ”Target-Cluster”; E = { $tab[(Get-Cluster -VM $_).Name] } },


Blog: lucd.info  Twitter: @LucD22  Co-author PowerCLI Reference

0 Kudos