Today’s update provides a ton of additional capability across many parts of your Losant applications. The SQL Node and Redis Trigger provide new ways to interact with your own databases to make it easier to enrich device data and integrate with existing data flows. Standard Deviation is now a built-in aggregation to make it easier to perform additional analytics and alerting based on your device data trends.
SQL databases are a popular system-of-record and the new SQL Node now makes it easy to merge device and IoT metadata together to drive complex business logic directly using Losant Workflows.
The SQL Node, which is available for Edge, Application, and Experience Workflows, makes it easy to query from and insert data into MSSQL, PostgreSQL, MySQL, and SQLite (Edge only) databases. The SQL Node was primary created for Edge Workflows to make it possible to query your own on-premises databases over your private network. However, the node is also available in Application and Experience Workflows and supports all the necessary encryption to securely connect to cloud-hosted databases from providers like AWS, Google Cloud, and Azure.
The workflow screenshot above is showing a common example of when an SQL Node would be used. In this example, your customer information is stored in a PostgreSQL database hosted on Google Cloud. As device data is streaming in, an alert threshold is checked using a Conditional Node. If a notification should be sent to the user, the SQL Node queries the database for the owner of the device in order to get a phone number. The workflow can then send an appropriate SMS notification to the customer.
I chose PostgreSQL on Google Cloud Platform (GCP) as the example, because it is one of the more complicated databases to configure since it requires client-certificate authentication when using SSL (recommended). GCP does make it easy to generate the certificate, which you then need to provide in the SSL Configuration text field:
{
"rejectUnauthorized": false,
"ca": "-----BEGIN CERTIFICATE-----\nMIID...",
"key": "-----BEGIN RSA PRIVATE KEY-----\nMIIE...",
"cert": "-----BEGIN CERTIFICATE-----\nMIID..."
}
All cloud providers require you to whitelist IP addresses that can connect to your SQL 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 or 0.0.0.0/0) for database access. Because of this, Losant highly recommends you enable SSL for all of your database connections. Enterprise installs 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.
We'll be publishing many more detailed guides on how to connect to hosted SQL servers on Azure, GCP, and AWS in the coming weeks.
Redis Pub/Sub provides a powerful messaging framework that many organizations have already adopted and integrated into their environment. The new Redis Trigger, which is available on Edge Workflows, makes it easy to tap into that flow of data and integrate it into your Losant applications.
The Redis Trigger takes a hostname (or IP), port, and password for the underlying connection. You can then provide a channel or channel pattern for the subscriptions you'd like to make. Any time a message is published to a matching channel, the workflow will be triggered. If you don't see messages when you expect them, inspect the Edge Agent's log file. The Edge Agent will print any connection errors to that file.
Losant supports several built-in aggregations that are used in many different places throughout the platform. Today's update adds standard deviation to the set of built-in aggregations. The most common places you'll see these aggregations are on Time Series Graphs, Gauge Blocks, and in the Data Explorer.
The above screenshot is showing the humidity and the standard deviation plotted together. The graph is showing seven days of data with a data point every six hours. By adding the standard deviation, you can see how much variance there is within each six-hour window.
Standard deviations are also a popular way to calculate alerts. If an incoming sensor reading is some multiple of the standard deviation away from the average, you can generate a notification that the reading is "out of ordinary". You can now easily perform this type of analysis using the Gauge Query Node.
With every new release, we really listen to your feedback. By combining your suggestions with our roadmap, we can continue to make the platform easy for you. Let us know what you think in the Losant Forums.