Solved

Powershell Query - Input Excel List of AD Security Groups -> Output Excel Sheet of Group Members

Posted on 2012-03-23
7
1,444 Views
Last Modified: 2012-03-23
Hi,

I have an excel sheet with a single column of Active Directory security groups (40 in total). I want to run a powershell query that outputs the members of each group to an Excel sheet (Column1: groupName, Column2: memberName).

There are plenty of examples on the web on how to report on all groups in Active Directory, but we have thousands of groups, but I only want to report on 40 specific groups.

Help!

Fin
0
Comment
Question by:cpadm
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 1

Author Comment

by:cpadm
ID: 37756842
I have the following powershell script from the web, but I want to be able to feed in an Excel sheet, and get the script to loop through a column of security group:

#Make ADSI Connection
$Group = [ADSI]"LDAP://<domain>/<ldap_cn_of_group"

#Setup our excel File
$Excel = new-object -comobject Excel.Application
$Workbook = $Excel.workbooks.add()
$Worksheet = $Workbook.worksheets.item(1)

# Setup a counter for our rows(Start at 2 0 wich is cell A2, the header stuff goes at 1,0 which is A1)
$Row = 2

#Create File and Header information
#sc -path ADUIout.txt -value "Group,Membership"
$Worksheet.cells.item(1,1) = "Group"
$Worksheet.cells.item(1,4) = "Group Membership"
#$Excel.visible = $TRUE

    $membership = $null
    foreach ($member in $Group.member)
    {
        $Worksheet.cells.item($Row, 1) = "$Group"
        $Worksheet.cells.item($Row, 2) = "$member"
        $Row++
    }

$Workbook.saveas("H:\ADUI.xls")
$Workbookl.save()
$Excel.quit()

Open in new window


Credits to http://serverfault.com/questions/121944/how-to-export-ad-security-list-to-excel
0
 
LVL 9

Expert Comment

by:Dan Arseneau
ID: 37757069
Powershell has this nice cmdlet call import-csv.  It'll take the listing of one column of data in a file (with a label) and dump it into a array.  Then you just have to loop through that array.

For example, if you have the following LIST.CSV

Name
groupone
grouptwo
groupthree

foreach($Group in Import-Csv c:\list.csv)
{
    Run your script using the variable.label
    Write-Host $Group.Name
}

Open in new window

0
 
LVL 37

Accepted Solution

by:
Neil Russell earned 500 total points
ID: 37757270
OK here goes....

Firstly you need to install the FREE (Comercial) Powershell commandlets from Quest, Quest QAD Cmdlets) from here.... http://www.quest.com/powershell/activeroles-server.aspx

Then save this script and run it.

Add-PSSnapin Quest.Activeroles.admanagement
clear
#Setup our excel File
$Excel = new-object -comobject Excel.Application
$Workbook = $Excel.workbooks.open("c:\scripts\Book1.xls") # <--- YOUR BASE EXCEL.XLS file
$Worksheet = $Workbook.worksheets.item(1)
$worksheet2 = $Workbook.worksheets.add($worksheet)
$Excel.visible = $TRUE
# Setup a counter for our rows(Start at 2 0 wich is cell A2, the header stuff goes at 1,0 which is A1)
$Row = 2
$GroupRow = 1

#Create File and Header information
#sc -path ADUIout.txt -value "Group,Membership"
$Worksheet2.cells.item(1,1) = "Group"
$Worksheet2.cells.item(1,2) = "Group Membership"

#get first group
$GroupName = $Worksheet.cells.item($GroupRow,1).value()
do {
	$Groupmembers= Get-QADGroupMember $GroupName 
    	foreach ($member in $Groupmembers)
    	{
    	    $Worksheet2.cells.item($Row, 1) = $GroupName
	        $Worksheet2.cells.item($Row, 2) = $member.DisplayName
        	$Row++
    	}
		$GroupRow +=1
		$GroupName = $Worksheet.cells.item($GroupRow,1).value()
	} While ($GroupName -ne $null )

$Workbook.save()

$Excel.quit()

Open in new window


Make sure your excel workbook contains only one workseeht and that it just lists the groups you want in column A with no blanks....

Hope that helps.
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 37

Expert Comment

by:Neil Russell
ID: 37757313
Oh and if you change the line...

      $Groupmembers= Get-QADGroupMember $GroupName
to
      $Groupmembers= Get-QADGroupMember $GroupName -Indirect

Then it will also list the members who are indirect members of a group.
i.e. it will expand membership when a group contains a group (contains a group ... n )
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 37757452
Finally, if you want a version that just creates a spreadsheet for ALL Members of ALL groups...

#Make ADSI Connection
#$Group = [ADSI]"LDAP://<domain>/<ldap_cn_of_group"
clear
#Setup our excel File
$Excel = new-object -comobject Excel.Application
$Workbook = $Excel.workbooks.open("c:\scripts\Book1.xls")
$worksheet2 = $Workbook.worksheets.add()
$Row = 2

#Create File and Header information
$Worksheet2.cells.item(1,1) = "Group"
$Worksheet2.cells.item(1,2) = "Display  Name"
$Worksheet2.cells.item(1,3) = "SamAccountName"

$groups = Get-QADGroup -Empty:$false 

foreach ( $group in $groups ) {
	$GroupName = $Group.name
	$Groupmembers= Get-QADGroupMember $GroupName -Indirect
    	foreach ($member in $Groupmembers)
    	{
    	        $Worksheet2.cells.item($Row, 1) = $GroupName
	        $Worksheet2.cells.item($Row, 2) = $member.DisplayName
	        $Worksheet2.cells.item($Row, 3) = $member.SamAccountName
        	        $Row++
    	}
      }
	
$Workbook.save()
#$Excel.Visible = $true 
$Excel.quit()

Open in new window

0
 
LVL 1

Author Closing Comment

by:cpadm
ID: 37757983
Brilliant mod to script - Only had to modify line 5 to point the script at the input Excel sheet.

Thanks very much
0
 
LVL 37

Expert Comment

by:Neil Russell
ID: 37758666
Glad to be of service.
0

Featured Post

Is Your AD Toolbox Looking More Like a Toybox?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article demonstrates probably the easiest way to configure domain-wide tier isolation within Active Directory. If you do not know tier isolation read https://technet.microsoft.com/en-us/windows-server-docs/security/securing-privileged-access/s…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question