Visualizing Sense Energy Usage with Azure Data Explorer

I have written in the past about monitoring energy usage and sending it to the cloud here: Connecting eGauge to Azure IoT

Today we will discuss a different device, and how to stream the data and report on it. Sense (https://sense.com/) makes a small device ripe for home energy monitoring. In addition to monitoring, it uses machine learning to identify specific devices. You can see the web application here:

While a very nice web (and mobile application), to date they do not allow you to download the real-time data nor visualize it as you need.

Today we will see how to view data, just like the web application is doing, and stream it to Azure Data Explorer so we can create dashboards and write customized reports.

Why [and what is] Azure Data Explorer you might ask? Azure Data Explorer (ADX) is a managed data analytics offering for large amounts of data with real-time ingestion. More info here: https://azure.microsoft.com/en-us/services/data-explorer/

To see the full architecture, consider this graphic, in which we will setup.

Step 1. Is the standard Sense device sending data to the Sense cloud (this should already be setup)

Step 2. We will have some python code, running in a docker container that read the energy data in real time

Step 3. We will stream the data to Azure Data Explorer

Step 4. We will write some simple dashboards to show the speed at which we can view real data.

Let’s Get Started

Setup Azure Data Explorer

  1. Open the Azure Portal (http://portal.azure.com) search for Azure Data Explorer and click on create:

2. Name your cluster, select your compute size and click create. This process may take ~10 minutes to fully deploy the needed compute.

3. Enable Streaming on your ADX, by clicking Configuration –> Streaming as shown below:

Configure the ADX database, table, and permission

  1. In the Azure Portal, select your ADX instance and click databases as shown below. Also, while here note the URL, it will be line 19 in your python file.

2. Click “Add Database” type a name of your database and click “create”. Note the name of your database, that will be line 28 in your python file.

3. Once your database is created, click the name of the database and click query. Next select your database in the center pane, past the query below in the query window and click Run as shown below. Note I named the table power, which is line 29 in your python file.

.create-merge table power (volt1: real, volt2: real, watts1: real, watts2: real, hz: real, totalc: int, ["time"]: long, currentDateTime: datetime, id: string, name: string, icon: string, watts: real, c: int)

4. Next run the following query to enable streaming (realtime) to the table as shown below:

.alter table power policy streamingingestion enable

Create an Azure AD Account for Authentication

  1. Click this link to access your Azure AD: https://portal.azure.com/#blade/Microsoft_AAD_IAM/ActiveDirectoryMenuBlade (not an AAD Admin, then have someone with permission run the next x steps) and note the tenant id. This is line 22 in your python file.

2. Click App Registrations –> New Registration as shown below:

3. Name your registration and click register as shown below:

4. At the next screen, note your client id, this is line 20 in your python file. Click Certificates & Secrets on the left as shown below:

5. Click New client secret, select your expiration date and click add.

6. With your new secret created, click the “copy to clipboard” icon. This is line 21 in your python file.

7. Last, back in the ADX at the Cluster Level, select Permissions –> Add –> Principal –> and search for the name of the registration created in step 3.

Python Application

  1. Download the github repo here: https://github.com/ksaye/senseToAzureDataExplorer
  2. Change to the directory where you just downloaded the repository (or at least the Dockerfile and sense.py.
  3. In sense.py, modify lines 19 – 22 and 28 with your ADX settings line 39 with your Sense userid and password. Save this file.

Your completed python file should look something like this:

4. Run the command to build, create and start the image:

docker build . --tag sense:latest

docker create --name sense sense:latest && docker start sense && docker logs -f sense

Create a Simple Query

  1. Back in the ADX Portal, select your database and then query. In the query window, select your database and type the query power and click run to see what data is already in the database as shown below:

2. Click on the Open in Web UI button in the center:

3. Select the database in the middle pane, and type the query below and click run to see linecharts of your data as shown below. This is a linechart based on minute, as we don’t have days of data yet!

power | project name, c, totalc, currentDateTime| extend datetime_part("Hour", currentDateTime), datetime_part("Minute", currentDateTime)| project-rename hourlyCost = c, totalcost = totalc, Name = name, Hour = Column1 , Minute = Column2| summarize avg(hourlyCost) by Hour, Minute, Name| project Minute, Name, avg_hourlyCost| render linechart

4. More visualization information here: https://docs.microsoft.com/en-us/azure/data-explorer/azure-data-explorer-dashboards

Example Dashboard

Below you can see a completed dashboard reporting on over 500,000 entries in sub second:

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s