Link to home
Start Free TrialLog in
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
Avatar of Jason Crawford
Jason Crawford
Flag of United States of America image

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

Avatar of Katia Nunez
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
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

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
Avatar of Ben Personick (Previously QCubed)
Ben Personick (Previously QCubed)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you Ben. The script you modified is what I needed.
Hey Katia,

  You're welcome, glad to help!

:)

Ben