Engine Works Blog

Under the hood of Alteryx: tips, tricks and how-tos.
15 - Aurora
15 - Aurora

I’m asking for a friend: what if you administer an Alteryx gallery and wanted to verify that all macros are being correctly setup? Where would you even start?

 

One solution would be to leverage the Alteryx API that comes with the Alteryx Gallery out of the box. I’ve used the regular API before via Excel: https://community.alteryx.com/t5/Alteryx-Server-Knowledge-Base/Excel-to-Alteryx-API/ta-p/85766; And Alteryx now includes an admin API (as of version 11.3) that could download workflows. My biggest problem with using Excel is that it’s not setup to handle this particular API job. I need to submit one API request to get a list of all the workflows, parse and cleanse the data, and then submit an API request for each app from the first response in order to download the yxzp. I’ve emphasized a couple catchphrases which pushed me to think that Alteryx itself would be best suited for this job. I decided to build an Alteryx API macro so that I can reuse this for various projects. To give credit where it’s due, my API macro started from a post by @Kanderson.

 

The Setup

 

I’ve attached my workflow (API workflow example) which a gallery admin can use to grab a list of all the workflows and then download the first couple to the temp directory.

 

 workflow1.png

It’s a working proof of concept that you can hopefully expand for your use case. In order for it to work, you would need to update the user constant GalleryBase URL with your gallery’s base URL, something like:

 

https://servername.domain.com

 

You would need to setup the macro by inserting your API key and secret found on your gallery’s settings page (see my article above for more details).

 

questions.png

 

constants.png

 

In the workflow, I’ve also included a list of all the available endpoints so you can further explore the API. Note that the Regular API and Admin API use a different set of keys/secrets.

 

TYPE

GET_POST

ENDPOINT

DESCRIPTION

ADMIN

GET

/gallery/api/admin/v1/insights/

Finds insights in a Gallery

ADMIN

GET

/gallery/api/admin/v1/serverdataconnections/

Returns data connections created in a private Gallery.

ADMIN

GET

/gallery/api/admin/v1/systemdataconnections/

Returns system data connections created on the server where Alteryx Server is installed

ADMIN

GET

/gallery/api/admin/v1/workflows/

Finds workflows in a Gallery.

ADMIN

GET

/gallery/api/admin/v1/workflows/jobs/

Returns the last run job and its current state for workflows.

ADMIN

GET

/gallery/api/admin/v1/{appId}/package/

Returns the app that was requested.

REGULAR

GET

/gallery/api/v1/workflows/subscription/

Finds workflows in a subscription.

REGULAR

GET

/gallery/api/v1/workflows/{appId}/questions/

Returns the questions for the given Alteryx Analtytics App.

REGULAR

POST

/gallery/api/v1/workflows/{appId}/jobs/

Queue an app execution job. Returns the ID of the job.

REGULAR

GET

/gallery/api/v1/workflows/{appId}/jobs/

Returns the jobs for the given Alteryx Analtytics App.

REGULAR

GET

/gallery/api/v1/jobs/{jobId}/

Retrieves the job and its current state.

REGULAR

GET

/gallery/api/v1/jobs/{jobId}/output/{outputId}/

Returns output for a given job.

REGULAR

GET

/gallery/api/v1/{appId}/package/

Returns the app that was requested.

 

The rest of the article is devoted to the inner workings of the macro.

 

The API Macro

 

workflow2.png

 

I’ve included a comment by every tool to help guide the user. Here, I will go through what’s happening step by step. I think it’s most instructive if we work backwards. I’ll be using the admin workflows endpoint throughout my example.

 

The last tool in my macro is the macro output. This tool sends the data back from the macro to the workflow.

 

Preceding the macro output is a select tool.

 

select.png

 

This tool is performing 2 functions. First, the select tool is limiting the number of fields that make it to the macro output. I’m only keeping the URL, The DownloadData and DownloadHeaders, and any unknown fields. The unknown fields would be any other data that comes into the macro. Second, I’ve connected an action tool to my select tool. The action tool is connected to the macro input, and I’m using the “Update Select with Reverse field map (Default)” option.

 

action.png

 

To explain what it’s doing, we have to look at how the macro input is configured. It has the “show field Map” option checked, and my template input has a single field called “URL”. When I placed the macro into my workflow, it has me select which field should be used as the field called  “URL”. I’ve chosen “EndpointURL”. This means my macro input will rename the incoming field “EndpointURL” as “URL” for use in the macro. Any other fields will flow through unchanged. In order to change “URL” back to “EndpointURL”, we use the action tool with the reverse field map connected to a select tool.

 

Right before the select tool is the download tool. Here is how the download tool is setup:

 

download.png

 

The download tool is requesting the URL “Full_Request_URL” and outputting the data to a string. Here is an example of the Full_Request_URL that we’re sending to the download tool:

 

https://server.domain.com/gallery/api/admin/v1/workflows/?oauth_consumer_key=abc123&oauth_nonce=000001&oauth_signature_method=HMAC-SHA1&oauth_timestamp=1540927739&oauth_version=1.0&oauth_signature=rTM3zMADEUPSIGNATUREaWSVXIeUnyGA=

 

Here is a more detailed explanation for each part going left to right:

 

https://server.domain.com/gallery/api/admin/v1/workflows/ This is the GalleryBaseURL and Endpoint1 user constants provided in the workflow.

 

? This is the start of the query string. All of our oauth parameters constitute the query string.

 

oauth_consumer_key=abc123 This is the API key that the user enters in the workflow. See my excel API article referenced above for more info on where to get the key from.

 

&oauth_nonce=000001 This is just a random string. Per the oauth instructions “The Consumer SHALL then generate a Nonce value that is unique for all requests with that timestamp. A nonce is a random string, uniquely generated for each request. The nonce allows the Service Provider to verify that a request has never been made before and helps prevent replay attacks when requests are made over a non-secure channel”.  I just used a recordID tool for this.

 

&oauth_signature_method=HMAC-SHA1 This is the hashing methodology that the Alteryx API employs.

 

&oauth_timestamp=1540927739 This is Unix-Time. One possible issue is that you need it to be UTC/GMT. So if I were to use alteryx itself to calculate this, it would be for my timezone (not UTC/GMT).  One simple way I’ve found to get the current UTC time is to actually make a dummy request to the server. I’m trying to connect to the api endpoint, but I’m not sending back any of the oauth_parameters. It correctly rejects the connection, but it sends back the UTC/GMT as well.

 

workflow3.png

 

We get back the following info in the header:

 

HTTP/1.1 405 Method Not Allowed
Allow: OPTIONS, GET
Content-Length: 0
Content-Type: text/html; charset=UTF-8
Access-Control-Allow-Origin: *
Access-Control-Allow-Methods: GET, POST, PUT, DELETE, OPTIONS
Access-Control-Allow-Headers: Content-Type, Accept, Authorization
Access-Control-Max-Age: 1728000
Date: Tue, 30 Oct 2018 19:56:58 GMT

 

Note that the last line gives us the time we need! We append this info to our main stream and then massage this into the Unix Time like so:

 

formula.png

 

&oauth_version=1.0 This is an optional parameter specifying the authorization version.

 

&oauth_signature=rTM3zMADEUPSIGNATUREaWSVXIeUnyA= The best has been saved for last here. There are so many little things that go into building the signature that if you don’t have it just right, the API call will fail. The signature itself is coming from the HMAC Encrypt macro.

 

input.png

 

output.png

This macro uses the R tool and thus requires the predictive tools be installed. There’s a lot going on in this tool. We’ve specified the sha1 hashing algorithm. On the output options, we’ve selected for it to be base64 encoded, but we DO NOT want it to Percent encode our entire string. As shown below, we’ll percent encode the pieces that need encoded separately. The Key in the HMAC macro is the API secret (not to be confused with the API key) WITH AN “&” at the end. So what is the Signature_Base_String that we’re setting as the string? Here is our example string:

 

