This is a follow up article of our "Connecting Azure IoT with Ubidots" series. If you haven't checked our first article, we highly recommend going through it first to learn how to configure your Azure account before connecting it to Ubidots. 

In this article....

  1. Set up an SQL database
  2. Assign credentials
  3. Create a Logic app
  4. Ubidots webhook

Integration Requirements

Now that you're able to handle the data from Azure to Ubidots, we're going to show you how to handle the data from Ubidots to Azure. Additionally, we're going to show you how to store data in a SQL database in Azure.   

  • Setup an SQL database

The fourth resource that we have to add is the SQL database. The SQL database is a collection of data organized into tables for simple organization.

1.- Add a new resource into your resource group previously created called "ubidots"

2.- Search "SQL Database" within the search bar, then select the SQL Database

Once the SQL database builder is opened, you have to assign the following parameters:

  • Database name: The name of the database, we called our "ubidots"
  • Subscription: select your azure subscription 
  • Resource group: the same resource group that your IoT hub uses.  
  • Select source: blank database 
  • Server: Create a new server. Assign the server name, admin login, and password. If you desire you can call it "ubidots-server" 
  • Pricing tier: The standard option is enough to this tutorial.
  • Collation: This parameter is automatically assigned

To finish, press "create".

3.- Now is time to build a table in the SQL database. Enter to the SQL database and press "Tools":

4.-  To start building the table, select the first option "Query editor (preview)":

5.- Once the Query editor is open, login into the SQL server recently created. Press "login"

Assign the credentials of the SQL Server previously created:

6.- Now we're going to create and visualize the table in the SQL database:

  • To create the table please copy the query below into the query editor, then press "Run"
CREATE TABLE MyUbidotsTable (
    ID int NOT NULL IDENTITY(1,1),
    valueUbi float,
    PRIMARY KEY (ID)
);
  • To visualize the table please copy the query below into the query editor, then press "Run". Once the query run successfully, press "result" to visualize the table:
SELECT * FROM MyUbidotsTable

You've just create a table into an Azure Database. As you can see in the image below a table is created, in just a few seconds. 

  • Create a Logic app

To handle the data from Ubidots to Azure we're going to create a new logic app which will receive a HTTP request from Ubidots with the data, then parse the data to send the last value to Azure. 

1.- Create a new logic app following the steps provided earlier in this tutorial. Once the logic app is created and the logic app designer is open, select the action "When an HTTP requests is received"

You will then be redirect to the logic app builder. This first action will generate a HTTP POST URL. We're going to using this POST URL to trigger a Webhook from Ubidots. For now, press "+ New step":

2.- Next, search "parse json" within the search bar as you can see below, then select the action "Data Operation - Parse JSON":

Assign the parameters below to the action:

  • Content: assign the Body
  • Schema: assign the following schema:
{
  "properties": {
    "value": {
      "type": "number"
    }
  },
  "type": "object"
}

To continue, press "+ New step".

3.- For the new step select "SQL Server":

As action select "SQL Server - Insert row":

Assign the table created before in the SQL database, also you have to assign the value parsed into the "valueUbi" option.

  • Table name: MyUbidotsTable
  • valueUbi: valu

Once you've assigned the table parameters required, establish the connection with the server. Enter your login and password credential to connect. When verified you will see "Connected to {your connection name}"

4.- Now your logic app is ready to be saved, but first verify the structure with following check list:

  • First action: When an HTTP requests is received
  • Second action: Data Operations - Parse JSON
  • Third action: SQL Server - Insert row

If the structure of you logic app is correct, press "save". Don't forget press "Run", to run the logic app.

Once the logic app is saved, the first action will generate a HTTP POST URL which will serve as our Ubidots WebHook. Please copy the URL and save it on a notepad for later use.

  • Create a Ubidots WebHook

To finish the integration, we are going show you how setup an Ubidots WebHook to Azure. Reference this article for additional details to create a WebHook event in Ubidots. 

1.- Go to the device section into your Ubidots, select the "azure" device previously created and create a new default variable called "button".
 

2.- Once the variable is created we're going to trigger that variable as a WebHook event. Go to the event section and press "Add event" to create a new event. Select the device and the variable where you desire to trigger the event. In this case the device is "azure" and the variable "button":

3.- Next, we have to establish the confines of our trigger. In this case we're going to set it "If button value is equal to 1" trigger the event.

4.- Then, select the WebHook option and assign the parameters below to the WebHook configuration:

  • URL: Assign the URL provided in the first action of our last logic app created
  • Method: POST
  • Payload: {"value": {{last_value}}}
  • Headers: content-type | application/json

To finish, press the "Finish" button.

5.- Go to the dashboard section to create a switch control widget to test how everything works. Every time that the button is equal to 1, it will trigger the WebHook posting the last value in Azure. 

To create the widget, press Create widget > Control > Switch > Azure > Button > Finish. Once the widget is created you see something like the below:

6.- Now you're ready to test how everything works. Verify if your logic app is running. If not, press "Run" to restart the function.

Now from your Ubidots dashboard press the switch 3 times, and visualize into the Azure SQL database tools how the values arrive. Please note that if you do not run the query. you data will not appear. 

Using this tutorial, you have created a table which stores inbound data from Ubidots in the Azure database. In this case we are evaluating how many times the button is pressed in Ubidots. The "ID" makes reference to the press count and the "valueubi" makes reference to the status of the button/switch

Also, in the overview of the logic app you can visualize run and trigger histories; if you do not see new/updated information press "Refresh" to update the page.

Now that you know how to manage a SQL database in Azure, you can make the integration for the first logic app created to store values sent to Ubidots in the Azure database.

Did this answer your question?