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
Katia NunezAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jason CrawfordTransport NinjaCommented:
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

0
Katia NunezAuthor 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
0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
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

0
Redefine Your Security with AI & Machine Learning

The implications of AI and machine learning in cyber security are massive and constantly growing, creating both efficiencies and new challenges across the board. Check out our on-demand webinar to learn more about how AI can help your organization!

Katia NunezAuthor 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.
0
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Hey Katia,

  This should exclude the users without an employeeID for you.

## 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 )  {
        IF ($U.EmployeeNumber -ne $null ) {
		    write-debug "User: $U.name`r"
		    #######
		    ## 1.5: Create a temporary object with the 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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Katia NunezAuthor Commented:
Thank you Ben. The script you modified is what I needed.
1
Ben Personick (Previously QCubed)Lead Network EngineerCommented:
Hey Katia,

  You're welcome, glad to help!

:)

Ben
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Active Directory

From novice to tech pro — start learning today.