Import end devices from CSV

[jpmeijers]

Summary

It looks like this was available before (https://github.com/TheThingsNetwork/lorawan-stack/pull/1448) but now only json is supported.

When we buy off-the-shelf LoRaWAN sensor, the vendors normally send us an Excel file with the DevEUI, AppEUI, AppKey, etc. A file like this can contain hundreds of entries, and I need to register these devices in TTS. The current (planned) solution is to write a python script to iterate through the Excel-csv, and call ttn-lw-cli for each one to register the device.

It would be nicer if TTS Console allowed importing devices directly from CSV. After uploading the csv, one would need to choose which columns contain which fields, and then enter default values for the fields that do not exist in the csv. A pre-defined format/column order would also be acceptable as Excel allows easily swapping columns. For default values one could fill the column with the default value in Excel.

I see TTS has a templating feature, but this seems too complicated and I'm not sure this is exactly what I need.

Why do we need this?

Make registering a batch of devices from a vendor provided Excel file easier.

What is already there? What do you see now?

I can import a batch of devices, but the imported file has to be in TTS JSON format.

What is missing? What do you want to see?

Also have a CSV import file format, with the ability to choose which columns contain which values.

Environment

TTS v3.17

How do you propose to implement this?

Purely on the console using Javascript. But maybe it can also be a ttn-lw-cli feature, but needs to be documented then.

How do you propose to test this?

Import a csv file that contains DevEUI, AppEUI, AppKey values.

Can you do this yourself and submit a Pull Request?

No

[NicolasMrad]

Thank you @jpmeijers for submitting this feature request. Can you please share with us a sample CSV template that you usually use, with the various fields required?

[jpmeijers]

Here are two examples from two different water meter vendors.

LORAWAN PARAMETER - Bove.xlsx Lora Axioma.xlsx

[htdvisser]

The information in these files alone is not enough to register those end devices. We'd still need to know things like LoRaWAN MAC/PHY version, possibly defaults, etc.

We should probably combine manifest import functionality with the device repository. That way we can also build functionality that understands the format of the manifest of a specific vendor. And we can be certain that those devices work correctly (we don't want someone to import 1000 of sensors only to find out that they don't work).

For the first sensor, we could define the manifest format as:

manifest:
  parser: csv # Perhaps we can even support xlsx?
  fields:
    dev_id: DeviceId # maybe this should be case-insensitive
    dev_eui: DeviceId
    join_eui: appeui
    app_key: appkey
    attributes: [ ProductName, MeterId ]

For the second sensor, it would be something like:

manifest:
  parser: csv
  fields:
    dev_id: [ Custom identifier, Serial no ] # Alternative idea: specify a list and select the first field that exists.
    dev_eui: Dev EUI
    join_eui: App EUI
    nwk_key: Lora network key
    app_key: Lora app key
    dev_addr: Lora device address
    nwk_s_key: Radio encryption key # ?
    app_s_key: Lora app s key
    attributes: [ ... ]

Then in the Console, a user would select the device vendor and model, and then instead of entering the details of a single device, upload the manifest file. The server (Device Claiming Server? End Device Template Converter?) then parses the manifest file and returns the list of end devices about to be registered. The Console can then show the user a nice table and ask for confirmation, after which the registration process starts.

[jpmeijers]

We'd still need to know things like LoRaWAN MAC/PHY version, possibly defaults, etc.

Yes indeed. I was thinking of having a "fallback values" form on the same page where I upload the csv/xlsx.

We should probably combine manifest import functionality with the device repository.

Yes, but not limited to this, as many of these sensors do not exist in the device repo. And I do not want to have to convince the (Chinese) vendor to create a PR on TTS device repo just to allow me import my manifest file.

I'm also concerned of cases where the vedor changes their xlsx schema.

The Console can then show the user a nice table and ask for confirmation, after which the registration process starts.

Great idea!

[NicolasMrad]

some discussions here: https://discuss.thethingsindustries.com/t/device-import-via-csv/801

[johanstokking]

Like @htdvisser I think conversion of a vendor-specific manifest belongs in the device repository. I don't think it should be a static mapping, I think that vendors should be able to define one or more manifest converters as JavaScript functions to convert a line to a simple JSON object. Like payload codecs basically. That function can be a simple mapping but it can also do some basic conversion (especially of MAC and PHY versions).

The Device Template Converter (DTC) feeds the lines of a csv, tsv, xlsx file to that manifest converter (as key/value map of headers and fields) and returns the end device template with the fields set in the manifest. This can be anything: EUIs, keys, claim authentication code, etc. The client (Console, CLI) then applies the selected end device version identifiers (brand, model, hardware and firmware version) and frequency plan to do the bulk create. This then applies the device settings in the device repository that are not in the manifest to complete the template.

If the resulting end device template contains a claim authentication code, the claiming is carried out by Device Claiming Server (DCS).

References #4847

[jpmeijers]

I like your ideas. But I still have one concern: What happens if I have a CSV/Excel file for devices that do not exist in the device repository?

[johanstokking]

Do you mean a CSV/Excel file that you received from a random device maker, as is? Or would you like to be able to import a CSV file according to a fixed The Things Stack format? The latter we can do, just like we support importing devices in The Things Stack JSON format already.

[jpmeijers]

Do you mean a CSV/Excel file that you received from a random device maker, as is?

Yes

But because it is in Excel one can easily swap columns or add columns to make it look like a "CSV file according to a fixed The Things Stack format".

So I would be happy with the latter.

[johanstokking]

There are two categories of required fields:

Per-device	General
JoinEUI	LoRaWAN (MAC) version
DevEUI	Regional Parameters (PHY) version
AppKey	Frequency plan
NwkKey

What I would suggest, is that we have inputs for the right category that act as a fallback. If the converted file does not contain those fields (lorawan_version, lorawan_phy_version, frequency_plan_id respectively), set them from the selected fallback.

This would be applicable for all device template converters, including JSON and anything we add.

This allows users to use files that only contain per-device settings and so they don't bother with exact input of other fields.

@kschiffer how hard is this?

[kschiffer]

This would be unproblematic.

[johanstokking]

OK. I'll start with a simple DTC based on CSV.

@kschiffer do you want to work in parallel on these input controls in the bulk import screen? Or instruct @ryaplots?

[kschiffer]

This is a good issue for @ryaplots.

What we need here is the following:

• Add fallback value fields to the device import form, as described above by @johanstokking, use a <Form.SubTitle /> with Fallback values above these fields.
• Upon submit, apply the fallback values if the converted templates don't already have the respective value set
• The fallback values are not required, but the import will error out devices that do not have all required fields set
• Update the existing end-to-end tests to verify that fallback values work