Data Driven Business Solutions

We specialise in Data Science and AI solutions for complex engineering and business problems.

Search
  • Phil de Silva

Real-time Data Visualisation and Alerting - Demo Pipeline Build

Introduction

This blog post covers the step-by-step build of the IoT pipeline solution defined HERE. All tasks are carried out via the Azure Portal, Azure CLI, PowerBI Desktop, and Visual Studio Code. A follow up post will contain instructions on how to deploy this solution via ARM templates and Azure DevOps.


This post will cover the following topics:

  1. Setup of a Azure IoT Hub to ingest data

  2. Configuration Stream Analytics to analyse and process messages

  3. Storage of sensor data in a storage table

  4. Creation of function app too send out email messages

  5. Usage of an IoT simulator to generate data

  6. Visualisation of real-time data in PowerBI

Create IoT Hub


1. Sign into the Azure Portal (https://portal.azure.com).


2. Navigate to the Resources Group you wish to deploy the solution to and click “Add”. Search and select IoT Hub.


3. Give your IoT Hub a name. We will be using the US East region for the demo. You can change this if you wish.


4. Select the Pricing and Scale Tier. We will be using the free tier for this demo. More information regarding pricing tiers can be found here . As we are using the free tier, the Azure Security Centre option is not available. More information on Azure Security Centre for IoT can be found here .


5. Click “Create” to deploy.



Register a Device in IoT Hub

1. Navigate to the Cloud Shell in the Azure Portal.


2. Run the following command to add the IoT Extension.

az extension add --name azure-iot

3. Run the following command to register your device. Replace YourIoTHubName with the name of your IoT Hub.


az iot hub device-identity create   --hub-name {YourIoTHubName} --device-id MyDotnetDevice

4. Run the following command to get the device connection string. Make sure to note this down for use later in this guide. Replace YourIoTHubName with the name of your IoT Hub.


az iot hub device-identity show-connection-string --hub-name   {YourIoTHubName} --device-id MyDotnetDevice --output table

5. Run the following commands to retrieve the Event Hubs-compatible endpoint, Event Hubs-compatible path, and service primary key. You will need these values later in this guide.

az iot hub show --query   properties.eventHubEndpoints.events.endpoint --name {YourIoTHubName}
 
az iot hub show --query   properties.eventHubEndpoints.events.path --name {YourIoTHubName}
 
az iot hub policy show   --name service --query primaryKey --hub-name {YourIoTHubName}

Create Destination Storage

This solution uses Azure Table Storage to record telemetry, alerts and events


1. Navigate to the Resources Group you wish to deploy the solution to and click “Add”. Search and Select “Storage Account”.


2. Give the Storage Account a name. Set the location to be the same as the IoT Hub. Select a replication type that most suits your requirements. Click “Create”. More information on storage replication can be found here.



Create Function App

This solution uses Azure Function Apps to trigger notification emails via SendGrid. Configuring SendGrid is outside the scope of this guide.


1. Navigate to the Resources Group you wish to deploy the solution to and click “Add”. Search and Select “Function App”.


2. Give the Function App a name, set the Runtime Stack to .Net Core, and ensure the Region is the same as the IoT Hub. Click “Hosting”.


3. Select the “Consumption (Serverless)” plan and click “Monitoring”. More information on Hosting Plans can be found here.


4. Enable Application Insights and click “Create”. Application Insights will allow us to monitor and troubleshoot the Function App. More information on Application Insights can be found here.


5. Navigate to the “Platform Features” --> Configuration section and add an app setting for your SendGrid API Key. Click “Save”.



Create Alerts Function

1. Navigate to the Function App created in the previous step and add a new Function.


2. Select the “HTTP trigger” template and give the function a name. Click “Create”.


3. Replace the code with the content below. Replace the email addresses as required. Click “Save”. Replace "RECIPIENT_ADDRESS_HERE" and "SENDER_ADDRESS_HERE" with your details.

#r   "Newtonsoft.Json"
#r   "SendGrid"
 
using   System.Net;
using   Microsoft.AspNetCore.Mvc;
using   Microsoft.Extensions.Primitives;
using   Newtonsoft.Json;
using   Newtonsoft.Json.Linq; 
using   System.Linq;
using   SendGrid;
using   SendGrid.Helpers.Mail;
using   System.Collections.Generic;
using   System.Text;
 
public static async   Task<IActionResult> Run(HttpRequest req, ILogger log)
{
 log.LogInformation("C# HTTP trigger function processed a request.");
 string   msgTo = "RECIPIENT_ADDRESS_HERE";
 string   msgFrom = "SENDER_ADDRESS_HERE";
 string   msgSubject = "Alert Detected";
 string   output= "";
 
 string   requestBody = await new   StreamReader(req.Body).ReadToEndAsync();
 log.LogInformation(requestBody);
 dynamic   data = JsonConvert.DeserializeObject(requestBody);
 
 foreach   (JObject item in data)
 {
 log.LogInformation((string)item["deviceId"]+ " " + (string)item["temperature"]);
 output = output + "\n" + "Device ID" + (string)item["deviceId"]+ "\n Temerature " + (string)item["temperature"];
 }
 
 var   client = new   SendGridClient(Environment.GetEnvironmentVariable("SENDGRID_KEY"));
 var   msg = new SendGridMessage();
 msg.SetFrom(new   EmailAddress(msgFrom, msgSubject));
 var   recipients = new   List<EmailAddress>
 {
 new   EmailAddress(msgTo)
 };
 msg.AddTos(recipients);
 msg.SetSubject(msgSubject);
 msg.AddContent(MimeType.Text, "Alerts generated for.....");
 msg.AddContent(MimeType.Html, $"{output}");
 
 var   response = await   client.SendEmailAsync(msg);
 
 return   data != null
 ? (ActionResult)new   OkObjectResult(output)
 : new   BadRequestObjectResult("Please pass a valid query   string or in the request body");
}

Create Events Function

1. Navigate to the Function App created in the previous step and add a new Function.


2. Select the “HTTP trigger” template and give the function a name. Click “Create”


3. Replace the code with the content below. Replace the email addresses as required. Click “Save”. Replace "RECIPIENT_ADDRESS_HERE" and "SENDER_ADDRESS_HERE" with your details.

#r   "Newtonsoft.Json"
#r   "SendGrid"
 
using   System.Net;
using   Microsoft.AspNetCore.Mvc;
using   Microsoft.Extensions.Primitives;
using   Newtonsoft.Json;
using   Newtonsoft.Json.Linq; 
using   System.Linq;
using   SendGrid;
using   SendGrid.Helpers.Mail;
using   System.Collections.Generic;
using   System.Text;
 
public static async   Task<IActionResult> Run(HttpRequest req, ILogger log)
{
 log.LogInformation("C# HTTP trigger function processed a request.");
 string   msgTo = "RECIPIENT_ADDRESS_HERE";
 string   msgFrom = "SENDER_ADDRESS_HERE";
 string   msgSubject = "Alert Detected";
 string   output= "";
 
 string   requestBody = await new   StreamReader(req.Body).ReadToEndAsync();
 log.LogInformation(requestBody);
 dynamic   data = JsonConvert.DeserializeObject(requestBody);
 
 foreach   (JObject item in data)
 {
 log.LogInformation((string)item["deviceId"]+ " " + (string)item["change"] + " " + (string)item["messageId"] + " " + (string)item["humidity"] );
 output = output + "\n" + "Device ID" + (string)item["deviceId"]+ "\n Humidity Change " + (string)item["change"] + "\n Message ID " + (string)item["messageId"] + "\n Curent Humidity " + (string)item["humidity"];
 }
 
 var   client = new   SendGridClient(Environment.GetEnvironmentVariable("SENDGRID_KEY"));
 var   msg = new SendGridMessage();
 msg.SetFrom(new   EmailAddress(msgFrom, msgSubject));
 var   recipients = new   List<EmailAddress>
 {
 new   EmailAddress(msgTo)
 };
 msg.AddTos(recipients);
 msg.SetSubject(msgSubject);
 msg.AddContent(MimeType.Text, "Alerts generated for.....");
 msg.AddContent(MimeType.Html, $"{output}");
 
 var   response = await   client.SendEmailAsync(msg);
 
 return   data != null
 ? (ActionResult)new   OkObjectResult(output)
 : new   BadRequestObjectResult("Please pass a valid query   string or in the request body");
}

Create a Stream Analytics Job

1. Navigate to the Resources Group you wish to deploy the solution to and click “Add”. Search and Select “Stream Analytics Job”.


2. Give the Stream Analytics Job a name, select the location to be the same as the IoT Hub and set the Streaming Units to be one. Click “Create”. More information on Streaming Units can be found here.


3. Navigate to the “Inputs” section in the Stream Analytics Job and click “Add Stream Input”. Select “IoT Hub” as the input type.


4. Give the input a name and select the IoT Hub created previously. We will use the default settings in this demo.



Configure Stream Analytics Outputs to Storage


1. Navigate to the “Outputs” section in the Stream Analytics Job and click “Add”. Select “Table Storage” as the output type.


2. Configure the three output tables for telemetry, events and alerts as shown below. Use deviceId and messageId as the Partition and Row keys. More information on partition and row keys can be found here.



Configure Stream Analytics Outputs to Function Apps


1. Navigate to the “Outputs” section in the Stream Analytics Job and click “Add”. Select “Azure Function” as the output type.


2. Configure two functions for Alerts and Events as shown below.



Configure Stream Analytics Outputs to PowerBI

Please note a powerBI Pro license is required


1. Navigate to the “Outputs” section in the Stream Analytics Job and click “Add”. Select “PowerBI” as the output type.


2. Authorize with PowerBI.


3. Configure the PowerBI outputs as shown below.


Configure Stream Analytics Queries to Analyse and Route Messages

1. Navigate to the “Query” section in the Stream Analytics Job.


2. Create a query to route all messages to the allTelemetryStorageTable by using the command below. Note that we validate the inputs by ensuring that they are greater than zero. This may not be suitable for your weather conditions.

SELECT * INTO alertsStorageTable FROM demoiothub WHERE temperature > 0 AND humidity > 0

3. Create a query to send all telemetry to PowerBI.

SELECT * INTO allTelemetryPbi FROM demoiothub WHERE temperature > 0 AND humidity > 0

4. Create a query to check for temperature values exceeding 30 deg C and send these to table storage.

SELECT * INTO alertsStorageTable FROM demoiothub WHERE temperature > 30

5. Create a query to check for temperature values exceeding 30 deg C and send these to PowerBI.


SELECT * INTO alertsPbi FROM demoiothub WHERE temperature > 30

6. Create a query to check for temperature values exceeding 30 deg C and trigger the Alert function.

 
SELECT * INTO alertsFunction FROM demoiothub WHERE temperature > 30 

7. Create a query to compare the humidity against the previous value.

WITH DiffTest as
(
SELECT 
     deviceId, 
     EventProcessedUtcTime,
     messageId,
     humidity,
 LAG(humidity) OVER (PARTITION BY deviceId LIMIT DURATION(hour, 1) WHEN humidity IS NOT NULL) - humidity as change  
 FROM demoiothub  
)

8. Trigger humidity events to log to the events table where the change exceeds 5%, indicative of soil watering.

select *
INTO eventsStorageTable
FROM DiffTest
WHERE change > 5
AND humidity > 0

9. Trigger humidity events to call the "Send Evert" alert function when the change exceeds 5%.


select *
INTO eventsFunction
FROM DiffTest
WHERE change > 5
AND humidity > 0

10. Trigger humidity events to send data to PowerBI when the change exceeds 5%.

select *
INTO eventsPbi
FROM DiffTest
WHERE change > 5
AND humidity > 0

11. The final query should resemble the screenshot below. Click “Save Query”.


12. Navigate to the Overview section and click “Start”.



Configure and Run the IoT Simulator


1. Download the simulator code from GitHub.


2. Navigate to iot-hub/simulated-device/ SimulatedDevice.cs and replace the contents of s_connectionString with your IoT Hub connection string.


3. In a terminal window, run the solution by using the following commands.

dotnet restore
dotnet run

4. The output should be as below:

Verify Results


1. Connect to the destination storage account via Storage Explorer.


2. Check the three tables and verify entries.


3. Check email and validate alerts and events are being received.


Visualise Results in PowerBI

1. Log into PowerBI, select “Workspaces” --> IOT.




2. Create a new Dashboard.


3. Add a “Custom Streaming Data” tile


4. Select the IoTDemoTelemetry data set.

5. Configure as shown below.

6. Your tile should start streaming live data.


78 views0 comments

Recent Posts

See All