How to Log MQTT Alarms to a Database

Open Automation Software can be configured to connect to MQTT brokers using the MQTT connector, trigger alarms and log them to one of the supported database providers including SQL Server, Access, Cassandra, MariaDB, MongoDB, MySQL, Oracle, PostgreSQL and SQLite. This guide walks you through downloading and installing OAS, configuring an MQTT connector, configuring tags, setting an alarm limit and configuring alarm logging to a database.

For this guide on how to log MQTT alarms to a database you will need:

To install MQTT Explorer or a similar tool for publishing messages to an MQTT broker

1 - Download and Install OAS

If you have not already done so, you will need to download and install the OAS platform.

Fully functional trial versions of the software are available for Windows, Windows IoT Core, Linux, Raspberry Pi and Docker on our downloads page.

On Windows, run the downloaded setup.exe file to install the Open Automation Software platform. For a default installation, Agree to the End User License Agreement and then click the Next button on each of the installation steps until it has completed.

If you'd like to customize your installation or learn more, use the following instructions:

The OAS Service Control application will appear when the installation finishes on Windows.

OAS Service Control

Click on each START SERVICE button to start each of the three OAS services.

2 - Configure OAS

Configure OAS is the main application used to configure local and remote OAS instances.

OAS Logo

From your operating system start menu, open the Configure OAS application.
Select the Configure > Tags screen.
Important
If this is the first time you have installed OAS, the AdminCreate utility will run when you select a screen in the Configure menu. This will ask you to create a username and password for the admin user. This user will have full permissions in the OAS platform.
For further information see Getting Started - Security.
If this is the first time you are logging in, you will see the AdminCreate utility. Follow the prompts to set up your admin account. Otherwise, select the Log In menu button and provide the Network Node, username and password.

Info

In this guide you will use the Configure OAS application to configure the local Network Node which by default is localhost.

If you have installed OAS on a remote instance you can also connect to the remote instance by setting the relevant IP address or host name in the Network Node field.

3 - Configure MQTT Data Source Connector

In the following steps you will create and configure a MQTT Connector to connect to a third party broker so that tags will be able to subscribe to a topic. For the purposes of this guide, you will connect to the public HiveMQ broker.

Select Configure > Drivers from the top menu.
Enter a meaningful Driver Interface Name to give this driver interface instance a unique name.
Ensure the following parameters are configured:
- Driver: MQTT
- IP Address: broker.hivemq.com
- Port: 1883
- Client ID: OAS_Client_123
Tips
If you are having trouble reading a value in later steps, try to change the Client ID to something else as each client ID connecting to the broker must be unique.
Click on the ADD DRIVER button on the left hand side to add this driver configuration. Once added, the driver interface name should appear in the list of drivers.

4 - Assign MQTT as Tag Data Source

You will now set the Tag's data source to the MQTT driver interface that you created previously.

At the end of this section you will need to use a tool like MQTT Explorer to publish a value to your Tag.

Select the Tag that will source data from the MQTT data source.
Set the following properties:
- Data Type: JSON
- Data Source: MQTT
- Select Driver Interface: MQTT Data Source
- Topic: oas/temperature
Click on the Apply Changes button to apply the changes.
Using an MQTT testing tool to publish messages, such as MQTT Explorer, publish a JSON message to the oas/temperature topic.
```
{
    "temperature": 24.9
}
```
Check that the quality status is Good Quality and you can see the JSON value.

5 - Set Tag Alarm Limit

Select the Tag where you want to enable the alarm limit.
Select the High tab to open the high alarm limit configuration screen.
Configure the following setting to enable the high alarm limit:
- Tick the Enable Alarm checkbox
- Set the High Limit value to your desired value threshold for triggering the alarm
Optionally you can also configure:
- Alarm Text to customize the alarm text that will be shown. You can combine this with different Alarm Text Type settings in order to prepend, append, overwrite or replace the alarm text with a calculation.
- Group to categorize the alarm into a specific group. This can be used to filter alarms when configuring logging and notifications.
- Priority to order and apply a specific priority to each alarm.
- Time Delay which suppresses the alarm for the specified amount of time after it is triggered.
- Log As Event which means the alarm will only be recorded as a single instance without an acknowledgement state.

6 - Configure Alarm Logging

You will now configure alarm logging to a database.

Select Configure > Alarm Logging from the top menu.
Enter a meaningful Logging Group Name to give this alarm logging group a unique name. An alarm logging group is defined by a specified filter, OAS node list (default localhost) and database connection or CSV file configuration.
On the Common tab leave all the default values which will ensure the logging group is active.
On the Filters tab you can customize which alarms should be included in your logging. This includes filtering by:
- Alarm state
- Alarm priority
- Alarm type
- Alarm group
On the Nodes tab you can leave the default settings if you only want to include alarms generated on the local node. Once you add the alarm group the localhost node will be added to the list automatically.
On the Database tab you will configure the database provider and connection parameters:
You can choose from one of the following providers:
- SQL Server
- MS Access
- Oracle
- ODBC
- mySQL
- MongoDB
- Cassandra
- PostgreSQL
- MariaDB
- SQLite
Once you have selected the provider (for example SQL Server) you then need to fill out the required parameters such as database server, database name, table name and security credentials.
Click on the ADD GROUP button to add the alarm logging group. Once added, the alarm logging group name should appear in the list of logging groups.
Your alarm logging group is now active.

7 - Save Changes

Once you have successfully configured your OAS instances, make sure you save your configuration.

On each configuration page, click on the Save button.

If this is the first time you are saving the configuration, or if you are changing the name of the configuration file, OAS will ask you if you want to change the default configuration file.

If you select Yes then OAS will make this configuration file the default and if the OAS service is restarted then this file will be loaded on start-up.

If you select No then OAS will still save your configuration file, but it will not be the default file that is loaded on start-up.

Change Default Configuration Files dialog

Important

Each configuration screen has an independent configuration file except for the Tags and Drivers configurations, which share the same configuration file. It is still important to click on the Save button whenever you make any changes.

For more information see: Save and Load Configuration

Info

On Windows the configuration files are stored in C:\ProgramData\OpenAutomationSoftware\ConfigFiles.
On Linux the configuration files are stored in the ConfigFiles subfolder of the OAS installation path.

8 - More Information

To view the guide relevant to your data source and database provider use the selection tool below.

Step 1: Choose a Source