Exporting VM Tags in readable format

[JBartholomew]

So I have been working on a script to import Tags from a CSV into vCenter so we can do batch imports of our vCenter tags (its much easier to edit 1000 rows of a spreadsheet than it is to do it directly in vCenter.)

See: Re: Working with Batch Tags in PowerCLI for the script itself.

The problem I have run into is I now have the import script running properly, but now I require a way to export the updated data to a readable CSV file so I can ensure the data being input is up to date. The issue with the export from powerCLI is how vCenter stores tags. within vCenter, tags are stored as TagCategory/TagName together as an object called $Tag and each $Tag is stored in a different element of the array Get-tagassignment. The problem is the input CSV is split out with each Line consisting of the VMName and which Tags it contains under each category with each category being a column.

so The CSV is as follows:

VMName     |     Category 1     |     Category 2     |      Category 3     |      Category 4      |

VM1            |                         |       Tag Value     |                           |     Tag Value         |

VM2            |     Tag Value      |      Tag Value      |     Tag Value        |                            |

Where the tags in VMWare are stored as follows:

VMName     |     Category/Tag        |

VM1            |     Category 2/Tag     |

VM1            |     Category 4/Tag     |

VM2           |     Category 1/Tag      |

VM2           |     Category 2/Tag      |

VM2           |     Category 3/Tag      |

So I am looking for a way to get the current output into the format I need, so I can basically create a cyclic loop so the updated tags can be exported back to the CSV so when the CSV is updated it already reflects any changes (such as additional VMs or Removed VMs)

[LucD]

Try something like this

foreach($vm in (Import-Csv tags.csv -Delimiter '|')){

    $vm | Get-Member -MemberType NoteProperty -Name 'Category*' | %{

        if("$($vm.$($_.Name))"){

            New-Object -TypeName PSObject -Property @{

                VMName = $vm.VMName

                'Category/Tag' = "$($_.Name)/$($vm.$($_.Name))"

            }

        }

    }

}

[JBartholomew]

Luc,

Thank you for getting back to me so quickly. It appears from what I can gather from the code, that it is the opposite of what I am looking to accomplish.

I have added a sample of the CSV file below for a little more clarity. The issue I am having is that I need I way to export from powercli all of the VMs and tags  in vmware in the format of the CSV so the CSV can be edited and imported back into to vmware with the tagging script I had written.

Sample CSV

--------------------------------------------------

Name,Environment,Client,Project

Desktop - Bob,Desktop,Internal,

Server- Web01,Prod,BigCarCompany,Website

Server- MYSQL01,DEV,,Database

---------------------------------------------------

The issue is, that without the ability to export the list of VMs with Tags, any machines that are added to vCenter will not be reflected in the CSV and will therefor never have their tags updated/added.

[LucD]

Sorry, but that seems to be a completely different CSV as the one in your original question.

I started from this

VMName | Category 1 | Category 2 | Category 3 | Category 4 |

VM1    |            | Tag Value  |            | Tag Value  |

VM2    | Tag Value  | Tag Value  | Tag Value  |            |

[JBartholomew]

my apologies, I realized after your original post my mistake. that was a visualization of how the CSV looked and not a representation of the CSV itself.

[LucD]

Just to make sure I understand the question, can you include an extract of the input CSV and the format in which you want the output to be.

[JBartholomew]

Here is the extract of the CSV

------------------------------------------------------------

Name,Environment,Client,Project

Desktop - Bob,Desktop,Internal,

Server- Web01,Prod,BigCarCompany,Website

Server- MYSQL01,DEV,,Database

--------------------------------------------------------------

and that is the format I need powercli to output in as well. within the CSV the first row (Column Headings) is what becomes the Tag Categories (Name,Environment,Client,Project)

[LucD]

Sorry, but I still don't get what you want as ouput.

In the beginning of the thread you said this should be like

VM1,Name/Desktop-Bob

VM1,Environment/Desktop

VM1,Client/Internal

VM2,Name/Server-Web01

VM2,Environment/Prod

VM2,Client/BigCarCompany

VM2,Project/Website

Is that correct ?

If yes, where do I get the VM names (VM1,VM2...) ?

Or do I compose that list from what is actually there, and not from the input CSV ?

[JBartholomew]

again, my apologies, I find web forums are difficult to explain things some times.

the output:

VM1,Name/Desktop-Bob

VM1,Environment/Desktop

