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?
LVL 35
YZlatAsked:
Who is Participating?
 
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:
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
On-Demand: Securing Your Wi-Fi for Summer Travel

Traveling this summer?Check out our on-demand webinar to learn about the importance of Wi-Fi security and 3 easy measures you can start taking immediately to protect your private data while using public Wi-Fi. Follow us today to learn more!

 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.