# Querying Device Data

Data that is stored in the [akenza Database](https://docs.akenza.io/akenza.io/get-started/your-data-flow/connectors/databases/akenza-db) can be retrieved via the [Query API](https://docs.api.akenza.io/#e754213a-147a-4a0f-a823-95042ff54531). 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)

{% hint style="success" %}
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.
{% endhint %}

{% hint style="warning" %}
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.).
{% endhint %}

## 1. Endpoint selection

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

<figure><img src="https://documents.lucid.app/documents/2b6ca9ec-366f-4ec5-888d-dd34eb4d5e21/pages/GP18WCZESPoW?a=882&#x26;x=-1436&#x26;y=-403&#x26;w=3406&#x26;h=1597&#x26;store=1&#x26;accept=image%2F*&#x26;auth=LCA%20953ffd5e92757f1d4ed933b77127d2ba3043cf526214324876e575305550c8d0-ts%3D1744186451" alt=""><figcaption></figcaption></figure>

**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`&#x20;

## 2. Using the selected endpoint

Below you can find the request/response pair for the endpoints documented above.&#x20;

**Prerequisites:**

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

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

* install [httpie](https://httpie.io/docs/cli/installation)

```bash
# 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
```

{% hint style="info" %}
Also refer to the [REST API](https://docs.api.akenza.io/#1819801d-0c87-47ca-8838-515df3818bc1) documentation.
{% endhint %}

### /v3/devices/$DEVICE\_ID/query

#### *root endpoint (no suffix needed)*

Load raw samples for a device.

request

```json
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

```json
[
  {
    "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

```json
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

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

#### .../aggregated/hourly/kpi

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

request

```json
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

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

#### .../raw/time-series

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

request

```json
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

```json
{
  "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

```json
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

```json
{
  "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

```json
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

```json
{
  "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

```json
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

```json
{
  "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

```json
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

```json
{
  "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

```json
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

```json
{
  "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:

```json
{
    "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 last value in 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).&#x20;

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.