This is a follow up article of our "Connecting Azure IoT Hub 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.

Requirements

Table of Contents

  1. Set up an SQL Server

  2. Set up an SQL Database

  3. Create a Logic app

  4. Ubidots webhook setup

  5. Feedback suggestions and related articles

1. Set up an SQL Server

  • The first resource that you need to add is SQL database. The SQL database is a collection of data organized into tables for simple organization

  • Use the search bar at the top and type SQL Server. Click on it.

  • Click the "+ Create" Button

  • Fill the following settings according to:

    • Subscription: select your azure subscription

    • Resource group: the same resource group that your IoT hub uses

    • Server name: the name for your SQL Server

    • Location: the location of the server hosting this resource. Note that you must select the same location for all of the following resources

    • Authentication Method: select the settings most appropriate for your particular implementation. For this article, it was chosen Use SQL Authentication method.

    • Set a admin login username and a password.

    • Click on Next: Networking button

  • Enable other Azure resources to interact with this SQL Database Server by setting to Yes the Firewall rule

  • Click on Review + create

  • Wait for the newly created resource to be deployed. Then click on Go to resource button

2. Set up an SQL Database

  • Click on Create database button

  • Fill the following settings according to:

    • Database name: the name of the database

    • SQL Elastic Pool: select this according to your needs. For this article, this feature was not required

    • Compute + Storage: Click on Configure database in order to change the pricing model and the requirements for your project according to your desired pricing quota

  • Click on Review + create to create the database

  • Once the resource is deployed click on the Go to resource button

  • Click on Query editor(preview)

  • Enter the credentials created with the SQL Server in the previous step

  • Click on OK button to login to your database

  • If a warning pops-up telling you that you can't login from your current IP address, just click on that warning and your IP address will be added to the firewall exception rules

  • Create a new table on the current database by copying the following query 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

3. Create a Logic app

In order to handle the data going from Ubidots to Azure IoT Hub, a Logic App will be implemented. It will receive a HTTP request from Ubidots, then parse the data to send it to the SQL Database in IoT Hub. 

  • Create a new logic app following the steps provided in previous the tutorial

  • Wait for the resource to be deployed. Then click the Go to resouce button

  • Create the first action on the Logic App by selecting When an HTTP requests is received

  • Click on Add new parameter

  • Click on POST

  • Click +New step

  • 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"
}
  • Press +New step

  • Search "SQL server" on the search bar and click on it

  • As action select SQL Server - Insert row (V2)

  • Fill the following settings according to:

    • Connection name: the name of the database

    • Authentication type: Select the same authentication method as you did for the SQL Database

    • SQL Server name: the name that given to the SQL Server on its set up, followed by ".database.windows.net"

    • SQL database name: the name of the database created on step #2

    • Username: the username that you choose at the SQL Server setup. This only applies if you chose SQL Server Authentication as authentication method

    • Password: the password that you choose at the SQL Server setup. This only applies if you chose SQL Server Authentication as authentication method

    • Gateway: Select No gateways found

  • Click the Create Button

  • Wait for the connection to the database to take effect

  • Set the following settings as:

    • Server name: select Use connection settings. This will set the value of the previously selected Server name

    • Datanase name: select Use connection settings. This will set the value of the previously selected Database name

    • Table name: select the table created on former steps. If you have been following this tutorial's nomenclature, it should be "MyUbidotsTable"

  • Click the dropdown menu displaying the message Add new parameter and select valueUbi

  • Set the mouse pointer on the valueUbi field

  • From the menu appearing on the right, search for value and drag it to the valueUbi field

  • Click Save button

  • Click Run Trigger, wait for it to finish and copy the HTTP POST URL

4. Ubidots WebHook Setup

To finish the integration, an Ubidots event will be setup to send data to Azure IoT Hub. Reference this article for additional details to create a WebHook event in Ubidots. 

  • Go to the Devices section on your Ubidots account and Create a new device. For this article it was labeled as "azure-device", however, you can use any label that you find appropriate

  • Create a new Raw Variable for this device. In this article it was named "button", however, you can use any name

  • Go to the event section and press "Add event" to create a new event. Select the device and the variable created on the previous step

  • Set the following settings in the IF triggers tab

  • Go to the THEN actions tab, select Add new action and then Trigger Webhook

  • On the HTTP Method and URL section select POST and paste the HTTP POST URL obtained on step #4

  • On the Body section, delete all and paste the following:

    • {"value": {{last_value}}}

  • Go go the BACK TO NORMAL tab

  • Check the Trigger this action when the event condition goes back to normal state option

  • Copy the following on the Body section

    • {"value": {{last_value}}}

  • Click on the green check mark to save the event configuration

  • Go to the dashboard section to create a switch control widget to test how everything works. Every time that the button changes its state, it will trigger the WebHook posting the last value in Azure

  • To create the widget, press Create widget > Control > Switch > "name of the device in Azure" > "name of the variable" > Finish. If you have had followed the same nomenclature as in this article, then the steps are Create widget > Control > Switch > azure-device > button > Finish. Once the widget is created you'll see something like the picture below:

  • 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

  • By following this technical guide, it was possible to create a table which stores inbound data from Ubidots in the Azure SQL database

  • In this case it is being evaluated the amount of times the button is being pressed in the Ubidots dashboard.

  • The ID field makes reference to the entry on the table 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.

5. Feedback, Suggestion and Related Articles

Feel free to post questions or suggestions in our community portal, or contact us via support@ubidots.com.


Other users also found helpful...

Did this answer your question?