Fetching User/Group , Role details using Powercli

[Sivaramsharmar]

Hi All,

I have written below script to fetch User, Role details using Powercli and I got the details.

$Excel = New-Object -ComObject Excel.Application

$Excel.visible = $True

$Excel = $Excel.Workbooks.Add()

$Sheet = $Excel.Worksheets.Item(1)

$Sheet.Activate() | Out-Null

$Sheet.Name = 'vCenter Roles'

$Sheet.Cells.Item(1,1) = "Name"

$Sheet.Cells.Item(1,2) = "Role"

$Sheet.Cells.Item(1,3) = "Entity"

$Sheet.Cells.Item(1,4) = "Entity Type"

$Sheet.Cells.Item(1,5) = "vCenter"

$intRow = 2

$WorkBook = $Sheet.UsedRange

$WorkBook.Interior.ColorIndex = 19

$WorkBook.Font.ColorIndex = 11

$WorkBook.Font.Bold = $True

$gps = get-vipermission

foreach($gp in $gps){

$vc = $gp.uid.split(':@')[1]

$Sheet.Cells.Item($intRow, 1) = $gp.principal

$Sheet.Cells.Item($intRow, 2) = $gp.Role

$Sheet.Cells.Item($intRow, 3) = $gp.entity.Name

$Sheet.Cells.Item($intRow, 4) = $gp.entityid.split('-')[0]

$Sheet.Cells.Item($intRow, 5) = $vc

$intRow = $intRow + 1

}

$WorkBook.EntireColumn.AutoFit()

Additionally I am trying to expand the Privilege List using below script.

$gvips = get-vipermission | group Role | select Name

foreach($gvip in $gvips){

get-virole -name $gvip.name | select Name,PrivilegeList

}

I can able to see the Name and Privilege List but not completely due to Long list of Privlieges for some roles. i am getting output as {xxx.xxxxxxxxxxxxxxxx.............}

Like this so not able to see complete Privileges.

If for single Role I want to see then I can execute the command get-virole -name admin | select -expandproperty PrivilegeList

Above command will list perfect Output. But this command cannot be used for Looping with "Name" Parameter.

Any one can suggest me how to get output with full details.

Is there any way that I can get 2 Scripts output in single Excel Sheet or HTML File with SameFormat.

Thanks in Advance.

[LucD]

One way of doing this is as follows.

$report = Get-VIPermission |

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

$report = foreach($row in $report){

    Get-VIRole -Name $row.Role | Select -ExpandProperty PrivilegeList | %{

        Add-Member -InputObject $row -MemberType NoteProperty -Name $_ -Value 'y'

    }

    $row

}

$report |

Sort-Object -Property {$_ | Get-Member | Measure-Object | Select -ExpandProperty Count} -Descending |

Export-Excel -Path C:\report.xlsx -WorksheetName Security

The script uses the Export-Xlsx function from Export-Xlsx, the sequel, and ordered data

The resulting file looks like this

[Sivaramsharmar]

Hi Lucd, Thank you so much for your support. As your Script is very much impressive to get the desired output. Is there any way that I can get the output as in the attachment, Please let me know.

[LucD]

If you want to use Export-Csv or Export-Xlsx, it is quite difficult to get that layout on 1 page.

With Export-Excel you can easily get that info on 2 separate worksheets in the same XLSX file.

[Sivaramsharmar]

It is fine for me if we can get that. The Problem which I have faced here is as you can see in the attachment that it is not showing the entire values of Privilege List as it stuck at "..............". Could you please provide me the Script to get the full details of Privilege List in the new Worksheet as per my attachment. Thanks in Advance.

[LucD · Accepted solution]

Try like this

$reportName = 'C:\Report.xlsx'

Get-VIPermission |

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

Export-Excel -Path $reportName -WorksheetName Permissions

Get-VIRole |

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

Export-Excel -Path $reportName -WorksheetName Roles

[Sivaramsharmar]

Hi Lucd,

Thank you so much for your support I have added one line as per my requirement.

$reportName = 'C:\Report.xlsx'

Get-VIPermission |

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

Export-Xlsx -Path $reportName -WorksheetName Permissions

$gvip = get-vipermission | group Role | select Name

%{get-virole -name $gvip.name | select Name,@{N='PrivilegeList';E={[string]::Join(',', $_.PrivilegeList)}} }

Export-Xlsx -Path $reportName -AppendWorksheet -WorksheetName Roles -SheetPosition end

