Remote gardener - project overview

The purpose of this project is to remotely control a water pump anywhere in the world. My personal motivation to build this solution is to be able to water my tomato plants during a long vacation to the other side of the globe, Australia!

Requirements:

• turn the pump on and off during an arbitrary interval
• get interactive feedback from the pumping, both visual and "state"
• get alert then the soil is too dry (i.e. when I need to pump)
• secure the solution
• recover power shortage and network connectivity loss
• operate solution from any connected device

Solution:

Turn on/off

In order to control the water pump remotely, one just need to control a relay in order to switch the current on and off. To do this remotely we need a connected device that is wired up with the relay and the perfect candidate to do this was for me the Arduino chip "mxchip az3166". This chip is promoted by Microsoft and very well integrated into the azure architecture thanks to a visual code plug-in.

Microsoft mxchip az3166 quick start

Once the chip got connection to azure IoT hub a blue led will turn on!

The chip also got a small display that is very useful for debugging.

And it is compatible with BBC micro bit accessories!

All in all the perfect choice for an IoT solution hosted on the Azure platform.

To send commands to the pump I simply used a web application hosted as an Azure app service.

Visual feedback

To get visual feedback from pumping I just choose to install a web cam pointing at the water outlet and the tomato plants:

The pump state is also displayed in a web application hosted as an Azure app service. More about that later.

Moisture alert

There're a lot of fun accessories that one can connect to the "mxchip". One of these is a moisture sensor:

BBC micro bit moisture sensor

I'll use this sensor to regularly check the moisture level in the soil and if the moisture level is too low I'll send an alert to a "Remote gardener" twitter account.

Security

To secure the solution I'm relying on azure active directory. Both the web portal and the azure functions need to be secured. 

Communication between azure IoT hub and the physical device is done using standard Microsoft libraries with an access key.

Recovery

If we lose power the IoT device will just reboot then the power comes back.

But if we lose network connectivity (for instance our router temporarily goes down or lose internet connection) we need to implement a recovery mechanism that on a regular interval tries to reconnect to the router. This has to be implemented in the IoT code.

Operate

We will just build a simple website to send commands to the IoT device and to monitor it's state.

Construction

The video below show the overall construction of the system:

• Tubes
• Pump
• MXChip and electronics

Result

The solution was fun to build and has worked flawlessly during summer 2019.

The video below shows some pumping from Singapore Airport :)

Hybrid integrations with azure components - large file to database on-premise import - Part 3 - call azure data factory pipeline from logica app

In this part, we will create a logic app that'll call the ADF pipeline that we created in part 1 and we'll use the on-premise data gateway that we created in part 2.
(Recently Microsoft released connectors to create and get pipeline runs from a logic app so we don't have to use the azure ad app that we created in part 2)

You can access the previous parts here:

Part 1
Part 2

We'll create a new logic app in the resource group where our azure data factory resides (in my case it's in the hybrid integrations resource group) - (it doesn't have to be in the same resource group, it's just convenient)

• Go to the resource group
• click the + add button
• search for logic app

• fill in the details as below

• click the create button
• once the logic app is created you might get a popup window where you can choose 'go to resource' (otherwise just locate your newly created logic app and click edit)
• you should have a selection of templates to choose between. I usually start with the http request response template. (easy to pass in parameters and easy to trigger from different clients)

We first want to list files in the folder from where the Azure data factory pipeline import file data to SQL server table (see Part 1). To do this we'll use the file connector action 'list files'.

• In the logic app designer add an action below the HTTP request trigger.
• In the search field type 'file system', be patient and then the file system connector appear choose the 'list files in folder' action.

You'll then need to define your connection to your local file system. It needs to know the UNC root folder, credentials to use (don't forget to prefix the username with the domain) and the gateway to use.

