Help with get-adpermission, exporting extended rights to CSV

This topic contains 11 replies, has 2 voices, and was last updated by Profile photo of Dave Wyatt Dave Wyatt 3 years, 7 months ago.

  • Author
    Posts
  • #10227
    Profile photo of Matthew McDonald
    Matthew McDonald
    Participant

    I am a pretty competent VB/VBscript developer but I swear I am never going to get the hang of Powershell. I run into so many little hang ups for reasons I cannot understand. It is so frustrating.

    I'm trying to export some ACL information from my Exchange environment. Take for example the following command:

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission

    I simply want to export the results of this onscreen information to a CSV file, but I cannot do it!

    Running the following gets me a CSV file full of more information and every permission column only contains type information, not the actual information I seek.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Export-CSV -NoTypeInformation myfile.csv

    In order to trim out unwanted information from the CSV I add a select-object statement to select the columns I want, but the Inherited and Rights column are all blank.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object Identity,User,Deny,Inherited,Rights

    Apparently Inherited and Rights are not valid columns, yet they are clearly identified as such in the on-screen output of the command. Using "get-adpermission | get-member" I find the columns I seek are actually IsInherited and AccessRights OR ExtendedRights. Two different columns combine to make up the "Rights" column from the on-screen display???

    So I modify my output to select the actual columns I want. This works on-screen, though now the data is in a list format instead of the table provided by the base Get-ADPermission command.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object Identity,User,Deny,IsInherited,AccessRights,ExtendedRights

    Try again to export to CSV. Same issue. AccessRights and ExtendedRights columns only contain type information.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object Identity,User,Deny,IsInherited,AccessRights,ExtendedRights | Export-CSV -NoTypeInformation myfile.csv

    Research shows I should use the ExpandProperty directive via "Select-Object -ExpandProperty ExtendedRights", for example. Attempting to factor this into my previous command yields errors.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object Identity,User,Deny,IsInherited, -ExpandProperty AccessRights, -ExpandProperty ExtendedRights

    Select-Object : A positional parameter cannot be found that accepts argument 'System.Object[]'.

    Most examples I find using -ExpandProperty only have people selecting one property when using it. I try that, and it fails!

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object -ExpandProperty ExtendedRights

    Select-Object : Cannot process argument because the value of argument "obj" is null. Change the value of argument "obj" to a non-null value.

    Apparently, when the property doesn't exist (e.g. not all ACLs have an extended right assigned), there is nothing to expand so it fails. So I modify my query to only return results that HAVE extended rights defined, and now this works.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Where {$_.ExtendedRights -ne $Null} | Select-Object -ExpandProperty ExtendedRights

    As I need more properties than just the ExtendedRights, I add just one additional column for testing but this fails miserably as well.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Where {$_.ExtendedRights -ne $Null} | Select-Object User,-ExpandProperty ExtendedRights

    After an extended search I found an example that specifies two properties while expanding one of them, but I notice there is no comma (,) between the properties. Emulating this with my search parameters it works.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Where {$_.ExtendedRights -ne $Null} | Select-Object User -ExpandProperty ExtendedRights

    While this works, I do not understand why the comma must be removed between the properties in order for this to work. Can Powershell get any more confusing?!

    Further, now my column name for ExtendedRights is "RawIdentity".

    As of this point, I still do not have a solution for what I want to do. All I simply want is the exact information printed to the console when running the base "get-adpermission" commandlet exported to CSV. Why is this so difficult in PowerShell???

  • #10230
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Your main problem is that the CSV file is basically a flat structure, but the security objects aren't flat at all. The Get-ADPermission cmdlet returns objects of type ADAcePresentationObject. The ExtendedRights property of those objects is an array of ExtendedRightIdParameter objects, and AccessRights is an array of ActiveDirectoryRights objects. Export-Csv doesn't handle embedded collections well.

    Depending on what you need to do with this data, you might be better off exporting it in a different format (XML or JSON), which will handle the more complex structure with less headache on your part.

  • #10239
    Profile photo of Matthew McDonald
    Matthew McDonald
    Participant

    Thanks for the reply. One major question I have is, why can't the on-screen data presented by the base run of get-ADPermission be simply exported? It's displayed in a simple flat structure. One would think that information could easily be exported to CSV.

    Powershell seems to display information so easily, but exporting the data that gets displayed in console is a nightmare. This is what I do not understand about Powershell. There are so many "gotchas", yet I hear everywhere how PowerShell is supposed to make my life easier! Every case I've needed PowerShell for so far has given me nothing but headaches. I'm really wishing I can just stick with VBScript LOL. Of course PowerShell is here to stay. I just don't know what I need to do so I can move past all these hurdles.

    **Edit**

    To clarify, all I want is to emulate all information that gets displayed in the console by running the first command in my post to be exported to an excel document so I can further manipulate it.

  • #10240
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Anything that you see at the console, you can have in a file via the Out-File cmdlet. That's not CSV format, though.

  • #10241
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    PowerShell has a pretty steep learning curve. Once you get the hang of it, though, it leaves VBScript in the dust.

  • #10242
    Profile photo of Matthew McDonald
    Matthew McDonald
    Participant

    What do I need to run to get the same information displayed on screen into a CSV format? As much as I hate to ask someone to write the command for me, I don't know how else to get to that point.

  • #10243
    Profile photo of Matthew McDonald
    Matthew McDonald
    Participant

    So I decided to use custom properties which makes the command ridiculously long, but I seem to have had some moderate success.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object Identity,User,Deny,IsInherited,@{n="AccessRights";e={($_ | select -expandproperty AccessRights) -join '|' }},@{n="ExtendedRights";e={($_ | select -expandproperty ExtendedRights) -join '|' }} | Export-CSV -NoTypeInformation myfile.csv

    I guess I'll have to create my own function to merge the AccessRights and ExtendedRights into a single column like the original command shows on-screen.

  • #10244
    Profile photo of Matthew McDonald
    Matthew McDonald
    Participant

    Can you tell me what I'm doing wrong here? Still just trying to work through this learning curve. When I try to run the following just to display info (NO CSV involved), it errors.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object -expandproperty AccessRights -expandprperty ExtendedRights

    Select-Object : Cannot bind parameter because parameter 'ExpandProperty' is specified more than once. To provide multiple values to parameters that can accept multiple values, use the array syntax. For example, "-parameter value1,value2,value3".

    Following their example I modify the code to be that which is below, I still get an error.

    Get-ExchangeServer | where {$_.IsClientAccessServer -eq $TRUE} | Get-ADPermission | Select-Object -expandproperty AccessRights,ExtendedRights

    Select-Object : Cannot convert 'System.Object[]' to the type 'System.String' required by parameter 'ExpandProperty'. Specified method is not
    supported.

  • #10246
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    Working with ACLs is complicated no matter what language you're using. Now that I'm at home, I'll play around with my test VMs and see what these commands are doing.

    Right away, I can help with the troubles you're having with Select-Object. You can only ever pass one property name to the -ExpandProperty parameter. With the -Property parameter, you can specify an array of property names. The difference is that when you use -Property, you are getting a PSCustomObject that contains those properties. When you use -ExpandProperty, you're getting whatever object is referred to by that property on the source object. So, if you ran these two commands:

    Get-Item C:\Windows | Select-Object -Property FullName
    Get-Item C:\Windows | Select-Object -ExpandProperty FullName
    

    The first one outputs a PSCustomObject that has a single string property called FullName. The second command just outputs the String itself.

    As for the Exchange commands that are giving you problems, it would help if you could paste the output that you're getting in the console, and then figure out what you'd want the CSV containing that data to look like. Don't worry about the code at this point, just try formatting it by hand. If you can create a CSV file manually and post its contents here, I can help you with the code to reproduce it.

  • #10248
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    I played around with these cmdlets a little bit in my test environment, and this code seems to produce a good CSV file. I split it into multiple lines to keep it tidy; as you pointed out, it's a giant mess if you try to do it all together.

    Note: In my tests the AccessRights and ExtendedRights properties were always single-element arrays. I don't know if that's always true, so just in case, the code is written to handle arrays of any size (joining multiple elements with a pipe symbol).

    Add-PSSnapin Microsoft.Exchange.Management.PowerShell.SnapIn
    
    # Create a properties array to send to Select-Object's -Property parameter later.  This is just to make the code
    # more clear, instead of putting all of this on one giant line.
    
    $properties = @(
        'Identity'
        'User'
        'Deny'
        'IsInherited'
        @{ Label = 'AccessRights'; Expression = { $_.AccessRights -join ' | ' }}
        @{ Label = 'ExtendedRights'; Expression = { $_.ExtendedRights -join ' | ' }}
    )
    
    Get-ExchangeServer |
    Where-Object { $_.IsClientAccessServer -eq $true } |
    Get-ADPermission |
    Select-Object -Property $properties |
    Export-Csv -Path $env:temp\ADPerms.csv -NoTypeInformation
    
    notepad $env:temp\ADPerms.csv
    
    

    Most of the time, getting PowerShell to do what you want isn't quite that annoying.

  • #10262
    Profile photo of Matthew McDonald
    Matthew McDonald
    Participant

    Thanks for taking the time to explain those things and put together that code sample. I like the idea of creating the properties array. Much appreciated.

    RE: PowerShell not being annoying, most of the time... I must have complex requirements because as mentioned, it seems every time I try to do anything in PowerShell I always run into snags like this. Things that I consider intuitive just don't seem to be in PowerShell. I guess I'll just chalk it up to my inexperience with this shell language. Don't get me wrong, I definitely see how remarkably powerful it is.

    Anyhow, thanks again for your time!

  • #10267
    Profile photo of Dave Wyatt
    Dave Wyatt
    Moderator

    I had a similar experience at first. The first two things I wanted to know, before I started using PowerShell scripts for basically anything, were how to do proper error handling, and how to reproduce the transparent mirroring of console output to a log file that I had in my VBScripts. Turned out that both were fairly complicated topics in PowerShell (due to the concept of Terminating versus Non-Terminating errors, and due to all the different streams capable of producing console output).

You must be logged in to reply to this topic.