Working with data on Linux Servers File Systems and Exchange Online Mailboxes - guidance required please

[Anonymous]

I am a new user of Power Automate and have some flows working between Microsoft technologies just fine. This may sound like a crazy question but is there a way / can someone point me in the right direction to working with data sitting on on-premise RHEL Linux Servers. We have an implementation of the Enterprise Data Gateway so I have worked with other on-premise resources (Oracle DB and Windows File Systems) but does anyone have any pointers about dropping files into and collecting files from these on-premise servers back and forward from emails and SharePoint doc libraries?

Much obliged in advance ...   

[tareenmj]

Were you ever able to solve this? I'm having a similar issue and I can't find any resource online with setting up a gateway on RHEL or Linux. It seems like the standard Gateway needs Windows, I was thinking of installing a Windows VM or Windows from Docker and then installing that gateway. 

[Paulie78]

This is something I have done a lot of and the best approach really depends on your requirements. 

The obvious thing is to use SFTP to transfer files to and from Linux machine, but it is pretty rare that I do that. The two main approaches I take:

• Use Python to send requests to Power Automate via the HTTP connector. The Python script will send a JSON or XML payload to Power Automate for further processing.
• When you need to send data back into Linux, I normally create some simple web services on the Linux server (again, with Python, but does not have to be). Then you can use the on-premises gateway to communicate with those APIs you have created.

Using these techniques you can create an array of integrations between Power Platform and your Linux machines, but you don't say specifically what your aim is.

[tareenmj]

Thank you for the valuable input, it is very much appreciated. Basically, I have an on-premise RHEL instance (which has access to a SQL). I have a power automate form (which is triggered when a user submits a form) and from the user-submitted information, I want to retrieve information from my SQL instance in my power automate flow. That's where I am a bit confused. I obviously can't go with Microsoft's on-premise gateway (unless I setup a Windows VM?) since I have a Linux system. 

From what I'm getting from your answer, I would need to setup a HTTP connector and a REST API of some sort. I don't really need to communicate from Power Automate to my Linux system. The thing is my Linux system isn't publicly accessible so I was thinking that I would need a Gateway, a HTTP Connector, and a REST API. 

[Paulie78]

So there are a number of ways. Certainly having the gateway in place would be a good start. That would have to be installed on a Windows machine, ideally on the same network as the Linux server you are trying to reach.

You don't say what SQL your RHEL is running. But most of them have ODBC connectors. So as I see it you have a few options:

• Setup a simple REST API/HTTP service that you queries your SQL database and returns the data you need to Power Automate (you could do this either on Windows or your Linux Server).
• Setup a dataflow which pulls information from your on-prem DB to dataverse and then query that.

There are lots of different ways you could achieve what you need, these are just some ideas to get you started.

[tareenmj]

Man you are so insightful and I really appreciate your answers! So I am running MariaDB on my RHEL. I like Option 1 better since it would keep my data internal. So I would setup a REST API on my RHEL but how would I pass that to Power Automate? Since that REST API would only be accessible on the on-premise RHEL. Wouldn't I need to setup a on-premise gateway and then use a connector from Power Automate? I could setup an HTTP service, but I'm confused on how I proceed from there.

[Paulie78]

You would need a Windows machine (physical or virtual) on the same network as the Linux server. Then it can communicate via HTTP on behalf of Power Automate to your internal machine (via a connector). It is a shame you cannot use "HTTP Request" against a gateway as that would make things easier.

It's much easier to go the other way - i.e. send some data from the linux machine to Power Automate, as you can just put some JSON or XML together and then send it via the HTTP connector. Something I do thousands of times per day with my customers.

[tareenmj]

I see okay so to send some data from my linux, I build a rest API to output data as JSON but then I also need to build something where that rest API is available to Power Automate. I would need a HTTP connector, do I install that connector onto my Linux machine and link it to my rest API? I'm just a bit confused on how I would build it. 

[Paulie78]

I'm gonna write a blog post on this subject I think.

To go from Linux to Power Automate

You simply get some data together in XML or JSON format and then use a tool like cURL or Python to send a request into Power Automate via the trigger "When a HTTP Request is received".

Technical Example: execute a command line query on Linux to get data from your DB. This could come out in CSV and then use simple python to convert the result to JSON and send to Power Automate. 

Real world use case: Order is placed on the system that Linux is running on. This triggers the command line which sends the order details to Power Automate. Power Automate emails a customer to provide an order confirmation.

For Power Automate to communicate with Linux

You need:

• A Windows machine running the gateway.
• Something for the gateway to communicate with. Probably a HTTP server of some kind on your Linux machine. This does not have to be a big deal, for example I often use web.py to create simple interfaces into the Linux (or Unix) servers which enable me to communicate directly with the database via Power Automate or Power Apps (or anything else!).

Use Case: Database on the Linux server holds stock inventory, warehouse users are doing a stock take and need to be able to see what the current stock level is in the database via PowerApps. They can use PowerApps to communicate directly with the stock DB via the API that queries it.

So - I hope that gives you some ideas. There is quite a bit of learning to do what you want to do, but once you have got your head around it, you will be able to do all kinds of clever integrations.

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