We help IT Professionals succeed at work.

Powershell script that can query a OU and get all group name,Members and count of members into a csv/Excel.

Hi,

Powershell script that can query a OU and get all group name,Members and count of members into a csv/Excel.
Only Distribution groups.

Can anyone help me with a code that can do this.

REgards
Sharath
Comment
Watch Question

Lee OsborneSenior Infrastructure Engineer

Commented:
Have you tried PowerGUI? http://www.powergui.org/index.jspa

This should help you do exactly what you want to do, and more!

Lee

Author

Commented:
thanks but how can i get the counts of each group members
x-menIT super hero

Commented:
try this:


$LDAP = "LDAP://OU=myOU,DC=myDomain,DC=MySuffx"
$RootPath = [ADSI] $LDAP
$groups = $RootPath.PSBase.Get_Children() | Where-Object {$_.Class -eq "Group"}
foreach ($group in $Groups){Write-host $group.Name"`,"$group.member.count"`,"$group.member}

Author

Commented:
X-men is this powershell
x-menIT super hero

Commented:
yes
x-menIT super hero

Commented:
here is the entire script (powershell)

Edit Row1 with your LDAP path

the result will be writen in the file "c:\ee.csv" (line 16)


$LDAP = "LDAP://OU=myOU,DC=MyDomain,DC=org"
$RootPath = [ADSI] $LDAP
$groups = $RootPath.PSBase.Get_Children() | Where-Object {$_.Class -eq "Group"}
$xTable = @()
foreach ($group in $Groups)
    {$o="" | select GName,GMembers,GCount; 
    $o.GName=$group.Name.ToString();
    $o.GMembers=$group.member.ToString(); 
    $o.GCount=$group.member.count; 
    $xTable += $o}
If ($xTable.Length -ne 0)
    {foreach ($x in $xTable)
        {$xGName=$x.GName;
        $xGMembers=$x.GMembers;
        $xGCount=$x.GCount
        Add-Content C:\ee.csv -Value "$xGName`,$xGMembers`,$xGCount"}
    }

Open in new window

Author

Commented:
Thanks
I get each portion in  each cell and its difficult to read or sort

OU=Named Users      OU=Users

Can i get just these

groupname ,Member1,                                         Count
                    Member2
                    Member3
                    Nested group name
                                                   Member1
                                                   Member2
                                                   Member3
The count should not include any nested group but should include members within the nested group
x-menIT super hero

Commented:
If you add a string delimiter on line 16,

EX: delimiter: "|":  Add-Content C:\ee.csv -Value "$xGName`,`|$xGMembers`|`,$xGCount"}

Wen you run the "Text to columns" in excel, place the "|" char as the string delimiter and the "," as the column delimiter

Author

Commented:
Thanks but still confusing

Can you remove all CN=,OU=,DN= all those paths and have just names
IT super hero
Commented:
here:
$LDAP = "LDAP://OU=myOU,DC=MyDomain,DC=org"
$RootPath = [ADSI] $LDAP
$groups = $RootPath.PSBase.Get_Children() | Where-Object {$_.Class -eq "Group"}
$xTable = @()
foreach ($group in $Groups)
    {$o="" | select GName,GMembers,GCount; 
    $o.GName=$group.Name.ToString();
    $o.GMembers=$Group.member | foreach {$_.tostring().substring(3,$_.tostring().IndexOf(",")-3)};
$o.GCount=$group.member.count; 
    $xTable += $o}
If ($xTable.Length -ne 0)
    {foreach ($x in $xTable)
        {$xGName=$x.GName;
        $xGMembers=$x.GMembers;
        $xGCount=$x.GCount
        Add-Content C:\ee.csv -Value "$xGName`,$xGMembers`,$xGCount"}
    }

Open in new window

Author

Commented:
I get this

You cannot call a method on a null-valued expression.
At line:3 char:34
+     $o.GName=$group.Name.ToString <<<< ();
    + CategoryInfo          : InvalidOperation: (ToString:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull

You cannot call a method on a null-valued expression.
At line:4 char:53
+     $o.GMembers=$Group.member | foreach {$_.tostring <<<< ().substring(3,$_.tostring().IndexOf(",")-3)};
    + CategoryInfo          : InvalidOperation: (tostring:String) [], RuntimeException
    + FullyQualifiedErrorId : InvokeMethodOnNull
x-menIT super hero

Commented:
have you altered line 1 with the correct LDAP path ?
x-menIT super hero

Commented:
Are there any AD objects of the class "Group" in the LDAP path you provided?

Author

Commented:
Yes path is fine and it has the groups as well
x-menIT super hero

Commented:
well, the mesage

null-valued expression.
At line:3 char:34
+     $o.GName=$group.Name

tells me that there are no objects of class 'Group' in the LDAP Path provided at line 1. so you might want to double check it. The machine is always right you know...

Author

Commented:
Sorry for the delay will check and get back