Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.
rpaugh
11 - Bolide

How does one automate that which cannot be automated?

 

We are in an information age.  An age where someone can monitor the security of their home and unlock their care from a mobile device; start streaming a movie at home, move to an iPad, and finish on a plane; and even blend, cleanse, and predict data with little or no data science knowledge.  And yet many, if not all of us, at some point have tried to gain access to data within our own organizations only to be met with the solution of “we can email you an Excel file”.  Really?!?  I can put money into my Starbucks account via my phone, and have Starbucks scan and recognize the payment, deduct the cost from my account and give me my bonus stars for the transaction, but the only solution you have to give me data is to email it to me?  Now is a good time to tell you something important about myself – I’m lazy.  I loathe manual, tedious, repetitive tasks.  I often joke with my coworkers that I’m going to automate everything I do so I can sit and watch YouTube all day.  In fact, were I to successfully automate myself out of a job I would consider that the single greatest accomplishment of my career.  As you can probably imagine, I was not willing to manually download and process Excel files each week.  Fortunately, I come from a software development background and was able to write code to extract attachments from Outlook emails and baked that into an SSIS package that ran weekly. 

 

Outlook Input toolOutlook Input toolFast forward a couple of years to Inspire 2017.  I was having breakfast before the opening keynote and I heard an Alteryx employee talking about using Alteryx to blend data from a file she receives from another department.  I had a hunch and asked her how she gets those files.  I stifled a literal “LOL” moment when she said she receives them via email.  I told her I had some code that might help her get to a fully automated solution.  Later in the conference I attended a session on using the Alteryx API and SDK.  I paid particularly close attention to the SDK portion because at the time I had no idea creating custom tools was even an option.  I know this is going to sound lame and cliché, but at that moment I was truly inspired to jump into code and build my own custom input tool to pull attachments out of Outlook emails.  I know, I know, too corny.  Moving on.  As a fantastic conference was coming to a close I just happened to spot one of the engineers, @JPKa,  who hosted the session on using the API and SDK at the closing reception.  I told him my idea and he pointed me in the right direction to get started.  Two weeks of personal time later I had  a solid version 1  of the tool and I shared it with my friends at Alteryx.

 

 

 

Working with the SDK

 

Outlook Input Tool ConfigurationOutlook Input Tool ConfigurationEverything I needed to get started was already installed with Alteryx Designer. I used a combination of reading through and interpreting the sample and the getting started documentation. Honestly, my first impression was confusion.  The sample project involves using an XML file containing Alteryx tool XML config information as an input to set the tool XML config information.  When it came to interpreting the code to understand how it was linking the xml config information to the input data I was often confused as to whether the xml components in question were part of the Alteryx configuration or the input tool that was providing the configuration.  Simply put, it was setting configuration from configuration. If anyone from Alteryx Product Management is reading this,  a better sample might be to pull dummy customer data from a .csv file and send it to an output stream.

 

My biggest obstacle was interpreting the sample code - figuring out how to separate the xml config as input from the xml config that was reading the input.  It makes perfect sense now, but when I was trying to understand the custom tool architecture and input file interpretation simultaneously for the first time I had some trouble.

 

 

Once I had a working tool I went back and forth with the folks at Alteryx and there were some limitations there with getting them the install and sharing attachments. I discovered I could wrap the install into a yxzp file and include instructions and a help file. JP was very helpful with identifying some of the nuances of working with the SDK and offered some suggestions on improving the tool. I needed to incorporate the "UpdateOnly" parameter because the C# code was executing every time a new tool was added to the canvas. I didn't know that the Alteryx Engine had a special feature where it runs the workflow (with no actual records other than metadata) every time a new tool is added.  

 

He also suggested I add some date filtering capability to the tool, allowing people to avoid downloading their entire inbox if they select “Inbox”.  For this I added a configuration element for the user to input a query string and included a link to the documentation to build the query.

 

 

 

Using the Outlook Input Tool

 

You may be tempted to point this sucker to your Inbox right out of the gate, and I don't know what your Inbox looks like, but if it looks anything like some of our testers, I'd advise against that. Play with your drafts folder or deleted items first.

 

You may find copying the search syntax from Outlook into the Query String helpful:

 

copy the query string from outlookcopy the query string from outlook

 

As for me, this is how I've been using the tool. In this example I filter out a specific Excel file and send it through a Dynamic Input tool to see the results. I'll leave it up to you to discover new and interesting ways to consume the files that you no longer have to manually fish out of your inboxes.

 

OutlookInputToolSample.png

 

 