VM1,Client/Internal

VM2,Name/Server-Web01

VM2,Environment/Prod

VM2,Client/BigCarCompany

VM2,Project/Website

is how VMware stores the tags in the internal database (ie.) how they appear if you do a 'Get-tagassignment'), which is not what I need for the export. I am trying to come up with a way to take that output and turn it into the output shown in the sample CSV

ie.)

Name,Environment,Client,Project

Desktop - Bob,Desktop,Internal,

Server- Web01,Prod,BigCarCompany,Website

Server- MYSQL01,DEV,,Database

[LucD]

Ok, now I got it.

Hold on, I'll convert the script

[LucD]

Try like this

$tagCat = @()

$tagTab = @{}

foreach($tag in (Get-VM | Get-TagAssignment)){

    $tagCat += $tag.Tag.Category.Name

    $key = $tag.Entity.Name

    if($tagTab.ContainsKey($key)){

    ` $val = $tagTab.Item($key)  

    }

    else{

        $val = @{}

    }

    $val.Add($tag.Tag.Category.Name,$tag.Tag.Name)

    $tagTab[$key] = $val

}

$tagCat = $tagCat | Sort-Object -Unique

$tags = foreach($row in ($tagTab.GetEnumerator() | Sort-Object -Property Key)){

    $obj = New-Object PSObject -Property @{

        VM = $row.Key

    }

    $tagCat | %{

        $obj | Add-Member -Name $_ -Value $row.Value[$_] -MemberType NoteProperty

    }

    $obj

}

$tags | Export-Csv tags.csv -NoTypeInformation -UseCulture

[JBartholomew]

Luc, you are a savior. That is exactly what I was looking for.

My issue is that I understand how arrays work, and how they are logically constructed, but I struggled to figure out the coding behind it to get the results I needed.

The only confusion I still have is the name Column is prefixing all of the names with "ClusterInvariantVMMId Veeam Status com.vmware.vdp2.is-protected com.vmware.vdp2.protected-by" and then the VM name.

I see that $key is assigned $Tag.Entity.Name which is just the name of the VM so I am not sure where the rest of that name is coming in..

[LucD]

When you do a Get-VM, you see the correct name, right ?

What if you do

Get-VM | Get-TagAssignment |

Select @{N='VM';E={$_.Entity.Name}}

If you see that same wrong name as well, I would need to have a look at the properties.

Do something like

Get-VM | Get-TagAssignment |

Select -ExpandProperty Entity

This might be a PowerCLI "feature".

Which PowerCLI version are you using ?

[JBartholomew]

I have tested this on both PowerCLI 5.5 and PowerCLI 6.0R3 with the same results.

I can see that the $Tags object has the clusterInvariantVMMId..... name for the VMname and the $Tagtab.keys also has it for each object.

I am using PowerGUI to help troubleshoot the script and am seeing a very weird thing when I attempt to pin down where this extraneous information is coming from.

If I do a Get-VM | Get-Tagassignment I do see the ClusterInvariantVMMId... and if I assign the script to a variable to better track the issue ie.) $VMInfo = Get-VM | Get-Tagassignment I see that each object has the ClusterInvariantVMMId as well, but if I expand the syncroot for the object I can see the Entity has the ClusterInvariantVMMId, and if I expand the Entity itself, the name magically changes to the correct name and drops the ClusterInvariantVMMId...

[JBartholomew]

I have pinned down the issue. its in the line

foreach($tag in (Get-VM | Get-TagAssignment)){

The Recursion of the Get-VM | Get-TagAssignment is unnecessary and was actually introducing the incorrect name to the system.

Thank you again Luc for your assistance and enlightenment in PowerCLI

Below is the updated correct code block for future reference:

$tagCat = @()

$tagTab = @{}

foreach($tag in (Get-TagAssignment)){

    $tagCat += $tag.Tag.Category.Name

    $key = $tag.Entity.Name

    if($tagTab.ContainsKey($key)){

    ` $val = $tagTab.Item($key)  

    }

    else{

        $val = @{}

    }

    $val.Add($tag.Tag.Category.Name,$tag.Tag.Name)

    $tagTab[$key] = $val

}

$tagCat = $tagCat | Sort-Object -Unique

