Stream analytics Query 2 iot devices

2022-01-03T11:59:26.437+00:00

Hi Community

I got two ioT devices (MxChip) connected to stream analytics (input from Iothub and output PBI). I am using the following query in stream analytics:

´´´´
SELECT
*

INTO
outputpbi

FROM
inputiothub

´´´´´

But how do I send data from more then one device to PBI ? I have already connected another device to the Iothub, but im not sure how to write the query in stream analytics. maybe something like:

`
SELECT 
PartitionId as deviceId,
temperature,
humidity,
pressure

INTO
    outputpbi

FROM
    inputiothub

WHERE
    GetMetadataPropertyValue(inputiothub, 'IoTHub.ConnectionDeviceId') = 'jrnh-dev'

SELECT 
PartitionId as deviceId,
temperature,
humidity,
pressure

INTO
    outputpbi-dev2

FROM
    inputiothub

WHERE
    GetMetadataPropertyValue(inputiothub, 'IoTHub.ConnectionDeviceId') = 'claes-dev'

`

Azure Stream Analytics
Azure Stream Analytics
An Azure real-time analytics service designed for mission-critical workloads.
376 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Sander van de Velde | MVP 34,686 Reputation points MVP
    2022-01-03T14:32:47.61+00:00

    Hello @JakobRasmusNrgaardHansenJRNHDK-3596 ,

    you want to support multiple devices using a Azure Stream Analytics job sending data to Power BI.

    Telemetry messages from the Azure IoT Hub contain IoT Hub related properties including the deviceId.

    You already found out the GetMetadataPropertyValue function that gives access to the property bag.

    The solution you show is not that scalable. Every time a device is added, you have to change the job.

    This could be fixed by making the device ID part of the columns outputted:

    SELECT   
        GetMetadataPropertyValue(inputiothub, 'IoTHub.ConnectionDeviceId') as deviceId,  
        temperature,  
        humidity,  
        pressure,  
        timestamp -- some datetime value?  
    INTO  
        outputpbi  
    FROM  
        inputiothub  
    

    I'm not sure what the value of the partitionId is for you, so I reused the same name. And I added a column representing the date and time.

    Now you can filter on specific devices in powerBI while the job keeps supporting any new deviceId.

    One step further is to group devices using the device twin tags and enriching IoT Hub messages with these tags.

    Using this structure, you are able to make subgroups of devices and either aggregate in Azure Stream Analytics or Power BI.

    Now you are in full control of what telemetry is sent to PowerBI.

    1 person found this answer helpful.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.