Query a OU and all Ou's below it and get each users membership and count alone.

bsharath
bsharath used Ask the Experts™
on
Hi,

Query a OU and all Ou's below it and get each users membership and count alone.
Like

Username 10
                            Groupname
                            Groupname2

The same for all users into a csv.

Regards
sharath
Vbs or powershell

Regards
sharath
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
What do you mean by "count" here? How many groups the user belongs to? What AD version do you use?
Do you need the recursive memberships of the users?

Author

Commented:
yes i want the count of membership of each user
Ad 2003 and 2008
Chris DentPowerShell Developer
Top Expert 2010

Commented:

In theory this should work. The formatting is a bit basic, but we can't output directly to CSV since there's no real format.

Anyway, give it a shot? Quest CmdLets again.

Chris
$OutputFile = "somefile.txt"

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 | ForEach-Object {
  "$($_.SamAccountName) $($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.DN | ForEach-Object { "  $_.Name" }
  ""
} > $OutputFile

Open in new window

Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Author

Commented:
Thanks works fine
I get .Name next to each group. Can this be removed and can i get in any other format where i can later filter the counts alone
Or have the data sorted from smallest to large while outputting itself
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Sorry, fixed here :)

Other formats... You can't really sort at all in the current format. The username, count and groups would all need to be a single data set, and in CSV that means a single line.

How would you like it to be formatted?

Chris
$OutputFile = "somefile.txt"

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 | ForEach-Object {
  "$($_.SamAccountName) $($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.DN | ForEach-Object { "  $($_.Name)" }
  ""
} > $OutputFile

Open in new window

Author

Commented:
Thanks Chris
Attached a sample output file
Sample.xls

Author

Commented:
After a long run and a lot of output i got this

Get-QADGroup : An invalid directory pathname was passed
At line:3 char:15
+   Get-QADGroup <<<<  -ContainsMember $_.DN | ForEach-Object { "  $($_.Name)" }
    + CategoryInfo          : NotSpecified: (:) [Get-QADGroup], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException,Quest.ActiveRoles.ArsPowerShellSnapIn.Powers
   hell.Cmdlets.GetGroupCmdlet
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Sample.xls doesn't really mean anything to me, is it the right file?

That suggests one the users contains a reserved character in their distinguished name.

Lets try this version, see if we can skip around that.

Chris
$OutputFile = "somefile.txt"

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 | ForEach-Object {
  "$($_.SamAccountName) $($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.SamAccountName | ForEach-Object { "  $($_.Name)" }
  ""
} > $OutputFile

Open in new window

Author

Commented:
I posted the xls to give an idea on the format the csv is needed so can filter the counts..
:-)

Author

Commented:
Hi Chris any views....
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Sorry, busy days.

So, the format. Do you only want the group count? Not sure how the list of groups fits into the sample.

Chris

Author

Commented:
I want the count and group membership as well.
The only logic would be to be able to filter the count...
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Which goes back to formatting :)

I can't say I know Excel particularly well, but if we have this format:

SomeName,GroupCount
                ,Group1
                ,Group2

Then you will not be able to sort on count.

Chris

Author

Commented:
Can i get as this

SomeName,    GroupCount
                                 2         Group1
                                            Group2
SomeName,    
                                 3         Group1
                                            Group2
                                            Group3

Author

Commented:
A reminder Chris...
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Like this?

Kind of tricky to put the group count on the same line as the groups, unless the groups are packed into a single cell.

Chris
$OutputFile = "somefile.txt"

"Username,GroupCount,Groups" > $OutputFile

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 | ForEach-Object {
  "$($_.SamAccountName),$($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.SamAccountName | ForEach-Object { ",,$($_.Name)" }
} > $OutputFile

Open in new window

Author

Commented:
I get the format perfecta nd get a lot of data also.
But at the end of script completion i get this

Get-QADGroup : An invalid directory pathname was passed
At line:3 char:15
+   Get-QADGroup <<<<  -ContainsMember $_.SamAccountName | ForEach-Object { ",,$($_.Name)" }
    + CategoryInfo          : NotSpecified: (:) [Get-QADGroup], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException,Quest.ActiveRoles.ArsPowerShellSnapIn.Powers
   hell.Cmdlets.GetGroupCmdlet

Get-QADUser : LDAP://I04.lth.local/schema/department
At line:1 char:12
+ Get-QADUser <<<<  -SearchRoot "OU=users,OU=C,OU=ia,OU=Oces,DC=lth,DC=local" -SizeLimit 0 | ForEach-
Object {
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], InvalidPathnameException
    + FullyQualifiedErrorId : Quest.ActiveRoles.ArsPowerShellSnapIn.DirectoryAccess.InvalidPathnameException,Quest.Act
   iveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.GetUserCmdlet
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Hmm try this?

Chris
$OutputFile = "somefile.txt"

