Problem exposing GetMetadataPropertyValue in a streaming job with powerbi or blob storage outputs

Question

Hi,

I have a python script that sends device telemetry to iothub. I add a custom User/Application property called cpuAlert

****from azure.iot.device import IoTHubDeviceClient, Message, MethodResponse
message.content_encoding='utf-8'
message.content_type='application/json'
if cpu_percent > 80:
message.custom_properties["cpuAlert"] = "true"
else:
message.custom_properties["cpuAlert"] = "false"****

I've configured two routes, one for Container Storage, and one for 'streaming' . For the Storage route, I can view the json files in the Container, and see that the custom property is identified.

{"EnqueuedTimeUtc":"2021-07-12T14:42:40.3050000Z","Properties":{"cpuAlert":"false"} ......

I then created a streaming input/output job, with sql as follows, with an output for powerbi or blob storage.

SELECT Timestamp,cpu_percent, GetMetadataPropertyValue(iotInput, '[User].[cpuAlert]') as cpuAlert
INTO
newiotpowerbi
FROM
iotInput

but in Powerbi or blob storage,, the cpuAlert colums just contains cpuAlert, for each message/event. I must be missing something very obvious.

thanks

Mike

Answer 1

Hello @mpazure ,

I tried to recreate the problem.

I tested your setup with this Python code:

print("Sending message...")
jsonText = {"temperature": 42}
message = Message(json.dumps(jsonText))
message.content_encoding='utf-8'
message.content_type='application/json'
message.custom_properties["cpuAlert"] = "true"
message.custom_properties["value"] = "1"
await device_client.send_message(message)

The messages were picked up by an IoT Hub and sent to a Stream Analytics job.

I added some lines seen in the GetMetadataPropertyValue documentation.

SELECT
GetMetadataPropertyValue(iothubinput, '[User].[cpuAlert]') as cpuAlert
,GetMetadataPropertyValue(iothubinput, '[User]') as userprops
, *
INTO
eventhuboutput
FROM
iothubinput

While testing the query in the azure portal query editor, indeed no valid output is seen for the parameters:

(if you record input messages, it seems the properties are left out in the recording?)

But, in the Event Hub output, the properties are now part of the outputted messages:

Can you reproduce the same solution?