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)
interestingly i get the same error.
although tried on another vcenter and worked ok.
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
hi
how do i run these modules in relation to the above script that you mentioned?
thanks
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
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
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
I reworked the script.
Does this return the correct results?
$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
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
Yes, I restarted from scratch :smileygrin:
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
yes that worked 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
Splitting out is not a problem, but how would you name those columns?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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!!!
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
that would work
The code becomes a bit more complex, but this should do the trick.
$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
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Legend works an absolute treat.
appreciate your assistance on this - now to decipher your code so i understand it
LucD,
How can I get the VM Folder details in the output ?
Try something like this
$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
}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference