Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Using Alteryx to query Active Directory

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.

clipboard_image_23.png

 

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

clipboard_image_24.png

 

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

clipboard_image_25.png

 

  1. 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 >>.

clipboard_image_26.png

 

  1. 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.

clipboard_image_27.png

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.

 

  1. Click Test Connection.

clipboard_image_28.png

 

  1. 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.

clipboard_image_29.png

 

  1. 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.

clipboard_image_30.png

 

  1. Click OK to close the Data Link Properties

clipboard_image_31.png

 

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

clipboard_image_32.png

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

 

 

  1. Decide on the desired SQL for your query:
  • First, select desired attributes from the list below to output

clipboard_image_33.png

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'

 

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

clipboard_image_34.png

 

  1. 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.
    clipboard_image_35.png

 

  1. Click OK to close the Test Query Success

clipboard_image_36.png

 

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

clipboard_image_37.png

 

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

clipboard_image_38.png

 

  1. 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.

clipboard_image_39.png

 

  1. 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.

clipboard_image_40.png

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.

 

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

clipboard_image_41.png

 

  1. Run the workflow!

clipboard_image_42.png

 

  1. 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

clipboard_image_43.png

 

II. Click the under option 4 in the Alteryx Designer x64clipboard_image_44.png

 

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 Errorclipboard_image_45.png

 

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.

32 REPLIES 32
DiganP
Alteryx Alumni (Retired)

@Data_is_mymiddlename Thanks for sharing this, amazing work! I was in the process of writing one as this does come up often!

Digan
Alteryx
rvinther
5 - Atom

I have been looking for this for so long and your description was perfect and caught all the problems there could be:)

 

You happen to know a method to write back into Active Directory? I tried modifying the SQL code, but ends up with an error. So might need a different tool.

 

I made the same procedure work with a Input Tool, and found a field that i could add * to pull out all the content, i could get all the fields though...

@rvinther, I'm happy to hear the instructions were helpful.

 

Unfortunately, I don't have a method for writing back to AD. That is definitely something to take up with your local AD Admin as each installation of AD has a different configuration.

 

It is cool that using the * in the Input Tool worked for you. I had just tried Dynamic Input based on my use case. Thanks for the insight.

Whenever I was looking for fields, I opened my desired AD item from the OU folder it was located in (as opposed to using Find) and there was an "Attributes" tab which listed all the possible fields. Remember, there are typically multiple schemas in place so one item's "Attributes" might not be the same as another. It is always best to open the specific item in question, if possible.

 

Thanks!

ChrisTX
15 - Aurora

Thanks for this information.  Very helpful.

 

I had to make one small change to the FROM portion of the SELECT in step 11:

 

SELECT cn, samaccountname, mail from 'LDAP://DC=DOMAIN,DC=com'
WHERE samaccountname = 'test'

 

 

(where DOMAIN is our company domain)

@ChrisTX, thanks for the feedback. It seems like different versions of Active Directory allow for slightly different connection methods (DN versus URL).

rvinther
5 - Atom

I had to make the same change to make it work:)

patrick_digan
17 - Castor
17 - Castor

@Data_is_mymiddlename This is awesome! Thanks so much. I just wanted to document my setup in case it helps anybody else.

 

1) In the input tool, here is my connection string:

 

 

odb:Provider=ADsDSOObject;Trusted_Connection=yes;Data Source=SERVER.DOMAIN.com;Location=SERVER.DOMAIN.com;

 

 

where you would have to put your own server and Domain in. I found my server using the Set command like you mentioned. 

 

2) Here is the sql statement I went with:

 

 

SELECT cn, samaccountname, displayname, mail, telephonenumber, name, sn, givenname From 'LDAP://DC=DOMAIN, DC=COM' WHERE   
objectClass='user' AND objectCategory='Person'

 

 

where you would again have to replace the Domain with yours. I'm not sure why, but my query would error If I didn't have the where clause. 

 

 

ChrisTX
15 - Aurora

For some reason I'm only getting back 1 record when I use the SELECT statement below.  Has anyone been able to resolve this?

 

SELECT cn, samaccountname, displayname, mail, telephonenumber, name, sn, givenname From 'LDAP://DC=mnet, DC=com' WHERE objectClass='user' AND objectCategory='Person'

 

jason_scarlett
10 - Fireball

Awesome.

 

If my query results in more than 1000 rows it throws this error: "Input Data (3) Error ReadRecord: Interface ISupportErrorInfo not available".

 

If any one has any ideas on how to get around this, let me know. I can't see a nice way to recursively loop through and get 1000 rows each time .. or get the syntax to bypass the 1000 record limit (Page Size=20000 in teh connection string does nothing).

 

 

 

Labels