How to Log AWS IoT Core Data to Oracle

Open Automation Software can be configured to connect to AWS IoT Core using the AWS IoT Core connector and log the data to an Oracle database. This guide walks you through downloading and installing OAS, configuring an AWS IoT Core data source connector, configuring tags and logging them to an Oracle database.

For this guide on how to log AWS IoT Core data to Oracle you will need:

• An AWS account with access to AWS IoT Core
• An Oracle database and user account with permission to create tables, procedures, indexes and read and write data

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:

• Windows Installation
• Linux Installation
• Raspberry Pi Installation
• Docker Installation

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

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

2 - Configure OAS

Web Configuration Application

Configuration of the OAS server is performed from the built-in Web Configuration Application. This is the main application for accessing any settings within the OAS server and is completely self-contained within the server.

You can reach this by opening your browser to:

http://<your server>:58725/app/config

If you are browsing from the OAS machine itself, you can use localhost, otherwise use the IP address of the machine. You will also need to ensure port 58725 is open on both the client and the server or you may not be able to reach the application.

On a new installation the OAS engine has no accounts yet, so the first screen you see is Create Admin Account. Once the administrator account exists that screen is not shown again, and browsing to the application displays the sign in screen instead.

1. Enter a name for the administrator in the Admin user name field. Typically you would use admin, but you can use whatever name you choose.

2. Enter a password in the Password field, then enter the same password again in the Confirm password field.

3. Click on the Create admin & sign in button. The account is created and you are signed in to the engine.

To sign in from then on, enter the Network node of the OAS engine, then the Username and Password of your account, and click on the Sign in button.

Legacy Configuration Application

1. From your operating system start menu, open the Configure OAS application.

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

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

   

   

3 - Create Subscriber Thing in AWS IoT Core

In this step you will create a Thing in the AWS IoT Core service and the required certificate and policies for subscribing to messages sent by AWS IoT Core. This represents your connection and security settings between AWS IoT Core and OAS.

1. Login to the AWS Console and select the AWS IoT service.

2. Under the Manage and All Devices menu select Things.

3. Click on the Create things button to start the create new things wizard.

4. Select Create single thing and click on the Next button.

   

5. Set the Thing name to OAS_Subscriber and click on the Next button.

   

6. Leave the default option to generate device certificate automatically and click on the Next button.

   

7. Select the Create policy button. This will open a new browser tab or window.

8. Set the Policy name to OASSubscriberPolicy. The policy will need to allow the iot:Connect and iot:Subscribe actions. For the purpose of this guide the policy will allow all resources using the * wildcard.

   

   Important

   For security best practices in production systems you should always restrict your policy to the client ID and AWS resource name (ARN) that represents your region, account and topic paths.

9. Go back to the create thing wizard and select the OASSubscriberPolicy. Click on the Create thing button.

10. You will see a window relating to certificates. Download the device certificate, the public key file, the private key file and one of the Root CA certificates. You should keep these files in a folder with limited permissions. You'll need them when configuring the AWS IoT Core subscriber in the OAS platform.

    

4 - Configure AWS IoT Core Subscriber

In the following steps you will create and configure an AWS IoT Core Subscriber for subscribing to tag values.

1. To determine the AWS IoT Core endpoint you will need to login to the AWS console and select the AWS IoT service. Select the Domain configurations menu.

2. If you don't already have a domain name use the Create domain configuration button to create one. You will need to take a note of the Domain name property, which represents your AWS IoT Core broker endpoint.

3. Select Drivers in the sidebar.

4. Click + Add, enter a meaningful Driver Interface Name to give this driver interface instance a unique name (for example AWS IoT Subscriber), and click Create. Once created, the driver interface name should appear in the list of drivers.