GET&https%3A%2F%2Fserver.domain.com%2Fgallery%2Fapi%2Fadmin%2Fv1%2Fworkflows%2F&oauth_consumer_key%3Dabc123%26oauth_nonce%3D000001%26oauth_signature_method%3DHMAC-SHA1%26oauth_timestamp%3D1540946480%26oauth_version%3D1.0

 

There are 3 parts, separated by an &.

 

1. GET or POST (all caps). I’ve included this as a dropdown so you can toggle between the 2. The only POST statement is when you want to start a workflow.

2. Percent encoded URL. We get the original Base URL + Endpoint from the workflow and feed it to the macro via the macro input. Then we use the Percent encoder macro to encode our URL per the Oauth 1.0 instructions.

 

percent.png

 

3. Percent encoded oauth parameters. We can take the 5 oauth_paramaters string from the full URL above (excluding the oauth_signature) and actually use Alteryx’s URLEncode like so:

 

formula2.png

 

We can use the URLEncode because we really just need to convert a few symbols (&=) and then this formula will take care of it. The percentile encoding was needed above for the url because it also needs to convert symbols like : and / which the URLEncode formula doesn’t change. Also note that here in the signature generation process, the oauth_parameters must be in alphabetical order. I’ve ordered it correctly in the field “Parameter_Key_Value".

 

Notice the similarities/differences between the oath_signature and the Full_Request_URL.

 

  1. The oauth_signature adds a GET& or POST& at the beginning.
  2. The oauth_signature percentile encodes the URL and oauth_parameters. The Full_Request_URL doesn’t encode these items.
  3. The oauth_signature puts an & after the URL whereas the Full_Request_URL uses an ?.
  4. The Full_Request_URL adds the base64 encoded and HMAC-SHA1 encrypted oauth_signature to the end.
  5. For what it’s worth, the oauth_signature requires the oauth_parameters be in alphabetical order while the Full_Request_URL would not have this same requirement.

 

What’s next?

 

There’s always room for improvement. Here are the top things on my list currently.

 

  1. It doesn’t accept the query string that would be necessary if you’re going to use the app endpoint and POST to start a workflow that requires user input. You would just need to modify the download tool and build a nice interface to grab the data from the workflow.
  2. Sending an invalid request to the server to get the UTC/GMT time feels a little quirky. This method was much better than any of my previous attempts, but I still feel like there has to be a better way.
  3. The hmac macro relies on the predictive tools being installed. It may be worth investigating other alternatives for those who don’t/can’t install the predictive tools.
Digan Ace.png
Patrick Digan
Actuary

Digan is an actuary for an insurance company in the Midwest. He's been an Alteryx customer since January 2015. He started the Indianapolis User Group in 2016. He finds a way to use batch macros in 98% of the workflows he creates. He still holds a grudge against the Detour tool (don't ask) and refuses to use them in any workflow. He is a layout snob, not associating with those who aren't in camp #vertical. Follow him on Twitter @PatrickDigan

Comments
Alteryx Alumni (Retired)

Really nice explanation @patrick_digan!

Alteryx Certified Partner

Double mega stars for this (if I could).  Well done, @patrick_digan!!

15 - Aurora
15 - Aurora

2 exciting updates:

1) I've converted my original macro to an html macro. It looks a little nicer and would allow for more flexibility for any future development. I've placed it here in the public gallery.

Capture.PNG

2) I had an ah-ha moment when I realized that Alteryx has a datetimetoUTC function! The macro no longer sends a dummy request to the server just to get the time. The Alteryx Designer is capable of converting the current time to UTC (and I had no idea!). This was 1 of the improvements I wanted to make. Here is what the macro now looks like:

Capture.PNG

 

8 - Asteroid

Hi @patrick_digan Thank you so much for building this! Magic 🙂

Alteryx
Alteryx

Hi @patrick_digan,

 

If it helps, or is of interest, OAuth info can be passed in on the API request's Authorization header without having to URL encode. This has been available since 18.4.

 

EX:

Authorization: OAuth oauth_consumer_key="8D61F0AC3DFB939fa1e0d2c0b058cf14c5e5f08a85ffd7f8e2fc49f", oauth_nonce="fsZDMPnJGLsFIKBD0hzBAFSgeQkXzc8K", oauth_signature="%2Fxtf5FYcwHXZbgz624tzhzjZ2iE%3D", oauth_signature_method="HMAC-SHA1", oauth_timestamp="1542143917", oauth_version="1.0"

 

16 - Nebula
16 - Nebula

Thank you for doing this post @patrick_digan  - very much needed and well done!

10 - Fireball

@patrick_digan 

 

Thanks for sharing! I am hoping to dig into this, but the macro looks like its's missing when I import the newest version you posted. Any idea where I went wrong?

 

Capture.PNG

15 - Aurora
15 - Aurora

@Greg_Murray  I would save the .yxzp (API Runner Macro - Example and .YXI Download) as a .zip and extract the files using standard right click options. Then you should have a folder called yxi installer which includes the Alteryx API Runner.yxi. Double clicking that should prompt you to install the macro.

10 - Fireball

@patrick_digan Awesome! That worked, thanks.

7 - Meteor

Great work!  

 

Any chance of a follow up for passing parameters?  I am surprised that I havent been able to find one example of this anywhere.  

 

Thanks Again

 

 

Andrew

Alteryx Certified Partner

@patrick_digan 

 

Hey, great work! This looks very promising but I'm getting on two different servers unauthorised error messages (401) when using the Admin API. I am able to make successful request in the interactive documentation on the server and I've been able to use the API in JavaScript / NodeJS. But your custom HTML tool keeps on giving me the 401 error.

 

Are there any things I am missing - I'm providing the URL + the endpoint followed by a forward slash (so workflows/) and provided the correct Admin API keys? 

 

Has anyone been able to run this HTML macro? I've also tried changing the underlying macro but same error message.

15 - Aurora
15 - Aurora

@andre347 Just going through all my emails from last week. 

 

API errors are loads of fun to track down 🙂

 

I don't have any great insights, other than just basic checks:

 

1) Are you using the latest version of the macro? 

2) Are you using the right key and secret? If it's the workflows endpoint, that would be the admin key and secret (which is different than your regular key and secret). I've mixed my keys and secrets before.

Alteryx Certified Partner

@patrick_digan Got it working! Had to remove the 443 (Port number) in the URL and then it worked. Thanks for your help.

7 - Meteor

Ive managed to develop a solution which will do the following:  

 

Execute a job passing in the required parameters from an Excel workbook - this transforms the questions into the correct JSON object and posts to the API.  

I have then created a second iterative macro to loop until the job completes.

I have also created a test harness to execute SQL tests to validate data and return pass or fails.

 

If anyone's interested I can post the solution.

 

Cheers

 

Andrew

8 - Asteroid

@patrick_digan @SeanAdams 

 

Hoping the community might be able to help me here:

 

I can connect to our internal Alteryx Gallery DEV Server via API using the above tools fine - Am using it to download workflows, and extract lists of users. 

 

I'm using the string Endpoint which combines the correct endpoint and the base address for our DEV gallery - https://<.....com>/gallery/api/admin/v1/workflows/

and passing in the Admin API key and Secret. 

 

However, replicating this for UAT / PRD environments isn't working for me.   

 

I am currently Curator in all three environments so can use the Admin API key and secrets for each environment, but wondering do i need admin on the actual Server box as well? That seems to be the only difference - unless our UAT / PRD boxes block any users calling it via API?

 

Hoping someone can help! 

Thanks in advance.

16 - Nebula
16 - Nebula

Hey @CiaranA ,

 

it may be that API access is turned off on your UAT / prod boxes - you can check this under settings / Keys / Admin API Enabled (yes / no).    You'll need to check that you're using the unique key & secret which are applicable for those boxes.

 

You should NOT individually need admin access to these boxes (on a windows level) - that's the reason that oAuth was used for authentication so that it doesn't rely on your Windows security level.

 

