Visualize Data

It's hard to know what's happening to your Devices if you can't see their telemetry with your own eyes. Therefore, the Spotflow IoT Platform allows you to visualize data using an integrated instance of Grafana, a popular open-source observability platform. You can use it to create custom queries over the received data, display their results in graphs, and organize the graphs into customized dashboards.

This page describes:

• How to configure the Platform so the Messages are routed to the integrated instance of Grafana.
• What is the format that the Messages must conform to.
• How to access the integrated instance of Grafana and query the received data.

tip

See Get Started for the particular configuration steps and code samples of sending Messages in the correct format.

Configuration

The instance of Grafana in the platform is a special type of Egress Sink that can be present at most once in each Workspace. If you want to visualize Messages from a Stream, follow Tutorial: Route Data to Spoflow Grafana. You can connect multiple Streams to Grafana Egress Sink:

Message Format

Messages routed to Grafana Egress Sink must be in the following format:

• The Message must be a JSON document encoded in UTF-8. The document root must be an object or an array of objects. Otherwise, the message is skipped.
• The field timestamp is required in each top-level object. The field must contain an ISO 8601 timestamp string. Otherwise, the object is skipped.
• A field name can contain only the characters 0-9, a-z, A-Z, _, and :. It also can't start with a number. If the field name doesn't conform to this format, it's skipped.
• A field value can be an object, a number, or a boolean. Objects are parsed recursively. Numbers are directly translated into data points; booleans are converted to 0 or 1 before that. Fields with other value types are skipped.

Example:

{
  "timestamp": "2023-04-03T15:16:03+01:00",
  "temperatureCelsius": 21.6,
  "revolutionsPerMinute": 7200
}

One message can also contain multiple data points and nested objects:

[
  {
    "timestamp": "2023-04-03T15:16:03+01:00",
    "machine": {
        "temperatureCelsius": 21.6,
        "revolutionsPerMinute": 7200
    },
    "weather": {
        "temperatureCelsius": 18.5,
        "humidityPercent": 65.8
    }
  },
  {
    "timestamp": "2023-04-03T15:17:03+01:00",
    "machine": {
        "temperatureCelsius": 21.9,
        "revolutionsPerMinute": 7201,
    },
    "weather": {
        "temperatureCelsius": 18.5,
        "humidityPercent": 65.8
    }
  },
]

Grafana

The integrated instance of Grafana is available to each User of the Platform. You can navigate to it from the Portal by clicking the link Grafana in the left sidebar. Each Workspace corresponds to a separate Grafana Organization. Therefore, if you want another User to see the data in Grafana, invite them to the corresponding Workspace.

Each Workspace provides two data sources:

• device_metric_workspace_<Workspace ID> - data points extracted from the Messages
• platform_metric_workspace_<Workspace ID> - usage data (automatically populated)

Both data sources use PostgreSQL with the extension TimescaleDB as the underlying database.

Device Metrics

The Platform extracts time series data points from each Message in the format described above routed to the Grafana Egress Sink. The data points are then stored in the table all_metric, see the schema:

Column Name	Type	Nullability	Description
time	TIMESTAMPTZ	NOT NULL	The timestamp of the data point.
id	TEXT	NOT NULL	The unique ID of the data point.
device_id	TEXT	NOT NULL	The ID of the Device that sent the Message.
site_id	TEXT	NULL	Currently unused, always NULL.
stream_group_name	TEXT	NOT NULL	The Stream Group that the Message was sent to.
stream_name	TEXT	NOT NULL	The Stream that the Message was sent to.
metric_name	TEXT	NOT NULL	The metric name.
value	DOUBLE PRECISION	NOT NULL	The metric value.

The metric name is constructed from the field name converted to snake case for each data point. In the case of nested objects, the field names are concatenated with an underscore. For example, the Messages from the previous section would produce the data points of the following metric names and values: temperature_celsius, revolutions_per_minute, machine_temperature_celsius, machine_revolutions_per_minute, weather_temperature_celsius, and weather_humidity_percent.

caution

There can be conflicts in the data points because of the following reasons:

• Naming conflicts between the fields in a single object: For example, the nested field a.x and the simple field a_x would both be converted to the metric name a_x.
• Multiple data points with the same timestamp: A single Device provides multiple data points whose timestamps differ by less than a millisecond.

In these cases, the Platform extracts only one of the data points with no guarantees about which one.

The Platform also creates a separate view for each metric name it receives. Therefore, you can easily query the data points of a particular metric:

SELECT "time", value FROM temperature_celsius

The schema of each of these views:

Column Name	Type	Nullability	Description
time	TIMESTAMPTZ	NOT NULL	The timestamp of the data point.
metric_name	TEXT	NOT NULL	The metric name.
value	DOUBLE PRECISION	NOT NULL	The metric value.
device_id	TEXT	NOT NULL	The ID of the Device that sent the Message.
stream_group_name	TEXT	NOT NULL	The Stream Group that the Message was sent to.
stream_name	TEXT	NOT NULL	The Stream that the Message was sent to.
site_id	TEXT	NULL	Currently unused, always NULL.

Platform Metrics

The Platform also provides usage data in the table all_metric:

Column Name	Type	Nullability	Description
time	TIMESTAMPTZ	NOT NULL	The timestamp of the data point.
device_id	TEXT	NOT NULL	The ID of the measured Device.
site_id	TEXT	NULL	Currently unused, always NULL.
stream_group_name	TEXT	NULL	The Stream Group of the measured Stream.
stream_name	TEXT	NULL	The measured Stream.
metric_name	TEXT	NOT NULL	The metric name.
value	DOUBLE PRECISION	NOT NULL	The metric value.
note	TEXT	NULL	An optional note.

No other tables are created in this data source. There are only two possible metric names:

• message_count - value contains the number of Messages received by the Platform since the previous measurement.
• total_bytes_sent - value contains the number of bytes received by the Platform since the previous measurement.

For example, you can query the number of Messages sent by a specific Device over time:

SELECT "time", value FROM all_metric
WHERE metric_name = 'message_count' AND device_id = 'my-device'

tip

See also the official documentation for more information about how to query and visualize data in Grafana. For example, it shows how to build dashboards that display multiple graphs and update them in real time.