Once the connection to your local file system is successfully created you just need to choose the folder where to list files. (in my case it's the same as the root folder so just a front slash)

Now it's time to trigger the Azure data factory pipeline. We'll use the Azure data factory connector to do this. Add a new action below the list file action. Then search for 'Azure data factory', be patient and then the connector actions are displayed choose the 'create a pipeline' run action.

You'll then be asked to sign in to your subscription and when to configure your action as below.

Quite self-explanatory but where do I put the pipeline parameters? As the ADF connector is quite new I think this is just a miss from the product team and it'll soon be corrected but actually, in the 'code view' of your logic app workflow, you can pass in parameters to the pipeline as is shown below.

Under the 'inputs' section of the 'create a pipeline run' JSON definition you can add a 'body' structure that contains the parameters to you ADF pipeline. Hopefully, in the future, you can just do this in the designer pane.

For now, we'll not use the output of the list files action and just use a static filename. Save the logic app and run it. If everything goes well it should trigger your ADF pipeline and import the specified on-premise file data to the on-premise SQL  server table.

Next, we'll improve this solution so it's more production like. The filename will be passed as a parameter to the Azure pipeline run action, we will handle multiple files and delete the files once successfully imported to the database.

Let's start by making our call to trigger a pipeline run more dynamic. Below the 'create a pipeline run' action add a foreach loop and configure its input to be the 'body' output of the list action. Also move the 'create pipeline run' inside the foreach loop.

We need to set the filename dynamically to the output filename we get from the list files action.

• Switch to code view (might not be needed in the near future) and replace the static FileName parameter as shown below.

We could just delete the file after the 'create a pipeline run' action but we'll not do this since we can't be sure that the import does succeed. Instead, we'll wait until the pipeline run is successful and then delete the file. To do this we need a do until loop and the 'get a pipeline run' action. We also need a variable with the pipeline run status.

• Below the HTTP request trigger add an action to initialize a new variable of type string.
• Below the 'create a pipeline run' action inside the foreach loop add an action to set the variable to an initial state (so we can handle multiple files)
• Finally, add a do until loop (configuration will follow)

• In the do until loop add an action to 'get a pipeline run'. (you can pick the runid dynamically from the 'create a pipeline run' action)

• Configure the do until loop as shown below and add a 'delay' action (10 seconds delay) and a 'set variable action' below the 'get a pipeline run' action. Set the variable to the Status field that you'll get dynamically from the 'get a pipeline run' action.

• Finally, add a 'delete file' action below the do until loop and set the file field to the 'path' variable you'll get dynamically from the 'list file' action.

Now we have a solution where we're able to import an arbitrary number of files and remove the file once successfully processed. (Some minor modifications should be done before going to production. One would need to handle the case then the import to the database does fail. I.e the do until loop times out or returns an error code. So some conditions should be added to the workflow with relevant actions taken in case of failure)

Otherwise, we could now test the happy scenario of importing two files to our database table just by triggering this logic app manually and check the logging. Below is how my final design looks like and a successful run of two imported files.

This is one simple example of how to use a logic app in combination with Azure data factory v2.

Hybrid integrations with azure components - large file to database on-premise import - Part 2 - create azure ad application and install on-premise datagateway

Before we can access the pipeline created in part 1 we need an azure ad application to get authorized to call the Azure service management API that does contain methods to create a pipeline run and check pipeline run status. We need this access to the Azure service management API in order to trigger our pipeline from a logic app which is our ultimate goal.

(As I wrote in part 1 this might not be necessary for future implementations as I've read that the dev team for logic apps does have an ADF connector in the pipe)

But right now it's needed so let's get started. Instead of going through all the detailed steps on how to create an azure ad application you can follow the guidelines in this Microsoft tutorial and if for some reason the link is removed or becomes obsolete you should be able to find a similar tutorial by googling on 'Azure ad application create tutorial'.

Follow the steps in the tutorial below:

How to create Azure ad application

You'll need to give your Azure ad application access to Azure service management API. You do this by setting required permissions on your ad app. Click on 'Azure Active Directory' -> 'App Registrations' and the app you just created (if it doesn't appear, select 'all apps' in the drop-down list)

Then click on 'Settings' -> 'Required permissions' and the + Add button. Search for 'Windows Azure Service Management API' and make sure that you select the delegated permission 'Access Azure Service Management as organization users (preview)'. Save your app changes.

Finally, in order for our ad app to have access to data factory pipelines, we'll add it as a contributor to the Azure subscription. (One should presumably be able to add this access in a more granular way but for simplicity, we'll give the ad app this high-level permission)

In the search field on top of the Azure portal site, type 'Subscriptions' and wait for the drop down result list to appear. Click on 'Subscriptions' and then click the subscription you're working in (in my case I only got one subscription).

Click on 'Access control (IAM)' -> +Add button and choose Role 'Contributor'. Search for your ad app name in the 'Select' field and finally click save.

Our ad app is now finalized and got enough permission to trigger and monitor our pipeline from a logic app.

However, you'll need some values for part 3 in this tutorial (then we create the logic app that'll trigger our pipeline) So I recommend that you collect these values right now, maybe in some notepad type of application.

• Tenant ID (Called Directory ID in the portal. I found this value clicking 'Azure Active Directory' TAB -> 'Overview' -> listed on top of overview blade)

