In the past few months, I have been playing around with different kinds of IoT devices and sensors. I quite enjoy how these can be used to monitor different things like humidity, temperature, pressure among other things in the house. In this tutorial, I want to show you how you can monitor sensor readings in a database and send alerts when it crosses a threshold value using workflows. n8n We’ll be using as the database and for sending SMS alerts. We’ll divide the tutorial in two workflows: PostgreSQL Twilio This would be the data generator workflow, which will create a database record for us every minute. We will create this workflow to simulate sensor readings being ingested by a database. In case you already have an IoT device pushing data to a database, feel free to skip this step. Nonetheless, I’d recommend skimming through this section to check how to add the database credentials. Workflow 1: This workflow will check for the threshold values every minute and in case of a value that crosses the specified threshold, it will trigger an alert with an SMS. Workflow 2: You can find information on how to set up and start n8n in the . documentation Workflow 1: Ingesting data in the database In this workflow, we’ll use a Cron node to trigger the workflow ever minute, a Function node to generate timestamp and a random value, and a Postgres node to insert the generated data to the database. If you haven’t already, you can find and install the database from their . website For this tutorial, create a table called and use this SQL statement to create a table: n8n n8n ( , sensor_id , , time_stamp , notification ); CREATE TABLE id SERIAL VARCHAR value INT TIMESTAMP BOOLEAN I have also submitted on n8n.io, in case you’d like to skim through this workflow. Please note that you’ll still need to configure a couple of things like your Postgres credentials. Workflow 1 1. Cron node: Trigger the workflow every minute First of all, start n8n with the following command: n8n start In the Node Editor view, add a new node by clicking on the + button on the top right of the Editor UI. Select the node under the section. Click on the button and select ‘Every Minute’ as the . Here’s a GIF of me following the steps above. Cron Triggers Add Cron Time Mode 2. Function node: Generate timestamp and random value Add a new node by clicking on the + button and select the node. Enter the following JavaScript code to generate a timestamp, a random value as well as set the values for the other columns so that they can all exist in a single JSON object. Function items[ ].json.sensor_id = ;
items[ ].json.value = .ceil( .random() * );
items[ ].json.time_stamp = ().toISOString();
items[ ].json.notification = ; items; 0 'humidity01' 0 Math Math 100 0 new Date 0 false return Here’s a GIF of me following the steps mentioned above. 3. Postgres node: Insert data to the database Add a new node by clicking on the + button and selecting the node. Create new credentials by clicking on under the section. Enter the details for your database here and click on the button. Postgres Create New Credentials Save Now, enter as the name of the Enter in the field. Here, I skipped the id as it is a SERIAL datatype and would be generated by the database. Click on the button and the record will be inserted to your database. n8n Table. sensor_id, value, time_stamp, notification Columns Execute Node Here’s a GIF of me following the steps mentioned above. Don’t forget to save and activate the workflow before moving on to the next workflow. Once you have done that, every minute a new record will be inserted into the database. Note: Workflow 2: Monitoring the database and sending alerts In this workflow, we’ll use a Cron node to trigger the workflow every minute, a Postgres node to fetch the threshold values, and a Twilio node to send us an SMS about them. Finally, we’ll use a Set and Postgres node to mark the records which we have already been alerted about. I have also submitted on n8n.io, in case you’d like to skim through this workflow. Please note that you’ll still need to configure a couple of things like your Postgres and Twilio credentials as well as the settings for the Twilio node. Workflow 2 1. Cron node: Trigger the workflow every minute You can create the Cron node following the same steps as you did in . Workflow 1, Step 1 2. Postgres node: Get all the records with the outlier values Create a Postgres node following the same steps as and select the same credentials that you entered there. Select ‘Execute Query’ for the field and enter the following SQL query: Workflow 1, Step 3, Operation * n8n &gt; notification = ; SELECT FROM WHERE value 70 AND false This query returns the records where the value is greater than 70 (feel free to change this to something else) and the notification is marked as . Click on the button. If you have the other workflow running or an active IoT device sending data into the database, you’ll likely have records as the result. false Execute Node Here’s a GIF of me following the steps above. You might want to turn off the other workflow or device at some point otherwise you ‘might’ end up using all your Twilio free credits during the first run Note: 😉 3. Twilio node: Send an SMS alert about the threshold value First of all, you’ll have to create a account. You can create a free trial account and once you have done that, go to the and create a for yourself. On that page, you can also acquire the and that you’ll need for the credentials. Twilio console Trial Number Account Sid Auth Token Once you have done that, add a Twilio node in n8n. Create new credentials for the Twilio API and enter the details. Now, enter the trial number that you created in the field and your number (the one that you verified your Twilio account with) in the field. In the message field, we want to include dynamic pieces of information. To do that click on the gears icon next to the field and select . I entered the following in that field: From To Message Add Expression 🚨 The Sensor ({{ [ ] [ ]}}) showed reading of {{ [ ] [ ]}}. $node "Postgres" .json "sensor_id" a $node "Postgres" .json "value" In case you have a large number of threshold values, I would recommend waiting on the execution, as you might end up using all your free credits. Note: Once you are done, click on the button and it’ll send an SMS alert to you. I got three alerts here since I had three threshold values in my records. Execute Node Here’s what I got: 4. Set node: Set the notification to true Add a new node by clicking on the + button and selecting the node. Open the Node Editor and click on toggle. We do this because Twilio node overwrites data and we want to pass along the data that is generated only by this node. Set Keep Only Set Under the section, click on the button and select ‘Number’. Enter as the Since the is a dynamic piece of information, click on and select the id in the by clicking on: Values to Set Add Value id Name. Value Add Expression Variable Selector Nodes > Postgres > Output Data > JSON > id Now, click on again and select ‘Boolean’. Enter as the and click on the toggle. Click on the button and you’ll see that it will set the value of the notification to for all the corresponding ids. Once we make this update in the database with the help of the next node, the SMS alerts will not go through twice for any given record. Add Value notification Name Value Execute Node true Here’s a GIF of me following the steps mentioned above. 5. Postgres node: Make update to the notification in the database Add a new Postgres node, select the Credentials and select ‘Update’ as the . In the Table field, enter and enter in the field. Operation n8n notification Columns Click on the button and this will make an update to the notification value in the database. Here’s a GIF of me following the steps mentioned above. Execute Node Conclusion Today we created a workflow to continuously monitor a database and alert us about an abnormal reading by sending an SMS. You can also use other databases such as MongoDB or MySQL. You can even use other Postgres compatible databases like using the Postgres node. I am curious to learn what kind of workflows you’ll create using these or other nodes. I’d love to check them out, please consider those workflows with the community. CrateDB sharing In case you’ve run into an issue while following the tutorial, feel free to reach out to me on or ask for help on our 💙 Twitter forum Previously published at https://medium.com/n8n-io/database-monitoring-and-alerting-with-n8n-f5082df7bdb2

Fetch

Twitter

Book a call

Read My Stories

Too Long; Didn't Read

Making security fun one episode at a time

Database Monitoring and Alerting with n8n 📡

Too Long; Didn't Read

Companies Mentioned

Tanay Pant

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

Database Monitoring and Alerting with n8n 📡

Too Long; Didn't Read

Companies Mentioned

Tanay Pant

About Author

TOPICS

THIS ARTICLE WAS FEATURED IN...

RELATED STORIES