by j_internet at 2013-02-26 18:33:46
Hi,by DonJ at 2013-02-27 07:41:34
I am trying to do a couple simple things but my mind is not so simple
What I am trying to accomplish is
1) Read from a file list of users (have to get the oneliner to work first)
2) Output in CSV
3) The CSV must be in SamAccountname, Name, group name (no DN just group name)
4) Figured I can clean up the DN on the memberof in Excel
Would look like this
123Logon, Bob, Domain Users, Remote Access, File Share1,
Or even
123Logon, Bob, Domain Users
123Logon, Bob, Remote Access
123Logon, Bob, File Share1
222Logon, Sam, File Share2
etc
Well
Example 1 almost gets there.
Example 2 It works but For some reason the memberof returns "Microsoft.ActiveDirectory.Management.ADPropertyValueCollection"
Example 3 yes I can output the wrong output of the Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
Example 1
Get-ADUser bob -property * | Select name, SamAccountname, memberof
Example 2
Get-ADUser Bob -property * | Select-Object name, SamAccountname, Memberof | convertto-csv
Example 3
Get-ADUser Bob -property * | Select-Object name, SamAccountname, Memberof | convertto-csv | out-file c:\ps\aaa.txt
I know this is formatting its the | that is thorwing me off.
Thanks Again!
Example 2 does that because MemberOf contains multiple values (it isn’t a "property value," it is a "property value collection"). CSV files aren’t intended to contain multiple values in a field; it’s a "flat" data structure, not a hierarchical one. This isn’t a formatting thing at all - it’s that users can belong to more than one group.by DonJ at 2013-02-27 07:48:50
Also, the contents of the MemberOf property are full DNs, not just the CN of the group. Because DNs have commas in them, they’ll mess up your CSV formatting.
What do you want the CSV file to look like when the user is a member of more than one group (which most users are)?
If you want something like "Name,SamAccountName,Group,Group,Group,Group" then you might do something like this:
Get-ADUser -Identity Bob -Property * |
Select -Property Name,SamAccountName,
@{name=‘MemberOf’;
expression={ ($.MemberOf | ForEach { ($ -split ‘,’)[0] -replace ‘CN=’,‘’}) -join ‘,’}}
Pretty proud I cam up with that ;). It’ll work, but I’m farm more interested in making sure you understand why it works, so ask questions if you don’t.
Eh, I’ll explain for future generations ;). There’s a bunch of parenthetical commands and curly-bracketed expressions you have to unwind.by coderaven at 2013-02-27 07:57:35
I’m using a hashtable in Select-Object’s -Property list to generate a custom (or "calculated") property. The expression defines its contents.
I’m piping the MemberOf property - which, remember, can contain multiple values - to ForEach, which will let me work with just one value at a time. Note that the whole "piping MemberOf to ForEach," and ForEach itself, is in a parenthetical expression. Let’s call in Paren1, just for reference purposes.
Inside Paren1, for each value, I’m splitting it on the comma in the DN. This is done inside another parenthetical expression, which I’ll call Paren2.
Paren2 will be an array; the first item in the array (index [0]) will be the "CN=Domain Users" group name. I replace the "CN=" with a blank string to get just the group name.
The output of ForEach (that is, the result of Paren1) will be a collection of strings - but a CSV file can’t display collections. So as one last step, I join those collection elements into their own comma-separated list, using the -join operator.
This isn’t strictly legal CSV, or at least it’s not a well-designed CSV file, but I should get you the result you were looking for.
As a side note, if I need to export information like this that contain property value collections, I use Export-CliXML. The XML is great at holding this entire structure with no problem. Of course, the use entirely depends on where the data is headed. That is a very nice expression, I like the way you pull the name out of the DN.by DonJ at 2013-02-27 08:01:55
Agreed! XML is a far better way of persisting the information. Although if I’m trying to build a report, I’d do something entirely different and output a nice-looking HTML instead - you can use a similar command to construct a table column that includes the group names, one per line.