Retrieving User properties from Active Directory



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 *

Retrieve User properties from AD(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

adpowershell2

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
adpowershell3

2.2 Click on From Text
adpowershell4

2.3 Browse to the CSV file you just created, and then click on Import
adpowershell5

2.4 Choose Delimited, then click Next
adpowershell6

2.5 Delimiters will be Comma, click on Next
adpowershell7

2.6 Click on Finish
adpowershell8

2.7 Data will be imported to existing work sheet, so click OK
adpowershell9

2.8 This will give you the following output
adpowershell10

2.9 To change it to true table format, where you can sort the information alphabetically, click on Home
adpowershell11

2.10 Click on Format as Table, choose the type of table you want to use, and finally click OK
adpowershell12

2.11 Now you will have the information presented in true table format
adpowershell13

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.

4 Comments

  1. Pingback: Exporting multivalued attributes with Export-CSV cmdlet » ShabazTech

  2. Tom

    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

    Reply
    1. Tom

      whoops sorry I got it, just removed the “-searchscope 1” and it worked. Beaaauty! Thanks

      Reply
  3. sağlık

    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.

    Reply

Leave a Comment

Your email address will not be published. Required fields are marked *

Captcha * Time limit is exhausted. Please reload the CAPTCHA.