Proactive Connection Test

[dataguyW]

We have had many issues in the last 6 months with various teams implementing firewall rules, vpn changes, server changes, etc. so I am attempting to create a proactive connection test so I can be notified when someone breaks things.   

1. Query list of data connections using Mongo innerdb.  (working)

2. Run a batch macro to pass in the user/pass/SQL needed to modify a dynamic input tool and run a very basic SQL query to test connectivity.

3. If it connects --> Report Success and move on to the next

4. If it FAILS -->  Get the log message for the failure, report it and move on to the next connection.

The query would be something that has no issues with permissions etc. and should work on any database type (i.e. for Oracle it would be SELECT sysdate from dual)

My only idea was to use the CReW macro "runner" or "conditional runner" and run the macro.  These don't allow for passing parameters or macro inputs to change the variables inside so there is an issue.  Also, they are not fully supported in the gallery.   This does report error or success though without stopping the workflow.

Any ideas?

[KGT]

If you are looking to put this on the gallery, then the CReW Runners are not the method.

Using the Gallery API means that each test will be it's own run of a workflow. And you can pass credentials to it for each run.

How I would do it. 

• Set up a workflow that does the test as an app, with the variables as inputs.
• Host this on the gallery
• Run a workflow that calls that app via the API to test each one by one and return the result.
• This will be a mix of Workflow API to create a job and Job API to return the results of that job.

That's a pretty robust way of doing it and also makes it easier to have separate "Test apps" for different types that require different connections. For example, you could have an "Oracle Test App" that your controlling workflow only sends the oracle connections to.

The only caution I can see is that this will send hundreds or thousands of jobs to the queue, although they will be quick, the "Simultaneous workflow" setting may limit the speed of the whole process running, so pick a good time.

[OllieClarke]

@dataguyW The CReW Test Macros  (http://www.chaosreignswithin.com/p/macros.html) might be useful here, you can have a folder containing workflows testing your connections, and the test runner will run them all and log the outputs of the tests.

There's a bit more info about the macros in the CReW blog, especially this one: http://www.chaosreignswithin.com/2015/03/testing-alteryx-macros-part-2-our-first.html

Hope that helps,

Ollie

[dataguyW]

This sounds like a reasonable way, but I don't have experience to date with API calls for any of this.   I will have to check the links you have included.  Thanks!

I

[KGT]

Also, look into the Gallery API macros as you may be able to repurpose some of them. There's a ton of different examples on the community, but be wary of the different versions of API. The current version is v3 and has been around for a few years now.

[dataguyW]

Was finally able to get back to this.

(batch)Created a batch macro that takes name, _id, password and substitutes to a dynamic input tool.

(batch-wrapper)Called the batch from a standard macro that queries Mongo and gets a list of connections and inputs (name,_id,pass); made sure no error on running checked in both

(calling) Created another standard workflow that calls the CreW Runner which calls (batch-wrapper) with audit log parsers as outputs for both.

A bit of massaging of the outputs and I have something that seems to do what I need.