"Username,GroupCount,Groups" > $OutputFile

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 | ForEach-Object {
  "$($_.SamAccountName),$($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.DN | ForEach-Object { ",,$($_.Name)" }
} > $OutputFile

Open in new window

Author

Commented:
I still get the same error in the initial stage itself...
Chris DentPowerShell Developer
Top Expert 2010

Commented:

It doesn't output anything?

You'll need to check the value for SearchRoot if it doesn't.

Chris

Author

Commented:
I get this


Get-QADUser : Server not exist or could not be contacted: DC04.lth.local
At line:1 char:12
+ Get-QADUser <<<<  -SearchRoot "OU=users,OU=Ch,OU=Iia,OU=Offices,DC=th,DC=local" -SizeLimit 0 | ForEach-
Object {
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], ServerNotOperationalException
    + FullyQualifiedErrorId : Quest.ActiveRoles.ArsPowerShellSnapIn.DirectoryAccess.ServerNotOperationalException,Ques
   t.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.GetUserCmdlet
Chris DentPowerShell Developer
Top Expert 2010

Commented:

You can connect to a specific server with:


$OutputFile = "somefile.txt"

"Username,GroupCount,Groups" > $OutputFile

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 -Service "yourserver" | ForEach-Object {
  "$($_.SamAccountName),$($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.DN -Service "yourserver" | ForEach-Object { ",,$($_.Name)" }
} > $OutputFile


Note that you must set the Service parameter twice, once for Get-QADUser, and once for get-QADGroup.

Might be a good idea to find out why it can't talk to dc04 though.

Chris

Author

Commented:
I get this

Get-Mailbox : Missing an argument for parameter 'RecipientTypeDetails'. Specify
 a parameter of type 'Microsoft.Exchange.Data.Directory.Recipient.RecipientType
Details[]' and try again.
At line:1 char:126
+ Get-Mailbox -OrganizationalUnit "OU=Ch,OU=Ia,OU=Oes,DC=lth,DC=local" -ResultSize 8000 -RecipientTypeDetails <<<<  | %{
    + CategoryInfo          : InvalidArgument: (:) [Get-Mailbox], ParameterBin
   dingException
    + FullyQualifiedErrorId : MissingArgument,Microsoft.Exchange.Management.Re
   cipientTasks.GetMailbox
Chris DentPowerShell Developer
Top Expert 2010

Commented:

Wrong thread? Because it's not a command I've used here.

Chris

Author

Commented:
Sorry :-(

Get-QADGroup : An invalid directory pathname was passed
At line:3 char:15
+   Get-QADGroup <<<<  -ContainsMember $_.DN -Service "dc01" | ForEach-Object { ",,$($_.Name)" }
    + CategoryInfo          : NotSpecified: (:) [Get-QADGroup], COMException
    + FullyQualifiedErrorId : System.Runtime.InteropServices.COMException,Quest.ActiveRoles.ArsPowerShellSnapIn.Powers
   hell.Cmdlets.GetGroupCmdlet

Get-QADUser : LDAP://DC01.lth.local/schema/department
At line:1 char:12
+ Get-QADUser <<<<  -SearchRoot "OU=Chi,OU=Iio,OU=Offices,DC=lth,DC=local" -SizeLimit 0 -Service "dc01" | ForEach-Object {
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], InvalidPathnameException
    + FullyQualifiedErrorId : Quest.ActiveRoles.ArsPowerShellSnapIn.DirectoryAccess.InvalidPathnameException,Quest.Act
   iveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.GetUserCmdlet
PowerShell Developer
Top Expert 2010
Commented:

Goody, I suspect some of your users have less than legal characters in their names.

Can you try this version, it will echo out the Distinguished Names it's trying, you'd need to see which it throws back before it errors.

Chris
$OutputFile = "somefile.txt"

"Username,GroupCount,Groups" > $OutputFile

Get-QADUser -SearchRoot "ou=somewhere,dc=domain,dc=com" -SizeLimit 0 -Service "yourserver" | ForEach-Object {

  Write-Host "Getting groups for $($_.DN)"

  "$($_.SamAccountName),$($_.MemberOf.Count)"
  Get-QADGroup -ContainsMember $_.DN -Service "yourserver" | ForEach-Object { ",,$($_.Name)" }
} > $OutputFile

Open in new window

Author

Commented:
I get this

Get-QADUser : Server not exists or could not be contacted.
At line:1 char:12
+ Get-QADUser <<<<  -SearchRoot "OU=C,OU=I,OU=O,DC=lth,DC=local" -SizeLimit 0 -Service "dc01" | ForEach-Object {
    + CategoryInfo          : NotSpecified: (:) [Get-QADUser], Exception
    + FullyQualifiedErrorId : System.Exception,Quest.ActiveRoles.ArsPowerShellSnapIn.Powershell.Cmdlets.GetUserCmdlet
Chris DentPowerShell Developer
Top Expert 2010

Commented:

It doesn't like the server name you've specified... I can't do a lot to help with that one unless I was onsite doing it for you :)

Chris

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial