Using Alteryx to query Active Directory

[Data_is_mymiddlename]

Here are my instructions for using Alteryx to query Active Directory. We use Alteryx 2019.1 and this workflow was run against a Windows Server 2008 R2 AD instance. Your mileage may vary if your configuration is different.

Note: This is my first new thread post on the community, so please let me know if I missed something or need to make changes.

I am also working on a "group membership" workflow/macro. I hope to post those instructions in the near future.

Feel free to provide feedback (even grammar!).

Thank you.

How to add an Active Directory lookup to your Alteryx workflow

1. Add the Dynamic Input tool to your workflow. It can be found under the Developer tab.

2. Select the newly added Dynamic Input tool, then click Edit in the Configuration pane to add the Active Directory server and query information.

3. In the new Alteryx Designer x64 window, click the dropdown below Connect a File or Database and select Other Databases then OleDB…

4. Select OLE DB Provider for Microsoft Directory Services from Select the data you want to connect to: section in the Provider tab in the Data Link Properties window, then click Next >>.

5. On the Connection tab, enter the name of the Active Directory server in the Data Source: and Location: textboxes in section then select the Use Windows NT Integrated security radio button.

Note: Please use a local domain control for best performance. To see the local Windows PC’s currently connected domain controller, open Command Prompt (CMD.exe) and type SET. The information will be under the LOGONSERVER value. Be sure to add your domain name to the end if SET is used to find the domain controller.

6. Click Test Connection.

7. An error message will appear, but don’t worry. Now click Yes on the Microsoft Data Link Error window to continue with a test connection.

8. If a successful message (like the one below) does not appear, return to step 4 and check that everything was entered correctly, otherwise proceed to step 9.

9. Click OK to close the Data Link Properties

10. In the new Choose Table or Specify Query window, click the SQL Editor

Note: If you close this window, you will have to restart from step 3.

11. Decide on the desired SQL for your query:

• First, select desired attributes from the list below to output

For more attributes, check with your Active Directory Administrator or the Internet.

• Next, select attributes from the list above to use for the search
• Finally, combine output attributes, Active Directory server, and search attributes to create a SQL query:

SELECT (add desired attributes separated by a comma here)

from 'LDAP://(Active Directory Server)’

where (desired attribute search) = ‘test’

Note: The search criteria is ‘test’ because the Dynamic Input tool will replace ‘test’ with the actual results from a preceding tool in the workflow.

Here is an example where name, userid, and email would be returned from a local Active Directory Server with a search based on userid:

                SELECT cn, samaccountname, mail from 'LDAP://ADServer.domain.com’

WHERE samaccountname = 'test'

Here is a second example where name, title, supervisor name, supervisor userID, job hierarchy info, employee type, and office city would be returned from a local Active Directory Server with a search based on email address:

SELECT cn, title, ReportsToName, SupvID, division, employeeType, location from 'LDAP://ADServer.domain.com’ WHERE mail = 'test'

12. Click Test Query after entering the “SQL” to test the query.

13. If a successful message (like the one below) does not appear, return to step 11 and check that everything was entered correctly, otherwise proceed to step 14.
    

14. Click OK to close the Test Query Success

15. Click OK again to now close the Choose Table or Specify Query

16. Click OK one more time to now close the Alteryx Designer x64

17. Select the Dynamic Input tool, then select the Modify SQL Query radio button, click Add, and select SQL: Update WHERE Clause to open the SQL: Update WHERE Clause configuration window.

18. In the new SQL: Update WHERE Clause window, use the Replacement Field: dropdown, to select the field that will be used to provide the attribute used for searching. If you do not see anything in the Replacement Field: dropdown, confirm that the Dynamic Input tool is connected to the appropriate previous tool which has the desired replacement field.

Note: In this example, the mail attribute is being used for the search and so the email_address field from a previous tool in the workflow will be used to populate the search.

19. Click OK after confirming all information is correct in the SQL: Update WHERE Clause

20. Run the workflow!

21. Profit!

Additional information:

To modify your SQL query after completing step 19, follow the steps below:

I. Select the Dynamic Input tool then click Edit in the Input Data Source Template section

II. Click the … under option 4 in the Alteryx Designer x64

III. An error message like the one below will appear. Do not worry as this is normal and due to the way that Alteryx works. Click OK to close the Parsing Error

IV. Follow steps 10 through 16 above to enter and test the updated SQL query.

V. Continue through steps 17 through 19 if the attribute used in the WHERE clause was changed.

[jason_scarlett]

Are you only getting yourself back? Maybe your connection is limited by who (you) makes the query request.

[Data_is_mymiddlename]

@ChrisTX, I had some issues when I tried to using that query as well:
I tried both DN ('LDAP://DC=domain, DC=com') and URL ('LDAP://domain.com') with the Input Data tool.
Only one record was returned and I received this error message:
Error: Input Data (2): Error ReadRecord: Interface ISupportErrorInfo not available
 
