- Example 1: Extract user information
- Example 2: Extract computer information
- Selecting only columns that contain requested information
- Expand columns to choose individual attributes
- Filtering columns to show only attributes matching certain criteria
- Transform the existing column and remove the unneeded one
- Adding new columns to extend current possibilities
- Aggregate information with a pivot table
- Aggregate information with a pivot chart
You can follow him on Twitter @Ruben8Z.
- Monitoring Microsoft 365 with SCOM and the NiCE Active 365 Management Pack - Tue, Feb 7 2023
- SCOM.Addons.MailIn: Monitor anything that can send email with SCOM - Mon, May 25 2020
- Display a user’s logged-on computer in Active Directory Users and Computers (ADUC) - Mon, Jan 21 2019
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
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
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
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
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
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
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
Proceed by selecting the attributes givenName, lastLogonTimestamp, and userAccountControl.
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
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
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
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
You can arrange columns in a different order simply by dragging and dropping them.
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
Sort the city in Ascending order by clicking the option in the l column.
Apply ascending sorting for the selected column
Omit records with empty telephone numbers by unchecking (null) values.
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
As the last step, configure automatic data refreshing so it automatically updates information upon opening the workbook.
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
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
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
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
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
Type Server in the text box and confirm by clicking OK.
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
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
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
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
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
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
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
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
Choose Pie (for example) and Confirm the window.
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)?
You can find it in ‘top’.
When working with groups I found the following page useful:
http://thebaretta.blogspot.de/2016/06/export-group-membership-from-active.html
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.
Hi Daniel,
I can’t give an explanation why you can’t see it. – I tried to research it and found some hints.
Please check the bottom of the first article:
https://4sysops.com/archives/excel-get-transform-connect-to-data-sources/
If you find a solution please share!
Thank you
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.
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.
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?
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.
…
Hello
Is there a way to get historic logon logoff information for a User/Users
Thanks
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
Thank you for your fast reply ruben
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?
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
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!
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
Hi Ruben,
I'm interested to know if below method is feasible and if it would reduce the load query imposes on the Active Directory
AD has too many records and we need to filter them upfront based on the usernames
Filtering rows based on usernames in excel
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 …
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.
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.
How can we query the Global Catalogue Service compared to the LDAP Directory Service?
With 2000+ records, the 'data refresh' time can be in excess of 30-40 seconds using LDAP. but GC can give the same data set within 5 or so seconds. In oldskool VBScript examples it's the difference between setting the Transport to "LDAP://" or "GC://".
Can excel do one or the other as well?
Hi Simon,
sorry for the late feedback. I cannot provide an answer to your question.
But I have a suggestion 😉
Try to limit the columns to only what you need directly from the beginning. That makes queries faster.
It is like with databases. A "SELECT * FROM Table" will take much longer than "SELECT ColumnA, ColumnB From Table".
Kind regards
Ruben
Another, related good read: https://docs.microsoft.com/en-us/windows/win32/adsi/what-makes-a-fast-query
Hi Ruben,
I would like to reduce search base like in powershell to Isolate users from a specific OU.
How can translate that search onto Get & Transform : Get-ADUSer – Filter * -searchbase ="OU=xx,OU=yy, etc" ?
Hi Ruben: Can you please provide another example of AD data get and transform – How do you get the manager and direct reports information?
I am trying to export AD data that would be used to create an org chart with User, Title, Department, Manager fields. Then either use PowerBI or Visio to create org chart.
I, too, am trying to extract manager information – would really like to end up with a table of user names (actually SamAccountName), and the user names of their managers. A user's manager can be found in Power Query Editor, by clicking the [record] link in the Manager field, then looking at that user's SamAccountName, but not sure how to transform this to return person and manager side-by-side in Excel.
Is there a similar process for importing into Access from AD?
Hi Ruben,
How can I restrict and remove ability to query Active Directory in Excel for a specific AD group? From a security perspective, allowing regular users to dump entire AD User table into a XLS not ideal at my company.