PowerCLI

 View Only
Expand all | Collapse all

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

  • 1.  Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted May 01, 2019 07:11 PM

    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.



  • 2.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted May 01, 2019 07:44 PM

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



  • 3.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted May 01, 2019 07:49 PM

    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!!



  • 4.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?
    Best Answer

    Posted May 02, 2019 12:59 AM

    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



  • 5.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted May 02, 2019 07:06 PM

    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!



  • 6.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted May 03, 2019 06:18 AM

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



  • 7.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Jan 16, 2020 06:14 PM

    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



  • 8.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Jan 16, 2020 06:24 PM

    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?



  • 9.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Jan 16, 2020 06:54 PM

    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



  • 10.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Jan 16, 2020 07:03 PM

    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



  • 11.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Feb 09, 2021 06:59 PM

    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.



  • 12.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Feb 09, 2021 07:33 PM

    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  
    }

      



  • 13.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Mar 30, 2021 06:26 PM

    Thank you very much.

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

     



  • 14.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Mar 30, 2021 06:41 PM

    Yup, my "Splatted Perm" variable might be misunderstood 



  • 15.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Sep 30, 2021 07:39 AM

    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
    }



  • 16.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Sep 30, 2021 08:18 AM

    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.



  • 17.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Nov 17, 2021 08:31 PM

    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.



  • 18.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Nov 17, 2021 08:36 PM

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



  • 19.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Nov 17, 2021 08:48 PM

    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 
    }

     

     



  • 20.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Mar 01, 2022 04:17 PM

    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?



  • 21.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Mar 01, 2022 05:06 PM

    If you mean the SSO accounts, have a look at the VMware.vSphere.SsoAdmin module



  • 22.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Mar 01, 2022 11:05 PM

    LucD,

    Thanks again for all of your insight. Can we place the two scripts together in order to repopulate the roles and permissions?

    Import-excel -Path Report-20220301-1614.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
      }

      New-VIRole
    }

    Import-excel -Path Report-20220301-1614.xlsx -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
    }



  • 23.  RE: Export Roles Permissions Privileges - Export-Excel not appending the data?

    Posted Mar 02, 2022 07:34 AM

    Not sure what you mean, but you can place those 2 snippets together in 1 script.