Export virtuals with Tags to CSV file

[JDMils_Interact]

Seems easy, and I can get the data I need, I just cannot get it into the CSV file the way I want. Here's what I'm looking for:

CSV file format:

VM_Name, Tag#1, Tag#2, Tag#3, Tag#n

The CSV file should have all virtuals in the vCenter, and include all tags against each virtual, one per column- this last bit is giving me headaches!

I've got the following code & outputs:

Script #1:

$Output = @()
foreach ($vm in Get-VM) 
	{
	$tags = (Get-TagAssignment -Entity $vm)
	$TagColl =@()
	foreach ($Tag in $Tags)
		{
		if ($Tag)
			{
			$TagColl += $Tag 
			# $TagColl += ", "
			}
		}
		$VMName = $vm.Name
       $Output += $VMName + $tagcoll}
	}
# $output
$Output | Select * |  Export-CSV -Path "TagExport.csv"

Output:

#TYPE Selected.System.String
Length
29
29
29
29
29
29

Script #2:

Get-VM | Select-object Name,
   @{N = 'vCenter'; E = {([system.uri]$_.ExtensionData.Client.ServiceUrl).Host}},
   @{N = 'Tags'; E = {(Get-TagAssignment -Entity $_).Tag -join ', '}} | Export-Csv -path “tagexport.csv” –NoTypeInformation -UseCulture

Output:

CRPsldcp01 MyvCenter01 Company.operations.customer.code/CRP, Company.operations.object.status/decommissioned, Company.operations.server.role/scilodc

Problem is that the tags are all in one column.

Script #3:

Get-VM | Select Name,@{Name="Tags";Expression={(Get-TagAssignment -Entity $_).Tag.Name}} | Export-Csv -Path vCenterP1-Tags.csv -Encoding ascii -NoTypeInformation

Output:

CRPsldcp01 CRP decommissioned scilodc

Problem is that the tags are all in one column.

Can someone help me out to extract the tags into separate columns pls?

[LucD]

You could do something like this

$allVM =  Get-VM | Sort-Object -Property Name
$assigned = $allVM | Get-TagAssignment
$ordered = $assigned.Tag.Name | Sort-Object -Unique

$allVM | ForEach-Object -Process {
  $obj = [ordered]@{
    VM = $_.Name
  }
  $ordered | ForEach-Object -Process {
    $obj.Add($_, '')
  }
  $assigned | Where-Object { $_.Entity.Name -eq $obj.VM } |
  ForEach-Object -Process {
    $obj[$_.Tag.Name] = 'X'
  }
  New-Object -TypeName PSObject -Property $obj
} | Export-Csv -Path .\report.csv -NoTypeInformation -UseCulture

[JDMils_Interact]

Thanks LucD,

Very nice script however it did not present the Categories & Tags as I would hoped for. The result looked like this:

VM, tag#1, tag#2

VMName1, x
VMName2, , x

So based on your gr8 work, I modified the script a bit to give me what I wanted:

VM, Category#1, Category#2

VMName1, TagName01
VMName2,     , TagName02
VMName3, TagName01

And now I'm happy. Although, my changes may be doing superfluous actions, specifically this section of code which adds the Categories to the top of the table as this code seems to be cycling thru everytime a new VM is processed:

    # Create a column header using the Categories.
    $assigned | ForEach-Object -Process {
    if (!($Obj.Keys -Contains $_.Tag.Category.Name))
        {
        $obj.Add($_.Tag.Category.Name, '') 
        }
                                        }

Please let me know if the code can be made more efficient as I still don't understand how this part works. Here's the full code which I modified:

#
# Get a list of all VMs in connected vCenter
#
$allVM =  Get-VM | Sort-Object -Property Name
#
# Get all Categories & Tags
#
$assigned = $allVM | Get-TagAssignment
$ordered = $assigned.Tag.Name | Sort-Object -Unique

#
# Cycle through all VMs and associate the Categories & Tags to those with 
# Categories & Tags assigned.
#
$allVM | ForEach-Object -Process {
     
    $obj = [ordered]@{
    VM = $_.Name
                    }
    # Create a column header using the Categories.
    $assigned | ForEach-Object -Process {
    if (!($Obj.Keys -Contains $_.Tag.Category.Name))
        {
        $obj.Add($_.Tag.Category.Name, '') 
        }
                                        }
    # Assign the Category & Tag.    
    $assigned | Where-Object { $_.Entity.Name -eq $obj.VM } |
    ForEach-Object -Process {
        $obj[$_.Tag.Category] = $_.Tag.Name
                          }
    New-Object -TypeName PSObject -Property $obj
  } | Export-Csv -Path .\report.csv -NoTypeInformation -UseCulture

Thanks again for helping out!

[LucD]

Ok, so you want the Category as the column header, but what if a VM has multiple Tags in the same Category?

[LucD · Accepted solution]

This should take care of multiple tags in the same category

#
# Get a list of all VMs in connected vCenter
#
$allVM =  Get-VM | Sort-Object -Property Name
#
# Get all Categories & Tags
#
$assigned = $allVM | Get-TagAssignment
$ordered = $assigned.Tag.Category.Name | Sort-Object -Unique

#
# Cycle through all VMs and associate the Categories & Tags to those with
# Categories & Tags assigned.
#
$allVM | ForEach-Object -Process {
    $obj = [ordered]@{
      VM = $_.Name
    }

    # Create a column header using the Categories.
    $ordered |
    ForEach-Object -Process {
          $obj.Add($_, '')
    }
    $assigned | Where-Object { $_.Entity.Name -eq $obj.VM } |
    Group-Object -Property {$_.Tag.Category.Name} |
    ForEach-Object -Process {
      $obj[$_.Name] = $_.Group.Tag.Name -join '|'
    }
    New-Object -TypeName PSObject -Property $obj
} | Export-Csv -Path .\report.csv -NoTypeInformation -UseCulture

[JDMils_Interact]

I did not consider multiple tags in the same Category as my tags are one-2-one with Categories- it's just how it worked out! But your changes makes the code fool-proof. Thank you so much for your help.