Importing VM Tags from CSV file

[Jrt1general]

I've been struggling to complete a massive import task of assigning upwards of a dozen categories of tags to a few thousand VMs.

I've started by using Alan Renouf's code to begin the process but I'm only successful at importing one or two categories before the script unexpectedly ends. Anyone else have suggestions?

[LucD]

No error messages whatsoever?

[Jrt1general]

None. Which makes me think that it's exiting on an existing tag and just ending gracefully. I've nuked all the tags in my test environment, starting from a blank slate and it runs for less than a second and ends without an error.

get-tag returns nothing.

If I create a tag manually, get-tag returns that tag.

The file I'm importing is a csv that looks like this:

Name,Application,Cost Center,Department,GroupA,GroupB,Owner

tagtestvm1,Application A,123,IT,Cat5GroupA,Cat5GroupB,Owner1

tagtestvm2,Application A,1234,IT,Cat5GroupA,Cat5GroupB,Owner2

tagtestvm3,Application A,1235,IT,Cat5GroupA,Cat5GroupB,Owner3

[LucD · Accepted solution]

Can you try with this variation on Alan's script?

Connect-viserver myvc.corp.local -user administrator@vsphere.local -pass Pa$$w0rd

$CMDBInfo = Import-CSV .\cmdbinfo.csv

# Get the header names to use as tag category names

$TagCatNames = $cmdbinfo | Get-Member | Where {$_.MemberType -eq "NoteProperty"} | Select -Expand Name

# Create the Tag Category if it doesnt exist

Foreach ($Name in ($TagCatNames | Where {$_ -ne "Name"})) {

  Try {

   $tCat = Get-TagCategory $Name -ErrorAction Stop

  }

  Catch {

   Write-Host "Creating Tag Category $Name"

   $tCat = New-TagCategory -Name $Name -Description "$Name from CMDB"

  }

  # Create Tags under the Tag Categories

  $UniqueTags = $cmdbinfo | Select -expand $Name | Get-Unique

  Foreach ($Tag in $UniqueTags) {

   Try {

   $tTag = Get-Tag $Tag -Category $tCat -ErrorAction Stop

   }

   Catch {

   Write-Host "..Creating Tag under $Name of $Tag"

   $tTag = New-Tag -Name $Tag -Category $tCat -Description "$Tag from CMDB"

   }

   # Assign the Tags to the VMs/Hosts

   $cmdbinfo | Where {$_.($Name) -eq $Tag} | Foreach {

   Write-Host ".... Assigning $Tag in Category of $Name to $($_.Name)"

   New-TagAssignment -Entity $($_.Name) -Tag $tTag | Out-Null

   } 

  }

}

[Jrt1general]

I was penning this update to my reply above:

I found the issue, the tag-category was still populated with the categories of a former import - must have been failing silently then.

I ran your script and it works perfectly without error - even when it encounters duplicates.

One thing I'm looking to improve on, is to populate the description along with the tag.

In the spreadsheet, I was thinking

Name, Category1, ,Category2, ,Category3, ,Category4

vmname, tag1, tag1 description, tag2, tag2 description,

Or maybe make it easier to parse, by using a colon between the tag name and description.

Name, Category1,Category2,Category3,Category4

vmname, tag1:description, tag2:description,

I can get the data in the csv in just about any format and maintain a tight naming convention - which would you recommend?

[LucD]

Afaik a Tag can have a description, but not a Tag assignment.
That would mean, you would need to repeat the same description on each line, which is quite a bit of redundancy in your CSV file.

When you go for the 2nd option, that would mean something like this

Name,Application,Cost Center,Department,GroupA,GroupB,Owner

TS1,Application A:Description,123,IT,Cat5GroupA,Cat5GroupB,Owner1

TS11,Application A:Description,1234,IT,Cat5GroupA,Cat5GroupB,Owner2

TS111,Application A:Description,1235,IT,Cat5GroupA,Cat5GroupB,Owner3

Wouldn't it be easier to place the TagCategories, the Tags and their description in a separate CSV, and only use the one you have now for Tag assignments?

[Jrt1general]

