A PowerShell module for Active Directory was released with PowerShell 2.0, the version that shipped with Server 2008 R2. This module includes several cmdlets that let you work directly with Active Directory objects. In this blog post, we will look at retrieving user properties and attributes from Active Directory, with the Get-Aduser cmdlet. We will also look at how to present them in a clean and tidy format in Microsoft Excel.
We will of course have to import active directory module into a PowerShell console first.
import-module activedirectory
Then we can start retrieving user properties.
If you run the following command, you can see a list of all user related properties/attributes that are available to the get-user cmdlet
get-aduser username -properties *
(This screenshot has been cut at the CN property, but totally many more properties are listed as well)
By running this command you can get the properties you define for all users in a an OU (and for users in it’s child OUs)
get-aduser -searchbase ‘ou=user accounts,dc=contoso,dc=com’ -searchscope 1 -filter * -properties define properties here in a comma separated list | select define properties here in a comma separated list
In this command I am retrieving the name, samaccountname and userprincipalname properties for all users in the User Accounts OU, and presenting them in a table format. At the end I am piping the result to a text file. By setting searchscope to 1, I will limit the search to only that OU. If I wanted to include all of it’s child OUs, I would have just omitted the searchscope parameter.
get-aduser -searchbase ‘ou=user accounts,dc=contoso,dc=com’ -searchscope 1 -filter * -properties name, samaccountname, userprincipalname | select name, samaccountname, userprincipalname > c:\temp\users.txt
Ok, so you have been given the task to retrieve, for example, the following information for all users in your domain;
Full name, username, E-mail address, office, and all groups the user is member of. Its also a requirement that the information must be presented in table format in Microsoft Excel. This is how you can go about to achieve that.
1. Run this script to export the information to a csv file
import-module activedirectory get-aduser -searchbase ‘ou=user accounts,dc=contoso,dc=com’ -searchscope 1 -filter * -properties name, samaccountname, emailaddress, office, memberof | select name, samaccountname, emailaddress, office, @{n=’MemberOf’; e= { ( $_.memberof | % { (Get-ADObject $_).Name }) -join “,” }} | Sort-Object -Property Name | export-csv “c:\Temp\Users.csv” -notypeinformation -Encoding UTF8
This will make a csv file, with a comma separated list of all groups the user is member of. Also it will get the group name, and not the distinguished name of the group.
2. Start Microsoft Excel (I’m using Excel 2010 here)
2.1 Click on Data
2.2 Click on From Text
2.3 Browse to the CSV file you just created, and then click on Import
2.4 Choose Delimited, then click Next
2.5 Delimiters will be Comma, click on Next
2.6 Click on Finish
2.7 Data will be imported to existing work sheet, so click OK
2.8 This will give you the following output
2.9 To change it to true table format, where you can sort the information alphabetically, click on Home
2.10 Click on Format as Table, choose the type of table you want to use, and finally click OK
2.11 Now you will have the information presented in true table format
2.12 Save the workbook in .xlsx format
If you rather want to output the distinguishedname of the groups (instead of their name), you can use this script instead
import-module activedirectory
get-aduser -searchbase ‘ou=user accounts,dc=contoso,dc=com’ -searchscope 1 -filter * -properties name, samaccountname, emailaddress, office, memberof | select name, samaccountname, emailaddress, office, @{name=”MemberOf”;expression={$_.memberof -join “;”}} | export-csv “c:\temp\users2.csv” -notypeinformation -Encoding UTF8
The separator for each GroupDN will then be a semicolon.
Pingback: Exporting multivalued attributes with Export-CSV cmdlet » ShabazTech
Hi there, trying the command:
get-aduser -searchscope 1 -filter * -properties name, samaccountname, emailaddress, office, memberof | select name, samaccountname, emailaddress, office, @{n=’MemberOf’; e= { ( $_.memberof | % { (Get-ADObject $_).Name }) -join “,” }} | Sort-Object -Property Name
Gives only this output:
name : LDAPQUERY
samaccountname : ldapquery
emailaddress :
office :
MemberOf :
Any ideas why I’m not getting richer output?
Cheers.
Tom
whoops sorry I got it, just removed the “-searchscope 1” and it worked. Beaaauty! Thanks
The clarity in your post is simply excellent and i could assume you are an expert on this subject.
Well with your permission allow me to grab your feed to keep
updated with forthcoming post. Thanks a million and please
carry on the enjoyable work.