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:

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.

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

n8n

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

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 Cron node under the Triggers section. Click on the Add Cron Time button and select ‘Every Minute’ as the Mode. Here’s a GIF of me following the steps above.

2. Function node: Generate timestamp and random value

Add a new node by clicking on the + button and select the Function 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.

items[0].json.sensor_id = 'humidity01';
items[0].json.value = Math.ceil(Math.random() * 100);
items[0].json.time_stamp = new Date().toISOString();
items[0].json.notification = false;

return items;

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

sensor_id, value, time_stamp, notification

Here’s a GIF 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 GIF 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

Nodes > Postgres > Output Data > JSON > id

Now, click on Add Value again and select ‘Boolean’. Enter

notification

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 Operation. In the Table field, enter

n8n

notification

Click on the Execute Node 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.

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 💙

Previously published at https://medium.com/n8n-io/database-monitoring-and-alerting-with-n8n-f5082df7bdb2