Link to home
Start Free TrialLog in
Avatar of FundFire
FundFire

asked on

AD Script to display a report of groups, group description and group members in a specific OU

Help.

This works to display group name and member name but does not give me group "description"

Add-PSSnapin Quest.ActiveRoles.ADManagement
$memberships = @()

Get-QADGroup -SizeLimit 0 -SearchRoot "OU=XX User Groups,OU=XXX,OU=Security Groups,dc=XXX,dc=COM" -IncludedProperties Description | Foreach-Object {

        $NameGroup = $_.Name
        Write-Host "Working with $NameGroup"
        $membership = Get-QADGroupMember $_.DN -Enabled -SizeLimit 0 -IncludedProperties Description
        if ($membership -ne $null ) {
        $membership | Add-Member -type NoteProperty -name AuditGroupUserIsMemberOf -value $_.Name
        $memberships += $membership
        }
    }
$memberships

$memberships | Select-Object AuditGroupUserIsMemberOf, Name | Export-Csv c:\scripts\report.csv
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
So, the script below does not use the "Quest.ActiveRoles.ADManagement" Snap in.  It uses the standard Microsoft Active Directory PS Module.

It the script creates the following output files:
1. CSV containing the list of all groups in the defined AD location (OU)
--- named:  Get-AdGroupInfo-Report.csv
2. 1 CSV per AD Group found, containing the fields Name and SamAccountName.
--- named: Get-AdGroupInfo-Report_<AdGroupNameIsInsertedHere>.csv

I recommend placing the script in its own directory before it is run, since it creates many files.

You only have to update the variable $SearchBase to match your OU Structure

# Load the AD Module
Import-Module ActiveDirectory

# Setup Globals
$SearchBase = "<YourOuStructureGoesHere>"
$Groups = Get-ADGroup -Filter * -SearchBase $SearchBase -Properties Name, DistinguishedName, groupType, Description | Select Name, DistinguishedName, Description
$GroupListOutputFile = "Get-AdGroupInfo-Report.csv"
$GroupOutput = @()
$GroupMembers = @()

##### Main #####
Write-Host "Found " $Groups.Count " groups in Active Directory location : " $SearchBase

foreach ($group in $Groups)
{
	if ($Group.DistinguishedName -ne $Null)
	{
		$ginfo = New-Object PSObject
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupName" -Value $group.Name
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDN" -Value $group.DistinguishedName
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDescription" -Value $group.Description
		$GroupOutput += $ginfo
		$GroupMembershipOutputFile = "Get-AdGroupInfo-Report_"+$group.Name+".csv"
		Get-ADGroupMember -Identity $group.DistinguishedName | Select Name, SamAccountName | Export-Csv $GroupMembershipOutputFile -NoTypeInformation -UseCulture -Encoding UTF8
	}
}

$GroupOutput | Sort GroupName | Export-Csv $GroupListOutputFile  -NoTypeInformation -UseCulture -Encoding UTF8

Open in new window


Dan
Avatar of FundFire
FundFire

ASKER

Hi Dan,  

Thanks so much for your help first off.

Is there no way to have one single report with 3 columns?   groupname, group description, user display name?  

Is this possible or am i asking for something that doesnt exist?
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Probably could, but if you have groups that are large (50, 100, 250+) members, this would make the report hard to use.

I have groups with 500-600 members controlling access to various resources, having a row entry with a column having 600 names in it is completely useless.

Plus, pulling the User's display name requires some extra coding.

Dan
Avatar of FundFire
FundFire

ASKER

ok, what about just the name? i understand this may not be practical but it is the situation im in right now.  thanks.
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
You want the group membership in a single field, no matter how many users are are in the group?

You realize the output will hard to read in some cases, right?

Dan
Avatar of FundFire
FundFire

ASKER

Right now i get

Group Name       Name
Group1               Member1
Group1               Member2
Group1               Member3
Group1               Member4
Group1               Member5
Group1               member6
Group1               Member7

I'd like to get

Group Name      Group Description      Name
Group1             Group1 Description      Member1
Group1             Group1 Description      Member2
Group1             Group1 Description      Member3
Group1             Group1 Description      Member4
Group1             Group1 Description      Member5
Group1             Group1 Description      member6
Group1             Group1 Description      Member7
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
I understand what you are asking for, but how will an entry, like below, be useful?

