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

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
LVL 1
cpadmAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

cpadmAuthor Commented:
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
Dan ArseneauDevOps EngineerCommented:
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
Neil RussellTechnical Development LeadCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Neil RussellTechnical Development LeadCommented:
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
Neil RussellTechnical Development LeadCommented:
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
cpadmAuthor Commented:
Brilliant mod to script - Only had to modify line 5 to point the script at the input Excel sheet.

Thanks very much
0
Neil RussellTechnical Development LeadCommented:
Glad to be of service.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.