Azure IoT offers a great solution for connecting IoT devices to the cloud and communicating with them in a secure way and in a two-way fashion: D2C and C2D.
Once you start ingesting telemetry you probably, at some point, want to represent the data in some kind of dashboarding.
This can either be a custom dashboard that gives you the most flexible way to represent the data. I have shown how to do this with Blazor. Or, you could choose PowerBI which is a well-known and productive tool used by many Data Scientists already.
Recently, our team invested some time in building dashboards using Grafana.
With Grafana you can create, explore and share all of your data through beautiful, flexible dashboards.
Azure supports Grafana in various ways in the Azure Marketplace.
Update: Please check out the recent Managed Grafana offering. Here, Grafana is offered as PaaS solution instead of IaaS, seen in this post.
For this blog post, I selected the official Grafana template which is hosted in a single VM:
The telemetry is ingested by an IoT Hub and send to a SQL Azure database using Azure Stream Analytics.
As you will see, this is quite an elaborate solution due to all the Azure resources being used.
Still, the solution is quite straightforward and certainly interesting if you are already familiar with Grafana.
It all starts with generating some telemetry using an IoT Device.
In this demonstration, I combine an Azure IoT hub device registration with the Raspberry PI device simulator:
Just open the page and on the right, notice you have to provide a device connection string.
If you registered a device in an IoT Hub, these symmetric key connection strings are easy to spot. Pick the primary key:
Fill in the connection string in the simulator code and press Start:
At the bottom, you will see messages being sent to the cloud. Now, telemetry is arriving in the IoT Hub.
Note: during the next steps of the blog post, we can keep the simulation running. In case you do not trust the arrival of the messages in the cloud anymore, please stop and restart the simulation. Or, check out the arrival of messages using the IoT Explorer.
Note: If you want to use the Azure IoT Edge temperature simulation module, that’s possible too. I will give you the specific ASA query for those messages later on.
Ingesting telemetry using the Stream Analytics job
Incoming telemetry will be routed to a Stream Analytics Job using the IoT Hub default eventhub-compatible endpoint. Do not worry, this is just done using an ASA input wizard.
Just create an Azure Stream Analytics job and add an ASA input for IoT Hub:
There, reference your IoT Hub which is ingesting the simulated temperature telemetry.
Note: I added a separate consumer group for the Stream Analytics job to the endpoint in the IoT Hub. This is a good practice preventing having multiple readers using the same consumer group (like the IoT Explorer and Azure Stream analytics both using the same $default consumer group).
Save the input and see the connection is tested successfully.
Writ the Stream Analytics job query
The query which handles the incoming messages for our Raspberry PI simulator is fairly simple:
SELECT
CAST(temperature AS bigint) as temperature,
CAST(humidity AS bigint) as humidity,
System.Timestamp() as timestamp
--INTO
-- sqloutput
FROM
iothubinput
From the incoming message, we take both the temperature and humidity. The timestamp is just the moment in time the job consumed that message.
I also transform all floats into integers. This is needed to match the database columns later on. I do this just because that Azure SQL table uses integers in my SQL example.
Note: For now, the future ASA output is still commented out… There is no database table yet.
Now, test it while the simulation is running (hit the Test query button):
We see the arrival of telemetry. Notice, because I use the system timestamp, during the test all timestamps are generated at the same time. Just ignore this, it works fine in production.
If you are using the Azure IoT Edge temperature simulation module, use this second query. It references the ‘ambient’ temperature and humidity:
SELECT
CAST(ambient.temperature AS bigint) as temperature,
CAST(ambient.humidity AS bigint) as humidity,
timeCreated as timestamp
--INTO
-- sqloutput
FROM
iothubinputAt this moment, we have a working Stream Analytics job query but that query does not make use of any outputs.
We want to output to an Azure SQL database table.
So, before we continue with the Stream Analytics job, let’s create a database first.
Creating an Azure SQL database
We are going to follow this tutorial of the Stream Analytics SQL Server output which is still in preview.
It says it needs both:
- An Azure Stream Analytics job.
- An Azure SQL Database resource.
We have the job already. Now, we create the database first.
To limit the costs, we try to create a ‘serverless’ version of the SQL Azure database.
This means there is still a database server somewhere but Microsoft is in control of it. We do not care about it. So eventually it’s cheaper for us because Microsoft can scale it in a way that is most optimal for them…
Create the SQL Database for now by adding the resource to the resource group (search for ‘SQL’). There are multiple options. Go for the SQL databases, you can scale up or migrate later on if needed:
Fill in a database name and server credentials:
Although we only want to run a ‘serverless’ database, we have to provide server-specific credentials (if you want to actually create tables in the database you need these rights).
Note: Save the DB server administrator name and password in secure storage like Azure KeyVault. We will need these credentials later on in this post.
By default, the database is not ‘serverless’ yet. We have to force the use of a ‘serverless’ database.
Choose the Configure database. This result in this dialog:
Select that big ‘serverless’ button.
Although we are building a demonstration, it is still a scalable solution. You can scale to larger databases or you can try out smaller sizes. If you think you are lucky, check out the standard or even basis tiers…
Close the dialog page. See the updated ‘serverless’ text being added if you followed the normal flow:
Leave all other settings. Just create the database as-is.
Once created, the database is not directly accessible for us to play with. The database is ‘firewalled’.
We have to white list the access for resources to access the database:
- Our own (developer) IP address so we can work on the database
- Other Azure resources (like Azure stream analytics)
Go to the Firewall settings and add your IP address and also allow Azure resources to access the database:
In Client IP addresses, your internet router IP should be added. And, that ‘Allow azure services’ should be set to ‘Yes’.
Save these settings.
There is an additional option that forces the TLS support to be 1.2 at least:
This also bumps up the security a little bit.
Create a SQL database table
The SQL Server, hosted and scaled and maintained by Microsoft, is runnig now. The database is ready to be used for the very first time.
All we have to do now is adding a table in the database.
So, go to the database in the Azure Portal (pick it from the list of SQL databases in the server):
We want to execute some queries against the database. The Azure portal offers a query editor in the browser (very convenient).
Open the query editor, log in with the database administrator name and password you remembered (or copy it from the KeyVault):
A query editor is shown with a single (empty) query tab already available.
We want to add this ‘telemetry’ table:
CREATE TABLE telemetry (
id INT PRIMARY KEY IDENTITY (1, 1),
temperature int NOT NULL,
humidity int NOT NULL,
timestamp DATETIME
);
Run the query and see it succeeds:
The table is created.
Notice, the two columns named temperature and humidity are both integers (on par with the Stream Analytics job query).
To test the table, execute this Insert statement followed by a query:
insert into telemetry (temperature , humidity , timestamp) values (FLOOR(RAND()*(100)), FLOOR(RAND()*(100)), GETUTCDATE())
select * from telemetryThis results in one record saved in the table:
We are able to add more records. Let’s connect the Stream Analytics job to this table!
Azure Stream Analytics output needs Azure SQL Server access credentials
Back in the Stream Analytics job, we could add that SQL database output:
You have to select the database. And you have to fill in the table name ‘telemetry’.
In the output dialog, notice there are two ways to authenticate the connection:
We could take the SQL Server connection string (so name and password are put there in plain sight) but this new Managed Identity feature is more secure (administrators are in control of who is accessing what).
Yes, this new method is a bit cumbersome (as we will experience) but we just follow the guide of adding the SQL Server output (which is still in preview).
So skip/cancel adding the SQL server job output, for now, let’s set some settings first in both the Stream Analytics job and the SQL Server!
Managed Identity
Note: At this moment, it’s important to remember the exact name of your Stream Analytics Job (which is ‘cloud-daskbaord-weu-asa’ in my case, including the hyphens and spelling mistakes 🙂 ).
In the job, activate the Managed Identity of the job first:
See the activation succeeds, a Principal ID is created:
Select an Active Directory admin
Next, go to the SQL Server and check the Azure Active Directory pane.
Note: this name of the pane differs from the original documentation!
There, set an administrator for AAD access:
I added myself.
Save the selection!
Be sure the initial ‘no active…’ text changes into a reference to your selected name/account.
Create a contained database user
We move over to the database.
We have to run some queries on the database server using the elevated rights of the AAD admin.
In the original documentation, the SQL Server management studio application is used.
But, the query editor in the Azure portal is sufficient too. Go for AD authentication:
Once logged in, execute this statement:
CREATE USER [ASA_JOB_NAME] FROM EXTERNAL PROVIDER;
This could look like (but this one will fail):
Unfortunately, I used hyphens in the Stream Analytics Job name which results in an error:
Failed to execute query. Error: Incorrect syntax near '-'.
If you have the same issue, fix the name using these double quotes:
This time the query runs successfully.
Test this with this query:
SELECT * FROM <SQL_DB_NAME>.sys.database_principals WHERE type_desc = 'EXTERNAL_USER'
The output looks like this:
Note: I had the same hyphen issue. Use double quotes again.
Grant Stream Analytics job permissions
The next step is granting permissions:
GRANT CONNECT, SELECT, INSERT ON OBJECT::[TABLE_NAME] TO [ASA_JOB_NAME];
This looks like (using double-quotes to fix the hyphen issue but still fails):
For some reason, this query fails on the ‘CONNECT’ part.
I’m not sure why ‘CONNECT’ is needed. I found out that leaving out ‘CONNECT’ part just works fine. I have not seen any issues by leaving out ‘connect’.
Try again:
We can test it with:
SELECT dbprin.name, dbprin.type_desc, dbperm.permission_name, dbperm.state_desc, dbperm.class_desc, object_name(dbperm.major_id)
FROM sys.database_principals dbprin
LEFT JOIN sys.database_permissions dbperm
ON dbperm.grantee_principal_id = dbprin.principal_id
WHERE dbprin.name = '<ASA_JOB_NAME>'
Notice the single quotes to fix the name of the job:
Finally, the Stream Analytics job output can be added.
Azure Stream Analytics output for SQL Server
These steps follow the original documentation and all steps were executed successfully.
Now, it’s time to add the SQL Server database output:
Give it a proper output name like ‘sqloutput’.
We connect using the Managed Identity.
We fill the telemetry table with the name ‘telemetry’.
Save the output and see it’s tested successfully.
Now, remove the comments seen in the query (which we already added before):
We ingest IoT Hub telemetry and finally persist them into the database after a correct casting to integers.
Save the query and start the job:
Check if the simulation is still running.
After a few minutes, we should be able to see records being outputted to the database using the Output Event (sum):
In the database, we see records being added if we run a simple Select query on the ‘telemetry’ table:
Right now, the table is appended with records as long as the simulation is running.
We have a full flow from device to IoT Hub, to Azure Stream Analytics to SQL Server.
Adding a Grafana dashboard
Yes, this is the moment we all waited for. We are going to add a Grafana dashboard in the cloud.
If we check the Microsoft Azure Marketplace, at this moment, Azure offers 21 Grafana solutions, from single VMs to large enterprise cloud solutions:
We go for the Grafana dashboard which is the Microsoft preferred solution:
We create a VM running a Grafana website in one go using this Marketplace wizard.
Note: afterward, we need to fix some extra settings and a bug.
First, as soon in the wizard dialog, we have to create a new (empty) resource group:
Adding a second resource group just for a VM is never a bad decision. In general, new VMs come with a lot of additional features (IP address, Network security group, etc.) which ‘pollute’ your existing resource groups. So, create this new, extra, resource group.
Next, fill in the security credentials for both the VM and Grafana website:
Note: put these credentials in eg. an Azure KeyVault.
Next, choose the smallest VM size (DS1v2 in my case) to host the Grafana website on:
As you notice, running a Grafana website is not free. This is just Infrastructure as a Service (IaaS) so somewhere a processor is running your website. Luckily, we can limit the costs using the right (minimal) size or even shut down the VM temporarily when it’s not needed. This lowers the cost dramatically.
Configure the Network settings. We only add that domain name:
Once this is done, deploy the VM.
This will end up in this list of resources:
Upgrading the VM settings
Before we dig into Grafana dashboarding, we check and alter a few settings to make our life a bit easier and more secure.
First, git the VM a static IP address. We want to be able to revisit the same IP address multiple times:
Then, configure the auto-shutdown. If we only use the VM for eight hours a day, we only pay a third of the CPU costs of this VM:
Our VM has inbound ports (both SSH and the Grafana website) so the whole world is allowed to try to access it.
So, limit access to the VM to selected IP addresses only. Whitelist our own development machine in the firewall.
For this to happen, go to the Networking settings of the VM:
Wut?
We are missing the firewall feature!
It says it’s not available because there is no Network Security Group in the Network Interface?
But, we have seen an NSG is created already:
It seems something strange is going on in the Grafana Marketplace template… I’m not sure if this is just to protect you or if it’s a glitch in the ARM template of this Azure resource…
Luckily, there is an easy fix!
Connect it just by hand. In the Connection Network Interface, select our existing NSG:
Now, we see the firewall rules appear in the VM running Grafana:
The access to both inbound ports (SSH and Grafana) must be limited to certain IP addresses only. In this case, add your own public IP address of your router.
Once this is done, Go to port 3000 of the public static IP address of your VM:
So, Grafana is running as a website. Other users from other IP addresses are not allowed to access this site before being whitelisted too.
Note: A newer version of Grafan is available. I have not tested this update 🙂
Finally, try to log in with the Grafana credentials.
Add a Grafana SQL Server data source
Grafana dashboards rely on ‘data sources’. So on the main page, it’s advertised to add your first data source.
From the long list of possible types of data sources offered, choose MSSQL:
Fill in the host, database and, credentials. Encrypt the communication. You can find the missing parts in the connection string of the Azure SQL Server database.
There is a separate page in the Azure portal showing the connection string:
Test the quality of the connection using the button at the bottom of the page. Eventually, you will get a ‘green light’ because Grafana (or better, the VM) is allowed to access the database:
We have a (default) data source now.
It’s time to create your first dashboard. Follow that link from the Grafana main page.
Add a new panel and look at the sample query at the bottom of the page.
Use this query below, just copy-paste it into the area with the sample query:
SELECT
$__timeEpoch(timestamp),
temperature as temperature,
humidity as humidity
FROM
telemetry
WHERE
$__timeFilter(timestamp)
ORDER BY
timestamp ASC
We want to see both the temperature and humidity from the telemetry table, part of the default data source (our SQL Server database).
As you can see, the telemetry is showing in your chart directly:
If nothing is showing, check the flow of the telemetry, beginning by resetting the Rasberry PI simulation and ending with checking if new records are written into the database table.
Next steps
We have seen the creation of a Grafana IoT dashboard on top of IoT Hub, Azure Stream Analytics, and SQL Server.
There are still a few things to take into consideration.
First, this VM website access is based on HTTP, not HTTPS. Probably, the Grafana password was sent to Grafana in clear text… This needs some extra HTTPS attention.
The biggest challenge of this VM will be scaling this solution when your IoT dashboard becomes successful.
At this moment, this Grafana dashboard is just using one VM running on one ‘CPU’.
It does not scale well (no scale-out without extra azure resources).
So think about adding a VM scale set (which can automatically create and integrate with Azure load balancer or Application Gateway) or make use of another scalable Azure Marketplace Grafana offer.
Conclusion
Keep in mind, the Grafana template needs some tinkering to make it secure. That missing Network Security Group is annoying but it’s easy to fix.
I used SQL Azure in this post to demonstrate the new security features of the Azure Stream Analytics output to SQL Azure databases. In real life, a database based on time-series offers a better performance representing large amounts of IoT telemetry in Grafana. You could also try out the new MySQL support in Azure if you are confident with that type of database.
We also have to talk about costs. We run both a Stream Analytics job, a SQL Server instance, and a VM. These are excellent Azure resources with great value for money (I could add a list of why this is a great solution but Enterprise developers already know). This is an Azure resource pattern with Enterprise usage in mind.
For a demonstration or PoC, there could be some overkill.
There could be room for improvement.
Try to run the same Grafana instance on a custom VM (with a smaller size) that you created yourself. (please share possible combinations in the comments below).
Next to that, you could try to fill the database using an Azure Function (“Consumption plan pricing includes a monthly free grant of 1 million requests and 400,000 GB-s of resource consumption per month per subscription in pay-as-you-go pricing across all function apps in that subscription”)
This alternative is great as long as you know how to program your own logic and you are only doing simple message transformations (no windowing) on single messages.
Again, check out the security aspects of your solution. Eg. you should start investigating if moving all Azure resources to an Azure private network is a feasible solution for you.
In the end, we have built a solid IoT dashboarding alternative in the cloud.
Sander van de Velde 