Here I need your help again, as in the output(PrivilegeList) for each line as Datastore.AllocateSpace,Global.CancelTask,Global.LogEvent,Global.ScriptAction,Network.Assign,Resource.ApplyRecommendation and so on..........................

is there any way that I can split this line like below output

Principal                                     Privilegelist

ApplyonDataCenter                     Datastore.AllocateSpace

                                                 Global.Canceltask

ApplyonVM                                Poweron

                                                 Poweroff

Like this after Each comma the line should start in a new line.

I have tried in this way

%{get-virole -name $gvip.name | select Name,@{N='PrivilegeList';E={[string]::Join(',', $_.PrivilegeList.split(','))}} }

But I am not getting output If I am selecting Line number in split function then only I am getting output which is not correct as per my requirement.

Please suggest on this.

Thanks in Advance.

[LucD]

Like this you mean ?

$reportName = 'C:\Report.xlsx'

Get-VIPermission |

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

Export-Excel -Path $reportName -WorksheetName Permissions

&{foreach($perm in Get-VIPermission){

    $i = 0

    Get-VIRole -Name $perm.Role | %{

        if($_.PrivilegeList){

            $_.PrivilegeList |

            Select @{N='Principal';E={$perm.Principal}},@{N='Role';E={$perm.Role}},@{N='Privilege';E={$_}}

        }

        else{

            $null | Select @{N='Principal';E={$perm.Principal}},@{N='Role';E={$perm.Role}},@{N='Privilege';E={''}}

        }

    }

}} |

Export-Excel -Path $reportName -WorksheetName Roles

[Sivaramsharmar]

Hi Lucd,

Thanks for your support.

Mistake Happened in my last reply to you.

In my Previous Communication instead of Privliege, I have mentioned Principal.

I am looking output as in the attachment for Privilege List.

In Attachment,"Name" is Privilege name

[LucD]

Like this you mean (the privilege column is now with multiline cells)

$reportName = 'C:\Report.xlsx'

Get-VIPermission |

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

Export-Excel -Path $reportName -WorksheetName Permissions

Get-VIRole |

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

Export-Excel -Path $reportName -WorksheetName Roles

[Sivaramsharmar]

Hi Lucd,

You have done a great help for me.

I got exact output as per my requirement.

Could you please let me know what does [char]10 in this script.

Thanks a lot for your support.

[LucD]

The '[char]10' is to have a <Alt>-<Enter> between the different values in the cell.

This is used to have multiline values in Excel

[Sivaramsharmar]

Thank you so much for your support.

Your Script is working like Charm!!!!..

Thanks a lot.

[Sivaramsharmar]

Hi Lucd,

I am trying to fetch VM's in Inconsistent Folders using below script.

$vm = get-vm | get-view

$Folder = (($vm.Summary.Config.VMPathName).Split(']/').[1].Trimstart(' ')

$vm | select Name,@{N='Path';E={$_.Summary.Config.VMPathName.Split('/')[0]}} | where{$_.Name -ne $Folder}

I am getting all VM's with Name and Path Details, But I want output only VM's Name mismatch with Folder Path.

Here I cannot use foreach loop and if statement as I am putting this script for multiple outputs in single HTML file and using convertto-html -fragment which doesn't allow to get output in html files if those 2 functions are using in the script.

Please suggest on this request.

[LucD]

Try like this

Get-View -ViewType VirtualMachine |

where{$_.Name -ne $_.Summary.Config.VMPathName.Split('/] ')[2]} |

select Name,@{N='Path';E={$_.Summary.Config.VMPathName.Split('/] ')[2]}}

[Sivaramsharmar]

Hi Lucd,

I got the desired output.

Thanks a ton. Even Execution of Script is very fast. Thanks a lot.

[Sivaramsharmar]

Hi Lucd,

Your Script is giving desired output as well execution of Script is very fast. Thanks a ton.

[virtualtech_wor]

I'm getting the error:

Export-Xlsx : The term 'Export-Xlsx' is not recognized as the name of a cmdlet, function, script file, or operable program.

Can you please advise.

[LucD]

Did you install the ImportExcel module?

[virtualtech_wor]

Yes, I installed the ImportExcel module. This is what the ImportExcel folder has got (screenshot below). I dont see any Export-xlsx listed in this folder, if that gives some clue about cause of this error.

The error is still same. Please advise.

Export-xlsx : The term 'Export-xlsx' is not recognized as the name of a cmdlet, function....

I'm using PS 5.0/PowerCLI 6.5 R1 on Windows 2012 machine.