I like the idea of having the descriptions in an secondary file to keep them simple, I'm not sure how to incorporate that into a functional script. I can dump the data into a csv file fairly easily - it would mean the csv file was more bloated than it needed to be but I'm wide open to suggestions!

On a side note, I changed a value on a VM tag and reran the script found an error (as expected).

..Creating Tag under Cost Center of 9999999

.... Assigning 9999999 in Category of Cost Center to tagtestvm1

New-TagAssignment : 10/17/2018 2:14:50 PM New-TagAssignment  The tag with id 'InventoryServiceTag-83fa7d15-e5e9-48d9-b7b5-dbf27acb365b' cannot be assigned to entity with id 'VirtualMachine-vm-891'. The category of the specified

tag either does not support this entity type or only allows a single tag assignment per entity.

At line:30 char:4

+    New-TagAssignment -Entity $($_.Name) -Tag $tTag | Out-Null

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

    + CategoryInfo          : InvalidOperation: (Tag:String) [New-TagAssignment], ViError

    + FullyQualifiedErrorId : ViCore_TaggingServiceImpl_TagAssignment_InvalidTagAssociation,VMware.VimAutomation.ViCore.Cmdlets.Commands.Tagging.NewTagAssignment

Can you help with this? As the csv data may change and need to be applied again. I envision this script being part of a CMDB sync that we're trying to get running as things change the tags would update.

[LucD]

That is the Cardinality of the TagCategory.
Since we didn't specify it, it defaults to Single.

The other option for cardinality is Multiple, see the New-TagCategory cmdlet.

It will suffice to just add -Cardinality Multiple on the New-TagCategory cmdlet in the script

You can change the existing Tag Categories with the Set-TagCategory cmdlet.

[Jrt1general]

Perfect. Thank you.

[BjornJohansson]

Hi LucD​ and thank you for the script.

Sorry for kicking on a old thread, but I have a specific question about the script you provided. How do you adjust the script to take empty parameters into consideration?

Example:

My .csv looks like this, note the ",," on VM2. Category Backup is not mandatory and should indeed be missing on system not included in Veeam backup (which picks up a VM based on tag).

Name,Owner,BackupJob,Department

VM1,Benny,Backup1,Finance

VM2,Jerry,,Marketing