$tags = foreach($row in ($tagTab.GetEnumerator() | Sort-Object -Property Key)){

    $obj = New-Object PSObject -Property @{

        VM = $row.Key

    }

    $tagCat | %{

        $obj | Add-Member -Name $_ -Value $row.Value[$_] -MemberType NoteProperty

    }

    $obj

}

$tags | Export-Csv tags.csv -NoTypeInformation -UseCulture

[markshannon]

Hi LucD,

great post.

this works brilliantly apart from where a vm tag is configured as multiple cardinality so only selects one tag per category field -  ie. tag category is 'machine information' and there is multiple tags assigned to that category.

so when exported under 'machine information' column it only shows 1 tag.

is their a way to split each tag from a category?

thanks in advance

mark

[LucD]

That would be possible, but how would you place that in the result (CSV file).
The columns can' just be the Category names anymore.

[LucD]

You could try something like this.
Multiple tags belonging to the same Category will be represented as Tag1|Tag2|Tag3 under the Category column header.

$objTemplate = @{

   VM = ''

}

(Get-TagCategory).Name | Sort-Object | ForEach-Object -Process {

   $objTemplate.Add($_,'')

}

Get-TagAssignment|

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

   $obj = $objTemplate.Clone()

   $obj['VM'] = $_.Name

   $_.Group | Group-Object -Property {$_.Tag.Category.Name} |

   ForEach-Object -Process {

   $cat = $_.Name

   $vTag = $_.Group | Group-Object -Property {$_.Tag.Category.Name} |

   ForEach-Object -Process {

   $_.Group.Tag.Name

   }

   $obj[$cat] = (($vTag | Sort-Object) -join '|')

   }

   New-Object PSObject -Property $obj

} |

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

[markshannon]

hi

i tried that and got the below:-

Get-TagAssignment : 05/03/2019 06:47:49 Get-TagAssignment com.vmware.vapi.std.errors.internal_server_error {'messages': [com.vmware.vapi.std.localizable_message {'id': vapi.bindings.method.impl.unexpected, 'default_message': Provider method

implementation threw unexpected exception: Read timed out, 'args': [Read timed out]}], 'data':}

At line:8 char:1

+ Get-TagAssignment|

+ ~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [Get-TagAssignment], CisException

    + FullyQualifiedErrorId : VMware.VimAutomation.ViCore.Impl.V1.Service.Tagging.Cis.TaggingServiceCisImpl.GetTagAssignment.Error,VMware.VimAutomation.ViCore.Cmdlets.Commands.Tagging.GetTagAssignment

thanks

[LucD]

When you run Get-TagAssignment on it's own, do you get the same error?

[markshannon]

interestingly i get the same error.

although tried on another vcenter and worked ok.

[LucD]

Yes, there has been an issue reported on multiple occasions with the Get-TagAssignment cmdlet.

As an alternative try the functions from Kyle's CISTag module or my rCISTag module.

[markshannon]

hi

how do i run these modules in relation to the above script that you mentioned?

thanks

[LucD]

These modules contain cmdlets that do the exact same as the current Tag cmdlets.

You only have to change all Tag-related cmdlets in your script.

[markshannon]

Hi

so i modified this script :-

$objTemplate = @{

   VM = ''

}

(Get-rCisTagCategory).Name | Sort-Object | ForEach-Object -Process {

   $objTemplate.Add($_,'')

}

Get-rCisTagAssignment|

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

   $obj = $objTemplate.Clone()

   $obj['VM'] = $_.Name

   $_.Group | Group-Object -Property {$_.Tag.Category.Name} |

   ForEach-Object -Process {

   $cat = $_.Name

   $vTag = $_.Group | Group-Object -Property {$_.Tag.Category.Name} |

   ForEach-Object -Process {

   $_.Group.Tag.Name

   }

   $obj[$cat] = (($vTag | Sort-Object) -join '|')

   }

   New-Object PSObject -Property $obj

} | Export-Csv -Path c:\temp\tagreport.csv -NoTypeInformation -UseCulture

I get the below error after a while:-

Exception calling "Add" with "2" argument(s): "Item has already been added. Key in dictionary: 'VM'  Key being added: 'VM'"

At line:8 char:4

+    $objTemplate.Add($_,'')

+    ~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : ArgumentException

New-Object : Cannot process argument because the value of argument "name" is not valid. Change the value of the "name" argument and run the operation again.

At line:41 char:4

+    New-Object PSObject -Property $obj

