Author Posts

September 18, 2013 at 9:54 am

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

September 18, 2013 at 10:14 am

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.

September 18, 2013 at 12:45 pm

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.

September 18, 2013 at 12:49 pm

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

September 18, 2013 at 12:51 pm

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

September 18, 2013 at 12:54 pm

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.

September 18, 2013 at 1:15 pm

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.

September 18, 2013 at 1:22 pm

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.

September 18, 2013 at 3:13 pm

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.

September 18, 2013 at 5:47 pm

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.

September 19, 2013 at 10:07 am

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!

September 19, 2013 at 11:22 am

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).