Engine Works
Under the hood of Alteryx: tips, tricks and how-tos.
Background information
We’ve all seen and done it… Entered our username/email address and password into a login form, only to be prompted for an additional code in order to prove it’s us. Nowadays, between 2-factor authentication and email, this is a fairly standard approach for increasing the security of whatever you’re trying to access. And, like a lot of things in life, we can recreate this functionality in Alteryx! Is there any need? Well, not really. However, much like playing Snake in Alteryx, this is one of those things where I went ahead and built something anyway… just because I could! This mini project offered a nice chance to play around with chained applications (apps), as well as reporting tools, including the email functionality within Alteryx which I hadn’t previously used. For those wanting to take a closer look under the hood, I’ve attached a copy of the packaged product to this blog post (obviously cleaned of credentials etc.) and there’s also documentation within the workflows themselves, as seen in the screenshots. Therefore, I’ll just be giving an overview of what’s happening at each stage in this article, as well as some further steps you would need to consider if you were to use this in a live environment.
1. First app: Check incoming email address and generate a code if valid
Given that this whole process starts with the end user entering their email address, we obviously want to check that it is an acceptable input. To handle this, we first of all apply the LowerCase() function to both sides of our Join tool. This helps to standardise both input values, as joins are conducted on exact matches, meaning Nathan.Purvis@theinformationlab.co.uk wouldn’t join with nathan.purvis@theinformationlab.co.uk, due to the difference in capitalisation between the two records.
For this simple example, we just have a small list of hand-typed email addresses as our reference list - see below. However, in a real deployment, this would obviously be a database, google sheet, centralised Excel file, or something similar.
Now, the rest of the workflow is really based upon the Join tool output. If no match is achieved between the user-provided value and the dataset containing valid email addresses, we would then expect this to come back out of the L (left) result anchor. When this happens, the Summarize tool will obviously return a record count above 0, triggering our Message tool to produce an error message that is fed back to the user.
On the other hand, if a match is found between the user-typed value and the list of acceptable email addresses, we carry on downstream. The first step in this scenario is to create a verification code that is a) emailed out to the user, and b) used as an input for app #2 in order to check whether the code provided is correct. To get these codes, we have used the UuidCreate() function which is designed to generate a unique identifier each time the workflow is run. We also generate a timestamp at the time the workflow is run because, in this example, we want the code to eventually ‘expire’. As a result, there is another check built into our second app that ensures the time difference between the code being generated in app #1 and entered into app #2 is less than 15 minutes.
Once we have our code, we need to execute points a and b mentioned above. For b, this is simply a case of publishing the result (including the timestamp) somewhere. While this proof of concept outputs to a .yxdb, this isn’t a production ready approach which will be explained later in the section detailing considerations for live deployment.
Here’s an extra screenshot showing what will actually be pushed out of the workflow:
With regards to the email, we first use a Report Text tool to construct a standard message around the code, formatting it as necessary. The Email tool is then configured to dynamically take the email address from the data, as well as the message body from the Report Text output. For things like the subject, these are manually adjusted as necessary. In terms of actually setting up an email account connection within Alteryx, there are several articles explaining how to do so, which will be linked at the end of this blog.
… And that’s a wrap for the first application!
2. Second app: Verify user code and allow access to ‘main’ app if successful
The good thing about this app is that it’s incredibly short and simple - 4 tools to be exact.
Following on from the output (not the email) of app #1, which prints our generated verification code and timestamp, we now point our app #2 Input Data tool towards this, ready to conduct our checks that ensure the code is 1) valid and, 2) no older than 15 minutes. This is all handled in a single Formula tool, using a couple of very simple expressions as shown below:
Statement 1 handles the code validity check. Although we see a totally random string (!?&#) above, this is just acting as a dummy/placeholder value; the Action tool linked to our Text Box has been configured to replace this with whatever the user enters and so when the workflow is actually run, the check becomes [Unique ID] = <User Input>. The result of this is a boolean value of -1 or 0 for True and False respectively.
Statement 2 is very similar in the sense that we’re just conducting a True or False (boolean) check that will output either 0 or -1. The only difference here is the functions used, as we’re obviously looking at the time elapsed in this scenario. DateTimeNow() does exactly what it says on the tin - returns the current date & time, down to the second, at the time the workflow is run. Therefore, we are checking that the time difference, in minutes, between now and the [Gen_Timestamp] field - which, if we glance back to our app #1 breakdown, is the result of a DateTimeNow() function triggered at the same time as the verification code is generated - is no more than 15.
If either of the checks described above fail, then we obviously don’t want to continue on, which in this instance would load the ‘main’ application that we’re protecting with email verification. Therefore, we have reused the same approach detailed in app #1, this time linking two separate Message tools to the outcome of our aforementioned Formula tool expressions. We know that a 0 output from a boolean (True/False) check represents False and as a result, both Message tools are configured to trigger when records containing a 0 pass through them (shown below), which once again feeds back to the user.
3. What does a failed attempt look like?
We’ve conducted all of our checks, so this is mainly just to show each stage in action. First of all, if a user enters an incorrect email address, they’ll be met with the following message:
In the scenario above, I entered my email address ending in .com as opposed to .co.uk which is the correct suffix, triggering the error message. Below, you can see the result of an end-user submitting an incorrect verification code:
And here’s the final check being triggered, due to entering the code after more than 15 minutes have elapsed:
Fairly simple stuff, but a clear and descriptive error message to let people know the issue!
4. What does a successful attempt look like?
We’ve seen the errors, so now it’s time to look at what happens when a user enters the correct email address and verification code. Starting with the email address, a successful submission will trigger the email in app #1, generating an email like so:
Obviously, this can be changed, between the Report Text and Email tools, to be as flashy as you’d like (within the limitations of Alteryx), but you get the idea from the sample shown above! Once our user enters this code correctly, they’ll then be able to pass through to the final app (which has all along been the ‘main’ app that we’re protecting with email verification):
Voila!
5. Steps/considerations for going live
The likelihood of this being used in a business environment is low and the idea of this was mainly to showcase something cool that has been built with chained apps. However, if somebody did want to integrate this extra step into their app(s), then there are definitely a few steps that you’d need to take in order to make this viable. Most of these are very simple changes that haven’t been included in the example here for the sake of simplicity:
5.1 Don’t push generated outputs to a .yxdb
As demonstrated in the app #1 screenshots, if we were to output the verification code to a local file like an Alteryx database then, as long as the user knows a legitimate email address to enter, they could simply input this in order to generate the output and then open it up to reveal the code, without needing to actually access the email account. Therefore, a more secure way to approach this would be to push the code/timestamp output out to a database that the end user can’t access. As this whole process is a chained application, the code/timestamp Input in app #2 would also need to be pointed toward the same location.
5.2 Lock the workflows once finalised
With the whole point of this build centring around increased security, we obviously want to make sure these apps can’t be tampered with. Without doing so, the end user would be able to simply edit the workflows to output codes to a standard file, mess around with email settings, and so on. Therefore, before going live, the workflow owner would also need to ensure that it is locked before releasing it to other users. It’s important to note that locking a workflow does so for everyone, including the creator - more info can be found here in this great LinkedIn post from Alteryx ACE, Alex Gross. As a result, whoever is acting admin ought to keep a ‘hot’ copy of the apps that can be changed if necessary.
…And that’s a wrap! For those who’ve made it this far, I hope you enjoyed the blog and found this to be an interesting use-case. There is a cleansed, demonstration version of the app attached to this blog if you’re interested in having a play around with it! Chained apps are a powerful way to ensure that we only show relevant content to our user based on the results of the previous application. I also recently built a currency converter which leverages this functionality that can be found here. If you’d like to learn more about building/using apps and chained apps in Alteryx then here’s a handful of links to get you started:
https://help.alteryx.com/20223/designer/analytic-apps
https://help.alteryx.com/20223/designer/apps-and-macros
https://community.alteryx.com/t5/Interactive-Lessons/Chaining-Analytic-Apps/ta-p/243120
https://help.alteryx.com/20223/designer/lock-your-workflow