giphy-downsized (2).gifBut wait, there’s more!  Act now and as a bonus you get the added message stream allowing you to perform any number of analytics on your email messages.  And if that isn’t enough, join the two streams together on Message ID for maximum analytical potential!!

 

 

 

 

What's included in the package

 

You'll find an Alteryx *.yxzp file attached to this post. When you open it in Alteryx Designer, you will see installation instructions:

 

Outlook Tools Install instructions.png

 

and a help file

 

Outlook Tools Install help file.png

 

The workflow is a single RunCommand tool that will install the Outlook Input tool. You can find my source code on Git Hub.

 

I hope you find the Outlook Input tool useful. This has been a fun and exciting project and I can't wait to see what creative ways the community finds to use this tool. Use the comments below to let me know your experience working with the tool.

 

UPDATE 8/1/2017:

A new version is available on the gallery: https://gallery.alteryx.com/#!app/Outlook-Tools-Install/597b35c2f499c716ec34a782.

 

Changes:

  • Implemented paging to prevent timeouts through the Exchange Web Services when working with large inboxes and complex filters.  Note: you still may need to adjust throttling settings on your server: https://msdn.microsoft.com/en-us/library/office/jj945066(v=exchg.150).aspx.
  • Updated the installer to remove previously installed versions of the tools so it's not cluttering up your programs list.
Comments
blyons
11 - Bolide

@rpaugh Thanks for the reply. As I said in my original question, we are using https://outlook.office365.com/EWS/Exchange.asmx, which means it is already a manual service URL, or I wouldn't have to enter the URL. And because the same workflow, which I opened from the Gallery, works perfectly on my desktop, I know there is no typo. And I copied and pasted the above URL directly from the manual service URL field.

 

I passed this on to our technology infrastructure group, who spent quite a bit of time tracking this down. Apparently the link you provided did not help because, "The commands in the article are to be run in an Exchange command shell for Exchange 2013, we are running in Office 365 with Exchange 2010 for hybrid which is being upgraded to 2016." However, in their digging, they found the cause: your tool uses TLS 1.0, which is disabled on all of our servers due to security restrictions. "TLS 1.0 is vulnerable to man-in-the-middle attacks making it non PCI compliant.  This article describes it a bit: https://www.comodo.com/e-commerce/ssl-certificates/tls-1-deprecation.php."

 

The reason it works on my desktop is that the security policy only applies to servers. And the PowerShell test on the server works because it uses TLS 1.2. Perhaps this is why Microsoft is deprecating that version of the C# API?

 

So, we are hoping that the new HTML/JS SDK version might not have this issue. Can you help us save some time by sharing whether that may be the case?

 

rpaugh
11 - Bolide

@blyons I ran a packet analyzer on both versions of the tool and it looks like the C# version is using TLSv1 and the JS version is using TLSv1.2.  Are you able to install and test the new version on your server?

JMoore
8 - Asteroid

@rpaugh I downloaded off of https://github.com/rpaugh/Alteryx-Outlook-Input and renamed the .zip and got an error that the config file was missing, tried going into it one folder lower and zipping/renaming it as well but same outcome on it. Will attempt to email it to myself this weekend.

rpaugh
11 - Bolide

@JMoore ah I see.  Downloading from GitHub nested it in another folder with the GitHub project name.  After you download the full project zip, you'll have to open it and re-zip the sub-folder content with the yxi extension:

 

Alteryx Tool Installer.png

A_Ribeiro
6 - Meteoroid

@rpaugh thanks for the prompt reply!


1) I tried your example and sucessfully saved a CSV attachment, but it did not work with .xlsm or .xlsx, which saved with proper name and extension, but did not have any information inside (1 KB). After this I read a little about Blob and binary files and found it is really not supposed to work, am I correct? Is there any workaround?

 

empty blobempty blob

2) Will make sure I relay that information to my support, they say the tool is installed at server running instance, but I keep getting the following error, which I strongly believe says server could not find the tool:

 

The job read_outlook_ews did not complete successfully and resulted in the following errors:

Error: Unable to resolve plugin HTML 'Outlook Input\Outlook InputEngine.html' (Tool Id: 1)

 

 

JMoore
8 - Asteroid

@rpaugh that worked perfectly! I will start testing over the next few weeks, interested to see where the blob discussion on attachments ends up and on PDF's. 

blyons
11 - Bolide

@rpaugh Thanks so much for checking the TLS version. I installed the latest JS beta version on our server and it works great!

 

Others who make the switch will probably want to note that the format of some fields changed significantly, specifically any DateTime field (received, sent, etc.) and Duration. Changes will need to be made to the parsing in the Alteryx workflow accordingly.

 

Thanks again for this tool. I really appreciate your continued support and the latest version of the tool.

rpaugh
11 - Bolide

@JMoore I believe I’ve figured out how to make the attachments work. I’ll get an updated version published hopefully over the weekend.

rpaugh
11 - Bolide

@JMooreand @A_Ribeiro  I think I've got the attachment issue worked out.  The content was coming in as Base64, so I switched the output from blob to V_WString in the code and leave the conversion up to you.  This allows you to output in the original format using blob convert/output:

 

Alteryx Outlook Input - Blob Output Example 2.png

 

I've updated the yxi so downloading from the gallery again will give you the latest version.

rpaugh
11 - Bolide

@A_Ribeiro to your second question: did they install the tool for all users?  Also, are you able to go into the server folders and see if you have "C:\ProgramData\Alteryx\Tools\Output Input" (or similar path depending on your structure)?

A_Ribeiro
6 - Meteoroid

@rpaugheverything works! Running on the server, filters OK, different mailbox works, downloading .xlsm to a network drive and rocking my productivity!! Thank you so much for your time and effort towards this tool! Allow me to buy you a (lot of) beers when you come to Brazil!

 

rpaugh
11 - Bolide

@A_Ribeiro I'm so excited that it's working for you!!!  Just curious, were you able to use the autodiscover feature or are you using a manual service URL?

aehrenwo
11 - Bolide

We use SSO to connect to Office 365 email.

https://outlook.office.com/owa/

 

I am having zero success connecting. 

 

Any insights or workarounds that I should be doing to get this to work- or am I out of luck? 

 

Regards,


Adam

rpaugh
11 - Bolide

@aehrenwo you should still be able to use the tool - I was able to successfully connect when I had my Office365 dev account.  You'll just have to switch your login from your regular email address to your domain formatted email address (username@domain.com).  For example, john.doe@acme.com connecting to a North American domain might switch to something like jdoe@na.acme.com.  You may have to check with IT to get your exact domain format and settings, but it should still work.

A_Ribeiro
6 - Meteoroid

@rpaugh Using manual service URL, but this is really minor, I don't mind a single bit... As far as I'm concerned this version is production ready! Thanks, and I'm serious about the beers! Cheers mate!

rpaugh
11 - Bolide

Haha, thanks @A_Ribeiro !  And honestly, your best bet is to use a manual service URL anyway because it will be faster than autodiscover.

aehrenwo
11 - Bolide
Thanks I will try that combination and will reach out to IT if I can't get it to work

Regards,

Adam Ehrenworth, CCEP

This email was written on my mobile device apologies for any typos.

Get Outlook for Android
aehrenwo
11 - Bolide

I tried a few different combinations and one, in particular, showed promise it ran for a full 3-4 minutes and then finally produced a similar error.

 

Is there a particular manual service URL I should try if we can connect to our mail server.

https://outlook.office.com/owa/

???

 

I may be complicating things by trying to use an alternative email box instead. that is the one I need the data on. 

 

Thanks- trying to solve without IT first as those tickets tend to go to a black hole. 

 

Adam

rpaugh
11 - Bolide

@aehrenwo try https://outlook.office365.com/ews/exchange.asmx with your domain credentials.

DENNISRTURNER
5 - Atom

I am having an issue with this tool.  I have it set up on a workflow and have it attached to a Mailbox and it returns information from that mailbox.  However, I do not get the whole message body in any of the fields that have body type information in them (Body, Preview, TextBody, Normalized body, etc) they all end with ...

 

Any suggestions?

 

Dennis

rpaugh
11 - Bolide

@DENNISRTURNER you're probably reaching the cell limit in the Alteryx results window.  Try putting a browse tool at the end and viewing the cell contents from there - or output to a file to see if it gets all of the data.  Let me know if you still can't see all the data.

DENNISRTURNER
5 - Atom

@rpaugh, You are correct.  Guess you can tell I am new.

rpaugh
11 - Bolide

@DENNISRTURNER haha, we were all new at some point :).

rohan_tibarewala
9 - Comet

Hi, 

 

I installed the tool, but i am getting "FindItems" error everytime i tried running the workflow. Can someone please help me in resolving this.

FYI, i was using the C# version of this tool with similar settings and it used to work perfectly. Any suggestions please ??

Outlook Input_JS error msg.JPG

rohan_tibarewala
9 - Comet

Hi,

 

I think i got the new tool (JS version) working using the manual URL service now (didnt work with autodetect i guess).

But i am still facing some issues like - 

1. Alteryx designer crashes (hangs) when i try to run the tool with 'Attachments' as one of the selected elements. (even when querying a very small data)

2. Not able to get the data from Sub-folder of shared mail box.

3. Feels like it is bit unstable compared to the earlier C# version, as sometimes the workflow runs smoothly but other times it hangs or show no results.

 

Not sure if i am using some wrong configuration settings or any issue with installation.

Any suggestions would be greatly appreciated !! 

 

Thanks,

Rohan.

 

rpaugh
11 - Bolide

@rohan_tibarewala yes, that error was an issue with autodetect.  

  1. Are you pulling big attachments, or many attachments from a small set of emails?  Also, may not be the issue but just curious, are you selecting the "Has Attachments" field in conjunction with the "Attachments" field, or just the "Attachments" field?
  2. What does your configuration look like for the shared mailbox?  If the sub-folder is under the Inbox on a shared mailbox then you would check the field for "Use Different Mailbox", enter the name of the other mailbox, select "Inbox" as the folder to search, and check the box for including sub-folders.  You may have to play around with the configuration hierarchy a bit (e.g. selecting something like MsgFolderRoot or PublicFoldersRoot for the shared mailbox and search for the sub-folder under there).  If it's a public shared folder (not necessarily associated with a mailbox) then that may not work yet - I still need to update the tool to pull in public shares.
  3. What criteria changes between a smooth run and a hanging run?  Are you able to pinpoint a particular field or folder that triggers the crash?
pgeorges
5 - Atom

Hi @rpaugh,

 

Firstly, thank you for all the great work here. I do appreciate it. 

 

Could you please let us know what you inputted in the configuration panel for "blob convert" and "Blob Output" tool?

 

Capture.PNG

Below is the output i get from your Outlook Input tool. I can't seem to figure out how to open this file.

 

Capture2.PNG

 

Thank you in advance,

 

Peter

rpaugh
11 - Bolide

@pgeorges yes, of course.  

 

Blob Convert:

Alteryx Outlook Input - Blob Convert.png

 

Blob Output:

Alteryx Outlook Input - Blob Output.png

 

Result:

Alteryx Outlook Input - Blob Output Result.png

pgeorges
5 - Atom

@rpaugh Thanks, it worked!

 

Is there an alternative solution where instead of using the "Blob Output", I can use a different tool to read .xlsx file and continue my workflow?

 

rpaugh
11 - Bolide

@pgeorges if you have something that meets one of the default encoding offered by the blob convert tool (e.g. csv file) then you can convert or use a base64 decoder from there and stream directly to the rest of your workflow.  However, anything else looks like it will have to be written to a file that Alteryx can expect.  This really isn't any different from the previous version of the tool with the exception that you now have full control over naming, location, and other manipulations of the file attachment that you didn't have before.  The good news is that you can still do this using a combination of blob output and dynamic input:

 

Alteryx Outlook Input - Blob Output Stream.png

pgeorges
5 - Atom

@rpaugh genius. Thanks for all your help!

daguilar076
5 - Atom

Hi, 

 

Does this tool work under version 2018.4? or even the newest version of alteryx?

I am having a hard time installing the tool. Although installation is successful I cannot see the tool under Connectors.

 

Any help is highly appreciated!

rpaugh
11 - Bolide

@daguilar076 What version of the tool are you installing, the yxi installer or the other one?

memo70
5 - Atom

Hi @rpaugh,

 

Is there anyway to install without running the setup msi?  Long story, but we do not have access to install executables on our pc's.  We currently have v11.5.1 installed.

 

Thanks,

 

Mark

 

rpaugh
11 - Bolide

@memo70 the only way to install without the msi is to use the newer tool built on the html/js SDK.  It's currently technically in beta but so far seems to be running well.  

asteryx
8 - Asteroid

This tool looks like it will help immensely. However, I'm getting the "There are no fields" error.

I looked up my Exchange Server version and learned that it's Outlook 365. Is that the problem? Is that supported?

Thank you.

rpaugh
11 - Bolide

@asteryx no, Office 365 is not a problem and is fully supported.  Would you mind sharing an example of your tool's configuration?  Feel free to PM me if you don't want to share that here.

asteryx
8 - Asteroid

Here's a screen shot of my config:

OutlookTool.PNG

rpaugh
11 - Bolide

@asteryx What fields are listed in the "Select Fields" list?

asteryx
8 - Asteroid

Oh - Duh.

Getting to know the tool, I guess. I didn't realize that I had to select fields and didn't understand that the message was referring to that option.

Thanks for taking the time...

mecaboot
5 - Atom
Hi rpaugh! Do you know of a way to query the Global Address List from the Exchange Server? I am hoping there is a way to pull the current list of those in the GAL, specifically the e-mail address and a few of the fields on their profile card. Thanks, Melanie
rpaugh
11 - Bolide

