Simulating CSV Data in Cumulocity IoT - Two Approaches

Knowledge base
, , , , , , ,
1

Introduction

This article is broken up into two parts, one for each approach for pulling CSV data into Cumulocity IoT. The first approach is technical and hands-on, while the second approach is more automated and easy to use but less flexible.

The first part of this article is meant to guide users on how to convert CSV sensor data into Cumulocity IoT measurements by using Cumulocity IoT’s OpenAPI specification. The steps described here should be usable across a wide range of CSV formats, as long as you understand how your sensor data correlates to Cumulocity IoT’s domain model .

The second part of this article takes you through a much easier but less flexible approach using a tool from our open-source community called Application Builder , which includes a CSV simulator capability in the UI that can ingest very specific measurement data.

Pre-requisite

First approach

Cumulocity IoT tenant (free trial works), a graphical API tool like Postman, and some scripting knowledge

Second approach

Cumulocity IoT tenant (free trial works) with Application Builder deployed on the tenant as well as the csv-simulator-ms microservice

Detailed installation instructions for Application Builder and the CSV microservice can be found here

Approach 1 – CSV Simulator from Scratch

CSV Format

Let’s get started with an example CSV that includes 4 devices spread across 2 groups, each reporting a combination of pressure, temperature, and vibration from their associated motor sensors. We’re also sending a unit for each measurement as well as a timestamp of when the measurement took place, which helps to create an accurate simulation of events that occurred in the past.

GroupName DeviceName MeasurementName MeasurementSeries MeasurementValue Unit Timestamp
Group1 Device1 MotorSensors Pressure 123.45 Pa 2023-07-12T06:00:00.000Z
Group1 Device2 MotorSensors Temperature 12 C 2023-07-12T07:00:00.000Z
Group1 Device2 MotorSensors Temperature 13 C 2023-07-12T07:00:00.000Z
Group2 Device3 MotorSensors Pressure 124.35 Pa 2023-07-12T06:32:12.000Z
Group2 Device3 MotorSensors Pressure 13 Pa 2023-07-12T06:32:34.000Z
Group2 Device4 MotorSensors Vibration 2 Hz 2023-07-12T06:12:23.000Z

In true CSV form, it would look like this.

GroupName,DeviceName,MeasurementName,MeasurementSeries,MeasurementValue,Unit,Timestamp
Group1,Device1,MotorSensors,Pressure,123.45,Pa,2023-07-12T06:00:00.000Z
Group1,Device2,MotorSensors,Temperature,12,C,2023-07-12T07:00:00.000Z
Group1,Device2,MotorSensors,Temperature,13,C,2023-07-12T07:00:00.000Z
Group2,Device3,MotorSensors,Pressure,124.35,Pa,2023-07-12T06:32:12.000Z
Group2,Device3,MotorSensors,Pressure,13,Pa,2023-07-12T06:32:34.000Z
Group2,Device4,MotorSensors,Vibration,2,Hz,2023-07-12T06:12:23.000Z

CSV Simulation Process

The simulation process is a great way to get familiar with Cumulocity IoT’s API for inventory and measurements. I recommend trying this out in Postman using either our provided Postman collection or by importing our OpenAPI specification into Postman.

The steps below will list each API URL, what information you need to provide, and what you should keep track of along the way. In your code, you should be iterating through each row, and for each row, each of the following steps should be executed, while keeping track of which groups and devices you have already created and what their IDs are.

Step 1 – Create a group if it doesn’t exist

We’ll start by checking to see if the group is already created.

GET {{CumulocityURL}}/inventory/managedObjects?query=$filter=(name eq '{{GroupName}}')

If the group does exist, save the ID from the return payload and skip to Step 2.
If the group does not exist, use the following API call to create it.

POST {{CumulocityURL}}/inventory/managedObjects

{
    "name": "{{GroupName}}",
    "type": "c8y_DeviceGroup",
    "c8y_IsDeviceGroup": {}
}

Response for either API call should include an id which is used in future calls and referred to as {{GroupID}}
Example response can be found here: https://cumulocity.com/api/core/#operation/getManagedObjectResource

Step 2 – Create a device if it doesn’t exist

Now let’s check to see if the device is already created.

GET {{CumulocityURL}}/inventory/managedObjects?query=$filter=(name eq '{{DeviceName}}')

If the device does exist, save the ID from the return payload and skip to Step 4 .
If the device does not exist, use the following API call to create it.

POST {{CumulocityURL}}/inventory/managedObjects

{
    "name": "{{DeviceName}}",
    "type": "c8y_SimulationDevice",
    "c8y_IsDevice": {}
}

Response for either API call should include an id which is used in future calls and referred to as {{DeviceID}}
Example response can be found here: https://cumulocity.com/api/core/#operation/getManagedObjectResource

Step 3 – Place device in group

Now the device is created, but it doesn’t belong to any group. Let’s place it in the correct group with the following API.

POST {{CumulocityURL}}/inventory/managedObjects/{{GroupID}}/childAssets

{
    "references": [
        {
            "managedObject": {
                "id": "{{DeviceID}}"
            }
        }
    ]
}

Example response can be found here: https://cumulocity.com/api/core/#operation/postManagedObjectChildAssetsResource

Step 4 – Create measurement on device

Now your group is created, device is create, and device is put in the correct group. It’s finally time to send the measurement using the rest of the CSV information in the current row.

POST {{CumulocityURL}}/measurement/measurements

{
    "time": "{{TimeStamp}}",
    "source": {
        "id": "{{DeviceID}}"
    },
    "{{MeasurementName}}": {
        "{{MeasurementSeries}}": {
            "value": {{MeasurementValue}},
            "unit": "{{Unit}}"
        }
    },
    "type": "{{MeasurementName}}"
}

Next Steps

Continue iterating through each row of the CSV and repeat these steps for each row. If you already know you’ve created that group, created the device, and put the device in the right group, you can skip straight to Step 4 and send the measurement right away.

Try this out in Postman and then build it into an automated script using any programming language, so that you can re-run it whenever needed.

Approach 2 – Application Builder Simulator

CSV Format

Let’s get started with an example CSV that simulates 1 device, which can either be a brand new device or an existing one. From this CSV, we’ll be reporting a combination of pressure, temperature, and vibration from their associated motor sensors. We’re also sending a unit for each measurement as well as a timestamp of when the measurement took place.

MeasurementFragment Measurement1Series Measurement2Series Measurement3Series Measurement1Value Measurement2Value Measurement3Value Measurement1Unit Measurement2Unit Measurement3Unit Timestamp
MotorSensors Temperature Pressure Vibration 1 1 1 C Pa Hz 2023-07-12T06:00:00.000Z
MotorSensors Temperature Pressure Vibration 2 2 2 C Pa Hz 2023-07-12T06:00:01.000Z
MotorSensors Temperature Pressure Vibration 3 3 3 C Pa Hz 2023-07-12T06:00:02.000Z
MotorSensors Temperature Pressure Vibration 4 4 4 C Pa Hz 2023-07-12T06:00:03.000Z
MotorSensors Temperature Pressure Vibration 5 5 5 C Pa Hz 2023-07-12T06:00:04.000Z
MotorSensors Temperature Pressure Vibration 6 6 6 C Pa Hz 2023-07-12T06:00:05.000Z
MotorSensors Temperature Pressure Vibration 7 7 7 C Pa Hz 2023-07-12T06:00:06.000Z
MotorSensors Temperature Pressure Vibration 8 8 8 C Pa Hz 2023-07-12T06:00:07.000Z
MotorSensors Temperature Pressure Vibration 9 9 9 C Pa Hz 2023-07-12T06:00:08.000Z
MotorSensors Temperature Pressure Vibration 10 10 10 C Pa Hz 2023-07-12T06:00:09.000Z

In true CSV form, it would look like this.

MeasurementFragment,Measurement1Series,Measurement2Series,Measurement3Series,Measurement1Value,Measurement2Value,Measurement3Value,Measurement1Unit,Measurement2Unit,Measurement3Unit,Timestamp
MotorSensors,Temperature,Pressure,Vibration,1,1,1,C,Pa,Hz,2023-07-12T06:00:00.000Z
MotorSensors,Temperature,Pressure,Vibration,2,2,2,C,Pa,Hz,2023-07-12T06:00:01.000Z
MotorSensors,Temperature,Pressure,Vibration,3,3,3,C,Pa,Hz,2023-07-12T06:00:02.000Z
MotorSensors,Temperature,Pressure,Vibration,4,4,4,C,Pa,Hz,2023-07-12T06:00:03.000Z
MotorSensors,Temperature,Pressure,Vibration,5,5,5,C,Pa,Hz,2023-07-12T06:00:04.000Z
MotorSensors,Temperature,Pressure,Vibration,6,6,6,C,Pa,Hz,2023-07-12T06:00:05.000Z
MotorSensors,Temperature,Pressure,Vibration,7,7,7,C,Pa,Hz,2023-07-12T06:00:06.000Z
MotorSensors,Temperature,Pressure,Vibration,8,8,8,C,Pa,Hz,2023-07-12T06:00:07.000Z
MotorSensors,Temperature,Pressure,Vibration,9,9,9,C,Pa,Hz,2023-07-12T06:00:08.000Z
MotorSensors,Temperature,Pressure,Vibration,10,10,10,C,Pa,Hz,2023-07-12T06:00:09.000Z

When modifying this CSV for your use case, it’s important to note that each measurement series column needs a corresponding measurement value column and a corresponding measurement unit column

CSV Simulation Process

The simulation process is a great way to get familiar with Application Builder’s simulation capabilities. There are many other functions beyond just CSV simulation, including simulating random values, sine waves, square waves, DTDL, and more.

To get started, open Application Builder and create a new application. I named mine Device Simulators, since it is where I consolidate all of the simulators I use on my tenant.

image
image527×753 28.5 KB

Open your application, navigate to Configuration → Simulator, and click on Add Simulator.

image
image529×790 28.2 KB

Here we’ll choose the File (CSV/JSON) option, select New Device, and then select our formatted CSV. Fill out the fields as shown in this screenshot.

image
image743×789 51.1 KB

Form Descriptions

  • Name - The name of your new device
  • Column Header - Gives you the option to either use a column header or have it pre-generated with default values (I recommend including a column header)
  • Category - Type of data being sent, in this case we’re just focusing on measurements (i.e., numeric data)
  • Simulation Type - Sequential will go through the rows in order, Random will randomize the row order, and +Steps will skip a certain amount of rows every iteration
  • Interval Type - Original indicates that you want to use a timestamp column, Fixed will use the current time
  • Run in a Loop - Loop back to the beginning of the file and simulate the values again
  • Loop Delay - Amount of time before the next loop is run
  • Select Type - The type of measurement that each measurement series belongs to
  • Select Fragment - The fragment which represents all of your measurement series (I recommend making this the same as your measurement type)
  • Interval - Delay between each row of data being simulated
  • Series - CSV column with the different measurements/sensors that have associated values and units
  • Value - CSV column with the numeric value of the measurement series
  • Unit - CSV column with the unit of the measurement series
  • Select Timestamp - CSV column with the timestamp of the measurement series (only if Interval Type = Original)

Once you fill out the form and click Save, activate the simulator by clicking on the slider.

image

If everything is set up correctly, you’ll see measurements start to come into your new device!

image
image1428×641 39.9 KB

Next Steps

Play around with different CSV simulation setups, try using events instead of measurement, add many new sensor types, or different Application Builder simulators altogether. There are many other ways to pull values into these simulators.

6 Likes
2

Really good article. I propose to mention a third option using c8y tool. This has a specific util repeatcsv to work with data in cvs format.

1 Like