Enthusiast
Enthusiast

VM Notes (Annotations) inconsistant format - want to export

Our VM notes (annotations) have several styles depending on what point in history and who entered them. One thing that is common is there are multiple lines in the notes. One 'gotcha' I foresee problems in exporting the notes is some are using full colons and dates.

For example:

Team: the one that asks for VMs

Record ID: <data>

Date: <data>

Deployed by: <data>

Other VMs have no notes. Others have a one or two liner of text.

I'd like to export to csv in the following format:

VM NameHeader 2Header 3Header 4
machine1Team: InfrastructureDeployment Date: June 20, 2019Deployed by: Fred
machine2TelephonyTemplate used: xxxxxxxx
machine3line of textanother line of text

One column per line of notes.

0 Kudos
5 Replies
Leadership
Leadership

To make sure I understood correctly, there is a maximum of 4 lines in any of the Notes?
And the 1st line should appear under Header1 I assume?


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

0 Kudos
Enthusiast
Enthusiast

Luc, there is very little consistency. Notes can contain up to 10 lines but have as few as 0.

0 Kudos
Leadership
Leadership

And do you want all those lines in the output?
Or only the first 4?


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

0 Kudos
Enthusiast
Enthusiast

My apologies. If the notes has 7 lines, I want 7 columns. If the notes has 3 lines, I want 3 columns. If it has 10 lines, then 10 columns.

This will end up with a csv with the vm name and the number of columns equal to the max number of lines in the annotations; some VMs will have blank columns due to the fact they had less notes than other VMs. This is a problem with inconsistency and why I prefer tags.

I hope this makes more sense. I want all the notes output with column separators when there is a line break in the notes. In the table I added, Column 1 (Header 1) = vmname

0 Kudos
Leadership
Leadership

Since Export-Csv use the 1st element in an array to determine how many columns there will be in a CSV, the best way to handle this would be to provide the same number columns to each entry.

Get-VM -PipelineVariable vm |

ForEach-Object -Process {

    $obj = [ordered]@{

        VM = $vm.Name

    }

    1..10 | ForEach-Object -Process {

        $obj.Add("Line$_",'')

    }


    $lineNr = 1

    $vm.Notes.Split("`r`n") | ForEach-Object -Process {

        $obj.Item("Line$lineNr") = $_

        $lineNr++

    }

    New-Object -TypeName PSobject -Property $obj

} | Export-Csv -Path .\report.csv -NoTypeInformation -UseCulture


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

0 Kudos