Using Excel 2016 to query Active Directory (AD) directly is my personal favorite Get & Transform feature. Excel 2010 and Excel 2013 users can download the free Microsoft Power Query plug-in for Excel.

Ruben Zimmermann

Ruben is an infrastructure specialist who specializes in Active Directory, public key infrastructure (PKI), and System Center Operations Manager. He automates in VBS, PowerShell and C#. Ruben lives in Suzhou, China, and you can follow him on Twitter @Ruben8Z.

After years of creating Visual Basic and PowerShell scripts, I finally found a way to coach IT users to get AD data themselves. Excel stores all steps performed to extract, transform, and load the information as an M query within the workbook. You can also select an option to refresh the data when opening it or on a schedule.

See two examples below showing Get & Transform features and what you can do with the information.

Example 1: Extract user information ^

In the first example we extract the first name, last name, user ID, telephone number, division, city, last logon, and employment date. Furthermore, we set only enabled users from two cities as criteria. We also set the employment date as equal to the creation date of the user account.

Excel table showing specific up to date user information

Excel table showing specific up to date user information

To avoid high memory consumption and long durations, we split all the steps into smaller pieces.

Selecting only columns that contain requested information

Open a new Excel workbook and navigate to the Data ribbon. In the New Query drop-down menu, point to From Other Sources and select From Active Directory.

Selecting From Active Directory

Selecting From Active Directory

In the next window, confirm or change the current logged-on domain by clicking OK.

In the Navigator window, type in user to specify the concrete object class and proceed by clicking Edit.

Navigate to the user object class

Navigate to the user object class

Within the Query Editor, first select displayName, user, organizationalPerson, person, top, and securityPrincipal. Then click on Remove Other Columns.

Reduce the query to required columns only

Reduce the query to required columns only

Finish the first query by giving it a name (such as qry_AD_Usr_Raw) and clicking Close & Load.

Confirm and close after naming the query

Confirm and close after naming the query

Expand columns to choose individual attributes

To proceed with the data based on the first query, qry_AD_Usr_Raw, create a Reference to it.

Referencing the first query

Referencing the first query

In the Query Editor, start with the user column and clear the defaults by unchecking (Select All Columns) and Use the original column name as prefix.

Clearing defaults in the column context window

Clearing defaults in the column context window

Proceed by selecting the attributes givenName, lastLogonTimestamp, and userAccountControl.

Select the necessary attributes in the column context window

Select the necessary attributes in the column context window

Proceed by filtering other columns as follows:

  • L and company from organizationalPerson
  • sn and telephoneNumber from person
  • whencreated from top
  • SamAccountName from securityPrincipal

Finalize the second query by giving it a proper name (such as qry_AD_Usr_Expand) and clicking Close & Load.

Filtering columns to show only attributes matching certain criteria

Based on qry_AD_user_Expand, create a reference. Within the Query Editor, start by limiting it to enabled users (User Account Control = 512).

Set a number filter on the userAccountControl column

Set a number filter on the userAccountControl column

In case city names are not consistently in proper case, ensure this by fixing the Format of the l column.

Capitalize Each Word to harmonize names

Capitalize Each Word to harmonize names

Use the check boxes to limit the cities to those of your interest, such as Suzhou and Shanghai.

Select the desired cities in the column context

Select the desired cities in the column context

If Excel does not interpret the logonTimeStamp correctly, fix it by changing it to the correct data type (Date/Time).

Correct data type for logonTimeStamp

Correct data type for logonTimeStamp

You can arrange columns in a different order simply by dragging and dropping them.

Reorder columns via drag and drop

Reorder columns via drag and drop

As we don't need the displayName and userAccountControl columns, Remove them from the query.

Remove the DisplayName and userAccountControl columns

Remove the DisplayName and userAccountControl columns

Sort the city in Ascending order by clicking the option in the l column.

Apply ascending sorting for the selected column

Apply ascending sorting for the selected column

Omit records with empty telephone numbers by unchecking (null) values.

Deselecting (null) values for the telephoneNumber

Deselecting (null) values for the telephoneNumber

Last, change the query name to qry_AD_Usr_ActiveChina, and load the result back into the workbook.

Make the result more user-friendly

Depending on the audience, you may need to make the results a bit easier to understand. To do so, create a reference for qry_AD_Usr_ActiveChina.

Perform the following transformations:

  • Rename the columns by double-clicking givenName to First Name, sn to Last Name, l to City, company to Division, lastLogonTimestamp to Last Logon, and whenCreated to Employment Date.
  • Change the Employment Date data type from Date/Time/Timezone to Date.
  • Rename the query to qry_AD_Usr_ActiveChina_Friendly and click on Close & Load.

On Excel's Design ribbon, choose the table styles to change from the defaults.

Choose table style to change defaults