+    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : InvalidArgument: (:) [New-Object], PSArgumentException

    + FullyQualifiedErrorId : Argument,Microsoft.PowerShell.Commands.NewObjectCommand

thanks in advance

mark

[markshannon]

Hi Luc

Wondered if you had any info on the above please?

also do you have another book coming out or is the one with the green cover the latest?

thanks

[LucD]

Do you see the same category appear more than once when you do Get-rCisTagCategory?

The green cover (Ed 2) is the latest one.
And no, no new edition in the planning for now.

[markshannon]

great i have just ordered.

No there isn't - all the names are different.

PS C:\> $obj

Name                           Value                                                                                                                                                                        

----                           -----                                                                                                                                                                        

Last Backup Date                                                                                                                                                                                            

Datastore                                                                                                                                                                                                   

Zer                                                                                                                                                                                                         

Protected by Zerto                                                                                                                                                                                          

Backup Result                                                                                                                                                                                               

Storage Type                                                                                                                                                                                                

vSphere Folders                                                                                                                                                                                             

BC Level                                                                                                                                                                                                    

OSS VMs with Shavlik Snapshots                                                                                                                                                                              

VMs with Snapshots                                                                                                                                                                                          

Sample Business View Category                                                                                                                                                                               

VM Location                                                                                                                                                                                                 

Owner                                                                                                                                                                                                       

Protection Method                                                                                                                                                                                           

VM Network                                                                                                                                                                                                  

VM    

when i run the 1st part of the script it comes with:-

PS C:\> $objTemplate = @{

   VM = ''

}

PS C:\> (Get-rCisTagCategory).Name | Sort-Object | ForEach-Object -Process {

   $objTemplate.Add($_,'')

}

Exception calling "Add" with "2" argument(s): "Item has already been added. Key in dictionary: 'VM'  Key being added: 'VM'"

At line:2 char:4

+    $objTemplate.Add($_,'')

+    ~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException

    + FullyQualifiedErrorId : ArgumentException

[LucD]

I reworked the script.
Does this return the correct results?

$assigned = Get-VM | Get-TagAssignment

$tagCat = $assigned.Tag.Category.Name | Sort-Object -Unique

$assigned | Group-Object -Property { $_.Entity.Name } |

ForEach-Object -Process {

   $vm = $_

   $obj = [ordered]@{

   VM = $vm.Name

   }

   $tagCat | ForEach-Object -Process {

   $cat = $_

   $tags = ($vm.Group | where { $_.Tag.Category.Name -eq $cat }).Tag.Name -join '|'

   $obj.Add($cat, $tags)

   }

   New-Object PSObject -Property $obj

}

[markshannon]

Hi

sorry to sound a bit like a novice, but do i replace 'ALL' of the below with what you sent?  thanks

$objTemplate = @{

   VM = ''

}

(Get-rCisTagCategory).Name | Sort-Object | ForEach-Object -Process {

   $objTemplate.Add($_,'')

}

Get-rCisTagAssignment|

Group-Object -Property {$_.Entity.Name} |

ForEach-Object -Process {

   $obj = $objTemplate.Clone()

   $obj['VM'] = $_.Name

   $_.Group | Group-Object -Property {$_.Tag.Category.Name} |

   ForEach-Object -Process {

   $cat = $_.Name

   $vTag = $_.Group | Group-Object -Property {$_.Tag.Category.Name} |

   ForEach-Object -Process {

   $_.Group.Tag.Name

   }

   $obj[$cat] = (($vTag | Sort-Object) -join '|')

   }

   New-Object PSObject -Property $obj

} | Export-Csv -Path c:\temp\tagreport.csv -NoTypeInformation -UseCulture

[LucD]

Yes, I restarted from scratch :smileygrin:

[markshannon]

yes that worked :smileyhappy:  thankyou

i have a tag category named 'machine information' that has multiple tags against it (tag category could be named anything),  in the report it lists all the tags in same excel field like below with pipe splitting the tags:-

 

Machine Information

Production|Owner IT|Non SQL|Internal Facing

how easy is it to split these out?

ps.  your 2nd ed book arrived yesterday :smileyhappy:

[LucD]

Splitting out is not a problem, but how would you name those columns?

[markshannon]

good question i dont know!!!

i guess it needs to dynamic if possible in case new categories are created with multiple tags?

to be easier, could just another column be created called the same whatever the category is called so in this example we would just have 4 columns called 'machine information'?

i say easier, thats why im here asking ha!!!

