How to read IoT Hub message 'application properties' in Stream Analytics?

Question

I have enriched the IoT Hub message based on Microsoft documentation and routing it to the built-in endpoint, and then having Stream Analytics access the messages by providing IoT Hub as the input to it.

IoT Hub message enrichment adds the enrichment data to the application properties of the message and not the body itself, so I'm having challenges trying to get that enrichment data from Stream Analytics - as the output of Stream Analytics is the Blob and it only contains the actual message I sent to IoT Hub.

Enrichment data here refers to certain data (like location, identity, etc.) that I'm mapping to the devices registered in IoT Hub based on device-twin properties.

I have tried the steps mentioned in GetMetadataPropertyValue and parsing JSON in Stream Analytics, but no luck in terms of getting the 'application properties' from Stream Analytics directly.

Could someone help me figure out how to access the application properties from Stream Analytics or at least point to the right resources?

Thank you.

docs.microsoft.com/en-us/stream-analytics-query/… This should work. Please show the code that you have tried that is not working for you — silent
Thanks for your response, but it didn't work. I'm attaching some screenshots. Screenshot_EnrichmentData - (i.stack.imgur.com/JIa1I.png) Screenshot_IotHub_EnrichMessages - (i.stack.imgur.com/X4fy5.png) Screenshot_SA_query_results - (i.stack.imgur.com/P3INQ.png) However, I can see the enrichment data as part of 'Properties' section in the file, when I do IoT Hub message routing to Blob. Unfortunately, this method cannot be used as body of the message in this file is encoded. Screenshot_data_stored_in_blob_after_enrichment - (i.stack.imgur.com/8XPVO.jpg) — Govind

Roman Kiss Roman Kiss · Accepted Answer · 2019-08-08T13:14:11

try the following in your query:

GetMetadataPropertyValue(iothub, '[User]') as userprops

your enrichment data will be in the userprops.

Example:

device telemetry data:

{"counter":29,"time":"2019-08-08T13:42:26.1517415Z","deviceId":"device1","windSpeed":8.2023,"temperature":16.06,"humidity":79.46}

publishing on topic:

devices/device1/messages/events/$.ct=application%2Fjson&$.ce=utf-8&abcd=1234567

IoT Hub Enrich messages:

ASA job:

select 
    *,
    GetMetadataPropertyValue(iothub, '[User]') as userprops 
into
    outAF
from 
    iothub

Output on the Azure Function (outAF):

[
  {
    "counter": 29,
    "time": "2019-08-08T13:42:26.1517415Z",
    "deviceId": "device1",
    "windSpeed": 8.2023,
    "temperature": 16.06,
    "humidity": 79.46,
    "EventProcessedUtcTime": "2019-08-08T13:42:25.7495769Z",
    "PartitionId": 1,
    "EventEnqueuedUtcTime": "2019-08-08T13:42:25.568Z",
    "IoTHub": {
      "MessageId": null,
      "CorrelationId": null,
      "ConnectionDeviceId": "device1",
      "ConnectionDeviceGenerationId": "636842046144267242",
      "EnqueuedTime": "2019-08-08T13:42:25.363Z",
      "StreamId": null
    },
    "User": {
      "abcd": "1234567",
      "status": "inprocess",
      "version": "42"
    },
    "userprops": {
      "abcd": "1234567",
      "status": "inprocess",
      "version": "42"
    }
  }
]

The following screen snippet shows an event message from a second custom endpoint for enrich messages such as the EventGrid:

{
  "id": "b983e8bf-88b5-cac3-9370-2c64037b2f1c",
  "topic": "/SUBSCRIPTIONS/00000000-0000-0000-0000-000000000000/RESOURCEGROUPS/myRG/PROVIDERS/MICROSOFT.DEVICES/IOTHUBS/myIOT",
  "subject": "devices/device1",
  "eventType": "Microsoft.Devices.DeviceTelemetry",
  "eventTime": "2019-08-08T13:42:25.363Z",
  "data": {
    "properties": {
      "abcd": "1234567",
      "status": "inprocess",
      "version": "42"
    },
    "systemProperties": {
      "iothub-content-type": "application/json",
      "iothub-content-encoding": "utf-8",
      "iothub-connection-device-id": "device1",
      "iothub-connection-auth-method": "{\"scope\":\"device\",\"type\":\"sas\",\"issuer\":\"iothub\",\"acceptingIpFilterRule\":null}",
      "iothub-connection-auth-generation-id": "636842046144267242",
      "iothub-enqueuedtime": "2019-08-08T13:42:25.363Z",
      "iothub-message-source": "Telemetry"
    },
    "body": {
      "counter": 29,
      "time": "2019-08-08T13:42:26.1517415Z",
      "deviceId": "device1",
      "windSpeed": 8.2023,
      "temperature": 16.06,
      "humidity": 79.46
    }
  },
  "dataVersion": "",
  "metadataVersion": "1"
}

How to read IoT Hub message 'application properties' in Stream Analytics?

2 Answers