Querying Device Data

Data that is stored in the akenza Database can be retrieved via the Query API. akenza builds different data products that can be retrieved:

Raw: The raw sensor data
Time series: Time series aggregations based on the raw data (Bucketing, Resampling)
Aggregations: Hourly aggregates of the raw data as a single kpi (number) or time-series (efficient for larger timespans)

akenza automatically generates hourly aggregates of data, which can be used to easily visualize trends over a longer period of time. The aggregates can be queried via a specific API for aggregated historical data.

If all data should be accessible at any point, a pull-based loading mechanism via REST API is not the recommended approach. Instead, an event-based output connector should be considered, where data is automatically forwarded to the client application using a data sink output connector (Kafka, Azure Events Hub, AWS Kinesis, GCP Pub/Sub, SQL database, etc.).

1. Endpoint selection

Please follow the diagram below to select the right data product for your use case:

Examples:

Query the temperature measured by one sensor yesterday, with high time resolution
- finding the right endpoint
  - querying a single device
  - no aggregation function is required
  - high time resolution is needed
- resulting endpoint would be /v3/devices/$DEVICE_ID/query
Query the average temperature measured by one sensor today from 08:00 to 18:00
- finding the right endpoint
  - querying a single device
  - aggregation functions are required (average)
  - the timespan is less than 24h (08:00 to 18:00 ⇒ 10 hours)
  - a single metric is needed (the average)
- resulting endpoint would be /v3/devices/$DEVICE_ID/query/raw/kpi
Query the maximum daily CO2 concentration measured by one sensor during the last 3 three months
- finding the right endpoint
  - querying a single device
  - aggregation functions are required (maximum)
  - the timespan is more than 24h (3 months)
  - a time series is needed (daily maximum)
- the resulting endpoint would be /v3/devices/$DEVICE_ID/query/aggregated/hourly/time-series
Query the minimum temperature of several sensors having a common tag today from 08:00 to 18:00
- finding the right endpoint
  - querying multiple devices using tag
  - the timespan is less than 24h (08:00 to 18:00 ⇒ 10 hours)
  - a single metric is needed (the minimum)
- the resulting endpoint is /v3/tags/$TAG_ID/query/raw/accumulated-kpi

2. Using the selected endpoint

Below you can find the request/response pair for the endpoints documented above.

Prerequisites:

create an akenza api key that has the asset.read permission for the required scope

export API_KEY=<your api key>
export DEVICE_ID=<target device id>
export TAG_ID=<target tag id>

install httpie

# universal - using pip
python -m pip install --upgrade pip wheel
python -m pip install httpie

# on macOS
brew update
brew install httpie

# on Windows
choco install httpie

Also refer to the REST API documentation.

/v3/devices/$DEVICE_ID/query

root endpoint (no suffix needed)

Load raw samples for a device.

request

http POST "https://api.akenza.io/v3/devices/$DEVICE_ID/query" x-api-key:$API_KEY --pretty=all  --output ./raw/raw-result.$DEVICE_ID.json -d << 'EOF'
{
  "topic": "climate",
  "timestamp": {
    "gt": "2023-06-25T06:00:00.000Z"
  },
  "limit": 8000,
  "skip": 0
}
EOF

result

[
  {
    "timestamp": "2023-06-30T11:20:51.060+00:00",
    "deviceId": "<deviceId>",
    "data": {
      "temperature": 23.87,
      "humidity": 54.41,
      "pressure": 967,
      "co2": 380,
      "tvoc": 246,
      "light": 1255.7
    },
    "topic": "climate"
  },
  ...
  {
    "timestamp": "2023-06-25T06:05:52.577+00:00",
    "deviceId": "<deviceId>",
    "data": {
      "temperature": 24.84,
      "humidity": 50.97,
      "pressure": 972,
      "co2": 384,
      "tvoc": 264,
      "light": 135.13
    },
    "topic": "climate"
  }
]

.../raw/kpi