[LucD]

I'm afraid not, you can't have columns with the same name.
And Export-Csv has an issue exporting arrays where not all rows have the same number of properties.

We could first calculate how many columns we would end up with, and then create those columns for all rows.

[markshannon]

that would work :smileyhappy:

[LucD]

The code becomes a bit more complex, but this should do the trick.

$assigned = Get-VM | Get-TagAssignment

$tagCat = $assigned.Tag.Category.Name | Sort-Object -Unique

$report = $assigned | Group-Object -Property { $_.Entity.Name } |

ForEach-Object -Process {

   $vm = $_

   $obj = [ordered]@{

   VM = $vm.Name

   }

   $tagCat | ForEach-Object -Process {

   $cat = $_

   $tags = ($vm.Group | where { $_.Tag.Category.Name -eq $cat }).Tag.Name -join '|'

   $obj.Add($cat, $tags)

   }

   New-Object PSObject -Property $obj

}

$colTab = @{ }

$report | Get-Member -MemberType NoteProperty | where { $_.Name -ne 'VM' } |

ForEach-Object -Process {

   $column = $report."$($_.Name)"

   $columnMax = ($column | where { $_ -ne '' } | % {

   $_.Split('|').Count

   } | Measure-Object -Maximum).Maximum

   $colTab.Add($_.Name, $columnMax)

}

$report | ForEach-Object -Process {

   $row = $_

   $obj = [ordered]@{

   VM = $_.VM

   }

   $colTab.GetEnumerator() | Sort-Object -Property Name | ForEach-Object -Process {

   if ($_.Value -gt 1)

   {

   $col = $_

   $values = ($row."$($_.Name)").Split('|')

   1..($_.Value) | ForEach-Object -Process {

   $obj.Add("$($col.Name)-$_", $values[$_ - 1])

   }

   }

   else

   {

   $obj.Add($_.Name, $row."$($_.Name)")

   }

   }

   New-Object PSObject -Property $obj

}

[markshannon]

Legend works an absolute treat.

appreciate your assistance on this - now to decipher your code so i understand it :smileyhappy:

[ganapa2000]

LucD,

How can I get the VM Folder details in the output ?

[LucD]

Try something like this

$assigned = Get-VM | Get-TagAssignment

$tagCat = $assigned.Tag.Category.Name | Sort-Object -Unique

$report = $assigned | Group-Object -Property { $_.Entity.Name } |

ForEach-Object -Process {

    $vm = $_

    $obj = [ordered]@{

        VM     = $vm.Name

        Folder = $vm.Group[0].Entity.Folder.Name

    }

    $tagCat | ForEach-Object -Process {

        $cat = $_

        $tags = ($vm.Group | Where-Object { $_.Tag.Category.Name -eq $cat }).Tag.Name -join '|'

    $obj.Add($cat, $tags)

}

New-Object PSObject -Property $obj

}

$colTab = @{ }

$report | Get-Member -MemberType NoteProperty | Where-Object { 'VM', 'Folder' -notcontains $_.Name } |

ForEach-Object -Process {

    $column = $report."$($_.Name)"

    $columnMax = ($column | Where-Object { $_ -ne '' } | ForEach-Object {

            $_.Split('|').Count

        } | Measure-Object -Maximum).Maximum

$colTab.Add($_.Name, $columnMax)

}

$report | ForEach-Object -Process {

    $row = $_

    $obj = [ordered]@{

        VM     = $_.VM

        Folder = $_.Folder

    }

    $colTab.GetEnumerator() | Sort-Object -Property Name | ForEach-Object -Process {

        if ($_.Value -gt 1) {

            $col = $_

            $values = ($row."$($_.Name)").Split('|')

            1..($_.Value) | ForEach-Object -Process {

                $obj.Add("$($col.Name)-$_", $values[$_ - 1])

            }

    } else {

        $obj.Add($_.Name, $row."$($_.Name)")

    }

}

New-Object PSObject -Property $obj

}

[ganapa2000]

LucD,

Thank you very much, that worked perfectly. :smileyhappy:

[Sasidhar1234]

LucD,

Thanks for your script. It worked as expected and fulfilled my customer's requirements. They wanted to schedule this report in vROPs.

Is there a way we can import this script to get a report in vROPs? Please help.

Regards,

Sasidhar

[LucD]

No clue.
I suggest you ask this in the vROPs community.