?
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,538 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 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
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Wouldn't it be nice if objects in Active Directory automatically moved into the correct Organizational Units? This is what AutoAD aims to do and as a plus, it automatically creates Sites, Subnets, and Organizational Units.
High user turnover can cause old/redundant user data to consume valuable space. UserResourceCleanup was developed to address this by automatically deleting user folders when the user account is deleted.
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…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

840 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