Export Roles Permissions Privileges - Export-Excel not appending the data?

[virtualtech_wor]

Trying to export vcenter roles permissions and privileges from multiple vcenters. however, the output xlsx file is only dumping 1 or 2 vCenters data.

$reportName = "E:\Scripts\ExportRolesPermissions\Report-$Date.xlsx"

foreach($vc in $vcenters){

$global:DefaultVIServers = $null

Connect-VIServer -Server $vc -User $username -Password $password

Get-VIPermission | Select @{N='vCenter';E={$_.Uid.Split('@:')[1]}},Principal,Role,@{n='Entity';E={$_.Entity.Name}},@{N='Entity Type';E={$_.EntityId.Split('-')[0]}} |

Export-excel -Path $reportName -WorksheetName Permissions

Get-VIRole | Select @{N='vCenter';E={$_.Uid.Split('@:')[1]}},Name,@{N='PrivilegeList';E={[string]::Join([char]10,$_.PrivilegeList)}} |

Export-Excel -Path $reportName -WorksheetName Roles

}

Also please advise if there is any better tool for audting/reporting/exporting on vCenter Roles/Permissions/Privileges.

[LucD]

You're overwriting the Roles and Permissions worksheet in each iteration.
You could use worksheetnames that include for example the name of the VC

[virtualtech_wor]

Oh I got it, can we change the code to get all the vCenters Roles in One sheet and the other sheet for permissions, instead of vcenter specific sheet as that would be lot.

Please let me know.

Thanks for your quick responses!!

[LucD · Accepted solution]

Try something like this

$reportName = "E:\Scripts\ExportRolesPermissions\Report-$Date.xlsx"

Connect-VIServer -Server $vcenters

Get-VIPermission |

