Avatar of Katia Nunez
Katia Nunez
 asked on

Group Members to a csv file using PowerShell.

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
Active DirectoryPowershell

Avatar of undefined
Last Comment
Ben Personick (Previously QCubed)

8/22/2022 - Mon
Jason Crawford

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

Katia Nunez

ASKER
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)

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

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Katia Nunez

ASKER
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.
ASKER CERTIFIED SOLUTION
Ben Personick (Previously QCubed)

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Katia Nunez

ASKER
Thank you Ben. The script you modified is what I needed.
Ben Personick (Previously QCubed)

Hey Katia,

  You're welcome, glad to help!

:)

Ben
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.