question

randomrabbit avatar image
0 Votes"
randomrabbit asked asergaz commented

IoT Central export to ADX - table format

I'm having trouble exporting data from IoT Central into Azure Data Explorer (ADX). The reason is that my IoT devices send telemetry with a range of values. For example these are valid messages

 {
  "deviceId": 1,
  "data": {"temperature": 10}
 }
    
 {
  "deviceId": 2,
  "data": {"humidity": 40}
 }

I can export to ADX if I create a table with a dynamic data field - then it will contain the entire "data". But I'd like to have columns for temperature and humidity. So I create a table as

 .create table iot (deviceId:string, temperature:real, humidity:real)

but it will not be populated by the data export.

I'm using a transform in IoT Central export:


{
deviceId .device.id,
timestamp: .enqueuedTime,
temperature: .data[] | select(.name=="temperature") | .value,
humidity: .data[] | select(.name=="humidity") | .value,
}

and the preview shows it picks those values correctly from my template json. I'd expect ADX to have nulls in the column for which there is no data in a given message. So how to make the export work?

btw I'm aware ingestion mappings could be useful but I could not figure out how to make them run when using IoT Central, since I cannot connect to a Hub source..

azure-iot-centralazure-data-explorer
· 7
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hello @randomrabbit ,
I am reaching out to SMEs on Azure Data Explorer to help understand how to overcome this challenge.

Thanks!

0 Votes 0 ·

I think the issue is with my transformation on iotc. The export never fires.

1 Vote 1 ·

Hello @randomrabbit ,
From the documentation here: Pre-transformation message structure we can read that

You can export the following streams of data from IoT Central: telemetry, property changes, device connectivity events, device lifecycle events, and device template lifecycle events.


I notice you are trying to export your values within the data JSON object. You didn't share the entire JSON but I am assuming that those values are inside the telemetry array ? If so please can you try the following:

 import "iotc" as iotc;
 {
   deviceId: .device.id,
   Timestamp: .enqueuedTime,
   temperature: .telemetry | iotc::find(.name == "temperature").value,
   humidity: .telemetry | iotc::find(.name == "humidity").value,
 }

Thank you!




0 Votes 0 ·
Show more comments

0 Answers