Engine Works

patrick_digan · ‎11-06-2018

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.

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

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

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.

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.

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:

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.

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:

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

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.

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:

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.

The oauth_signature adds a GET& or POST& at the beginning.
The oauth_signature percentile encodes the URL and oauth_parameters. The Full_Request_URL doesn’t encode these items.
The oauth_signature puts an & after the URL whereas the Full_Request_URL uses an ?.
The Full_Request_URL adds the base64 encoded and HMAC-SHA1 encrypted oauth_signature to the end.
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.

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

Engine Works

Using the Alteryx API from….Alteryx