How to analyze EMnify usage data and events in AWS Quicksight?

Here is the code used for the manifest file:

Information:

• Quicksight is not a tool for live monitoring. Data is not refreshed live.
• When importing data from Amazon S3, each manifest can specify up to 1000 files as SPICE limits the number of rows you can add to a data set (more information here).

To not face such limitations, please follow this how to

How to visualize EMnify usage data and events in Quicksight with AWS S3 and Athena

By using the EMnify Data Streamer, it is possible to store Usage Data and connectivity Events in AWS S3. The information is stored as .CSV files and can be visualized in Quicksight (AWS data visualization tool). In order not to face specific limitations, we recommend using AWS Athena to query data in S3 before visualizing it in Quicksight.

1. Storing data in S3 using the EMnify Data Streamer

We recommend creating two buckets in S3: one for the usage data and one for events. Following this article, create one EMnify Data Stream for each. In the data stream for events, you can set filters.

Once you have configured two data streamers, you should see the following in your EUI account:

In this how to, the "event stream" sends the information to a S3 bucket called "webinarseds" and the "usage data stream" to another called "webinareds-data". AWS "default encryption" has not been enabled in this case.

2. Query information using Athena

In your AWS account, open AWS Athena.

2.1. Create a database

Create a database by clicking in the tab "AWS Glue Data Catalog". Give it a name a click on "create".

2.2. Create two tables

We now need to create one table for the events and one table for the usage data. In the query editor run the following queries (don't forget to change the location of your S3 bucket).

Query for usage data:

Query for events:

 If you have enabled AWS default encryption, make sure to change 'has_encrypted_data'='false', to 'has_encrypted_data'='true',.

2.3 Create views

By creating views, we define which information of the table we actually want to analyze in Quicksight. Choose the the rows you wish to analyze. Here are two query examples to create the views:

For Usage data:

CREATE OR REPLACE VIEW emnify_usage_data AS
SELECT event_start_timestamp, iccid, endpoint_name, endpoint_tags, endpoint_imei, operator_name, operator_country_name, traffic_type_description, volume, volume_tx, volume_rx, cost, ratezone_tariff_name, ratezone_name 
FROM webinaredsdata

For events:

CREATE OR REPLACE VIEW emnify_events AS
SELECT timestamp, event_type_description, event_severity_description, description, endpoint_name, endpoint_imei, endpoint_ip_address, endpoint_tags, sim_iccid
FROM webinaredsevents

3. Visualize data in Quicksight

In your AWS management platform, open Quicksight.

3.1. Add new data sets

Click on "Manage data" on the top right corner and then on"New data set". Select "Athena":

3.2. Edit the data set

As not all data appears in the right format, we need to edit specific fields. For example we need to manually input the format of event timestamp: "yyyy-MM-dd HH:mm.ss".

The following fields coming from the usage data stream need to be set as "decimal": cost, volume_rx, volume_tx and volume. Here is an overview of the usage data fields:

Once the fields are in the right format, click and "save & visualize" at the top to create analysis.

3.2. Create analysis

You can then create your analysis using Quicksight. Here is an example of usage data analysis: