none
Extract Members from Group in AD to CSV

    Question

  • Hi

    I am new to powershell and I need some assistance.

    We have a group of all the departments defined in AD. In each group there are sub groups of each departments. Here is an example

    IT Department

    - Developers

    - System Engineers

    - Network Engineers

    HR Department

    - Payroll

    - Recruitment

    - Training

    I would like a powershell script to extract all the members from the above group into a CSV file in the format below

    Group Group Description Group Owner Group Member
    Developers IS Developers Owner = Manager Member1
    Member2

    Member3

    Member4
    Member5
    Member6

    Anyway of doing this via Powershell and how to do it?

    Also, I am only interested in Security Groups and NOT Distribution Groups.


    Thanks


    • Edited by cyw77 Thursday, March 01, 2012 11:48 PM
    Thursday, March 01, 2012 11:46 PM

All replies

  • You can do this with any of the PowerShell AD tools.  Here is an example using the Quest AD tools.

    Add-PSSnapin Quest.ActiveRoles.ADManagement
    
    $Results = @()
    $Output = C:\GroupMembers.csv
    
    $Group = Get-QADGroup *
    
    ForEach ($SG in $Group)
        {
        $Results += Get-QADGroupMember $SG |
        Add-Member -Name "Group" -Value $SG -MemberType NoteProperty -PassThru |
        Select Group,Domain,SamAccountName,FirstName,LastName,Department,Description,AccountIsDisabled,ParentContainer
        }
    
    # Output results in selected format
    $Results | Export-CSV -Path $Output -NoTypeInformation


    Rich Prescott | Infrastructure Architect, Windows Engineer and PowerShell blogger | MCITP, MCTS, MCP

    Engineering Efficiency
    @Rich_Prescott
    Windows System Administration tool
    AD User Creation tool

    Friday, March 02, 2012 12:27 AM
  • Thanks for the response.

    I have got the below error. Is it because I need to pre-create the file on C:\GroupMembers.csv for it to work?

    The term 'C:\GroupMembers.csv' is not recognized as the name of a cmdlet, function, script file, or operable program. C
    heck the spelling of the name, or if a path was included, verify that the path is correct and try again.
    At C:\temp\GrabADMemberFromGroups.ps1:4 char:30
    + $Output = C:\GroupMembers.csv <<<<
        + CategoryInfo          : ObjectNotFound: (C:\GroupMembers.csv:String) [], CommandNotFoundException
        + FullyQualifiedErrorId : CommandNotFoundException

    Also, for your code, if I want it to check only the IT Department group, replace the following?

    Replace below

    $Group = Get-QADGroup *

    With this

    $Group = Get-QADGroup "IT Department"

    Am I right?


    Thanks

    Friday, March 02, 2012 2:02 AM
  • Yes.  And it looks like I forgot to put quotes around the filepath.

    $Output = "C:\GroupMembers.csv"


    Rich Prescott | Infrastructure Architect, Windows Engineer and PowerShell blogger | MCITP, MCTS, MCP

    Engineering Efficiency
    @Rich_Prescott
    Windows System Administration tool
    AD User Creation tool

    Friday, March 02, 2012 2:18 AM
  • Hi

    I managed to run your scripts but sending the output to the screen but there seems to be some issue

    Add-PSSnapin Quest.ActiveRoles.ADManagement

    $Results = @()
    $Group = Get-QADGroup "IT Department"

    ForEach ($SG in $Group)
        {
        $Results += Get-QADGroupMember $SG |
        Add-Member -Name "Group" -Value $SG -MemberType NoteProperty -PassThru |
        Select Group,Domain,SamAccountName,FirstName,LastName,Department,Description,AccountIsDisabled,ParentContainer
        }

    # Output results in selected format
    $Results

    The results return was all the Sub Groups that were found in "IT Department". It does not have all the members from the query. Are we missing out something?

    I think it is kinda tricky as it needs to look for the members in the Sub Group.

    "IT Department" contains

    "Developer Group" which contains

    Member 1, Member 2, Member 3 etc


    Thanks


    • Edited by cyw77 Friday, March 02, 2012 2:39 AM
    Friday, March 02, 2012 2:37 AM
  • Basically to understand this better is

    "IT Department" is the Main group which contains "Developer Group", "Systems Engineers Group" etc

    When I ran your scripts to Get-QAD "IT Department", I got the results as "Developer Group", Systems Engineers Group". But the actual members are actually in the Developer Group and the Systems Engineers Group.

    So does that mean it need a nested loop or something?


    Thanks

    Friday, March 02, 2012 2:57 AM
  • http://wiki.powergui.org/index.php/Get-QADGroupMember

    Update this line: $Results += Get-QADGroupMember $SG -Indirect |


    Rich Prescott | Infrastructure Architect, Windows Engineer and PowerShell blogger | MCITP, MCTS, MCP

    Engineering Efficiency
    @Rich_Prescott
    Windows System Administration tool
    AD User Creation tool

    Friday, March 02, 2012 2:59 AM
  • Hi

    Thanks for the response. I have replace it with the -Indirect switch which does a recursive. But this is the output that it displays

    Group Group Description Group Member
    IT Department Member 1 Description Member 1

    But the challenging part is the output to be the following

    Group Group Description Group Member
    Developers IS Developers Member 1

    Meaning I only want the name of the members that is displayed from the Sub Group

    Thanks for your help by the way. Appreciate it.


    Thanks

    Friday, March 02, 2012 4:02 AM
  • I tried to have a go on my own but when I run the scripts, it seems to freeze up my PC and not sure what happens. Maybe due to my bad coding.

    Add-PSSnapin Quest.ActiveRoles.ADManagement
    Set-QADPSSnapinSettings -DefaultSizeLimit 0
    $Results = @()
    $Output = "C:\Temp\GroupMembers.csv"
    $ParentGroup = Get-QADGroup "IT Department"
    ForEach ($Group in $ParentGroup) {
        $SubGroup += Get-QADGroupMember $Group
    	ForEach ($Member in $SubGroup) {
    		$Results += Get-QADGroupMember $Member | 
    		Add-Member -Name "Group" -Value $SubGroup -MemberType NoteProperty -PassThru |
    		Select Group,Description,DisplayName
    	}
    }
    # Output results in selected format
    $Results | Export-CSV -Path $Output -NoTypeInformation

    Any idea what could be the mistake? Too much nested loops?

    Thanks

    Friday, March 02, 2012 4:46 AM
  • Hi,

    If groupA has groupB as member, and groupB has groupA as member, the loop willbe endless. Please make sure that there is no such groups.

    To get nested groups, please also check below code:

    $groupnames = get-qadgroup -resultsize 0 | select -expand dn
    &{foreach ($name in (get-content d:\names.txt | where {$_})) {

    Get-QADGroupMember -Service domain.com -sizelimit 0 $name |
       
    foreach {if ($groupnames -contains $_.dn){
           
    new-object psobject -property @{Group = $_.dn;NestedIn = $name}
           
    }
       
    }
    }
    } | export-csv -NoTypeInformation d:\members.csv

    The below link should be helpful

    http://gallery.technet.microsoft.com/scriptcenter/fa4ccf4f-712e-459c-88b4-aacdb03a08d0

    Best Regards,

    Yan Li


    Yan Li

    TechNet Community Support


    Monday, March 05, 2012 5:59 AM
    Moderator
  • Hi Yan Li

    This the code that I copied from you and changing it to my own domain.

    Add-PSSnapin Quest.ActiveRoles.ADManagement
    Set-QADPSSnapinSettings -DefaultSizeLimit 0
    $Results = @()
    $Input = "C:\Groups.txt"
    $Output = "C:\GroupMembers.csv"
    Clear-Content $Output
    $GroupNames = Get-QADGroup | select -expand dn
    &{foreach ($name in (Get-Content $Input | where {$_})) {
    Get-QADGroupMember -Service domain.com -sizelimit 0 $name |
        foreach {if ($groupnames -contains $_.dn){
            New-Object psobject -property @{Group = $_.dn;NestedIn = $name}
            }
        }
    }
    } | Export-Csv -NoTypeInformation $Output

    I get this error below

    Get-Content : Cannot bind argument to parameter 'Path' because it is an empty string.
    At C:\Test.ps1:23 char:33
    + &{foreach ($name in (Get-Content <<<<  $Input | where {$_})) {
        + CategoryInfo          : InvalidData: (:) [Get-Content], ParameterBindingValidationException
        + FullyQualifiedErrorId : ParameterArgumentValidationErrorEmptyStringNotAllowed,Microsoft.PowerShell.Commands.GetContentCommand


    Thanks

    Tuesday, March 06, 2012 1:55 AM