5. Ensure the following parameters are configured:

   • Driver: AWS IoT Gateway
   • Broker Port: 8883
   • Create Certificate: Turn this on if running Windows
   • Client Certificate File: The device certificate pem.crt file from the previous section
   • Client Private Key File: The private pem.key file from the previous section
   • Server Certificate File: The Root CA pem file from the previous section
   • Client ID: OASSubscriber
   • IoT End Point: This is your AWS Iot Core endpoint from step 2 above

   Info

   If you are using Linux, turn Create Certificate off and use PFX certificates instead. The form then asks for a Client PFX Certificate File, a Client PFX Certificate Password, a Server PFX Certificate File and a Server PFX Certificate Password.

   You can generate both PFX files using OpenSSL. The client certificate holds the device certificate and its private key:

   openssl pkcs12 -export \
       -out oas-connection-certificate.pfx \
       -inkey 2ed57ff8e30d1a12345f69bc2a8a6b4a1721b123456789912e675cc74111ced7-private.pem.key \
       -in 2ed57ff8e30d1a12345f69bc2a8a6b4a1721b123456789912e675cc74111ced7-certificate.pem.crt \
       -certfile AmazonRootCA1.pem
   

   The server certificate holds the Amazon Root CA, which has no private key of its own:

   openssl pkcs12 -export -nokeys -out oas-server-certificate.pfx -in AmazonRootCA1.pem
   

   Enter the path to each file in the matching PFX Certificate File field and the export password you chose in the matching PFX Certificate Password field. Leave a password field empty if you exported that certificate without one.

6. Turn on Enable and click on the Apply changes button. The driver interface is not active until it is enabled and the changes are applied.

   

5 - Assign AWS IoT Gateway as Tag Data Source

You will now set the Tag's data source to the AWS IoT Subscriber interface that you created previously.

1. Select the Tag that will source data from the AWS IoT Core data source.

   

2. Set the following properties:

   • Data Source: AWS IoT Gateway
   • Select Driver Interface: AWS IoT Subscriber
   • Topic: oas/temperature

   

3. Click on the Apply changes button to apply the changes.

4. Login to the AWS Console and select the AWS IoT service.

5. Under the Test menu select MQTT test client and then select the Publish to a topic tab.

6. In the Topic name specify oas/temperature.

7. In the Message payload enter a value and then click on the Publish button.

   

8. Check that the quality status is Good and you can see the value.

   

6 - Configure Data Logging

You will now configure data logging to an Oracle database.

1. Select Data Logging in the sidebar.

2. Click + Add, enter a meaningful Logging Group Name to give this data logging group a unique name, and click Create. Once created, the data logging group name should appear in the list of logging groups. A data logging group is simply a data logging configuration for a set of Tags.

3. In the Common section leave all the default values. This will create a configuration that uses Continuous logging with a 1 second Logging Rate. This simply means, log data at a rate of 1 second.

   

4. In the Tags section you will add all of the Tags that you want to log. Each Tag will be a field (column) in the database. Click on the Add button to bring up the Browse for Tag window.

5. Select the Tag you want to add and then click on the Use this tag button.

   

   Tips

   Each Tag has dozens of properties that are available for you to access. The Value property is selected for you, shown at the bottom of the window as TagName.Value. It is the most commonly used property as it presents the Tag's current value. This is what we want to log to the database.

6. The Database tag window will appear. This is where you set the database field name (column name) and the database data type that will be used. The most common types will be Double Float, Boolean, Integer and String.

   The Field name is filled in for you from the Tag name. Let's replace it with Temperature. Now click on the OK button.

   

7. The Tag will appear in the list of Tags to be logged.

   

8. In the Database section you will configure the database type and connection parameters:

   • Turn on Log to Database to enable logging to a database
   • Set Provider to Oracle
   • Set Server to your Oracle server (e.g., localhost)
   • Set Database to oas_logging
   • Set Table to a table name such as Temperatures
   • Set User Name to a user with permissions to manage the oas_logging database
   • Set Password to the user password

   

9. Return to the Common section and turn on Logging Active. New logging groups are created inactive, so this is what starts the group logging.

   

10. Click on the Apply changes button to apply the data logging group configuration.

    

11. Your database 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 that has a Save button, click on it.

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.