@mecaboot to my knowledge, no.  In order to do this you would likely have to query Active Directory directly, otherwise you're just pulling in contacts you've already added to Exchange.  If someone else has seen anything different, please correct me.

CalDowd98
8 - Asteroid
Hey @rpaugh ! I think i could get great value from using this tool like these above ! I've downloaded by im really not sure how to configure it - i have the error below popping up when it runs - any idea what this could be ? Error: OutlookInputTool (1): The request failed. The remote server returned an error: (403) Forbidden. ServiceRequestException Thank you !
rpaugh
11 - Bolide

HI @CalDowd98, I'd have to see your configuration to get a better idea, but my guess is that you're dealing with one of the following issues:

  1. Invalid credentials - make sure you're using the correct domain format of your email/username.
  2. Invalid or incorrect service address - if you're using a manual service URL, check with your IT team to ensure that you have the correct endpoint.
  3. Firewall issue - check with your IT department to make sure there are no firewall rules on either your computer or the Exchange Server that's preventing web service requests.
  4. Exchange web services restricted - check with your IT team to make sure that web services are even enabled for your Exchange server.  I did have one user who found out their IT team closed off all web service/API access to their server as a security measure so they were unable to use this tool.

Feel free to PM me a screenshot of your configuration if you do not wish to share it here.

CalDowd98
8 - Asteroid
Hi @rpaugh - thanks for getting back to me. I've checked with Mat my colleague in IT and he has said that "there is no option to set the connection to use SSL/TLS" - which is why it wont work for us. Is there a way around this ? Thanks :)
rpaugh
11 - Bolide

@CalDowd98 The tool uses SSL/TLS, but it's using TLS 1.0, which may be the problem.  Try downloading the new beta version which uses newer versions of SSL/TLS and let me know if that works.

CalDowd98
8 - Asteroid
@rpaugh I've downloaded the beta version thanks ! Im having a quick play and im getting a "find items" error ?
rpaugh
11 - Bolide

@CalDowd98 would you mind sharing your configuration settings and the full error message (if available)?

jstewart
7 - Meteor

Hello, 

 

I am having issues with the Outlook input tool due to authentication rights that we cannot change for security purposes. 

 

An Alternative would be to create an app in Azure AD to make the EWS call. I'm likely losing some information in translation but the suggested approach from IT is below. The question is how to set this up in Alteryx/ Outlook input tool?

 

Cheers, 

Jack

 

Geo-Direct - Email Updater + MFA

April 3, 2019

Register App in Azure

  1. You first need to create a new application registration in your Azure portal. Excluding the brackets.
    1. Name: EWS.GeoDirect (Can be custom name)
    2. Application type: Native (Must be set to native)
    3. Sign-on URL: auth://ewstest (Can be custom just needs to be a unique value in URL format)

 

  1. Once created, add API access in Azure to the newly registered app for “Office 365 Exchange Online”.

 

  1. Grant the appropriate permissions to Office 365 Exchange Online.

 

Configure Geo-Direct

  1. In Geo-Direct navigate to the Email Updates section.
  2. For the field: “Email Protocol” select “Office 365” from the drop down.
  3. For the “Server” field: Input the text seen below with the exact same format.

{

  "authority": "https://login.windows.net/GeoSteering.microsoft.com",

  "clientID": "123abc12-123a-123a-123a-123abc123abc",

  "clientAppUri": "auth://ews_client",

  "serverName": "https://outlook.office365.com/"

}

 

  1. The user will need to configure the values above in red with your own values.

authority: The tenant address of the newly registered app in Azure.

clientID: The Application Id from the newly registered app in Azure.

clientAppUri: We used: “auth://ews_client” however any unique value in url format can be used.

serverName: Your Office 365 email server address.

gantaanvesh
8 - Asteroid

Hi @rpaugh, I have come up with the same requirement and I followed the same steps which you have mentioned in the document. Firstly I tried to install on my local machine but I am not able to install the tool in Alteryx. 

 

Then I gave trail in my Alteryx production server somehow it got installed and added the tool in connectors pallet. but then coming to the configuration part again am facing the issues like shown below.

 

( OutlookInputTool (1) The request failed. The remote name could not be resolved: 'anvesh-9113'¶ServiceRequestException¶ at Microsoft.Exchange.WebServices.Data.ServiceRequestBase.BuildEwsHttpWebRequest_)

 

can you please help me to resolve the issue.

 

Note:  In Alteryx server currently we don't have Access for the Internet.

           In local Machine cant able to install the tool.

 

Waiting for your reply  !!

 

Thanks in advance,

Anvesh