Hi all
I would like to export to CSV all VM's with Custom Fields (6) and their values, something like VMname | Value1 | Value2 | etc | Value6| and second script which will let me import custom fields values from that report back to VM's.
Any ideas how to do it ?
You can use the first PowerCLI script to export annotations (custom fields) to a .csv file VMAnnotations.csv:
Get-VM | ForEach-Object { $VM = $_ $VM | Get-Annotation |` ForEach-Object { $Report = "" | Select-Object VM,Name,Value $Report.VM = $VM.Name $Report.Name = $_.Name $Report.Value = $_.Value $Report } } | Export-Csv -Path VMAnnotations.csv -NoTypeInformation
And you can use the second script to import them:
Import-Csv -Path VMAnnotations.csv | Where-Object {$_.Value} | ForEach-Object { Get-VM $_.VM | Set-Annotation -CustomAttribute $_.Name -Value $_.Value }
Regards, Robert
You can use the first PowerCLI script to export annotations (custom fields) to a .csv file VMAnnotations.csv:
Get-VM | ForEach-Object { $VM = $_ $VM | Get-Annotation |` ForEach-Object { $Report = "" | Select-Object VM,Name,Value $Report.VM = $VM.Name $Report.Name = $_.Name $Report.Value = $_.Value $Report } } | Export-Csv -Path VMAnnotations.csv -NoTypeInformation
And you can use the second script to import them:
Import-Csv -Path VMAnnotations.csv | Where-Object {$_.Value} | ForEach-Object { Get-VM $_.VM | Set-Annotation -CustomAttribute $_.Name -Value $_.Value }
Regards, Robert
I wrote this post last year on this topic on my old blog. http://day2dayadmin.blogspot.com/2009/10/whos-vm-is-this-custom-fields-for-vms.html
Are you looking for logic to only get VMs with the attribute populated or a full report?
Hi
Sorry for late response, works like a charm
Thank you Robert
Hi -
Would this be correct if I want to export host by host, and not all at once?
Host by host: Get-VMhost NAME | Get-VM | ForEach-Object .........
Would the import be the same?
Kind regards,
Bjørn.
Yes, that should work.
Hello,
Is there a way to export annotation attribute into columns instead of rows for both export & import? It would make the CSV easier to update in EXCEL as column repeats. Can you modify the import & export script to write & read the CSV with the table below as csv?
Table
VM | DNS | Expiration | External | Owner | Purpose |
Do you mean something like this?
$Report = @()
Get-VM | foreach{
$Summary = "" | Select VM
$Summary.VM = $_.Name
$_ | Get-Annotation | foreach{
Add-Member -InputObject $Summary -MemberType NoteProperty -Name $_.Name -Value $_.Value
}
$Report += $Summary
}
$Report | Export-Csv -Path Annotations.csv -Delimiter ";" -NoTypeInformation
Yes that worked great. Do you have the script that can import/update in that format? Thanks
Try:
## Set CSV Path
$CSVPath = "C:\file.csv"
## Import CSV
$CSV = Import-CSV -Path $CSVPath #-Delimiter ";" ## Use -Delimiter if needed
## Get all Columns in the CSV
$Properties = $CSV | Get-Member | ?{$_.MemberType -eq "NoteProperty"}
## Set Annotations
foreach($row in $CSV){
$VM = Get-VM $row.VM
## For each Column
foreach($Property in $Properties){
## Get the name of the current Column
$AnnotationName = $Property.Name
## Skip column if no matching Custom Attribute exist
if($VM | Get-Annotation | ?{$_.Name -eq $AnnotationName}){
## Check that column is not empty
if($row.$AnnotationName){
## Set the new value
$VM | Set-Annotation -CustomAttribute $AnnotationName -Value $row.$AnnotationName
}
}
}
}
When i ran the import i received a error below.
Get-Member : No object has been specified to the get-member cmdlet.
At C:\PSH\ImportATTRColumns.ps1:7 char:22
+ $Properties = $CSV | Get-Member | ?{$_.MemberType -eq "NoteProperty"}
+ ~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-Member], InvalidOperationException
+ FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand
Input file
"CLONE-WIKIJIRA;""https://clone-wikijira.ibaset.com:8963"";""No"";""No"";""MIS"";""Testing Jira Wiki upgrades"""
Try removing the comment for the import-csv:
$CSV = Import-CSV -Path $CSVPath #-Delimiter ";" ## Use -Delimiter if needed
It could be a formatting issue, which could easily be observed by printing the $CSV variable.
Got the same error and i tried different formats with quotes or delimeter
First try
#$CSV = Import-CSV -Path $CSVPath #-Delimiter ";" ## Use -Delimiter if needed
$CSV = Import-CSV -Path $CSVPath ";"
## Get all Columns in the CSV
$Properties = $CSV | Get-Member | ?{$_.MemberType -eq "NoteProperty"}
Second Try
#$CSV = Import-CSV -Path $CSVPath #-Delimiter ";" ## Use -Delimiter if needed
$CSV = Import-CSV -Path $CSVPath
## Get all Columns in the CSV
$Properties = $CSV | Get-Member | ?{$_.MemberType -eq "NoteProperty"}
Get-Member : No object has been specified to the get-member cmdlet.
At C:\psh\ImportATTRColumnsND.ps1:8 char:22
+ $Properties = $CSV | Get-Member | ?{$_.MemberType -eq "NoteProperty"}
+ ~~~~~~~~~~
+ CategoryInfo : CloseError: (:) [Get-Member], InvalidOperationException
+ FullyQualifiedErrorId : NoObjectInGetMember,Microsoft.PowerShell.Commands.GetMemberCommand