CODEWITHSUNDEEP
azureiotpowerbi
user4383269
user4383269

Reputation:

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

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.

Upvotes: 0

Views: 1043

Answers (1)

Alex Belotserkovskiy
Alex Belotserkovskiy

Reputation: 4062

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/

Upvotes: 0

Related Questions