Group Name      Group Description      Name
Group1             Group1 Description      Member1Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1;Member1

Open in new window


That being a single record with around 50 users in a single AD group.

But I need to update the script.

Dan
Avatar of FundFire
FundFire

ASKER

I see what your saying but i'd need the Name field to have all the members of that group not just "member1"  ie (member1, member2, member3)  

If its just member 1 then it isnt useful.

I essential need a report like this because the group names dont make sense in layman's terms and someone needs to review the report frequently.  To see who is in each group, which is why i need the description as well.
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Again, I understand what you want. I was just making an example of an entry with 50 or more users.

I'll update the script to output the info as you want.

But from experience, having audited large & small AD deployments, a report like this will hard to review on a regular basis.  

Dan
Avatar of FundFire
FundFire

ASKER

ok.  thank you.
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Updated script:

# Load the AD Module
Import-Module ActiveDirectory

# Setup Globals
$SearchBase = "<YourOuStructureGoesHere>"
$Groups = Get-ADGroup -Filter * -SearchBase $SearchBase -Properties Name, DistinguishedName, groupType, Description | Select Name, DistinguishedName, Description
$GroupListOutputFile = "Get-AdGroupInfo-Report.csv"
$GroupOutput = @()

##### Main #####
Write-Host "Found " $Groups.Count " groups in Active Directory location : " $SearchBase

foreach ($group in $Groups)
{
	if ($Group.DistinguishedName -ne $Null)
	{
		$gm = Get-ADGroupMember -Identity $group.DistinguishedName | Select Name
		Write-Host "Working on group : " $group.Name
		$ginfo = New-Object PSObject
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupName" -Value $group.Name
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDN" -Value $group.DistinguishedName
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDescription" -Value $group.Description
		$ginfo | Add-Member -MemberType NoteProperty -Name  "Members" -Value ($gm.Name -join '; ')
		$GroupOutput += $ginfo
	}
}

$GroupOutput | Sort GroupName | Export-Csv $GroupListOutputFile  -NoTypeInformation -UseCulture -Encoding UTF8

Open in new window


Just remember to update the $SearchBase variable with the appropriate AD path.

Dan
Avatar of FundFire
FundFire

ASKER

Thank you, but the Members column in the output column is all blank.
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
I tested it against my AD instance, it output 1551 groups and the associated membership of each object.

Can you post the output?

Dan
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Updated script to detect AD accounts and AD contacts that are members of groups.

# Load the AD Module
Import-Module ActiveDirectory

# Setup Globals
$SearchBase = "<YourOuStructureGoesHere>"
$Groups = Get-ADGroup -Filter * -SearchBase $SearchBase -Properties Name, DistinguishedName, groupType, Description | Select Name, DistinguishedName, Description
$GroupListOutputFile = "Get-AdGroupInfo-Report.csv"
$GroupOutput = @()

##### Main #####
Write-Host "Found " $Groups.Count " groups in Active Directory location : " $SearchBase

foreach ($group in $Groups)
{
	if ($Group.DistinguishedName -ne $Null)
	{
		$gm = Get-ADGroup -Identity $group.DistinguishedName -Properties member | Select -ExpandProperty member | Get-ADObject
		$ginfo = New-Object PSObject
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupName" -Value $group.Name
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDN" -Value $group.DistinguishedName
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDescription" -Value $group.Description
		$ginfo | Add-Member -MemberType NoteProperty -Name  "Members" -Value ($gm.Name -join '; ')
		$GroupOutput += $ginfo
	}
}

$GroupOutput | Sort GroupName | Export-Csv $GroupListOutputFile  -NoTypeInformation -UseCulture -Encoding UTF8

Open in new window


Dan
Avatar of FundFire
FundFire

ASKER

Sorry.  the Members field output is still blank.  Could version of powershell have anything to do with it?
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
This runs with PS v2+.

Dan
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Can you post the output from this command?

$PSVersionTable | ft

Open in new window


Dan
Avatar of FundFire
FundFire

ASKER

User generated image
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
OK, the script should work.  I need that screens shot of a member of the first group.

Dan
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Another try...

# Load the AD Module
Import-Module ActiveDirectory

