Motivation

I'll probably be the first to admit that I spend way too much time in my room with my door and window shut, either coding or sleeping. Recently, I saw “This Is Your Brain On Stale Air” by Tom Scott and it has made me consider what kind of effect this is having on my, being in an enclosed space for a large amount of time. To figure this out, I wanted to get a rough idea of air quality in my room, in terms of CO2 (because I spend a lot of time there) and dust (because of allergies). I used the Sensing the World Challenge as an excuse to sense the air quality of my room to see whether I should be concerned, or start opening a window more often. I wanted to create a system with the Azure Sphere that would be able to capture and display data on a time series plot with some indication of whether my window was open, the current CO2 levels and current dust levels.

 

Dupont lines are both the best and worst things to exist (Picture of assembled circuits, cables everywhere)

Thankfully despite my horrible dupont line wiring, the Azure Sphere was able to gather the required information.

 

Grafana graph of my data

Based on my end result, I had a time series plot of dust, temperature, CO2 and whether my window was open at the time.

How it works.

I read the PMS-7003 and MH-Z19 dust senors using two of the Azure Spheres ISUs set to UART mode (ISUI0 & ISU1), leaving ISU2 to be set to I2C mode to drive on OLED screen which gives basic PM5 and CO2 readouts. I get information about whether my window is open or closed by reading a reed switch that's attached to one of the GPIO pins.

The data from these sensors then gets sent to Azure IOT Hub. An Azure function processes the telemetry messages and dumps the data to an Azure hosted SQL database. Grafana uses the data in this SQL database to provide an interactive dashboard to see dust levels, CO2 levels and whether my window is currently open in one place.

 

While I don't currently utilize this feature, I also update the devices digital twin model with the current sensor readings. I had originally planned to do something with this information but ran out of time.

 

I also built a simple Flask app that communicated with a C# API that I wrote (because of issues getting the official python API to work properly on Azure App Service) to do direct method calls on the Azure Sphere. This direct method allows the user to change the polling period of the device, storing the current polling period in the Azure Spheres mutable storage. Doing this allows the polling rate to be permamently set, staying the same between reboots. This Flask app is hosted on Azure App Service, with the C# API being hosted as an Azure function. To protect against unauthorized users modifying my device, I secured this app with Active Directory.

 

Parts List

Labelled parts list.

  1. PMS-7003 Dust Sensor
  2. MH-Z19 CO2 Sensor
  3. OLED screen
  4. 10K resistor
  5. Azure Sphere MT3620 Starter Kit
  6. Reed Switch

Parts Choice

It looked like there were a lot of cheap sensors that claim to be accurate at measuring CO2, but looking at reviews online they seemed to be very questionable. I chose the MH-Z19 CO2 sensor because it was one of the cheaper NDIR sensors that had the ability to automatically calibrate itself in fresh air, it also happened to be one of the sensors visible in that Tom Scott video.

I chose the PMS-7003 because it was one of the cheaper Dust sensors that was capable of measuring PM 2.5, PM 5.0, PM 10 that I could find with some long term data to back it up and seemed to be reasonably accurate for my purposes.

I picked the first reed switch that I could pick up at my local electronics store, as I did not plan that part out very well (originally I was using a hall effect sensor, but I realized that what I was after was probably a solved problem and commercial product) and left it as a late addition to the project. This reed switch had a normally open or normally closed mode so I could select this in hardware rather than software. I used this in normally open mode with a 10k pulldown resistor to make sure I didn't get a floating input. The OLED screen was one that I had laying around from another project.If I were to do this project again, I would probably buy a slightly larger screen as I found the character limit was quite restrictive.

As this was made for an Azure Sphere competition, it would probably be a pretty good idea to include the Avnet Azure Sphere Starter Kit (Thanks Danzima).

 

Of course, any guide involving sensors needs to come with a disclaimer. These sensors have not been calibrated by a professional and I have not yet generated enough data to prove their accuracy, precision or sensitivity. The readings that you get from them should be treated as a rough guide.

 

 

Wiring Guide

Wiring guide for project

(Diagram made with Fritzing using the Azure Sphere model by vanepp)

Data Flow

Data flow 1. Getting data from the device to grafana

  • Data flows from the Device to the IOT Hub through digital twin updates and telemetry messages.
  • These messages are then picked up from the IOT Hub the /events topic by an Azure Function App (EnvironmentSenseToDB) which then get put into the required SQL tables.
  • Grafana is then used as a visualization layer for this data.

Azure Sphere Setup

