Logic apps are one of the nicest features I've discovered so far in Azure - they allow application logic to be built very simply without really having to do 'proper coding'. The one downside is that these are only available as public endpoints (which is great for many use cases) but in my example case i wanted to run a logic app that could put some data back into an on premises database - by default this is generally not possible (unless you are in the habit of exposing your databases on the public internet directly - which in most cases i think you probably wouldn't).
The good thing is there are a few different solutions to handle this and expose internal addresses to make them usable for logic apps - I'm only going to talk about the one that i used - which in my case is the on premises data gateway.
There is more info on the 'on premises data gateway' here https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem don't be put off by the fact it says power bi - that's maybe the main use case but it also has lots of other connectivity options.
The gateway does not have to be installed on the machine you want to actually connect to - for example I will connect to an oracle db on linux but the gateway will be on a windows server - all the gateway server needs to have is connectivity to the actual server you want to reference.
If you click the download link from within there you then go through the following steps to install this (note it has to be on windows and there are some specific requirements - all mentioned in the link above).
Then we just click next (note the below comment that the server should be up all the time - otherwise your gateway will not work of course....)
Choose a location and accept the terms of use (all of which you will of course read......)
Then its installed and we need to configure it with some details - in this case you need to use an Azure AD account that can authenticate from the server you are running the gateway on (so for example it must be able to log on to portal.azure.com from the server itself with this id)
Once authenticated give it a name and choose a secret recovery key (not sure what this is actually for - need to read up on that bit :-))
If all works you'll get some nice green messages - in the example below it didn't work and its because i didn't have the correct outgoing ports enabled (nothing incoming is needed but there is a list of outgoing ones that have to work). For easy finding that list is here https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem#ports
Once it has worked you'll see this screen and we're ready to create the gateway object inside the portal
So in the portal we navigate to this service
Fill in the relevant details - note at this point the only account that seems to be able to see the 'installation' is the one you used in the earlier step to register it - so the gateway seems to have to be created as this user - even if you try with an account with owner rights on the subscription it doesn't seem to be able to find it.
So after successfully getting past that we see the summary of that new object being created.
Now we can make use of it in our logic app to try and send some data back to oracle via this new gateway. Now i found here that if i gave other users the 'owner' role on the data gateway object i just created that they can make use of the gateway themselves in the logic app - it doesn't all have to be done as that same user. It may work with a lesser grant but i didn't try that any further.
So in my logic app I'm doing a pointless random task just to illustrate the functionality - i want to insert a row into a table just showing an id number (which ended up always being 1 in my simple case) and the from address of every email that ends up in a certain folder in my o365 mailbox.
Easy right?
So lets create the pre-requisite which is a very basic table inside oracle - i create this with the very basic SQL:
SQL> create user demo identified by "verycomplexpassword!!!!";
User created.
SQL> grant connect,resource,unlimited tablespace to demo;
Grant succeeded.
SQL> conn demo
Enter password:
Connected.
SQL> create table test (id number, info varchar2(4000));
Table created.
So now we have the 'bucket' to put the data in.
OK so now lets create an 0365 initial step to fire whenever an email turns up in a folder i have called hpgsd, i have some other basic filters on that based on the options i have for this step - the main thing being that the subject line has to contain the string 'logicdemo'
So that's all fine - that will trigger when a new message comes in that matches the criteria (which is checked every 3 minutes in this case). Now i add an action to do something after this step - in this case the oracle 'insert row' feature (which is still in preview at the time of writing)
The first thing i need is the 'server' - this is in the format server:port/SERVICE_NAME - i had some trial and error to get that working - so an example one would be something like
myservername.mydomain:1521/MYDB.mydomain.com
(note that the first separator is : and the latter / - earlier versions of the driver seemed to use : twice which was very confusing for a while)
Then just include username/password and select the gateway we created earlier
And.......
Turns out we're having trouble with mashups..... (who comes up with these terms?) Now i had initially thought that maybe the gateway includes some sort of driver to connect to oracle - turns out it doesn't and the oracle client has to be installed on the gateway server - that's the reason for the error.
Now this was easier said than done - you have to pick the right oracle software - you need ODAC but make sure not to choose the xcopy versions as they do not seem to work and you get the error shown below
The one that worked for me was this one (which involved using the full oracle installer and choosing the systemwide option when prompted to update the GAC - and also needed a reboot):
this has the description "64-bit ODAC 12.2c Release 1 (12.2.0.1.0) for Windows x64"
So assuming you got past that part we can now configure the next steps of the logic app. Now the part to be wary of here is that it seems to do some datatype mapping of the objects available from the first step of the flow - so only matching datatypes are available to select as dynamic content for the field.
So in my case the first column (id) is a number - and none of the content from the first step seems to be a number - so i just ended up hardcoding 1 as the value here (in the back end i would maybe have some autonumber sequence overriding this anyway).
The second column is a long character column and there are lots of fields available to choose here - in my case i just select 'from' from the available list.
So all is looking good - the app saves without error. So now lets try running it - i click the run button at the top of the screen and let it go - this then comes back with some summary info and shows me some nice green ticks showing it worked - but also allows me to see what values etc were used - making this very useful for debugging issues.
I can then see further summary info on the overview screen for this (and previous runs)
If i then login to sqlplus i can see the data has been inserted fine (it's redacted - but trust me it worked fine :-))
So there you have it - a public logic app fetching from a public o365 mailbox and inserting the results into an on premise oracle DB and other than the messing round setting up the software/gateway the logic app contains almost no code.
Rich, liked it - a very useful scenario. And I still remember - every click counts!
ReplyDeleteBoris