In the our previous post, we set up the golf-ball machine to generate some data and brought it into KEPServer. If you haven’t had a chance to read it yet, it’s here:
In this article, we’ll continue the exercise of connecting the system to the cloud…
Step 3: KEPServer IoT Gateway to Azure IoT Hub
I used two KEPServer documents to figure out how to accomplish this. The IoT Gateway Manual is a thorough document on the functions and features of the KEPServer plugin:
More helpful for this task, though, was this technote for connecting as an MQTT client:
The first step is to log into your account at portal.azure.com and create and create a new Microsoft IoT Hub:
It’s not immediately clear what an “IoT Hub” is or does, but in a nutshell, it’s the server to KEPServer’s MQTT client. When an event occurs, KEPServer will contact the cloud service that we set up here and send it a “message.” What happens with that message once it arrives at the “IoT Hub” will be covered below.
Once the IoT Hub is created in Azure, download and install the Device Explorer from github, and follow the instructions in the TechNote to connect it to the IoT Hub and retrieve the connection string. Then, follow the directions to create a “Device” in the IoT Hub using Device Explorer. This, in essence, tells the IoT Hub to expect data from KEPServer.
The authentication details were a little tricky:
On the Management tab, create the “Device”. Then click “SAS Token” and “Generate” to show the key. Copy everything after and including “SharedAccessSignature” to the clipboard. This is the key that KEPServer will use to authenticate to the Azure IoT Hub.
Now, in KEPServer, we’ll create the connection to the IoT Hub Device. In the tree, click “Add Agent” under “IoT Gateway” and select “MQTT Client.”
As outlined in the knowledgebase article, change the URL to
where HostName is given in the Overview screen for the IoT Hub in the Azure portal. Note that both the protocol and the port are different from the default values given by KEPServer.
The topic should follow the convention given in the knowledgebase article too:
for me, deviceID is “BALL_COUNT”, so I use devices/BALL_COUNT/messages/events/topic
The final window “MQTT Credentials” corresponds to the login information for the “Device” you created in the Azure IoT Gateway earlier.
For the Client ID use the same string as the deviceID.
For the Username, use the hostname/deviceID.
For the password, use the string that you copied from the Device Explorer above.
Verify that no connection errors are shown in the KEPServer diagnostic list. If they are, the IoT Gateway Manual that I linked to above has a helpful and detailed section on troubleshooting and diagnostics. Assuming all is well, click “Add IoT Items” to add items to publish to the IoT Hub.
For my setup, I’ll change the Scan Rate to 30 seconds, so that I’m not sending messages to the IoT Hub any quicker than this.
To verify that the messages are coming through, click on the “Data Tab” in the Device Explorer and click the “Monitor” button:
Step 4: Azure SQL Server
Now our data is getting to “the cloud.” More specifically, it’s hitting our Azure IoT Hub via MQTT messages. Azure has myriad opportunities for performing actions and passing the data around from there, notably using Event Hub, Azure Functions and Routing. For this exercise I wanted to stick to what I’m already comfortable with: SQL Server. I set a goal of parsing the data from the IoT Hub and passing it to a SQL Server instance for later reporting and analysis.
The tool I chose to parse and pass the data is Azure Stream Analytics. Before we can set this up, though, we have to give the data a place to go.
For this, I went again to the Azure Portal and selected “Create a resource” and selected “SQL Database.” I used the same resource group as the IoT Hub, and selected the lowest priced tier to minimize costs. In order to work with the database directly, I went to the SQL Server resource, “Security” > “Firewalls” and added an exception for my client IP address. This allows me to access the database using SQL Server Management Studio.
The URL and the admin user can be accessed under Settings > Properties under the SQL Server configuration within Azure Portal.
Within SQL Management Studio, I created a new database table called “ball_machine” with columns “timestamp” and “ball_count” to receive the data from the IoT Gateway.
At this point we’ve got data to our Azure IoT Hub, and we’ve set up a SQL Server database to collect it. In the next post, we’ll discuss connecting the two together and building a Power BI report…