Choose table style to change defaults

As the last step, configure automatic data refreshing so it automatically updates information upon opening the workbook.

Refresh data when opening the file

Refresh data when opening the file

Example 2: Extract computer information ^

In the second example, we extract computer object information. Queried attributes are cn, operatingSystem, operatingSystemServicePack, operatingSystemVersion, and whenCreated.

Set a filter to include only server operating systems (OSes).

Server names here derive from a name convention where the third to fifth characters stand for a site code.

For example: XZLINVM123 (LIN is the site code).

The query result aggregated in a pivot table will show the number of servers created each year aligned with the three-character site code they belong to.

Excel pivot table aggregating OS versions and computer object creation date

A diagram as a second aggregation will show the distribution of OS versions in use.

Excel pivot chart visualizing the distribution of OS versions

Excel pivot chart visualizing the distribution of OS versions

Selecting only columns that contain requested information

Open a new Excel workbook and navigate to the Data ribbon. In the New Query drop-down menu, point to From Other Sources and select From Active Directory.

In the next window, confirm or change the current logged-on domain by clicking OK.

Type or select the computer class in the Navigator window and continue by clicking Edit.

Select the computer class in the Navigator window

Select the computer class in the Navigator window

Keep the columns displayName, computer, and top. Rename the query (e.g., qry_AD_computer_raw) and confirm by clicking Close & Load.

Name the query properly and close it

Name the query properly and close it

Create a reference based on qry_AD_computer_raw.

Expand columns to choose individual attributes

In the Query Editor, expand the computer column. First uncheck (Select All Columns) and Use original column name as prefix.

Clear defaults in computer column properties

Clear defaults in computer column properties

Stay in the computer column and select the attributes cn, operatingSystem, operatingSystemServicePack, and operatingSystemVersion. Confirm by clicking OK.

In the top column, limit the selection to the whenCreated attribute.

Rename the query to qry_AD_computer_filtered and click on Close & Load.

Create a new query referencing qry_AD_computer_filtered.

Filtering columns to show only attributes matching certain criteria

Back in the Query Editor, filter the operatingSystem column so it shows only server OSes. Use the Text Filter Contains… for this.

Set a text filter on the operatingSystem column

Set a text filter on the operatingSystem column

Type Server in the text box and confirm by clicking OK.

Specify and apply filter

Specify and apply filter

Transform the existing column and remove the unneeded one

Stay in the current query and choose the Transform ribbon. Select the whenCreated column and pick Year in the Date drop-down menu.

Transform whenCreated date to Year

Transform whenCreated date to Year

Remove the displayName column.

Adding new columns to extend current possibilities

Change to the Add Column ribbon and click on Custom Column. Type in Site as a new column name.

Add a custom column for site information

Add a custom column for site information

Double-click on the cn column and confirm by clicking OK.

Change to the Transform ribbon, select the Site column, and choose Range in the Extract drop-down menu.

Extract a Range from the Site column

Extract a Range from the Site column

Choose 2 for Starting Index and 3 for Number of Characters. Confirm by clicking OK.

Rename the query to qry_AD_computer_Servers and click on Close & Load.

Aggregate information with a pivot table

In Excel, change to the Insert ribbon, place the cursor in the table, and click on Pivot Table.

Insert a pivot table

Insert a pivot table

Confirm the default settings in the Create PivotTable dialog.

In the pivot table field selector window, drag operatingSystem and Site into Rows, cn into Values, and whenCreated into the Columns section.

Drag columns into the field areas

Drag columns into the field areas

On the Design ribbon, click on Subtotals and choose Do Not Show Subtotals.

To improve visibility, pick one of the PivotTable Styles and check Banded Rows and Banded Columns.

Check PivotTable Style Options and Styles

Check PivotTable Style Options and Styles

Aggregate information with a pivot chart

In Excel, change to the Insert ribbon, place the cursor in the table, and click on PivotChart.

Click PivotChart in the Insert ribbon

Click PivotChart in the Insert ribbon

Confirm the default settings in the Create PivotChart dialog.

Pull cn into Values and operatingSystem into Axis.

Change the chart type by selecting it and clicking on Change Chart Type in the Design ribbon.

Change Chart Type of the selected bar diagram

Change Chart Type of the selected bar diagram

Choose Pie (for example) and Confirm the window.

Are you an IT pro? Apply for membership!

Your question was not answered? Ask in the forum!

3+

Users who have LIKED this post:

  • avatar
