Export and inmopt custom fields

[arturka]

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 ?

Cheers
Artur

Please, don't forget the awarding points for "helpful" and/or "correct" answers.

[RvdNieuwendijk · Accepted solution]

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

[aerodevil]

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?

[arturka]

Hi

Sorry for late response, works like a charm

Thank you Robert

Cheers
Artur

Please, don't forget the awarding points for "helpful" and/or "correct" answers.

[bni007]

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.

[LittleNickey]

Yes, that should work.

[kenp11]

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

[LittleNickey]

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

[kenp11]

Yes that worked great. Do you have the script that can import/update in that format? Thanks

[LittleNickey]

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

            }

        }

    }

}

[kenp11]

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"""

[LittleNickey]

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.

[kenp11]

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