# Setup Globals
$SearchBase = "<YourOuStructureGoesHere>"
$Groups = Get-ADGroup -Filter * -SearchBase $SearchBase -Properties Name, DistinguishedName, groupType, Description | Select Name, DistinguishedName, Description
$GroupListOutputFile = "Get-AdGroupInfo-Report.csv"
$GroupOutput = @()

##### Main #####
Write-Host "Found " $Groups.Count " groups in Active Directory location : " $SearchBase

foreach ($group in $Groups)
{
	if ($Group.DistinguishedName -ne $Null)
	{
		$gm = Get-ADGroup -Identity $group.DistinguishedName -Properties member | Select -ExpandProperty member | Get-ADObject
		$ginfo = New-Object PSObject
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupName" -Value $group.Name
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDN" -Value $group.DistinguishedName
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDescription" -Value $group.Description
		$ginfo | Add-Member -MemberType NoteProperty -Name  "Members" -Value ($gm.Name -join '; ')
		$GroupOutput += $ginfo
	}
}

$GroupOutput | Sort GroupName | Export-Csv $GroupListOutputFile  -Delimiter ";" -NoTypeInformation -UseCulture -Encoding UTF8

Open in new window


I'm forcing the semicolon as the field delimiter.  I noticed that your output is using a comma.  

Let me know...

Dan
Avatar of FundFire
FundFire

ASKER

User generated image
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Can run this command and post the output:

Get-ADGroupMember -Identity "<Put-DN-Here>" | Select Name

Open in new window


And replace the <Put-DN-Here> with the DN of one of your groups?  Let me know if you get a list users

Dan
Avatar of FundFire
FundFire

ASKER

Yes, I got output of group members.
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
OK, so the script is working but the exporting of the data seems to be an issue.

Can you replace the last line in the script with this?

$GroupOutput | Sort GroupName | Export-Csv $GroupListOutputFile -NoTypeInformation -UseCulture

Open in new window


If that doesn't work, can you try this?

$GroupOutput | Sort GroupName >> $GroupListOutputFIle

Open in new window


Dan
Avatar of FundFire
FundFire

ASKER

The first one gives me the same output ive been getting.  The 2nd one im going to pm you the output but it doesnt give the desired results.
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Can you run the very first script I posted and tell me if the group named files contain the members of the group?

Dan
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
Can you post or send me the output of this command:

Get-ADGroupMember -Identity "<Put-DN-Here>" | Select Name

Open in new window


I need to see want is being returned and how the data is formatted.

Dan
Avatar of Dan McFadden
Dan McFadden
Flag of United States of America image
One more script edit.  Can you try this?

# Load the AD Module
Import-Module ActiveDirectory

# Setup Globals
$SearchBase = "<YourOuStructureGoesHere>"
$Groups = Get-ADGroup -Filter * -SearchBase $SearchBase -Properties Name, DistinguishedName, groupType, Description | Select Name, DistinguishedName, Description
$GroupListOutputFile = "Get-AdGroupInfo-Report.csv"
$GroupOutput = @()

##### Main #####
Write-Host "Found " $Groups.Count " groups in Active Directory location : " $SearchBase

foreach ($group in $Groups)
{
	if ($Group.DistinguishedName -ne $Null)
	{
		$gm = Get-ADGroupMember -Identity $group.DistinguishedName | Select Name
		Write-Host "Working on group : " $group.Name
		$ginfo = New-Object PSObject
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupName" -Value $group.Name
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDN" -Value $group.DistinguishedName
		$ginfo | Add-Member -MemberType NoteProperty -Name  "GroupDescription" -Value $group.Description
		$ginfo | Add-Member -MemberType NoteProperty -Name  "Members" -Value ([string]::Join(";",$gm.Name))
		$GroupOutput += $ginfo
	}
}

$GroupOutput | Sort GroupName | Export-Csv $GroupListOutputFile  -NoTypeInformation -UseCulture -Encoding UTF8

Open in new window


I think this has to do with the way the Join works in PowerShell v2 as compared to PS3+.

Let me know.

Dan
Avatar of FundFire
FundFire

ASKER

This is what im getting.  Should i try going to version 3 at this point?
ScreenHunter_270-Sep.-15-15.04.jpg
ASKER CERTIFIED SOLUTION
Avatar of Dan McFadden
Dan McFadden
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
Avatar of FundFire
FundFire

ASKER

Thanks for everything.