CODEWITHSUNDEEP
azurepowerbiazure-functionsazure-iot-hubazure-stream-analytics
Jay Yang
Jay Yang

Reputation: 89

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

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!

Upvotes: 0

Views: 515

Answers (2)

Jean-Sébastien
Jean-Sébastien

Reputation: 737

I noticed you use a double quote in the WHERE clause. You need a simple quote to get a match on strings. In this case it will be

WHERE deviceId = 'NodeMCU1'

If the deviceId is the one from IoT Hub metadata, Matthijs answer will help you to retrieve it.

Upvotes: 0

Matthijs van der Veer
Matthijs van der Veer

Reputation: 4085

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'

Upvotes: 1

Related Questions