[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1201
  • Last Modified:

Exporting Active Directory group members to csv

I need to export cn, sAMAccountName, and mail attributes for each user of a particular AD group (CN=Test users,OU=Test,OU=Production,DC=MyDomain, DC=com) into csv file.

Can someone help me with powershell code for that?
0
YZlat
Asked:
YZlat
  • 6
  • 3
2 Solutions
 
GusGallowsCommented:
I would do it the following way (and this is a preference of mine to use tab delimited instead of csv just for readability in the code, either way it can still be opened in Excel):
$out = "C:\output.txt"
$inp = "cn`tSamAccountName`tmail"
out-file $out -inputobject $inp
$members = get-ADGroupMember "GroupName"
foreach ($member in $members)
{
    $UserDN = $member.distinguishedName
    $User = get-adUser $UserDN
    $adUser = [adsi]"LDAP://$UserDN"
    $Sam = $adUser.sAMAccountName
    $cn = $adUser.cn
    $mail = $adUser.mail
    $inp = "$cn`t$Sam`t$mail"
    out-file $out -inputobject $inp -Append
}

Open in new window

0
 
GusGallowsCommented:
By the way, this has to be done in the Active Directory Module for Powershell. If you can't do it that way, let me know and I will try to write you one that does it using only LDAP.
0
 
YZlatAuthor Commented:
so instead of "GroupName" I can put the path to the group? LDAP://CN=Test users,OU=Test,OU=Production,DC=MyDomain, DC=com?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
GusGallowsCommented:
If you do not have windows 2008 R2, you can use ADSI LDAP commands to do it as follows:

$out = "C:\output.txt"
$inp = "cn`tSamAccountName`tmail"
out-file $out -inputobject $inp
$root=([ADSI]"").distinguishedName
$Group = [ADSI]("LDAP://CN=Test users,OU=Test,OU=Production,"+ $root)
$members = $Group.Member
foreach ($member in $members)
{
    $adUser = [adsi]"LDAP://$member"
    $Sam = $adUser.sAMAccountName
    $cn = $adUser.cn
    $mail = $adUser.mail
    $inp = "$cn`t$Sam`t$mail"
    out-file $out -inputobject $inp -Append
}

Open in new window

0
 
GusGallowsCommented:
Yes, that is the distinguished name of the group, so you can put that in the place of GroupName.
0
 
YZlatAuthor Commented:
how can export it to csv instead of txt?
0
 
GusGallowsCommented:
Of, my bad. In the first script, for the group name, you would leave off the ldap:

It would just be "CN=Test users,OU=Test,OU=Production,DC=MyDomain, DC=com"

In the second script, it would be as posted.
0
 
chrismerrittCommented:
I would like to chime in with an update to the script above, this uses arrays instead of out-file (which is ugly!).

Will give you a CSV file instead :)

#New Blank Array to hold all the members
$MasterArray = @()

$root=([ADSI]"").distinguishedName
$Group = [ADSI]("LDAP://CN=Test users,OU=Test,OU=Production,"+ $root)
$members = $Group.Member
foreach ($member in $members)
{
    $adUser = [adsi]"LDAP://$member"
	
	#Write to host which user is being processed. Useful to see if script is processing users properly.
	Write-Host -ForeGroundColor "Yellow" "Processing Member: $($adUser.Mail)"
	
    $Sam = $adUser.sAMAccountName
    $cn = $adUser.cn
    $mail = $adUser.mail
	
	#New Blank Array created again every time a user is processed. Add whichever fields you want into the list.
	$TempArray = @()
	$TempArray = "" | Select SamAccountName, CN, Mail

	#Populate the fields in the Temp Array.
	#Add any other attributes you want as long as the Value has been added to the list in the line above.
	[string]$TempArray.SamAccountName = $Sam
	[string]$TempArray.CN = $cn
	[string]$TempArray.Mail = $mail
	
	#Copy the contents of the TempArray into the MasterArray. The TempArray is renewed for the next user.
	$MasterArray += $TempArray
}

#Export the MasterArray to host and CSV file. Do whichever you want.
$MasterArray | Out-Host
$MasterArray | Export-CSV "C:\Some Folder\SomeFile.csv" -NoType

Open in new window

0
 
GusGallowsCommented:
Thanks Chris. I haven't used export-csv in so long, I couldn't remember how to do it properly.
0
 
YZlatAuthor Commented:
Thanks to the both of you!
0

Featured Post

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now