8 - Asteroid
https://......:443/gallery/api/admin/v1/workflows/

 hmm, I've enabled API Access within settings / keys / admin API - it is set to yes on both UAT / PRD. 

 

I've tried adding :443 after the URL

 

But am still getting the below message:

 

HTTP/1.1 401 Unauthorized
Content-Length: 0
Server: Microsoft-HTTPAPI/2.0
WWW-Authenticate: NTLM
Date: Fri, 20 Dec 2019 09:10:28 GMT

16 - Nebula
16 - Nebula

Hey @CiaranA  - I presume you are also using the admin key & secret for each box which is related to that box?

(the admin key and secret are different for every box)?

 

I struggled with this too - for us we had a problem with timezone, so the time component of the OAuth was not working.   We found this out by looking at the detail in the response message.

 

BTW - NTLM authentications are generally a proxy error - you may be having a networking issue.

Are you able to hit any pages on your Alteryx server using the download tool?   For example - just try to hit the main page of the gallery using the download tool to see if you can see the server from within Alteryx.   You should be able to bring down an HTML page into the downloadData field.   If you get the same error when you try to hit the main gallery page, then you have a proxy problem.

 

 

8 - Asteroid

Hi @SeanAdams 

Thanks for your response. 

 

Yes I am using a different Admin Key & Secret that corresponds to each server.

 

These are the log errors we are seeing:

 

2019-12-20 14:35:07.496963,INFO,96,AuthorizationMessageInspector,AfterReceiveRequest,8533ceeba84845aaab9788eb4c1e3da2,unauthenticated, <IP ADDRESS>,<SERVERNAME>,GET,/gallery/api/admin/v1/workflows/,,-1,CloudRequest,

2019-12-20 14:35:07.502965,ERROR,34,ErrorHandler,HandleError,8533ceeba84845aaab9788eb4c1e3da2,unauthenticated,<IP ADDRESS>,<SERVERNAME>,,GET,/gallery/api/admin/v1/workflows/,,-1,Exception caught by ErrorHandler and marshalled to client,"Alteryx.Cloud.Common.Exceptions.UnauthorizedException: The provided API Key (oauth_consumer_key) is invalid.->   at Alteryx.Cloud.Models.Presenters.UserPresenter.GetUserByApiKey(String apiKey)->   at Alteryx.Cloud.Models.Presenters.AdminApiRequestAuthenticationPresenter.ValidateSignature(String requestMethod, Uri uri, String key, String timestamp, String nonce, String signature)->   at Alteryx.Cloud.Models.Presenters.ApiRequestAuthenticationPresenter.AuthenticateApiRequest(String requestMethod, Uri uri, NameValueCollection requestparams)->   at Alteryx.Cloud.Server.AuthorizationMessageInspector.AfterReceiveRequest(Message& request, IClientChannel channel, InstanceContext instanceContext)->   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.AfterReceiveRequestCore(MessageRpc& rpc)->   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage2(MessageRpc& rpc)->   at System.ServiceModel.Dispatcher.ImmutableDispatchRuntime.ProcessMessage11(MessageRpc& rpc)->   at System.ServiceModel.Dispatcher.MessageRpc.Process(Boolean isOperationContextSet)"

2019-12-20 14:35:07.588970,INFO,13,QueueWorker,Hibernate,,,,<SERVERNAME>,,,,,,Queue worker sleeping for 1 seconds.,

 

I'm sure I'm using the correct admin api key and secret.

 

I've just tried your second suggestion of hitting the main page of the Gallery using the download tool in Designer. This was unauthorised. 

 

HTTP/1.1 401 Unauthorized
Content-Length: 0
Server: Microsoft-HTTPAPI/2.0
WWW-Authenticate: NTLM
Date: Mon, 30 Dec 2019 11:34:36 GMT

 

Are there any suggestions to get around this proxy problem?

 

Thanks again for help.  

 

6 - Meteoroid

I am experiencing this same problem.  "The provided API Key (oauth_consumer_key) is invalid"

6 - Meteoroid

When I changed my URL from http to https it fixed the authorization issue.  Should have thought of that earlier...

Labels