Load a single metric for a device based on raw data.

request

http POST "https://api.akenza.io/v3/devices/$DEVICE_ID/query/raw/kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/raw-kpi-device.$DEVICE_ID.json -d << 'EOF'
{
    "topic": "climate",
    "dataKey": "temperature",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-30T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "deviceId": "<deviceId>",
  "topic": "climate",
  "dataKey": "temperature",
  "kpi": 24.21
}

.../aggregated/hourly/kpi

Load a single metric for a device based on hourly aggregates.

request

http POST "https://api.akenza.io/v3/devices/$DEVICE_ID/query/aggregated/hourly/kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/aggregate-kpi-device.$DEVICE_ID.json -d << 'EOF'
{
    "topic": "climate",
    "dataKey": "temperature",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-25T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "deviceId": "<deviceId>",
  "topic": "climate",
  "dataKey": "temperature",
  "kpi": 26.2
}

.../raw/time-series

Load a time series for a device based on raw data.

request

http POST "https://api.akenza.io/v3/devices/$DEVICE_ID/query/raw/time-series" x-api-key:$API_KEY --pretty=all  --output ./aggregated/raw-time-series-device.$DEVICE_ID.json -d << 'EOF'
{
    "topic": "climate",
    "dataKey": "temperature",
    "accumulator": "MAX",
    "bucketInterval": "PT1H",
    "interval": {
        "from": "2023-06-30T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "deviceId": "<deviceId>",
  "topic": "climate",
  "dataKey": "temperature",
  "dataPoints": [
    24.21,
    ...
    23.22
  ],
  "timestamps": [
    "2023-06-30T00:00:00Z",
    ...
    "2023-06-30T17:00:00Z"
  ]
}

.../aggregated/hourly/time-series

Load a time series for a device based on hourly aggregates.

request

http POST "https://api.akenza.io/v3/devices/$DEVICE_ID/query/aggregated/hourly/time-series" x-api-key:$API_KEY --pretty=all  --output ./aggregated/aggregated-hourly-time-series-device.$DEVICE_ID.json -d << 'EOF'
{
    "topic": "climate",
    "dataKey": "temperature",
    "accumulator": "MAX",
    "bucketInterval": "PT1H",
    "interval": {
        "from": "2023-06-25T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "deviceId": "<deviceId>",
  "topic": "climate",
  "dataKey": "temperature",
  "dataPoints": [
    25.24,
    ...
    23.22
  ],
  "timestamps": [
    "2023-06-25T00:00:00Z",
    ...
    "2023-06-30T17:00:00Z"
  ]
}

/v3/tags/$TAG_ID/query

.../raw/kpi

Batch load a single metric per device belonging to the tag based on raw data.

request

http POST "https://api.akenza.io/v3/tags/$TAG_ID/query/raw/kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/raw-kpi.$TAG_ID.json -d << 'EOF'
{
    "topic": "area_count",
    "dataKey": "peopleCount",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-30T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "content": [
    {
      "deviceId": "<deviceId1>",
      "topic": "area_count",
      "dataKey": "peopleCount",
      "kpi": 3
    },
    ...
    {
      "deviceId": "<deviceId1N>",
      "topic": "area_count",
      "dataKey": "peopleCount",
      "kpi": 336
    }
  ],
  "totalPages": 1,
  "totalElements": 12,
  "last": true,
  "size": 20,
  "number": 0,
  "first": true,
  "numberOfElements": 0,
  "empty": false
}

.../aggregated/hourly/kpi

Batch load a single metric per device belonging to the tag based on hourly aggregates.

request

http POST "https://api.akenza.io/v3/tags/$TAG_ID/query/aggregated/hourly/kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/aggregated-kpi.$TAG_ID.json -d << 'EOF'
{
    "topic": "area_count",
    "dataKey": "peopleCount",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-25T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "content": [
    {
      "deviceId": "<deviceId1>",
      "topic": "area_count",
      "dataKey": "peopleCount",
      "kpi": 36.0
    },
    ...
    {
      "deviceId": "<deviceIdN>",
      "topic": "area_count",
      "dataKey": "peopleCount",
      "kpi": 861.0
    }
  ],
  "totalPages": 1,
  "totalElements": 12,
  "last": true,
  "size": 20,
  "number": 0,
  "first": true,
  "numberOfElements": 0,
  "empty": false
}

.../raw/accumulated-kpi

Load a single metric summed across devices belonging to the tag based on raw data.

request

http POST "https://api.akenza.io/v3/tags/$TAG_ID/query/raw/accumulated-kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/raw-accumulated-kpi.$TAG_ID.json -d << 'EOF'
{
    "topic": "area_count",
    "dataKey": "peopleCount",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-30T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "kpi": 861.0,
  "tagId": "<tagId>",
  "topic": "area_count",
  "dataKey": "peopleCount"
}

.../aggregated/hourly/accumulated-kpi

Load a single metric summed across devices belonging to the tag based on hourly aggregates.

request

http POST "https://api.akenza.io/v3/tags/$TAG_ID/query/aggregated/hourly/accumulated-kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/aggregated-accumulated-kpi.$TAG_ID.json -d << 'EOF'
{
    "topic": "area_count",
    "dataKey": "peopleCount",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-25T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

response

{
  "kpi": 861.0,
  "tagId": "<tagId>",
  "topic": "area_count",
  "dataKey": "peopleCount"
}

/v3/devices/query/batch

same syntax as in /v3/tags/$TAG_ID/query but add a deviceIds list in the body as seen below:

{
    "deviceIds": [
      "$DEVICE_ID",
      "$DEVICE_ID_2",
      "$DEVICE_ID_3"
    ],
    "topic": "area_count",
    "dataKey": "peopleCount",
    "accumulator": "MAX",
    "interval": {
        "from": "2023-06-25T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}

3. Accumulators

akenza provides several aggregation methods to sum data in an interval.

MIN
- the reported minimum during the time range
MAX
- the reported maximum during the time range
AVG
- the average of reported values during the time range
SUM
- the sum of reported values during the time range
COUNT
- the count of reported values during the time range
FIRST
- the first value in the time range
LAST (or NONE)
- the count of reported values during the time range
DISTINCT_COUNT
- the unique value counts for string values during the time range
MATCHED_COUNT
- the number of values that are true during the time range
UNMATCHED_COUNT
- the number of values that are false during the time range
OCCUPANCY
- Refer to Smart Occupancy Aggregations

Smart Occupancy Aggregations

The algorithm to calculate occupancy is based on a bucketing algorithm (the hour is split into 12 buckets, the occupancy is determined by the number of buckets that are marked as occupied).

When configuring working hours/public holidays on workspace level, the value is normalized to only match working hours.

The resulting value is the percentage of occupied time.

http POST "https://api.akenza.io/v3/devices/$DEVICE_ID/query/aggregated/hourly/kpi" x-api-key:$API_KEY --pretty=all  --output ./aggregated/aggregate-kpi-device.$DEVICE_ID.json -d << 'EOF'
{
    "topic": "occupancy",
    "dataKey": "occupied",
    "accumulator": "OCCUPANCY",
    "interval": {
        "from": "2023-06-25T00:00:00.000Z",
        "to":  "2023-06-30T18:00:00.000Z"
    }
}
EOF

{"deviceId":"<deviceId>","topic":"occupancy","dataKey":"occupied","kpi":37.65}

4. Bucket Intervals

Bucket intervals can be used to resample the resulting time-series. Bucket intervals need to be specified in the ISO-8601 duration format (e.g. PT1H, P1D).

For hourly aggregations, the minimum bucket interval is PT1H.

Choose your bucket intervals, so that no more than 730 buckets are returned.

PreviousFiltering NextSchema inference

Last updated 21 days ago

Was this helpful?