Losant's SQL Node makes it easy to securely connect to a variety of SQL databases. This provides a way to enrich your device information with additional IoT metadata, like customer or other internal data. This tutorial provides step-by-step instructions for how to use Losant to query your MSSQL databases hosted on Azure.
1. Create Azure SQL Database
There's nothing special you have to do when creating a MSSQL database on Azure in order to query it from Losant. If you already have a SQL database instance, then you can continue to step 2. If you don't have a database yet, please check out Azure's documentation or video guide for details on how to create one.
2. Create Database User for Losant
Whenever you allow other services to access your database, it's recommended that you create a specific account for that service with only the required privileges. This provides an easy way to revoke access without affecting any other users or services interacting with your database. Please follow Azure's documentation for creating users.
3. Create the Firewall Rule for Losant
Azure requires you to whitelist any IP addresses that will connect to your database instance. Since Losant is a high-availability and fault-tolerant system, we utilize many outgoing IP addresses that can change at any time. Due to this, we are unable to publish a set of IP addresses for whitelisting when using Losant's fully-managed cloud environment. This means you're required to open all IP addresses (0.0.0.0 - 255.255.255.255) for database access. Although this does have security implications, all connections to Azure SQL databases are fully encrypted in transit, so your information is safe. Enterprise installs of Losant with dedicated infrastructure do have access to a known set of IP addresses. Please contact sales if this is something you'd be interested in.
To add a firewall rule, click the Set server firewall menu item on the database overview page.
Next, add a firewall rule for Losant with the IP range set to 0.0.0.0 to 255.255.255.255.
When complete, click the Save button at the top. At this point, you can now connect to this database from a Losant workflow.
4. Query SQL Database From Losant
You can now use the SQL Node to query your Azure SQL database.
The screenshot above is showing a good example of when to integrate data from a SQL database. In this example, a company's part information is stored in SQL. Whenever a device reports a fault code, which may indicate a failed part, the workflow will query the SQL database for additional information. An email notification is then sent to the user with the fault details and the specific part metadata.
Losant's SQL Node configuration is split into three parts: connection details, encryption settings, and the query to execute. You can get the SQL Server Address from your database overview page on Azure.
The Username and Password come from the user you created in step 2. The Database Name is the actual database you'd like to query.
Azure requires encryption to be enabled, so check the MSSQL Encrypt Option checkbox. Azure does not require or support any additional SSL configuration, so those fields should be left blank.
Next, provide the actual query to execute on your database. The query in the example above is the following:
SELECT * FROM SalesLT.Product
WHERE ProductModelID={{data.faultID}}
As you can see from the example, the query field supports templates, so you can inject payload data into your query. In this case, the device is sending an ID for the fault that we can use to look up the matching part.
Once the query completes, an array of results will be placed on the payload based on what you specified in the Payload Path to Store Result field.
In the screenshot above, my array of results was placed on the payload at working.result. You can then use this data in any subsequent nodes. In this example, we are using this additional data in the notification sent to the user so they can more easily re-order a replacement part.
The SQL Node supports pretty much all common SQL actions beyond just SELECT. This means you can also INSERT and UPDATE data using a Losant workflow as well. If you do use the SQL Node as part of your IoT solution, we'd love to know the use case, so please drop a message in the Losant Forums.