IoT data analytics

If you are not already using the Raijin Database Engine to collect your logs for data analysis, install Raijin. Since Raijin uses the HTTP protocol, your web browser serves as a Raijin client for sending SQL queries as well as creating, listing, and dropping databases and tables. Just enter the hostname or IP address of the host where Raijin is installed with its default port 2500 to connect: http://<raijin-host>:2500/.

The first step is to set up a new database and tables for collecting metadata from the emnify Data Streamer:

If you are not already an NXLog customer, download NXLog Enterprise Edition v5 trial for your platform to get started for free. The Deployment section of the NXLog User Guide has instructions for all supported platforms if you have any questions.

Once you have NXLog installed, the next step is to find and edit your nxlog.conf configuration file. On UNIX-like platforms the default path and filename are /opt/nxlog/etc/nxlog.conf.

The following configuration will enable you to:

Restart the NXLog service for the changes to take effect.

The emnify Portal provides an Integrations section where webhooks can be configured.

Create your webhooks using the IP address of the NXLog host and the port numbers you used for the ListenAddr directives in your NXLog configuration.

If the Raijin tables are empty, make sure that the webhook configurations are correct and the NXLog agent is capturing the raw data:

On the NXLog host, ensure that the raw data files are not empty:

If both files are empty, consult the Troubleshooting section of the NXLog User Guide.

If NXLog is capturing the metadata but the Raijin tables are still empty, consult the Raijin and NXLog logs.

From a security perspective, the events table is most important.

The next step is to identify and confirm fields that function as categories. In this case, these two fields appear to be candidates:

For this purpose, you can use the SQL SELECT DISTINCT <field name> to see if any categories emerge.

This query returns two categories:

This query returns a much longer list of categories that you might find useful for future queries involving types of events.

With the ability to write ad hoc SQL queries, Raijin can provide tabular views of events that are not possible with the views available in emnify Portal Dashboard or Connected Devices » Details » Events where you will find Severity and Event Type but not Event Description. From the following query, you can see that Event Type tells very little about what has actually happened.

From the query results above, you can see that both events are the result of data traffic limit that was configured in the Service Policy for this device. From the Description field, you would never imagine that these two event types, Create PDP Context and Endpoint blocked would ever have anything in common.

From the data contained in the usage table, you can track your costs and gain insights on usage trends.

Data usage for a specific month

Creating customs data usage reports for a variety of time spans is possible now that you have raw data and the ability to write aggregation queries using SQL.

In the following exercise, the database should report device data usage for a single month, May 2023. The following metrics should be returned for each IoT device:

Raijin’s native SQL functionality is indispensible for such reports.

In this Jupyter notebook, the following Python code cell demonstrates how you can query a Raijin database and transform the Raijin response into a pandas DataFrame. Finally, invoking the name of the pandas DataFrame events outputs a tabular view of the data.

Create a Python function for querying Raijin

It is more efficient to create a Python function from the code required to take an SQL statement, send it to Raijin, receive a response, and convert it to a pandas DataFrame. Another good practice is to store each SQL query as a file. SQL stored as files enables you to:

Using the function

After copying the SQL from the previous exercise to a file called emnify-events-match-exceeded.sql, you can now create the same pandas DataFrame from above by providing the filename to the new function:

When you run this in your Jupyter notebook, you should see the same 2,224 rows of 5 fields displayed above.

Within Jupyter Notebook, you can use plotting libraries like seaborn to visualize your data. The seaborn library is especially suited to plotting data distributions from pandas DataFrames. In most cases, data manipulation is not required, only a single function call with a few parameters will suffice.

Using the previous DataFrame, the following Jupyter Notebook code cell sets the style of the plot, customizes the title and labels for each axis, and defines a histogram that will aggregate events by device name on the x-axis.

Because of the extreme range of event count across IoT devices, an additional line is needed to change the y-axis to a logarithmic scale.

From the previous exercise, you saw the problem that an extreme range of values presents when plotting data. When an application continually logs the same message in short intervals until the state/error/warning changes, generating a histogram that accurately describes the true distribution of event categories is impossible. NXLog can filter duplicate events. However, you need to first identify which events are duplicates and find a way to define them.

Plotting a histogram of events by day of year

By converting each warning event’s timestamp to the day number of the current year, you can create a dataset for plotting a histogram of the number of warnings per day.

The description contains very long, detailed messages that are often very similar but can vary by something as minor as a mobile network provider’s IP address. By using only the first 30 characters, you can categorize such events as duplicates despite these insignificant differences.

The first 15 rows of these query results indicate that over 9,500 of these warning events are duplicates. This shows that 72% of the 13,299 events the emnfiy Data Streamer sent were essentially worthless.

The histograms above are confined to events that have a severity of Warning. Given a count of 9,748 warning events, this means that less than 3% of the warning events collected from the emnify Data Streamer are significant.

Next, you will learn how to leverage SQL and pandas DataFrames to isolate and remove duplicates from your emnify warning events.

The filter (warnings only) and new field Desc (the description field with all but the first 30 characters truncated) will pave the way for dropping superfluous events.

First, you will construct a Python dictionary called previous that uses the IoT device name as a key and the truncated description Desc as its value. This will serve as a lookup table for seeing if the current device has the same Desc value as the previous one while you iterate through each row of the DataFrame that Raijin has already sorted by timestamp. If you use the current IoT device’s name as the key for returning its previous Desc value, and it matches the Desc value for this row, a duplicate has been detected. In this case, the DataFrame drop function removes it.

After plotting this cleaned data, you can see that IoT Device 011 only has 4 significant warnings, not 2,155.

emnify logs sessions of data usage based on bursts of data transmissions using beginning and ending timestamps:

If you want to analyze the duration of data sessions, you could use milliseconds as a unit of time. However, the shortest duration recorded in this dataset of 6,410 sessions was 3.027 seconds.

For aggregating data over any time interval, the best practice is to use the timestamp that marks the completion of the data transmission. In this case, end_timestamp.

emnify data usage for a specific month

Create a report of data usage by month

In this exercise, you will create a plot of data usage by month for two categories: data transmitted and data received.

The SQL query aggregates the appropriate fields by month.

In the following Jupyter Notebook code cell, the seaborn barplot() function takes a category Mode and creates a bar chart of both values, either Data RX or Data TX.

Create a report of SMS usage for May 2023