• Client ID - (In the portal it's called ApplicationID and you'll find it via: 'Azure Active Directory' -> 'App Registrations' -> the app you just created (if it doesn't appear, select 'all apps' in the drop-down list))

• Client secret (this one was generated when you created your ad app and you should have stored a copy)
• Subscription ID (In the search field on top of the Azure portal site, type 'Subscriptions' and wait for the drop down result list to appear. Click on 'Subscriptions' and then click the subscription you're working in (in my case I only got one subscription).)

• Resource group name (where your data factory resides)
• Azure data factory name
• Azure data factory pipeline name

Finally, in order to access files locally on our on-premises file area from a logic app, we need to install Microsoft on-premises data gateway.

I just googled 'azure on premises data gateway download' and got the download link from Google in the first position. So today you can get it here:

On-premises data gateway download link 2018-04-22

Installation is quite simple. Just follow it.

1 - Installation process - set configuration information.

2 - During the configuration phase of the installation you'll need to log into Azure and unless you use a corporate account or a student account it won't allow you. But there's a workaround as I learned during global integration boot camp. You can create a new user in your azure ad that does belong to your azure ad tenant. So do this and assign this user to the contributor role in your Azure subscription.

3 - Create a gateway artifact in a resource group of your choice in your Azure subscription. Be aware that if you do not have a corporate account or student account (see point 2) you'll need to login to your Azure subscription with the azure ad user created in step 2.

Great, now we have all the artifacts we need to tie everything together in part 3 where we'll create a Logic app that'll use the on-premises data gateway and the azure ad application that we just created to trigger and monitor our pipeline. (part 1)

Hybrid integrations with azure components - large file to database on-premise import - Part 1 - create azure data factory v2 pipeline

This is a blog series about integration using Azure cloud components. The focus will be on how to combine the different tools available in the azure toolbox to build robust, scalable and cost-efficient integrations that might be challenging or expensive to achieve with a single tool.

For the first chapter in this series, I'll show how you can combine logic apps and Azure data factory version 2 in order to import an on-premise CSV file into an on-premise database.

The integration scenario is that it's a big file around 100MB so you can't use logic apps with on-premises data gateway as the limit is (currently) around 30MB. (Also if it's an on-premise to on-premise integration it doesn't make sense loading up such a large file to Azure datacenter and back again).

In order to allow a logic app to interact with an ADF pipeline one currently needs an azure ad application.

(The logic app team is working on an ADF connector so this part of the blog might quite soon be obsolete - part 2)

There will be 3 parts:

1 - Create the Azure data factory pipeline to import an on-premise flat file into an on-premise database.

2 - Create the azure ad application in order to be able to execute the ADF pipeline from a logic app.
Create the power bi on-premises data gateway in order to be able from a logic app to list files in a folder and to delete files.

3 - Create a logic app that does use all our components in 1 - 3 and tie everything together to finalize the integration.

Let's start creating the Azure data factory. I created a resource group named HybridIntegrations to hold all of the components.

Go to this resource group and click the + button to add an artifact, search for data factory and click create.

Give it a name, choose subscription, resource group (use existing), version v2 (preview) location of your choice (I used west Europe).

Go to the data factory you just created and click the author and monitoring button.
It'll open up another design window. From the rest of part 1, we will work in this design window to create our ADF pipeline.

Per default, you should be in the overview tab of the design window.

On the upper left side from top to bottom you got: a factory symbol, a rectangle with 2 squares (this is your current view/tab), a pencil (this is the actual graphical designer where you can create pipelines and linked services (ie. connections to other systems like file, sftp, SQL, blob storage...)) and a 'watch' / speedometer (where you can get basic execution history and status).

Click on the pencil symbol and when the designer window appears, click the + button and choose pipeline from the drop down. Give it a name in general TAB (csvFileToSQLImport in my case) and define 2 parameters in parameters TAB -> FileName and FileFolder both of type string. We'll use these to make the solution dynamic.

Now expand the 'Data Flow' headline under activities in the designer and drag and drop the Copy activity to the designer blank area. (it's expanded in the image above). We'll now focus on configuring this activity so just click on it.

It's mandatory to configure a source and a sink (as they call the destination, ETL - nomenclature?)
You can give your pipeline a name in general TAB. Then click the source TAB. If this is your first pipeline you won't have any dataset so click the +New button. We'll now basically configure artifacts to pick up the source file.

In the new dataset pane that appears, click the filesystem TAB and choose file system -> finish. You should now have a default named dataset. Give it a meaningful name and click the Connection TAB. You'll presumably not have any linked service so click the + New button and start to configure your new linked service for the file system dataset.

Give it a name, choose the type to be file system and click New in the Connect via integration runtime dropdown.

In the 'Integration runtime setup' pane that appears, choose 'Private network' and next. Give it a name and click next. Finally, click the 'express setup' and follow the instructions. (This will download and install a local integration runtime on the machine you're working on and finally register the runtime in your Azure data factory)

Once your local integration runtime is installed, configured and registered you can go on and finalize your linked service for the file source. You need a user that does have read and list files access to the folder where you want to pick up files. See below for the remaining parameters to fill. On a successful test of your connection click the finish button.

Almost done with the source file dataset configuration. We need to specify folder and file to pick up. You can do this by clicking the browse button and choose a specific folder and file. However, to make this component more reusable we will use the two input parameters to the pipeline that we defined earlier.

So set the folder to @pipeline().parameters.FileFolder and the filename to @pipeline().parameters.FileName 

Additionally, you need to specify the file format. I'm not going into the details here as these settings are standard for flat file parsing, see below.

Almost done ... for the flat file source. Just click the schema TAB and create/define the column names and types for your flat file structure.

Now it's time for the sink (destination) dataset. Click on your pipeline or the pipeline TAB in the designer area. Click on the copy task and on the sink TAB below. Choose new and in the new dataset pane that appears choose databases and SQL server. Click finish.

A new design TAB for the sink (destination) dataset will appear. Give it a name in the general TAB. In the connection TAB, click the new linked service button and fill in the values according to your local SQL server installation/database and target table where you want the flat file values to be inserted. (you should now be able to choose the same local integration runtime as you used for the file source linked service)

Once you have connectivity to your SQL connection click finish and choose target table.

In the schema TAB, you can autogenerate the schema for your target table.

We're done ... with the destination connection (the sink).

Just one last thing before we can test this pipeline, let's define the mapping between the source schema and target schema. Click on your pipeline component or the design TAB for your pipeline. Then choose the Mapping TAB and click the import schema button.

Based on your schema names for source and target the designer will try to automap source column names to target column names. You then have the choice to adjust the target columns by choosing items in a drop-down list. (Notice that I had to remove some target columns from the target schema so that source columns matched exactly the number of target columns. Otherwise, the 'mapping' failed even though I unchecked the target columns in my database that had default values)

(This is not much for the T in an ETL engine. (Transform) And it will limit, quite a lot, the real world scenarios that can be handled by the copy task. It's, of course, possible to do custom transformations, but from what I've read so far, it involves spinning up some cloud servers running the transformation processing cycles on Azure virtual machines and as soon as you got VMs involved, your costs are going to run away. But there are ways around this that can be used now. For instance, one can upload the source dataset to a blob and use a function to do the transformation)

We should now be able to test our pipeline and finalize part 1.
Normally you would create a trigger and define an import schedule for the file. To do this you just click the trigger button and choose new/edit. Then follow the steps to define your trigger.

For simplicity, we'll debug our pipeline by clicking the Debug button with the play symbol and enter our custom parameters as below.

If everything goes well you should have some new records in your database table.

Finally, let's do a load test to make sure that this ETL engine delivers. To do this I just added an 'ItemDesciption' column to boost data size in one record and I created a file with a million record. (250 MB). I thought that I would have to wait for some minutes but to my surprise after only 30 seconds the pipeline execution was complete and the records were inserted.

Great but what if you want to delete the source file? Well, it's not really doable out of the box in the designer (at least I haven't figured it out). Googling it will point you to using azure batch services, which involves virtual machines and that means rising costs as I pointed out earlier... However, there's another way to do it that I'll show you in the next 2 parts/post.

Android device fragmentation, it finally happened

I've been coding android apps on my free time for over 3 years now and I've never had any special issue on any specific device (i.e. what people call android device fragmentation) ... until some days ago.

I released a new feature in my app that was build with opengles 2.0 graphic engine. Everything was working fine on my dev devices: samsung s4 & s3 mini and a htc x one, android version 4.2. 

I happily released the new package to googleplay and after just a day errors related to opengles 2.0 started to pop up in my developer administration console. And all the devices had android version > 4.3.

So what to do?! At home I only had my android 4.2 devices and after checking program updates I realized that I would not be able to increase the android version number. I phoned to my local Samsung representative and they informed me that there was no plan today to release any upgrade to the Samsung s4 mini. Google the HTC x one I realized I could just forgot about any upgrade on that one...

So what to do? Without testing possibilities you can't solve a bug...

Enter my national phone operator! Normally you can't do much with the phones in the stores. Usually they're locked and lack internet connectivity. But I found this store that do allow browser with internet connectivity. So I create a couple of apk packages send them to an email account and go to the store. I found a suitable phone with android version > 4.2 and start to test my packages. First one fails, second one succeeds!! Happy with this positive result I start to test on more devices... until I manage to drag one of the phones anti theft cable a little too much triggering the alarm!! A little embarrassing and it might be difficult to come back for more testing...

The technicalities of and the solution to the bug can be found here:

android-strange-error-in-opengles-2-0-calling-gluniformmatrix4fvumatrixlocation

Script to get user SID

Just some weeks ago I ended up in a situation where I needed to get a user SID (Security Identifier). I found a good script on the net that I just needed to modify a little.

Just log in as the user you need SID for and run this script:

"strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set objAccount = objWMIService.Get _
    ("Win32_UserAccount.Name='xxxx',Domain='yyy'")
Wscript.Echo objAccount.SID"

replace xxxx with you user name and domain yyy.
Depending on your settings you'll get the output as alertbox or in the cmd.

(verified on windows server 2003)

System integration using dropbox

I've been using dropbox for some time and I think it's terrific for managing all kind of information accross your devices. It's so easy.

It just occured to me that dropbox could be used for integration purposes. After all it's 'just' an engine that integrate between all devices belonging to your account.

Dropbox sync folders on the devices connected to the same dropbox account. I don't think it's possible to have more than one account for each device.

There's no master. For instance if you delete a file from any device it'll propagate to all devices. So what kind of integration patterns could one achive with this tool?

The most obvious one is the point to point channel pattern.
(Ther're quite a lot of integration scenarios I've encountered where you need to exchange information between a windows server and a linux server. And I think that this technique could be usefull for such a scenario)

It should also be possible to broadcast information letting one publisher act as the master for the information and have 1 to many 'subscribers' that passively receive the information. I guess that if you don't need it to be transactional that one could use the dropbox service for this purpose.
(I won't be a true publish - subscribe channel as there's no way for the subscribers to filter the information. They'll get all of it so it's more like a broadcast.)

So let's test how it works.
(To create the servers I'm using windows azure)

1 - Create some dropbox account at: https://www.dropbox.com/login?lhs_type=anywhere

2 - Create the servers from windows azure management console.
Just choose virtual machines on the left meny -> NEW -> choose from templates
(I choosed a windows 2008 os and the 'SUSE' enterprise linux distribution)

3 - Install the client software on the servers between witch you want to sync information. For windows it's an installer and for linux you should follow the instructions provided here:

https://www.dropbox.com/install?os=lnx

(you can get an rdp file from the management console just clicking on the 'connect' button in the windows azure management console. For linux you need to use some SSH client. I used the putty client)

4 - I wrote a very simple windowsform application to post some information from the windows server to the linux server. All it does is take some text, put it on the dropbox sync folder on disk (on the windos server) and write out some logging. The rest of the work is left to dropbox :)

This is the code for the form events:

"using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace ClientPoster
{
    public partial class Form1 : Form
    {
        String syncPath = System.Configuration.ConfigurationSettings.AppSettings.Get("syncPath");
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                addMessageToLogWindow("try write to disk");
                writeToDisk(textToPost.Text);
                addMessageToLogWindow("write to disk successfull");
            }
            catch (Exception wDiskExp) {
                addMessageToLogWindow("Error writing to disk: ");
                addMessageToLogWindow(wDiskExp.Message);
                addMessageToLogWindow(wDiskExp.Source);
            }
        }

        private Boolean writeToDisk(string text) {
            try
            {
                StreamWriter mStreamWriter = new StreamWriter(syncPath + Guid.NewGuid().ToString() + ".txt", false, Encoding.Default);
                mStreamWriter.Write(text);
                mStreamWriter.Dispose();
                mStreamWriter.Close();
                return true;
            }
            catch (Exception e) {
                throw e;
            }
        }

        private void addMessageToLogWindow(string message) {
            textLog.Text += System.Environment.NewLine + DateTime.Now.ToShortTimeString() +
                ": " + message;
        }
    }
}"

5 - Finally I run a debug of the form client from visual studio to test it all.
See screen dumps below to follow the workflow from the windows server to the linux server.

conclusion:

I'm quite amazed on how easy and fast this was to implement. I guess there're a lot of options to communicate between a windows to a linux server but my own experience is that it's not so obvious. If you use sftp you need to create the accounts and make sure all ports are opened. With dropbox you (normaly) just need servers wired to the internet. And most of the servers are today, even the production servers (as you want to keep them up to date). Dropbox uses port 80 and 443 (https). It uses SSL to transfer your data so security should be decent. If one needs additional security I would encrypt the data.

I'm maybe completely wrong here but isn't this a viable way to transfer information between servers then it's not super critical (then you will want to use some transactional queue mechanism) instead of struggling with sftp / file shares (ie samba) ... ?