Database Monitoring and Alerting with n8n

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 n8n workflows.

We’ll be using PostgreSQL as the database and Twilio for sending SMS alerts. We’ll divide the tutorial in two workflows (these workflows were built using n8n@0.110.0):

1. Workflow 1: 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.
2. Workflow 2: 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.

You can find information on how to set up and start n8n in the documentation. Alternatively, you can also sign-up for n8n.cloud to get access to our hosted service.

Workflow 1: Ingesting data in the database

In this workflow, we’ll use a Cron node to trigger the workflow ever minute, a Set 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 n8n and use this SQL statement to create a table:

CREATE TABLE n8n (id SERIAL, sensor_id VARCHAR, value INT, time_stamp TIMESTAMP, notification BOOLEAN);

I have also submitted Workflow 1 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.

1. Cron node: Trigger the workflow every minute

In the Node Editor view, add a new node by clicking on the '+' button on the top right of the Editor UI. Select the Cron node under the Triggers section. Click on the Add Cron Time button and select ‘Every Minute’ as the Mode. Here’s a video of me following the steps above.

2. Set node: Generate timestamp and random value

Add a new node by clicking on the '+' button and select the Set node. Click on ‘Add Value’, under the Values to Set section, and select ‘String’. Enter sensor_id in the Name field and humidity01 for the Value field. To add a new value, click on ‘Add Value’ and select ‘String’. Name this as value. Click on the gears symbol next to the Value field and select Add Expression. Paste the following expression {{Math.ceil(Math.random() * 100);}}. This will create a random value between 1 to 100. We will also create a time uptime value.

Click on ‘Add Value’ and select ‘String’. In the Name field enter time_stamp. Click on the gears symbol next to the Value field and select Add Expression. Paste the following expression {{new Date().toISOString()}}. This will return the timestamp of when the data was generated.

Click on ‘Add Value’ and select ‘Boolean’. Enter notification in the Name field. Set the value to false.

Here’s a video 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 Postgres node. Create new credentials by clicking on Create New under the Credentials section. Enter the details for your database here and click on the Save button.

Now, enter n8n as the name of the Table. Enter sensor_id, value, time_stamp, notification in the Columns field. Here, I skipped the id as it is a SERIAL datatype and would be generated by the database. Click on the Execute Node button and the record will be inserted to your database.

Here’s a video of me following the steps mentioned above.

Note: 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

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 Workflow 2 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.

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 Workflow 1, Step 3, and select the same credentials that you entered there. Select ‘Execute Query’ for the Operation field and enter the following SQL query:

SELECT * FROM n8n WHERE value > 70 AND notification = 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 false. Click on the Execute Node 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.

Here’s a video of me following the steps above.

Note: 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 😉

3. Twilio node: Send an SMS alert about the threshold value

First of all, you’ll have to create a Twilio account. You can create a free trial account and once you have done that, go to the console and create a Trial Number for yourself. On that page, you can also acquire the Account Sid and Auth Token that you’ll need for the credentials.

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 From field and your number (the one that you verified your Twilio account with) in the To field. In the message field, we want to include dynamic pieces of information. To do that click on the gears icon next to the Message field and select Add Expression. I entered the following in that field:

🚨 The Sensor ({{$node["Postgres"].json["sensor_id"]}}) showed a reading of {{$node["Postgres"].json["value"]}}.

Note: 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.

Once you are done, click on the Execute Node button and it’ll send an SMS alert to you. I got three alerts here since I had three threshold values in my records.

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 Set node. Open the Node Editor and click on Keep Only Set toggle. We do this because Twilio node overwrites data and we want to pass along the data that is generated only by this node.

Under the Values to Set section, click on the Add Value button and select ‘Number’. Enter id as the Name. Since the Value is a dynamic piece of information, click on Add Expression and select the id in the Variable Selector by clicking on:

Nodes > Postgres > Output Data > JSON > id

Now, click on Add Value again and select ‘Boolean’. Enter notification as the Name and click on the Value toggle. Click on the Execute Node button and you’ll see that it will set the value of the notification to true 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.

Here’s a video 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 Operation. In the Table field, enter n8n and enter notification in the Columns field.

Click on the Execute Node button and this will make an update to the notification value in the database. Here’s a video of me following the steps mentioned above.

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 CrateDB 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 sharing those workflows with the community.

In case you’ve run into an issue while following the tutorial, feel free to reach out to me on Twitter or ask for help on our forum 💙

This post originally appeared on n8n.io blog.