How to filter Azure stream analytics input data from different IoT devices and process them to PowerBI

0
votes

I have 3 devices configured on my Azure IoT hub. All of them send the same kind of data (temp, humidity, light and their deviceId). I use this IoT hub as input in Azure stream analytics. Then it should be filtered based on the deviceId via a query. I use PowerBI as output. In PowerBI I would like to make a line chart showing the temperature of the three devices. Currently, I use a PowerBI output for each device, which creates three datasets in PowerBI and unfortunately it is not possible to combine datasets.

Any suggestions?

For now I use this query

WITH outputSander as(
    SELECT temp,deviceId,System.TIMESTAMP as time
    from Sensordata
    where deviceId = 'Raspberry_Pi_Sander'),
outputKoen as(
    SELECT temp,deviceId,System.TIMESTAMP as time
    from Sensordata
    where deviceId = 'Raspberry_Pi_Koen')
--outputLukas as(
  --  SELECT temp,deviceId,System.TIMESTAMP as time
   -- from Sensordata
   -- where deviceId = 'Raspberry_Pi_Lukas')
    select s1, s2
INTO
    [outputData]
FROM
    outputSander s1 LEFT JOIN outputKoen s2 on(DATEDIFF(s, s1, s2) BETWEEN 0 AND 5)

As output I would like to see something like this:

S1.temp S1.deviceId, S1.time, S2.temp, S2.deviceId, S2.time with their correct values respectively.

1
azureiotpowerbi
Why are you using a complex query? If you have only 3 devices connected to the same IoT Hub, you could use something like Select <your options> into <output> from <input> You'll get the output in the same dataset in PowerBI where you could plot a line graph of temp vs time. – Saurabh Kirtani

1 Answers

0
votes

you can use JOIN clause to join multiple streams into one and direct it to the one output. In your case, there can be an extra field that can be used for a join.

https://blogs.technet.microsoft.com/machinelearning/2015/06/01/the-azure-stream-analytics-query-language/