The EMnify DataStreamer makes connectivity data available in your Azure account for real-time insights on the consumption and state of your devices. This guide describes the steps to build a report in Power BI. The steps covered are building a basic Stream Analytics job, delivering selected metrics to Power BI, building reports and customising data visualizations to assist in troubleshooting device connectivity.
This guide will cover how to build charts which display the following information:
To be able to stream data to Power BI, a simple Stream Analytics job can set up to collect basic fields that we're interested in and stream them in real-time to Power BI. To create a Stream Analytics job:
When the deployment of the resource is complete, click view resource to view the stream analytics job. The next steps will set the data incoming to Event Hub to be the input and Power BI as the output for a basic analytics job. In the Stream Analytics job;
The last step for a basic analytics job is to select fields from the Event Hub that we can build reports from. In this case we are selecting from some nested JSON elements and flattening them into fields that have short, meaningful names. To build a basic query, navigate to Query and paste the following SQL query that the job will run continuously:
case when timestamp is null then CAST(end_timestamp AS DATETIME) else CAST(timestamp AS DATETIME) end as timestamp,
event_severity.description as event_severity,
event_type.description as event_type,
description as event_description,
endpoint.id as endpoint_id,
endpoint.name as endpoint_name,
endpoint.ip_address as ip_address,
endpoint.imei as endpoint_imei,
sim.id as sim_id,
sim.iccid as sim_iccid,
imsi.imsi as imsi_imsi,
detail.country.name as country_name,
detail.name as operator,
traffic_type.description as traffic_type,
CAST(alert as bit) as alert,
CAST(volume.rx AS FLOAT) as volume_rx,
CAST(volume.tx AS FLOAT) as volume_tx,
CAST(volume.total AS FLOAT) as volume_total
To begin the Stream Analytics job, click Save Query, navigate to Overview and click Start Job. This query will now select events that are of interest as they arrive in our Event Hub and stream them in real-time to the Power BI workspace.
The Overview panel of the stream analytics job should show a status of Running and the Monitoring graphs should display successful events incoming and outgoing.
In Power BI the dataset should be visible by navigating to https://app.powerbi.com/groups/me/list/dashboards, specifically in the Datasets + dataflows tab
The fields in the dataset can be inspected by selecting the dataset context menu and clicking Edit
For the examples in this guide to work, ensure that the following fields have the correct types:
In this view, reports can be created by selecting metrics from the Fields panel on the right and graphing them using one of the available visualization tools. Filtering can be applied based on example events that are streamed, which will allow for narrowing visualizations to events that are of interest or applying a global timeframe to the queries by means of a filter of timestamp by relative time.
Hint: filters can be applied in the following three ways:
This allows for making reports which are filtered by timeframe, by operator, or by device group using tags. For our case, it's useful to build pages (or entire reports) which are filtered to show events from the last 24 hours or the last hour. The necessary steps to filter by time range across multiple charts is detailed in a later step.
In the new report:
This chart now displays Data consumption in MB by device for the whole dataset.
An accelerator for creating these visualizations is to copy existing charts and adjust the filtering criteria. To create a corresponding SMS consumption chart:
To create a quick count of devices per operator;
As our example data stream uses both event and usage data, this chart covers both usage data and event data and is a quick indicator of the general activity of devices showing the most active by endpoint ID.
At this point, our first page is complete which covers a general overview of device fleet activity. To improve this view to offer more context, add a timerange filter.
All visualizations are now only showing queries of device activity within the last time range. To perform a basic drill-down, click one of the segments in the pie chart to single-out a specific device by ID, this will apply the same display filtering on all charts to highlight the activity of that device relative to data consumption, operator and SMS usage.
It's often useful to view the data consumption of devices as a time series to visualize device activity trends. To achieve this
The features built-in to Power BI that make charts more effective at conveying the story behind the metrics are included in the Analytics tab of the Visualizations panel. For graphs such as data consumption, it's often useful to enable a trend line or a line which shows average values:
The following graph sets a filter by top N devices by any metric, this example will use total data consumption.
To set conditional formatting based on expected values, select the Format panel under Visualizations and choose the formula icon in Data Colors. Min/max values can be visually differentiated or custom values can be specified which make graphs easier to read if values exceed expected thresholds:
Create a specific troubleshooting page with a table displaying the latest network alerts:
Clicking on the timestamp column header will sort the table events by timestamp from earliest or latest in the dataset.
This chart shows the distribution of Warning Events by endpoint, to create this chart:
Graphing PDP create and Delete events over time can be an additional method of troubleshooting device network activity, to build this graph:
To make the graphs actionable, the second page focuses on warnings. The data that each graph is built upon can then be exported as either CSV or Excel files.
Note that filters applied to a page enable exporting only events which are of interest, if a time range filter is applied to a page, only warning events from that time range will be exported. The generated reports which filtered to last hour, for example, can assist in operations teams for troubleshooting underlying issues which are time-critical:
Additionally, operations teams that use Microsoft Teams as a communication channel can enjoy native sharing support by means of the Chat in Teams button in the top-right of the report. A link to the report with the current filters can be instantly shared for communication across support and operations channels:
With this guide, we have covered some of the most useful metrics to graph on a report for quick insights on the connectivity information of your device fleet. Troubleshooting device connectivity is now simple to perform within Power BI by drilling down from platform warnings and generating custom reports for the devices and timeframes that matter.