How to Separate Data from Multiple Devices on Microsoft Azure Stream Analytics

Question

I am currently trying to connect 2 different devices to the IoT Hub, and I need to separate the data from each device. In order to do so, I tried configuring my stream analytics query like this:

SELECT
    deviceId, temperature, humidity, CAST(iothub.EnqueuedTime AS datetime) AS event_date
INTO
    NodeMCUOutput
FROM
    iothubevents
WHERE
    deviceId = "NodeMCU1"

However, for some reason, the output is not shown if the WHERE statement is in the code (the outputs are shown without it, but the data is not filtered). I need the WHERE statement in order to sort the data the way I want it. Am I missing something? Are there any solutions to this? Thanks a lot. Cheers!

Answer 1

The device ID and other properties that are not in the message itself are included as metadata on the message. You can read that metadata using the GetMetadataPropertyValue() function. This should work for you:

SELECT
    GetMetadataPropertyValue(iothubevents, 'IoTHub.ConnectionDeviceId') as deviceId, 
    temperature, 
    humidity, 
    CAST(GetMetadataPropertyValue(iothubevents, 'IoTHub.EnqueuedTime') AS datetime) AS event_date
INTO
    NodeMCUOutput
FROM
    iothubevents
WHERE 
    GetMetadataPropertyValue(iothubevents, 'IoTHub.ConnectionDeviceId') = 'NodeMCU1'