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 .ADPropert yValueColl ection
JEM2222E 3426815 Jensen Astra Off-Campus Eric Microsoft.ActiveDirectory. Management .ADPropert yValueColl ection
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
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
$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.
JEM2222E 3426815 Jensen Astra Off-Campus Eric Microsoft.ActiveDirectory.
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
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
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
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you Ben. The script you modified is what I needed.
Hey Katia,
You're welcome, glad to help!
:)
Ben
You're welcome, glad to help!
:)
Ben
Open in new window