# Telemetry API

> Query the tag values stored in the Proxus historian over HTTP with a single SQL SELECT, and read the rows back as JSON lines.

Source: https://proxus.io/docs/develop/telemetry-api

At the end of this guide, your script runs a SQL query against the stored tag values of a device and reads the
result, one JSON object per row.

## Before you start

You need:

- A token from the hub, as described in [REST API](https://proxus.io/docs/develop/rest-api#step-1-get-a-token).
- The hub's address, for example `http://localhost:8080`.
- A hub that stores telemetry in ClickHouse, the database in the delivered Docker installation
  (`TelemetryDB = "clickhouse"` in the `[Database]` section of the configuration).

> **Warning:**
The Telemetry API runs the SQL you send, so it cannot apply a user's Unified Namespace permissions. Only users
with an administrator role may call it; others get `403`. Give this access only to systems you trust. Users without
it read values through the screens, which apply [UNS permissions](https://proxus.io/docs/administer/uns-permissions).

## The request

```text
POST /api/Telemetry/execute?maxRows=10000
Authorization: Bearer <token>
Content-Type: application/json

{ "query": "SELECT ..." }
```

| Part | Meaning |
| --- | --- |
| `query` | One SQL statement, in ClickHouse SQL. |
| `maxRows` | The most rows to return, from 1 to 50000. Default 10000. A `LIMIT` in your query is lowered to this value when it is higher; a query without `LIMIT` gets one. |

The answer has the content type `application/x-ndjson`: one JSON object per line, one line per row. If the query
does not end with a `FORMAT` clause, `FORMAT JSONEachRow` is added for you.

## The table of stored values

Every value Proxus stores is one row in `DeviceRawData`:

| Column | Type | Meaning |
| --- | --- | --- |
| `Time` | `DateTime64(3)` | When the value was read, in UTC, to the millisecond. |
| `DeviceId` | `UInt32` | The device's ID (the `Oid` of the device in the [REST API](https://proxus.io/docs/develop/rest-api)). |
| `DeviceName` | `String` | The device's name at the time of reading. |
| `Key` | `String` | The tag name. |
| `DataType` | `Enum8` | `Bool`, `Short`, `UShort`, `Int`, `UInt`, `Float`, `Long`, `ULong`, `Double`, `String`, `Byte`, `DateTime`. |
| `Unit` | `String` | The tag's unit, empty when it has none. |
| `NumericValue` | `Float64` | The value of numeric and Boolean tags. |
| `StringValue` | `String` | The value of text tags. |

Rows are sorted by device, name, tag and time, so a filter on `DeviceId` or `DeviceName`, `Key` and a time range
is the fast way to read them.

## Step 1: Run a query

```bash
TOKEN=$(curl -s -X POST http://localhost:8080/api/Authentication/Authenticate \
  -H "Content-Type: application/json" -d '{"userName":"Admin","password":""}')

curl -s -X POST "http://localhost:8080/api/Telemetry/execute?maxRows=100" \
  -H "Authorization: Bearer $TOKEN" -H "Content-Type: application/json" \
  -d "{\"query\": \"SELECT DeviceName, Key, round(avg(NumericValue), 2) AS avg_value, count() AS samples FROM DeviceRawData WHERE Time >= now() - INTERVAL 1 HOUR AND DeviceName = 'LYS_PACKER_04' GROUP BY DeviceName, Key ORDER BY Key\"}"
```

The answer:

```json
{"DeviceName":"LYS_PACKER_04","Key":"BatchCount","avg_value":1522.98,"samples":"2262"}
{"DeviceName":"LYS_PACKER_04","Key":"MotorCurrent","avg_value":28.91,"samples":"2262"}
{"DeviceName":"LYS_PACKER_04","Key":"Speed","avg_value":81.68,"samples":"2262"}
{"DeviceName":"LYS_PACKER_04","Key":"Temperature","avg_value":24.64,"samples":"2262"}
{"DeviceName":"LYS_PACKER_04","Key":"Vibration","avg_value":1.85,"samples":"2262"}
```

> **Note:**
64-bit integers, such as the result of `count()`, arrive as strings (`"2262"`), so that no digits are lost in
JSON. Convert them in your code, or cast them in SQL (`toUInt32(count())`).

## Step 2: Use it from code

```python
import json
import requests

HUB = "http://localhost:8080"

token = requests.post(
    f"{HUB}/api/Authentication/Authenticate",
    json={"userName": "Admin", "password": ""},
    timeout=30,
).text.strip()

sql = """
SELECT Key, round(avg(NumericValue), 2) AS avg_value, max(NumericValue) AS max_value
FROM DeviceRawData
WHERE DeviceName = 'LYS_PACKER_04'
  AND Time >= now() - INTERVAL 1 HOUR
GROUP BY Key
ORDER BY Key
"""

response = requests.post(
    f"{HUB}/api/Telemetry/execute",
    params={"maxRows": 1000},
    json={"query": sql},
    headers={"Authorization": f"Bearer {token}"},
    timeout=60,
)
response.raise_for_status()
for line in response.text.splitlines():
    row = json.loads(line)
    print(row["Key"], row["avg_value"], row["max_value"])
```
```csharp
using System.Net.Http.Headers;
using System.Net.Http.Json;
using System.Text.Json;

var hub = new HttpClient { BaseAddress = new Uri("http://localhost:8080/") };

var login = await hub.PostAsJsonAsync("api/Authentication/Authenticate",
    new { userName = "Admin", password = "" });
login.EnsureSuccessStatusCode();
hub.DefaultRequestHeaders.Authorization =
    new AuthenticationHeaderValue("Bearer", await login.Content.ReadAsStringAsync());

// The latest value of each tag of one device
var query = new {
    query = """
            SELECT Key, argMax(NumericValue, Time) AS last_value, max(Time) AS last_time
            FROM DeviceRawData
            WHERE DeviceName = 'LYS_PACKER_04' AND Time >= now() - INTERVAL 1 HOUR
            GROUP BY Key ORDER BY Key
            """
};
var response = await hub.PostAsJsonAsync("api/Telemetry/execute?maxRows=100", query);
response.EnsureSuccessStatusCode();
foreach (var line in (await response.Content.ReadAsStringAsync()).Split('\n', StringSplitOptions.RemoveEmptyEntries)) {
    using var row = JsonDocument.Parse(line);
    Console.WriteLine($"{row.RootElement.GetProperty("Key")} = {row.RootElement.GetProperty("last_value")}");
}
```

## Read a long time range

One call returns at most 50000 rows. To read more, page by time: sort by `Time` and `DeviceId`, remember the last
row, and start the next query after it.

```sql
SELECT Time, DeviceId, Key, NumericValue
FROM DeviceRawData
WHERE DeviceName = 'LYS_PACKER_04'
  AND Time >= toDateTime64('2026-10-01 00:00:00', 3)
  AND Time <  toDateTime64('2026-10-02 00:00:00', 3)
  AND (Time > toDateTime64('2026-10-01 06:12:40.250', 3)
       OR (Time = toDateTime64('2026-10-01 06:12:40.250', 3) AND DeviceId > 69))
ORDER BY Time ASC, DeviceId ASC
LIMIT 50000
```

Stop when a page returns fewer rows than you asked for.

## What the API refuses

The query is checked before it reaches the database. These answers come back with status `400`:

| Message | Cause |
| --- | --- |
| `Query cannot be empty` | No `query` in the body. |
| `Invalid query: Only SELECT/CTE (WITH), comments, or 'SET TIMEZONE' are allowed at the beginning.` | The statement starts with something else, such as `SHOW` or `DESCRIBE`. |
| `Invalid query: Multiple statements are not allowed.` | More than one statement, separated by `;`. |
| `Invalid query: After CTEs, only a SELECT statement is allowed.` | A `WITH` block followed by anything but `SELECT`. |
| `Invalid query: Security violation: The SQL keyword 'SYSTEM' is not permitted.` | The query contains a word that changes data or the server: `UPDATE`, `DELETE`, `INSERT`, `MERGE`, `UPSERT`, `DROP`, `CREATE`, `ALTER`, `TRUNCATE`, `RENAME`, `ATTACH`, `DETACH`, `OPTIMIZE`, `SYSTEM`, `KILL`, `GRANT`, `REVOKE`, `COPY`, `CALL`, `EXEC`, `EXECUTE`. The check also applies to names, so `system.tables` is refused. Words inside quotes are ignored. |

Other answers:

| Status | Meaning |
| --- | --- |
| `401` | No token, or the token has expired. |
| `403` | The user does not have an administrator role. |
| Status of the database, with `Query failed: ...` | The database refused the query, for example a misspelled column or table. The text is the database's own message. |
| `500` with `ClickHouse connection failed: ...` | The hub cannot reach the telemetry database. See [Troubleshoot data](https://proxus.io/docs/troubleshoot/data). |

## What's next

- [MQTT and the UNS topics](https://proxus.io/docs/develop/mqtt): receive values live instead of querying them.
- [Historical import](https://proxus.io/docs/develop/historical-import): add older values to the same table.
- [Reports](https://proxus.io/docs/analyze/reports): ready-made reports on the same data.