Solved

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

Posted on 2012-03-23
7
1,367 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
  • 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Utilizing an array to gracefully append to a list of EmailAddresses
"Migrate" an SMTP relay receive connector to a new server using info from an old server.
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now