Select @{N='vCenter';E={$_.Uid.Split('@:')[1]}},

  Principal,Role,

   @{n='Entity';E={$_.Entity.Name}},

   @{N='Entity Type';E={$_.EntityId.Split('-')[0]} |

Export-excel -Path $reportName -WorksheetName Roles

Get-VIRole |

Select @{N='vCenter';E={$_.Uid.Split('@:')[1]}},

  Name,

   @{N='PrivilegeList';E={[string]::Join([char]10,$_.PrivilegeList)}} |

Export-Excel -Path $reportName -WorksheetName Roles

[virtualtech_wor]

Thanks, still trying to understand this. Do you want me to not use for each loop at all or did you make any other changes to the code that I couldn't catch!!

Regards!

[LucD]

There is no need to do a ForEach loop.
The pipeline construct and the Select + Export-Excel achieve the same result.

[vsphereadminmcm]

Hi Luc.. If you can help me out with this error im getting.. Trying export the roles, privileges.

At C:\temp\VC Role Privelage Export.ps1:16 char:4

+ Get-VIRole |

+    ~

Missing '=' operator after key in hash literal.

At C:\temp\VC Role Privelage Export.ps1:16 char:4

+ Get-VIRole |

+    ~

The hash literal was incomplete.

    + CategoryInfo          : ParserError: (:) [], ParseException

    + FullyQualifiedErrorId : MissingEqualsInHashLiteral

At line:1 char:1

Missing '=' operator after key in hash literal.PowerShell

The hash literal was incomplete.PowerShel

[LucD]

It looks like you might have a copy/paste problem.
Can you attach the .ps1 file that you are using?
And show how you run the script? From the PS prompt? From a .ps1 file?

[vsphereadminmcm]

PowerCLI C:\temp> & '.\VC Role Privelage Export.ps1'

At C:\temp\VC Role Privelage Export.ps1:16 char:4

+ Get-VIRole |

+    ~

Missing '=' operator after key in hash literal.

At C:\temp\VC Role Privelage Export.ps1:16 char:4

+ Get-VIRole |

+    ~

The hash literal was incomplete.

    + CategoryInfo          : ParserError: (:) [], ParseException

    + FullyQualifiedErrorId : MissingEqualsInHashLiteral

[LucD]

There is a brace missing on the 'Entity Type' calculated property, this is the corrected code

$reportName = "C:\Temp\Report-$Date.xlsx"

Get-VIPermission |

Select-Object @{N='vCenter';E={$_.Uid.Split('@:')[1]}},

  Principal,Role,

   @{n='Entity';E={$_.Entity.Name}},

   @{N='Entity Type';E={$_.EntityId.Split('-')[0]}} |

Export-excel -Path $reportName -WorksheetName Roles

Get-VIRole | Select @{N='vCenter';E={$_.Uid.Split('@:')[1]}},

Name,

   @{N='PrivilegeList';E={[string]::Join([char]10,$_.PrivilegeList)}} |

Export-Excel -Path $reportName -WorksheetName Roles

[Dr_Virt]

LucD,

Outstanding script as usual. 

Is there a function to blow these back into a vCenter? I am preparing to remove a vCenter from linked mode and want to restore the permissions after it is migrated out.

[LucD]

So I gather the Roles are not changed during this process?

In that case, it will just be a matter of reading the CSV file row by row and running a New-VIPermission with the data in each row.

On the export you should also capture the Propagate property.

Get-VIPermission |
Select @{N='vCenter';E={$_.Uid.Split('@:')[1]}},
  Principal,Role,Propagate,
   @{n='Entity';E={$_.Entity.Name}},
   @{N='Entity Type';E={$_.EntityId.Split('-')[0]} |
Export-excel -Path $reportName -WorksheetName Permissions

 Then for the import you could do something like this

Import-excel -Path $reportName -WorksheetName Permissions -PipelineVariable row |
Foreach-Object -process {
    $sPerm = @{
        Entity = Get-Inventory -Name $row.Entity
        Role = Get-VIRole -name $row.Role
        Principal = $row.Principal
        Propagate = $row.Propagate
        Confirm = $false
    }
    New-Vipermission @Sperm 
}

[Dr_Virt]

Thank you very much.

Not sure I am old enough to use those variables...

[LucD]

Yup, my "Splatted Perm" variable might be misunderstood 😁

[IM-coded]

Hello LucD,

I'm new to this and I'm stuck with vCenter Importing roles and permissions there is an error.. I will share the codes whatever I have it with me - need some help on this. We have created two output types - csv for human readable and XML for importing on vCenter. Kindly help on importing script part. I did try your import script but there is some error (posted in the end of the this thread).

This is the exporting of role and permission script:

#vCenter Role & Permission export#

Write-Host "`tExporting Permissions and Roles for vCenter.."

$vCenterHost = Read-Host "Enter vCenter Name:"

try {

#Establishing connection to vCenter
Connect-VIServer -Server $vCenterHost

#Permissions
$Permission = Get-VIPermission | Select-Object @{N='vCenter';E={$_.Uid.Split('@:')[1]}},
Principal,Role,propagate,
@{n='Entity';E={$_.Entity.Name}},
@{N='Entity Type';E={$_.EntityId.Split('-')[0]}}

#Export to CSV
$Permission | Export-Csv -Path "C:\Temp\$vCenterHost-Permission.csv"

#Export to XML
$PermissionXML = Get-VIPermission
$PermissionXML | Export-Clixml -Path "C:\Temp\$vCenterHost-Permission.xml"

#Roles
$Role = Get-VIRole | Select-Object @{N='vCenter';E={$_.Uid.Split('@:')[1]}},
Name,
@{N='PrivilegeList';E={[string]::Join([char]10,$_.PrivilegeList)}}

#Export to CSV
$Role | Export-Csv -Path "C:\Temp\$vCenterHost-Roles.csv"

#Export to XML
$RoleXML = Get-VIRole
$RoleXML | Export-Clixml -Path "C:\Temp\$vCenterHost-Roles.xml"

Write-Verbose "`tRole & Permission Data Exported Successfully from $vCenterHost" -Verbose

Write-Verbose "Disconnecting from $vCenterHost" -Verbose
Disconnect-VIServer -Server

}

catch {

Write-Verbose "`tError Encountered! Error:$_" -Verbose
$ErrorObject = New-Object -TypeName PSObject -Property @{
vCenterName = $vCenterHost
Error = $_

}

}

Import script error output:

New-VIPermission : Cannot process argument transformation on parameter 'Principal'. This parameter no longer accepts
an array. As an alternative you may pass multiple values by pipeline (if supported by the parameter).
At D:\Script\Import of role and permission of vcenter (1).ps1:12 char:18
+ New-Vipermission $Permission
+ ~~~~~~~~~~~
+ CategoryInfo : InvalidData: (:) [New-VIPermission], ParameterBindingArgumentTransformationException
+ FullyQualifiedErrorId : ParameterArgumentTransformationError,VMware.VimAutomation.ViCore.Cmdlets.Commands.Permis
sionManagement.NewVIPermission

Import Script which I'm referring to:

Import-excel -Path $reportName -WorksheetName Permissions -PipelineVariable row |
Foreach-Object -process {
$Permission = @{
Entity = Get-Inventory -Name $row.Entity
Role = Get-VIRole -name $row.Role
#Principal = $row.Principal
Propagate = $row.Propagate
Confirm = $false }

New-VIPermission $Permission
}

[LucD]

I replied on your other thread.
See Re: vCenter Roles and Permissions Export and Impor... - VMware Technology Network VMTN

Please don't post the same question multiple times.

[rgb99]

And what if the non-standard Roles do not exist? How would I import the roles I want from the Excel file to a new vCenter Server, for instance?

Let's say I go into the Report.xlsx file, then the Role tab, and I delete everything I don't need and only leave a couple of custom roles for various enterprise products and user VM management. Now I want to create the roles via importing rather than creating from scratch.

[LucD]

Have you tried to do that with the code in the other thread?
Do you have a problem doing that iimport?

[rgb99]

Oh whoops! I didn't notice the other thread. I got it working by removing all but one role from Report.xlsx, connecting to another vCenter. and running this:

Import-Excel -Path ".\Report.xlsx" -WorksheetName Roles -PipelineVariable row |
ForEach-Object -Process {
  $Role = @{
    Name = $row.Name
    Privilege = $row.PrivilegeList.Split("`n") | ForEach-Object { Get-VIPrivilege -Id $_ }
    #Server = $row.vCenter
    Confirm = $false
    WhatIf = $false
  }
  New-VIRole 
}

[Dr_Virt]

Have been testing this and I am concerned that I don't see the global roles permissions in the exports. Is there a separate function to make sure the same permissions are there when the roles are recreated?