Share
17 Comments
  1. Ken Piper 2 years ago

    I'm trying to add Groups/Group Membership to the initial User Query, but I'm not finding the "MemberOf" attribute in User.  Is there a way to extract that info, maybe by joining it to the Group Object Class somehow (that one seems a bit funky too)?

    1+

  2. Daniel 2 years ago

    I have Excel 2016 but the 'From Active Directory' command isn't available under Get & Transform. I am not sure why now. It's not under Data. When I try to add it from the command list under Get & Transform, the command is not available to add.

    1+

  3. Anthony Yates 2 years ago

    Was trying to work out why these extra Data Source such as Active Directory were missing for me too. Despite some of the wording in Microsoft's  blogs about how Power Query is now built into Excel 2016 its not actually available to all editions. Check the bottom of the following page: -

    https://blogs.office.com/en-us/2015/09/18/new-ways-to-get-the-excel-business-analytics-features-you-need/

    So basically if you have Excel that comes with Office 2016 Standard Edition, you don't get access to these extra data sources. You need Office 365 subscription version of Office, of Office 2016 Pro/ProPlus or you need to buy the Standalone edition of Excel 2016.

     

    2+

    Users who have LIKED this comment:

    • avatar
    • Author
      Ruben Zimmermann 2 years ago

      Thanks Anthony !

      I feel sad about restrictions. - As a work-around you could use Excel 2010. The plugin works here. I had Excel 2010 and Excel 2016 running parallel on my computer without problems.

      1+

  4. Ingo D. 2 years ago

    Very good article! There's little information about this powerful way to create reports out there. One thing I was not able to obtain was the lastLogonTimestamp for computer accounts. Anybody knows how to get it this way?

    1+

  5. Author
    Ruben Zimmermann 2 years ago

    Hi Ingo,

    happy it helps. - You can get this information if you also keep the 'User' column.

    ...
    Type or select the computer class in the Navigator window and continue by clicking Edit.
    Select the computer class in the Navigator window
    ..
    Select the computer class in the Navigator window
    ...
    Keep the columns displayName, user, computer, and top. Rename the query (e.g., qry_AD_computer_raw) and confirm by clicking Close & Load.
    ...

    5+

    Users who have LIKED this comment:

    • avatar
  6. Javier 8 months ago

    Hello

    Is there a way to get historic logon logoff information for a User/Users

    Thanks

    0

  7. Author
    Ruben Zimmermann 8 months ago

    Hi Javier,

    Active Directory only stores the last logon information. If you need historical information I suggest to look into audit solutions like NetWrix or ManageEngine.

    Ruben

    0

  8. Javier 8 months ago

    Thank you for your fast reply ruben

    0

  9. Anthony 8 months ago

    Hello,
    Hopefully I'm not posting in some dated old thread....Here's my scenario: I can successfully perform all the steps you listed above for an A/D query into excel. However, I can't seem to then SHARE the worksheet so that multiple users can edit simultaneously. Is this by design?

    0

    • Author
      Ruben Zimmermann 8 months ago

      Hi Anthony,

      as far I know co-authoring works only if a the Excel file is stored on SharePoint (Online) or in OneDrive. - Perhaps other document management systems also offer that functionality, but you need a 'server' in the background.

      The 'SHARE' button in my Excel works in O365 and gives the possibility to select users from AzureAD or to add externals via E-Mail addresses. The recipient will receive a link to the document which is either in OneDrive or on a SharePoint site.

      Ruben

      0

  10. Fernando 7 months ago

    This article is exactly what I was looking for, and really solved my problem, Congrats Ruben on this great piece of information!!! I have one question though; I have the names of only a few people, and I want to retrieve email and telephone, but the AD is almost 8000 people, so the query takes some time, so is there a way to tell the first query that I only want the info of the names I already have in my excel list? Again, congrats!

    0

  11. Author
    Ruben Zimmermann 7 months ago

    Hi Fernando,

     

    glad you liked it.  🙂

    You can speed up the query if you limit the information you need in the first step. Go from column to column, select the attributes you need and remove the ones you don't need. You directory size is not small but also not too much for Excel 😉

    I don't know how to filter for specific users upfront. 

    With query merging you can combine your AD query and your list  ( but you need to create table first from it and then also query that one ). You can specify then which of selection you need. E.g. if you want to have a Left-outer-join or a union-join or ...

     

    Hope that helped

     

    Ruben

    0

  12. Shya 6 months ago

    Hi,

    Very good article. I tried and it worked, but still I can't get the reporting manager, or I can't find the table related.
    Please could you help me.

     

    0

  13. Ehtisham 2 weeks ago

    Hi Ruben,

    Great article and very helpful. I have just one question though. I just noticed one of the groups in not showing all of the user objects. In group there are about 150 users and Excel data is showing 130. Is there a way to re-sync or force to get all of the users.

    Would really appreciate for your help.

    0

Leave a reply

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

*

© 4sysops 2006 - 2019

CONTACT US

Please ask IT administration questions in the forums. Any other messages are welcome.

Sending

Log in with your credentials

or    

Forgot your details?

Create Account