Tried to modify the script and it do works, but generates an error

   # Assign the Tags to the VMs/Host

   # Allow empty entries on certain VMs in csv $cmdbinfo | Where {$_.($Name) -eq $Tag} | Foreach {

  $cmdbinfo | Where {($tag) -and ($_.($Name) -eq $Tag)} | Foreach { 

  #$cmdbinfo | Where {$_.($Name) -eq $Tag} | Foreach {

   Write-Host ".... Assigning $Tag in Category of $Name to $($_.Name)"

   New-TagAssignment -Entity $($_.Name) -Tag $tTag | Out-Null

This is the error being throwned every time

New-Tag : Cannot validate argument on parameter 'Name'. The argument is null or empty. Provide an argument that is not

null or empty, and then try the command again.

At C:\Powershell\VMware\TagsImportAssignNEW.ps1:25 char:26

+    $tTag = New-Tag -Name $Tag -Category $tCat -Description "$Tag from ...

+                          ~~~~

    + CategoryInfo          : InvalidData: (:) [New-Tag], ParameterBindingValidationException

    + FullyQualifiedErrorId : ParameterArgumentValidationError,VMware.VimAutomation.ViCore.Cmdlets.Commands.Tagging.Ne

   wTag

How can I adjust the script to write-host "Tag missing from input file, skipping..." or similar?

Thanks!

[LucD]

Try changing the line to

   $UniqueTags = $cmdbinfo | where {$_."$Name"} | Select -expand $Name | Sort-Object -Unique

[BjornJohansson]

Latest answer ever, but thank you.

[jmarkey]

Hi LucD,

That was a great fix on the script.  Thank you!  One issue for me is that the script add the tags/categories in different orders on different VMs.  Basically, instead of listing Cat1 w/tag, Cat2 w/tag, Cat3 w/tag, Cat4 w/tag on each VM, I get VM1 Cat4 w/tag, w/ Cat2 w/tag, Cat3 w/tag, Cat1 w/tag and VM2 Cat3 w/tag, Cat2 w/tag, Cat4 w/tag, Cat1 w/tag.  Ideally, the tags/categories would be applying Cat1 w/tag, Cat2 w/tag, Cat3 w/tag on every VM.  The output looks like it should apply in order, but then they appear visually out of order in vSphere.  I appreciate any help you can provide!

.... Assigning Microsoft SQL Server 2016 Ent in Category of Database to VM1
.... Assigning None in Category of Database to VM2
.... Assigning Dev in Category of Environment to VM1
.... Assigning Dev in Category of Environment to VM2
.... Assigning Analytics in Category of Project to VM1
.... Assigning Infrastructure-Test in Category of Project to VM2
.... Assigning None in Category of Software to VM1
.... Assigning Various Tools in Category of Software to VM2

[LucD]

Did you try adding a Sort-Object to the line?

$TagCatNames = $cmdbinfo | Get-Member | Where {$_.MemberType -eq "NoteProperty"} |
    Sort-Object -Property Name |
    Select -Expand Name

[jmarkey]

Hi LucD-

Thank you for the  help!  I added the line, but unfortunately I am still getting results that are in different order on each VM.  I don't know what I am missing.  

# Get the header names to use as tag category names

$TagCatNames = $cmdbinfo | Get-Member | Where {$_.MemberType -eq "NoteProperty"} | Sort-Object -Property Name | Select -Expand Name

# Create the Tag Category if it doesnt exist

Foreach ($Name in ($TagCatNames | Where {$_ -ne "Name"})) {

Try {

$tCat = Get-TagCategory $Name -ErrorAction Stop

}

Catch {

Write-Host "Creating Tag Category $Name"

$tCat = New-TagCategory -Name $Name -Description "$Name from CMDB"

}

# Create Tags under the Tag Categories

$UniqueTags = $cmdbinfo | Select -expand $Name | Get-Unique

Foreach ($Tag in $UniqueTags) {

Try {

$tTag = Get-Tag $Tag -Category $tCat -ErrorAction Stop

}

Catch {

Write-Host "..Creating Tag under $Name of $Tag"

$tTag = New-Tag -Name $Tag -Category $tCat -Description "$Tag from CMDB"

}

# Assign the Tags to the VMs/Hosts

$cmdbinfo | Where {$_.($Name) -eq $Tag} | Foreach {

Write-Host ".... Assigning $Tag in Category of $Name to $($_.Name)"

New-TagAssignment -Entity $($_.Name) -Tag $tTag | Out-Null

}

}

}

[jmarkey]

Some more details I found.  It appears to apply them in the same order (not in category order) every time no matter if I do one column /tag category at a time or all at the same time.  The PowerCLI display shows them being applied in correct order, but the vsphere side shows them in different orders on each VM.

Name Port User
---- ---- ----
10.10 443 VSPHERE.LOCAL\Admin
.... Assigning Microsoft SQL Server 2016 Ent in Category of Database to ncdad
.... Assigning None in Category of Database to nccvr
.... Assigning Dev in Category of Environment to ncdad
.... Assigning Dev in Category of Environment to nccvr
.... Assigning Analytics in Category of Project to ncdad
.... Assigning Infrastructure-Test in Category of Project to nccvr
.... Assigning None in Category of Software to ncdad
.... Assigning Various Tools in Category of Software to nccvr

[LucD]

I'm not sure I fully grasp what you mean by "... appear visually out of order in vSphere"
Is that the order you see the Tags in the Web Client for each VM?
Perhaps some screenshots could help me understand

[jmarkey]

It looks like the first VM does receive it in proper order, but display from the bottom up in vSphere.  This would be fine if it was consistent, but once it goes to the second VM, all order is lost.   I am testing with two VMs, so I don't have to delete the tags on many VMs when I have failure.  

Attached are the orders they are showing in.  2nd reply will have 2nd screenshot as they only accept 1 per reply.  Thanks again for all your help!

[jmarkey]

2nd Screenshot

[LucD]

I did some further testing, and if I add Tags with PowerCLI or via the Web Client, the order they are displayed in the Web Client, seems to be somewhat random.
And as far as I can tell, there is no way to get them in alphabetical order in the Web Client.
I suspect that the Web Client is using some kind of internal ID (?) to order them.