Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2012-03-23
7
Medium Priority
?
1,506 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 2000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Compliance and data security require steps be taken to prevent unauthorized users from copying data.  Here's one method to prevent data theft via USB drives (and writable optical media).
Microsoft Office 365 is a subscriptions based service which includes services like Exchange Online and Skype for business Online. These services integrate with Microsoft's online version of Active Directory called Azure Active Directory.
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.
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

650 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