The VM notes in our environment are unfortunately not standardized. Quite often we end up with varying formats of identifying who requested and who created vms. I am currently working on reclaiming resources and need a way to extract the notes field into a csv for one column per newline.
My desired output regardless of what information each line of the notes state is one column per line as shown below for the above example VM notes.
I'm pretty sure the solution is going to be similar to Export all Notes from a VM in one line but with modifications. It's those modifications that I'm struggling with. Any and all help is appreciated.
Accepted Solutions
LucD
Thank you once again for a speedy reply. I get the following message onscreen:
The csv reads:
I thought it was because of an inconsistancy in variable: VM = $vmName vs $name later in the code. The output was the same when I made the switch to:
Get-VM -PipelineVariable vm |
ForEach-Object -Process {
$obj = @{
VM = $vmName
}
$vm.Notes.Split("`n") |
ForEach-Object -Process {
$vmName,$value = $_.Split(':')
$obj.Add($vmName,$value)
}
$obj
}
Anything is possible, Luc. I have 3760 VMs in this particular vcenter, and we've had staff turnover over the years. I can't be 100% certain that someone didn't have two Created By enteries in one note.
I strongly suspect the VM records will only contain one "Created By" per VM, however, it is our team's desire to have each VM record with a "Created by" and "Requested For" as well as "Team".
The csv generated this time around only had a list of VM names. No notes. As well there were only 1627 rows (VMs) in the csv. It's entirely possible to have some VMs with no notes whatsoever. The error/message below generated on screen during the running of the script.
I checked my command line to see what the variables gave me for output. This particular VM did not appear on the CSV. I've tried to find a pattern of why one vm name appears on csv vs another and I can't see one. It seems random but that's atypical for your coding. I'm perplexed for sure.
**Addition to above: I just noticed this is in the csv. Not sure why there is a column header "This is for Oracle".
We are getting closer. I'm willing to do some clean up on our vcenter where there are valid duplicates. It would be nice to get the multiple errors to errors.csv. It will help with data cleanup. That being said, I believe it's generating the multiple entry line on screen when it sees blank lines in the Notes.
I like what you're doing with the csv. Creator, Deployment Date, VM, etc columns. That's awesome. I do see someone used the equal sign vs the colon "Deployment Date=2013-10-22" when I look at the csv. I'm willing to clean up my data in those cases where there are valid duplicates in the Notes entry.
We also need to fix up how the csv is dealing with random notes in the Notes entry. Those notes that aren't prefaced with a colon as shown with blue highlight in the following image.
After all is said in done, I'm going to propose to our team we use tags for standardization of required information on VMs, but that's the future.
This is where we're at, Luc.
Closer. 
1. We need to deal with blank lines.
2. If there is a word: make a column for word.
3. If there is a statement with no : then dump into a single column for that vm row. No need to make a separate column for each statement without : in it.
I'd sooner not clean up blank lines in our VMs that have notes that way, even though they're not supposed to.
I want columns for those items that have a colon.
I get this for each VM that has notes with blank lines between rows of information.
This is the corresponding notes:
My guess is the blank lines are being viewed as a multiple entry. I am willing to clean up the valid mulitple entry but yikes!! Tons of VMs with blank rows, and tons without. In the meantime, I'll start the cleanup of valid duplicate data.
No red errors this time. Just script output. Progress, I'd say.
Well that gave me an interesting csv. I'm guessing I put your suggestion in the wrong place.
Get-VM -PipelineVariable vm |
ForEach-Object -Process {
$obj = @{
VM = $vm.Name
}
if($vm.Notes){
$vm.Notes.Split("`n") | where{$_ -ne ''} |
ForEach-Object -Process {
$name,$value = $_.Split(':=')
if($obj.ContainsKey($name)){
Write-Host "VM $($vm.Name) seems to have multiple entries for $name"
}
else{
$obj.Add($name,$value)
}
}
}
else{
Write-Host "VM $($vm.Name) has no notes"
}
try{
New-Object -Typename psobject -Property $obj -ErrorAction Stop
}
catch{
Write-Host "Error creating the object for VM $($vm.Name)"
$obj.GetEnumerator()
}
} | Sort-Object -Property {($_ | Get-Member).Count} -Descending |
Export-Csv -Path .\report-noblank.csv -NoTypeInformation -UseCulture
CSV:
How about we ask for those lines in notes that have verbiage with a colon separator? From the looks of it, someone has filesystem information recorded in notes. Skip everything that isn't prefaced with XYZ: (XYZ could be anything as long as it's followed by a colon).