Based on my experience (when creating these instructions and since then), this error message was essentially a AD server timeout. I tried adjusting the timeout information in Alteryx but I would still get this message. I am not an AD admin, so I do not know if there is a process around this "timeout".

My solution was to simplify the request. For my AD environment, I was able to pull back around 700-800 items and not timeout (get that message). When I tried running a query that I knew would produce around 1000 or more results, I would get this same error message and a single record (presumably the "first" one). That is why I went with Dynamic Input as opposed to Input Data. Dynamic Input would essentially force some sort of restriction on the magnitude of the query.

This is particularly painful when trying to pull back AD group membership, so I have actually moved away from this method for group membership pulls (I still use it for single user lookups). I have created a separate methodology which uses Alteryx connecting to an Active Directory REST API. I hope to publish that document in the near future (before EOY), but as this method is more complex, it has taken me quite a while to sanitize my company information from it. If anyone is interested in that process, please let me know as that might help me prioritize its importance in my workload.

Thank you.

[ChrisTX]

@Data_is_mymiddlename I'll look forward to your post on the Active Directory REST API.

Recently I found this AD functionality in Excel:

https://support.office.com/en-us/article/get-transform-in-excel-881c63c6-37c5-4ca2-b616-59e18d75b4de

Get & Transform in Excel
Excel for Office 365 Excel 2019 Excel 2016

To create a query in Excel, use the Data tab in the ribbon, then in the Get & Transform section, choose the drop-down for New Query, then From Other Sources > From Active Directory.

Enter your Domain name. In the Navigator window, expand the tree and use the object for "user". Then click the Load button at the bottom right.

I got back over 10,000 records in less than 5 seconds.

Wish I could figure out how to expose this same data to Alteryx.

[jason_scarlett]

I haven't tested this (we are on Office 2013 32-bit arghh).

But if Excel can connect, so should Access. If Access can connect, it can act like a live connection intermediary between Alteryx and AD. We use this approach with Tableau-to-Access-to-X to bypass connectivity issues.

[Samuel_To]

Thanks for sharing !!

It is very useful.

Is it possible to use wildcard in the where cause of the query ?

What is the syntax ?

Best Regards,

Samuel

[Data_is_mymiddlename]

@Samuel_To , I was never able to successfully use wildcards as they typically brought back too much information and my connection would fail in what seemed like a timeout.

About a month after my post last year, I started to really test out using the AD Web Services (https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-server-2008-R2-and-2008/dd...) instead. This method was much more robust and had no issues with wildcards (one of the primary issues I experienced). As a result, I stopped working on the method in this post and worked on the ADWS version instead. It took a while but I was finally able to make it work by leveraging the download tool and some macro nesting to hide a batch macro so that the end user only had a single input. There was also lots of trial and error with Postman. 🙂

I hope to some day create a post for that version... maybe I'll find some time in Q4 of 2020.

Thank you.

[Samuel_To]

@Data_is_mymiddlename ,

Thanks for replying me. 

For my case, i got no records when I try to use wildcard to search the AD groups. (Run completely with no Error)

memberof = 'CN=*Admin'

Not sure the syntax is correct or not. 

Best Regards,

Samuel

[IMSHAH]

It gives me error when i try to get "memberOf". Any help is appreciated.

SELECT memberOf From 'LDAP://OU=,DC=' WHERE cn='Person'

[nbrenner]

Thank you so much for your post.  I was wondering if you could help me understand one of the steps, #18.

I have managed to finally get the correct syntax and valid attributes into the SQL Query to test successfully.  However, I do not get a field listed in the replacement field for the updating of the WHERE clause.  

What does the latter portion of your post for #18 mean, "confirm that the Dynamic Input tool is connected to the appropriate previous tool which has the desired replacement field".  I don't have another previous tool on the workflow yet.  I am attempting to pull Active Directory data down with this tool and others connecting to other domains, then do analysis on that data.

Do I have to have a previous tool/dataset available to update from in order to use the Dynamic Input Tool?

[nbrenner]

The "Error: Interface ISupportErrorInfo not available in query" error that I kept getting was driving me mad.  I thought maybe it was an incompatibility of our AD environment.  However, I eventually worked around it, by first connecting with a freeware tool called Softerra, and dumping a list of all of my attributes.  Then going through the list and removing any attributes I was trying to pull (via lists found across the webz on available AD attributes).  Eventually, after the arduous task of splicing out half of my query - and adding a few new fields, I finally stopped getting the error messages.

The TLDR here is that maybe the expected AD attributes don't actually exist in your company's environment, and it helps to have a list of what does when writing the query, else the OLE component just gives back otherwise erroneous messages that are of little help.

Now, to just figure out how to close the deal and get the updated where clause piece figured out...