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.
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.
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
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Thank you so much for your support.
Your Script is working like Charm!!!!..
Thanks a lot.
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.
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]}}
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
Hi Lucd,
I got the desired output.
Thanks a ton. Even Execution of Script is very fast. Thanks a lot.
Hi Lucd,
Your Script is giving desired output as well execution of Script is very fast. Thanks a ton.
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.
Did you install the ImportExcel module?
Blog: lucd.info Twitter: @LucD22 Co-author PowerCLI Reference
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.