We help IT Professionals succeed at work.

Group Members to a csv file using PowerShell.

216 Views
Last Modified: 2017-04-13
Hello,

I have a script in PS that allows me to get a list of user in active directory and the groups those users are a member of.
What I have is this:

$OU= "OU=CIS Groups,OU=AllGroups,DC=xyz,DC=edu"
$groups = Get-ADGroup -Filter * -SearchBase $OU # Get list of Groups on CIS Groups
$output = ForEach ($g in $groups)
 {
 $results = Get-ADGroupMember -Identity $g.SamAccountname -Recursive | Get-ADUser -Properties displayname, SamAccountName, EmployeeNumber, Memberof
 
 ForEach ($r in $results){
 New-Object PSObject -Property @{

        GroupName = $g.Name
            GroupMember = $r.Memberof
        UserName = $r.SamAccountName
        ColleagueID = $r.EmployeeNumber
            FirstName = $r.GivenName
        LastName = $r.Surname
     }
    }
 }

$output | Export-Csv C:\GM.csv -NoTypeInformation

What I also need is to get the OU in which every AD user belongs. The partial result of the file is:
UserName      ColleagueID      LastName      GroupName              FirstName      GroupMember
MMX1111E      818523              Matlock            Astra Off-Campus      Michael       Microsoft.ActiveDirectory.Management.ADPropertyValueCollection
JEM2222E      3426815        Jensen            Astra Off-Campus      Eric               Microsoft.ActiveDirectory.Management.ADPropertyValueCollection

In GroupMember, the information I want to see is the OU in which the groupName Astra Off-Campus is in.
In this case, an OU called Astra Access Control has the GroupName Astra Off-Campus and others in it.

Please, refer to attached file to see results.

Can someone tell me what can I do to get those results?
Example.csv
Comment
Watch Question

Jason CrawfordTransport Ninja
CERTIFIED EXPERT

Commented:
If you're trying to pull the OU each user lives in, why are you A) naming the property 'GroupMember' when an OU isn't a group and B) specifying the 'MemberOf' attribute?  Assuming I understand your question correctly, this should work for you:

$OU= "OU=CIS Groups,OU=AllGroups,DC=xyz,DC=edu"
$groups = Get-ADGroup -Filter * -SearchBase $OU # Get list of Groups on CIS Groups
$output = ForEach ($g in $groups) {
$results = Get-ADGroupMember -Identity $g.SamAccountname -Recursive | Get-ADUser -Properties displayname, SamAccountName, EmployeeNumber, Memberof
 
 ForEach ($r in $results){
   New-Object PSObject -Property @{
      GroupName = $g.Name
      GroupMember = $($r.DistinguishedName.Split(',')[1])
      UserName = $r.SamAccountName
      ColleagueID = $r.EmployeeNumber
      FirstName = $r.GivenName
      LastName = $r.Surname
    }
  }
}

$output | Export-Csv C:\GM.csv -NoTypeInformation

Open in new window

Author

Commented:
Jason,

Thanks for your answer, but the results I get are not the ones that I need. See file attached.
I also attached part of the AD tree that includes the OU, security groups, and members.
The idea is to get: UserName      ColleagueID      LastName      GroupName          FirstName         GroupMember
                                ATM34553     123456           Andes          Analytics CCE       Thomas         Analytics Access Control

I know GroupMember is an OU. I will change the name later.
Example.csv
Capture.docx
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
I believe you are looking to find the OU the group is a member of, and put it into the list I have named that attribute "GroupOU" and expanded upon your original script to be the following:

## Script Name: GroupMemberReport.ps1
## Version: 1.0.0
#

# =================================== #
# = Define Parameters				= #
# =================================== #
param(
	$DebugPreference="SilentlyContinue"
	# $DebugPreference="Continue"
) 
# Set Debug Level, Continue shows debug messages, SilentlyContinue is Default
Write-Debug "Debug-Preferences:"
Write-Debug "DebugPreference = $DebugPreference"

# =================================== #
# = Begin Functions					= #
# =================================== #

#######
## Function: Email

#######
## Function: Get-IsISE

function Get-IsISE {
# Tests whether the current environment contains the $psISE Variable which is normally only set when running in ISE
    try {    
        return $psISE -ne $null;
    }
    catch {
        return $false;
    }
}


# =================================== #
# = Initialize Variables			= #
# =================================== #

#######
## 0.1: Set the script Path (If in ISE, set it to a manual value, otherwise use script location value)
$scriptPath = 'C:\Admin\Scripts\Experts-Exchange\29015543\Group-Members-to-a-csv-file-using-PowerShell'
if ( -not ( Get-IsISE ) ) {
	# Get Path for this script File and set it to a variable
	$scriptPath = split-path -parent $MyInvocation.MyCommand.Definition
}

#######
## 0.2: Set CSV Output name
$CSV_Path = "$ScriptPath\GM.csv"

#######
## 0.3: Set Group Search base to check specific groups for thier users.
$Group_SearchBase = "OU=Groups,OU=Root,DC=Domain,DC=com"

#######
## 0.4: Initialize Results variable
$Results = @()

# =================================== #
# = Begin Main						= #
# =================================== #

#######
## 1.1: Get all groups from AD which are within this search path:
$Groups = Get-ADGroup -Filter * -SearchBase $Group_SearchBase # Get list of Groups on CIS Groups


#######
## 1.2: Loop through these groups so we can finnd their members:
ForEach ($G in $Groups) {
	write-debug "Group: $G.name`r"
	#######
	## 1.3: Get all user atributes for all group members which are users:
	$Users = $($( $G | Get-ADGroupMember | Where-Object {$_.objectClass -eq 'user'} ) | Get-ADUser -Properties displayname, SamAccountName, EmployeeNumber )
	#######
	## 1.4: Loop through these users so we can package all the info
	foreach ( $U in $Users )  {
		write-debug "User: $U.name`r"
		#######
		## 1.5: Create a temporary object witht he info we want, and add it to the results variable
		$Results += New-Object PSObject -Property @{
			GroupName = $G.Name
			GroupOU = $($G.DistinguishedName -replace 'CN=[^=]+,OU=([^=]+),.*','$1')
			UserName = $U.SamAccountName
			ColleagueID = $U.EmployeeNumber
			FirstName = $U.GivenName
			LastName = $U.Surname
		}

	}

}
#######
## 1.6: Output results:
write-debug "Results:`r`n"
$Results | Out-String | Write-Debug
$Results | Export-Csv $CSV_Path -NoTypeInformation

Open in new window

Author

Commented:
Hi Ben,

Thanks. That is what I needed. One more thing, I was checking the CSV file and it also pulling up username accounts that don't have an EmployeeNumber and it shows ColleagueID cell as empty. Is there a way to avoid those accounts to be written in the CSV file?

Thanks for your help.
Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

Author

Commented:
Thank you Ben. The script you modified is what I needed.
Ben Personick (Previously QCubed)Lead SaaS Infrastructure Engineer
CERTIFIED EXPERT

Commented:
Hey Katia,

  You're welcome, glad to help!

:)

Ben

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions