3

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.

Govind
  • 31
  • 2
  • https://learn.microsoft.com/en-us/stream-analytics-query/getmetadatapropertyvalue#user-properties This should work. Please show the code that you have tried that is not working for you – silent Aug 08 '19 at 12:41
  • Thanks for your response, but it didn't work. I'm attaching some screenshots. Screenshot_EnrichmentData - (https://i.stack.imgur.com/JIa1I.png) Screenshot_IotHub_EnrichMessages - (https://i.stack.imgur.com/X4fy5.png) Screenshot_SA_query_results - (https://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 - (https://i.stack.imgur.com/8XPVO.jpg) – Govind Aug 11 '19 at 20:21

2 Answers2

3

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: enter image description here

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"
}
Roman Kiss
  • 7,925
  • 1
  • 8
  • 21
  • No, it didn't work. I'm attaching some screenshots. Screenshot_EnrichmentData - (https://i.stack.imgur.com/JIa1I.png) Screenshot_IotHub_EnrichMessages - (https://i.stack.imgur.com/X4fy5.png) Screenshot_SA_query_results - (https://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 - (https://i.stack.imgur.com/8XPVO.jpg) – Govind Aug 11 '19 at 20:18
  • I tried again but used Blob as output for SA job and it worked!! So basically it works when I actually run the SA job and use an outputs. But when I just tried to test the Query without actually “running” the SA job, the “userprops” is returned as null while all the other parts of data seems to be fine. Any idea why this might be happening? Also, how did you output your SA job to Function App? I only see Blob and SQL as the output options for SA job from the Azure portal. – Govind Aug 11 '19 at 20:50
  • the https://learn.microsoft.com/en-us/stream-analytics-query/getmetadatapropertyvalue#adapter-metadata-properties describes: *This function cannot be tested on the Azure portal using sample data.*, for the SA job outputs, see the following https://learn.microsoft.com/en-us/azure/stream-analytics/stream-analytics-define-outputs – Roman Kiss Aug 12 '19 at 06:09
0

Did you check GetRecordPropertyValue as explained here http://learniotwithzain.com/2019/08/alert-engine-using-azure-stream-analytics-and-sql-azure-as-reference-data/

Zainu
  • 120
  • 1
  • 7
  • Yes I did, but that does not seem to work. Also, I'm trying not to use "Reference Data" in Stream Analytics as that input is limited to only 300MB, which I suspect would pose an issue for me in the future. – Govind Aug 11 '19 at 20:27