Note: Some of the steps here provision Azure resources. My estimated costs per month were ~$15AUD. I would strongly suggest you set up an Azure spending limit before doing any of the mentioned steps to avoid getting a nasty surprise. Rude awakening  IoT -Central charges

Registering Device

Install Azure Sphere SDK for your required version of VS Code (2017 or 2019)

Once this is done you will find that you have the Microsoft Azure Sphere Developer Command Prompt Preview.

Sphere Developer Command Prompt Preview

In the Sphere Developer Command Prompt, enable the Azure IOT Hub extension

az extension add --name azure-cli-iot-ext 

Claim your Azure Sphere

azsphere device claim 

If your login fails with a message similar to “identity provider 'live.com' does not exist in tenant 'OsTechnology'", you'll need to create an active directory account in Azure.

Log in to your Azure Portal (https://portal.azure.com/) and select Active Directory.

 

AD Panel

Select "Users"

Create a new user under "Users". This user will not require any roles.

create user

 

 

Note: Based on the comments on the 19.10 release, it looks like these steps won't be required for a non enterprise AD account. I haven't had a chance to test this release.

 

Run azsphere device claim and log in as this new user. You may need to create a new tenant with tenant create --name “Name of your tennant”.

Setup WiFi with azsphere device wifi add --ssid "New SSID" --key “Your key” Run azsphere device wifi show-status to validate that your wifi is connected properly.

SSID                : YOUR SSID NAME Configuration state : enabled 
Connection state    : connected
Security state      : psk
Frequency           : 2457 Mode                : station
Key management      : WPA2-PSK WPA
State           :
COMPLETED IP Address          :
MAC Address         : 00:02:b5:xx:xx:xx  Command completed successfully in 00:00:01.4590142.

Prepare your Azure Sphere for development by running azsphere device prep-debug. If you upgrade the device and encounter this error

Copying application to the device 
Device would not stage app for sideload:
Image not trusted by device; application development capability is required ('azsphere.exe device prep-debug') (DeviceClientException)

You will need to run this command again.

 

Note: Under 19.10 this command has been replaced with azsphere device azsphere device enable development.

 

Setup IOT Hub

To create a free tier IOT Hub, you'll need to create a resource group and setup the IOT Hub inside that resource group.

az group create \
--name {your resource group name} \
--location westus  az iot hub create \
--name {your iot hub name} \
--resource-group {your resource group name} \
--sku F1

Create a new device on your IOT Hub with

az iot hub device-identity create \
--device-id {device name} \
--hub-name {hub name}

Grab the connection string of that device with

az iot hub device-identity show-connection-string \
--device-id {device name} \
--hub-name {hub name}

Flash Azure Sphere

Clone the repo with git clone https://github.com/SrzStephen/azure-sphere-monitor-environment.git or download as a zip file.

Copy the connection string you got with the az iot hub device-identity show-connection-string command into the MY_CONNECTION_STRING definition in connection_strings.h.

In app_manifest.json copy in your iot hub hostname{ iothubname}.azure-devices.net to "AllowedConnections" to allow your Azure Sphere to communicate with it.

Hit "Remote GDB Debugger".

And you should see that your IOT Hub connection has been successful.

If this is not successful, then you should double check your IOT hub strings.

[Azure IoT Hub client] INFO: connection to the IoT Hub has been established (IOTHUB_CLIENT_CONNECTION_OK) 

You should also see that you start getting "Device to Cloud Messages" through your IOT Hub.

iot hub showing messages

You can also view the exact content of the messages that arrive in your IOT Hub by downloading the Azure IOT Explorer.

Create Additional Azure Resources

SQL Server

az sql server create \
-l westus \
-g {group name} \
-n {server name} \
-u {admin username} \
-p {admin password}

az sql db create \
-g {group name} \
-s {server name} \
-n {database name}  \
--capacity -c 5 \
--tier basic

Show your connection string. You'll need this later.

az sql db show-connection-string \
-s {server name} \
-n {database name} \
-c ado.net

To manage the SQL server, download Dbeaver an open source database management tool and add a new Azure SQL Server connection

azure connection

And enter your login credentials

database connection string

To create the required tables, change yourdb name in the following SQL script to create the required tables.

CREATE SCHEMA dbo;

CREATE TABLE yourdbname.dbo.co2 (

    dt varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    co2 int NULL,

    temp int NULL,

    device varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) GO

CREATE TABLE yourdbname.dbo.dust (

    dt varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    PM1_0 int NULL,

    PM2_5 int NULL,

    PM10 int NULL,

    R0_3 int NULL,

    R0_5 int NULL,

    R1_0 int NULL,

    R2_5 int NULL,

    R5_0 int NULL,

    R10 int NULL,

    device varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) GO

CREATE TABLE yourdbname.dbo.[window] (

    dt varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

    window_open int NULL,

    device varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL

) GO;

The choice to save datetimes as varchars probably seems a bit strange if you've done some SQL before, as it would probably be more efficent to save them as unix epoch times and you'd be right. Originally I was going to use PowerBI as my presentation layer, which can easily convert datetime strings to an accepted datetime format. While I built my original dashboard in PowerBI, I ultimately thought that the licencing for it (limited trial period) would be too limiting for hobbyist use, so I ended up switching to an open source presentation layer (Grafana).

powerbi is kind of cool, it was quick to create a dashboard.

Old power BI dashboard during initial tests.

 

Azure Function 1 - IOT Hub to Database

As shown in the diagram, an Azure Function is needed to take the messages that enter IOT Hub and insert them into the SQL database.

I chose an Azure function to do this, because a rough back of the envelope calculation showed that my uses would fall well within the free tier of 400,000 GB-s/, 1m executions per month. If I were building this for a system with more sensors, I would consider looking at Azure Service bus instead, as it would probably work out cheaper, but I had planned to spent the least in OPEX that I possibly could, because some Azure services look very expensive if you're a hobbyist.

Create the required Azure resources:

az group create \
--name {group name} \
--location westus  az storage account create \
--name {storage name } \
--location westus \
--resource-group {group name} \
--sku Standard_LRS  az functionapp create \
--resource-group {group name} \
--name {function app name} \
--storage-account  {storage name} \
--runtime dotnet \
--consumption-plan-location westus

Deploy from a git repo

az functionapp deployment source config \
--branch master \
--manual-integration \
--name {function app name} \
--repo-url https://github.com/SrzStephen/azure-iot-hub-azure-function.git \
--resource-group {group name}

Set up the required environment variables

az functionapp config appsettings set \
--name {function app name} \
--resource-group {group name} \
--settings "MyConn={result from az iot hub device-identity show-connection-string command} DBConn={result from sql db show-connection-string}"

Monitor it through Azure Functions to check that it's working. Select the function app you're after.

And hit Monitor.

press monitor

Check that your function is running correctly, then check that data is appearing in your SQL server with DBeaver.

data example

Grafana

To keep this guide short, I will be running Grafana locally, so download it for your required distribution here. Once installed you will be able to connect to it by visiting http://localhost:3000 in your web browser.

If you want to run Grafana to have it accessible over the internet, I'd suggest you follow this great guide by l by Jamie Phillips on how to run Grafana in Azure App Service.

The remainder of the steps are applicable to both cases.

 

Add your database through "Data Sources".

add souces

Select Microsoft SQL Server.

select mssql

Enter your database credentials (Host, Database, User, Password, Encrypt and give this connection a name). The rest of the values can be left as their defaults.

enter creds

Create a new dashboard.

create dashboard

Go to dashboard settings.

Dashboard settings

You can copy the dashboard with the grafanaconfig.json file found here

Here is an example of one of the queries

SELECT

  $__timeEpoch(dt),

  co2 as value,

  device as metric

FROM

  databasename.dbo.co2

WHERE

   DATEDIFF(second, '1970-01-01', dt) between $__unixEpochFrom() and $__unixEpochTo()

ORDER BY DATEDIFF(second, '1970-01-01', dt) asc

And congratulations. After all of that you now have a dashboard that lets you view your IOT device.

Example dashboard.

You can change the time period you look at through the top bar.

Change timeframe

 

Taking a look at ~ 1 weeks worth of data, it looks as though the CO2 levels go above 2000 ppm which may not be ideal.

I notice that dust is less of an issue than I thought it would be, there are some period where I am breaching the WHO air quality guidelines but in general it seems like the values are reasonably good.

I can see the immediate effect of opening my window, although curiously soon after closing it again the CO2 readings begin to spike up, leading me to believe that my sensor may be too close to the window.

I can also see that keeping my window open when I sleep causes CO2 PPM to never rise even close to the levels it got to before, which should not be a surprise.

Same image from the start, long term trends of CO2 + Dust.

 

 

Implementing Direct Methods

With my main goal done, I wanted to try something extra based on the example in Implementing Direct Methods in Azure Sphere: I wanted to have a convenient way to change the sensors polling time, and have that persist between reboots. I also wanted to extend that tutorial by providing a nice UI to call the direct methods rather than relying on logging into the Azure Web Portal to use its direct method invoking portal.

 

My chosen implementation of this uses the direct methods to store a polling period value in the Azure Spheres mutable storage, to change sensor polling time without having to modify the devices code.

This is entirely unnecessary if you're goal is just to pull some sensor data from an azure sphere and show it in a visualization tool, but I thought it was pretty neat so went ahead and implemented it.

 

direct method data flow

 

Azure Function 2 - C# API

Create required Azure resources

az group create \
--name {group name} \
--location westus  az storage account create \
--name {storage name } \
--location westus \
--resource-group {group name} \
--sku Standard_LRS 

az functionapp create \
--resource-group {group name} \
--name {function app name} \
--storage-account  {storage name} \
--runtime dotnet \
--consumption-plan-location westus

Deploy function from a git repo

az functionapp deployment source config \
--branch master \
--manual-integration \
--name {function app name} \
--repo-url https://github.com/SrzStephen/Azure-IOT-Hub-API \
--resource-group mynewgroup

Set up the required environment variables

az functionapp config appsettings set --name {function app name} \
--resource-group {group name} \
--settings "MyConn={result from az iot hub device-identity show-connection-string command} DBConn={result from sql db show-connection-string}"

You'll need to grab your Azure Function host key to allow your application to access your API. There is a much better way to do this in code, but it's best explained by the original author.

Go to your Function App Settings

get host key from applications settings

WebApp

Create required Azure services for your webapp

az group create \
--location westus \
--name {group name} az appservice plan create \
--name {web plan name} \
--resource-group {group name} \
--sku F1  az webapp create \
--name {webapp name} \
--resource-group {group name} \
--plan {plan name}

Deploy your webapp from a git repo

az webapp deployment source config \
--name {webapp name} \
--resource-group {group name} \
--repo-url https://github.com/SrzStephen/azure-direct.git

Add in your required environment variables

az webapp config appsettings set --name {webapp name} \
--resource-group {group name} \
--settings-names "AZURE_API_ENDPOINT={azure endpoint url} AZURE_API_HOST_KEY={host key} DATABASE_USER={database username} DATABASE_PASSWORD={database password} DATABASE_SERVER={database server} DATABASE_DATABASE={database}"

Finally secure this front end with Azure Active Directory by going to your web application, selecting Authentication/Authorization and enabling active directory with express configuration.

enable ad authorization

And you should be done! Visit your Azure App service URL, log in with an email within your Azure Active Directory account and you should be good to go!

Device names and their statuses will appear, select an online device and change that poll time!

 

Polltime update

Conclusion

If I were to do this again, I probably would go down the route of Azure IOT Central instead of using Azure IOT Hub as it has a lot of features baked in based on what some other entries to this competition have done. It also has a pretty decent free tier with first 5 devices and 50,000 messages per month for free.

I would probably spend some more time properly setting up an Azure Resource Manager template. Initially I thought it would be a good idea to lay out all the steps of the Azure process so people could see what exactly they were provisioning (For me costs came out to ~$15 a month, most of that was the SQL Database), but looking back at this guide there are too many steps. I didn't have this in mind when I began,so my final ARM template final template that I created using ARM Template Merge, had far too many fields to be useful.

I would probably include some other sensors if I were to do this again, such as a PIR sensor to detect when I'm in my room to better correlate what's happening, and some more environmental sensors to see what Volatile Organic Compounds (VOC) are in the air.

I should reaaaaaly leave my window open whenever I'm in my room, especially when I sleep.

 

Thanks to Peter Fenn for his Azure Sphere OLED examples. Some of these libraries were used verbatim.

Thanks to Brian Willes for his guide on Implementing Direct Methods in Azure Sphere. It was very well written and understandable.

Thanks to Clem and Enrique for their help with UART Problems that I initially had. I probably would have given up at that stage and said it was too hard to use if they hadn't answered that question.

Thanks to Danzima for his help getting the Azure Sphere Kit for this competition.

Thanks to Bawiless for his Avnet Azure Sphere Starter Kit Reference Design code.It was very useful and some of the libraries he made were so useful I used them (nearly) verbatim.

 

 

Links to all code

Azure Sphere code: https://github.com/SrzStephen/azure-sphere-monitor-environment  (I've been out of uni for ~1.5 years and haven't touched C since, any code comments are greatly appreciated)

Azure Function #1 IOTHUB to DB: https://github.com/SrzStephen/azure-iot-hub-azure-function

Azure App Service code (Flask APP): https://github.com/SrzStephen/azure-direct

Azure Function #2 IOTHub API: https://github.com/SrzStephen/Azure-IOT-Hub-API

 

Note: Phase one completed with a different email address to this one.