# Analytics Query API > Query your raw bot detection events with read-only SQL over HTTP. Source: https://docs.centinelanalytica.com/api/analytics ## Overview The Analytics Query API gives you direct SQL access to your event data. Your key reads the validation events in this table. A request row has a nonzero `request_id`. You query the rows with ClickHouse SQL. The API returns the format that you select. Use the API to build a custom report. You can also load the data into a warehouse, or answer a question that the dashboard does not show. The API uses a read-only ClickHouse account. It applies a tenant row policy to every query. > **Note:** Your key reads only the events of your own organisation. The server applies this filter. You cannot disable it. ## Authentication Each request must have an API key in an `Authorization` header. Use the `Bearer` scheme: ```bash Authorization: Bearer sk_api_ro_your_key_here ``` Create a key in the [dashboard](https://docs.centinelanalytica.com/install/dashboard.md). You can also revoke a key there. Treat a key as a password. The key gives read access to all of your event data. Keep the key on your server. Do not put the key in a client application. If the key is missing, malformed, or unknown, the API returns `401` and an empty body. The API accepts no other authentication scheme. ## Send a request Send a `POST` request to the query endpoint. Put your SQL in the request body: ```bash curl -X POST https://api.centinelanalytica.com/query \ -H "Authorization: Bearer sk_api_ro_your_key_here" \ -H "Content-Type: text/plain" \ --data-binary "SELECT count() FROM events_distributed WHERE request_id != toUUID('00000000-0000-0000-0000-000000000000')" ``` The request has four constraints: * The API accepts only the `POST` method. * The SQL must be in the request body. The API ignores query string parameters. * Each request must have one statement. The API rejects multiple statements. * The request body has a limit of 64 KB. The endpoint sends no CORS headers. Therefore, a browser cannot call the API. Call the API from your server. ## Write a query Query the `events_distributed` table. Name the columns that you need: ```sql SELECT created_at, hostname, decision FROM events_distributed WHERE created_at > now() - INTERVAL 1 DAY LIMIT 100 ``` Avoid `SELECT *`. A named column list protects your result-size limit and makes the query stable when the schema changes. Use single quotes for a string value. In ClickHouse, double quotes identify a column. Thus `WHERE hostname = "example.com"` reads a column with the name `example.com`. The query then fails and returns an unknown identifier error. ```sql SELECT count() FROM events_distributed WHERE hostname = 'www.example.com' AND request_id != toUUID('00000000-0000-0000-0000-000000000000') ``` > **Warning:** The API has no bound query parameters. Escape a value only when you put it inside an already-delimited SQL string. Allowlist identifiers, operators, sort directions, limits, and every other SQL fragment. Quote escaping alone cannot secure structural SQL. Put a `created_at` filter in each query. The `created_at` column is the sort key of the table. A bounded time range prevents the execution timeout. ## Response formats The API returns tab-separated values by default. Add a `FORMAT` clause to select a different format: ```sql SELECT hostname, count() FROM events_distributed GROUP BY hostname FORMAT JSONEachRow ``` These formats are the most useful: | Format | Use for | | --------------- | ----------------------------------------------------------------------------------- | | `TabSeparated` | The default format. It is compact and has no header row. | | `TSVWithNames` | Tab-separated values with a header row. | | `CSVWithNames` | A spreadsheet import. | | `JSON` | One object with the `meta` field, the `data` field, and row statistics. | | `JSONEachRow` | One JSON object on each line. Use this format to load data into a different system. | | `PrettyCompact` | A table for a person to read. | The API does not compress the response. The `Content-Type` header is `application/x-ndjson` for each format. Parse the body by the format that you selected, not by this header. ## Event schema The table retains rows for 90 days. The `headers` column clears 14 days after the event. Other row data remains available for the full 90 days. Use `request_id != toUUID('00000000-0000-0000-0000-000000000000')` when you count or group requests. This excludes the older rows that have no request ID. A recent row is one request, so `count()` is correct. In the oldest rows, approximately one request in a hundred has two rows. For an exact count there, use `uniqExact(request_id)` on a time range of one hour or less. This function keeps each identifier in memory. A large time range exceeds the 4 GB memory limit. | Column | Type | Description | | ------------------------------- | -------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------------- | | `created_at` | DateTime64(3, 'UTC') | The time of the evaluation. Recent rows have the precision of one second. Older rows have the precision of one minute. | | `session_id` | UUID | The visitor session. Use this column to group the requests of one visitor. | | `request_id` | UUID | Unique for each request. | | `ip` | IPv6 | The client IP address. An IPv4 address has a mapped form, for example `::ffff:203.0.113.10`. | | `url` | String | The full request URL. | | `referrer` | String | The referrer header. The value is empty if the header is absent. | | `method` | String | The HTTP method. | | `hostname` | String | The host that serves the request. | | `endpoint_path` | String | The path of the URL, without the query string. | | `headers` | String | The request headers as a JSON object. The value is empty 14 days after the event. | | `user_agent` | String | The raw User-Agent header. | | `decision` | String | The conclusion of the detection engine. Refer to [Decisions and actions](#decisions-and-actions). | | `action` | String | The action that your policy applied to the request. | | `block_reason_categories_added` | Array(String) | The reasons that the engine flagged the request. The array is empty for clean traffic. | | `ip_country_code` | String | The two-letter country code from IP geolocation. | | `ip_region_code` | String | The region or state code, if known. | | `browser` | String | The browser family. The value is empty if the engine cannot identify the browser. | | `os` | String | The operating system. | | `device_type` | String | One of `desktop`, `mobile`, `tablet`, or `other`. | | `is_mobile` | Bool | True if the client has the properties of a mobile device. | | `crawler_id` | UUID | The identifier of the crawler in the crawler catalog. The value is `00000000-0000-0000-0000-000000000000` for other traffic. | | `crawler_name` | String | The name of the crawler, for example `Googlebot`. The value is empty for other traffic. | | `crawler_type` | String | The crawler category. Refer to [Crawlers](https://docs.centinelanalytica.com/api/crawlers.md) for the full list. | | `passport_id` | UUID | The identifier of the passport that the request verified with. The value is `00000000-0000-0000-0000-000000000000` for a request without a passport. | | `policy_verdict_rule_id` | UUID | The identifier of the rule in your policy that set the action. The value is `00000000-0000-0000-0000-000000000000` if no rule set the action. | | `is_datacenter` | Bool | True if the IP address belongs to a hosting or datacenter network. | | `is_proxy` | Bool | True if the IP address is a known proxy. | | `is_vpn` | Bool | True if the IP address is a known consumer VPN exit. | | `is_tor` | Bool | True if the IP address is a Tor exit node. | | `asn` | UInt32 | The autonomous system number of the client network. | | `asn_organization` | String | The name of the network operator. | To read the current column list, query the system catalog: ```sql SELECT name, type FROM system.columns WHERE database = 'default' AND table = 'events_distributed' ORDER BY position ``` ## Decisions and actions The `decision` column and the `action` column answer two different questions. If you confuse them, your numbers will be incorrect. | Column | Values | Meaning | | ---------- | ----------------------------------------------- | -------------------------- | | `decision` | `allow`, `block`, `redirect` | The verdict of the engine. | | `action` | `allow`, `monitor`, `skip`, `block`, `redirect` | The enforcement result. | The difference between the two columns is intentional. A rule in monitor mode writes `decision = 'block'` and `action = 'monitor'`. The engine flagged the request, and the visitor received the page. To count the traffic that you stopped, filter on `action`. A stopped request has the action `block` or the action `redirect`: ```sql SELECT count() FROM events_distributed WHERE action IN ('block', 'redirect') AND created_at > now() - INTERVAL 7 DAY AND request_id != toUUID('00000000-0000-0000-0000-000000000000') ``` To count the traffic that the engine would stop when every rule enforces, filter on `decision`: ```sql SELECT count() FROM events_distributed WHERE decision = 'block' AND created_at > now() - INTERVAL 7 DAY AND request_id != toUUID('00000000-0000-0000-0000-000000000000') ``` > **Warning:** If one rule is in monitor mode, the count of `decision = 'block'` is more than the number of requests that you stopped. Do not report that count as blocked requests. ## Example queries Daily traffic by decision: ```sql SELECT toDate(created_at) AS day, decision, count() AS requests FROM events_distributed WHERE created_at > now() - INTERVAL 30 DAY AND request_id != toUUID('00000000-0000-0000-0000-000000000000') GROUP BY day, decision ORDER BY day DESC, requests DESC FORMAT JSONEachRow ``` The crawlers that made the most requests in the last seven days: ```sql SELECT crawler_name, crawler_type, count() AS requests FROM events_distributed WHERE created_at > now() - INTERVAL 7 DAY AND crawler_name != '' AND request_id != toUUID('00000000-0000-0000-0000-000000000000') GROUP BY crawler_name, crawler_type ORDER BY requests DESC LIMIT 25 ``` The reasons that the engine flagged traffic: ```sql SELECT arrayJoin(block_reason_categories_added) AS reason, count() AS hits FROM events_distributed WHERE created_at > now() - INTERVAL 7 DAY AND request_id != toUUID('00000000-0000-0000-0000-000000000000') GROUP BY reason ORDER BY hits DESC ``` Datacenter traffic by network. This query is a good first check for a scraper: ```sql SELECT asn_organization, count() AS requests, uniq(session_id) AS sessions FROM events_distributed WHERE created_at > now() - INTERVAL 7 DAY AND is_datacenter AND request_id != toUUID('00000000-0000-0000-0000-000000000000') GROUP BY asn_organization ORDER BY requests DESC LIMIT 25 ``` The paths that flagged traffic requested most often: ```sql SELECT endpoint_path, count() AS requests FROM events_distributed WHERE created_at > now() - INTERVAL 1 DAY AND decision = 'block' AND request_id != toUUID('00000000-0000-0000-0000-000000000000') GROUP BY endpoint_path ORDER BY requests DESC LIMIT 50 ``` ## Limits | Limit | Value | | -------------------- | ----------- | | Query execution time | 30 seconds | | Rows returned | 1,000,000 | | Rows read | 500,000,000 | | Data read | 50 GB | | Query memory | 4 GB | | Concurrent queries | 8 | | Request body | 64 KB | | Response body | 4 MiB | | Row retention | 90 days | | `headers` retention | 14 days | If a query exceeds a limit, the API cancels the query. The API returns an error, not a truncated result. If a query exceeds the execution time, decrease the time range. As an alternative, aggregate the data in the database. The gateway collects each answer before it relays it. An answer above 4 MiB returns `502` with the text `clickhouse response too large`. Split a large export into time windows before it reaches that limit: ```sql SELECT created_at, session_id, decision FROM events_distributed WHERE created_at >= '2026-08-01 00:00:00' AND created_at < '2026-08-01 00:15:00' AND request_id != toUUID('00000000-0000-0000-0000-000000000000') FORMAT JSONEachRow ``` Then move the window forward and send the next request. Do not use `LIMIT` with a large `OFFSET`. If a response reaches the relay limit, divide the window and send the query again. The correct window size depends on your traffic and the selected columns. You cannot change the session settings. The API rejects a `SET` statement and a `SETTINGS` clause. This includes a change that decreases a limit. ## Errors The HTTP status shows who must correct the request: | Status | Meaning | | ------ | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | `200` | The query ran. The body contains the rows. | | `400` | The request is malformed, or ClickHouse refused the statement. Correct the statement. Do not send it again unchanged. | | `401` | The key is missing, malformed, unknown, revoked, or expired. The body is empty. | | `405` | The method is not `POST`. | | `502` | The database did not answer, or the answer is larger than 4 MiB. If the database did not answer, send the request again. If the answer is too large, decrease the time range. | The API returns a refused statement as a plain-text body. A ClickHouse error has a code, a message, and an identifier: ```text Code: 62. DB::Exception: Syntax error (Multi-statements are not allowed): failed at position 9 (end of query): ; SELECT 2. . (SYNTAX_ERROR) (version 26.2.19.43 (official build)) ``` The `Code` value and the identifier show the cause. These errors are the most frequent: | Code | Identifier | Cause | | ---- | ------------------------ | ----------------------------------------------------------------------------------------------------- | | 62 | `SYNTAX_ERROR` | The SQL is malformed. The API also returns this error for multiple statements. | | 47 | `UNKNOWN_IDENTIFIER` | The column does not exist. A double-quoted string literal is a frequent cause. | | 60 | `UNKNOWN_TABLE` | The table does not exist. Query `events_distributed`. | | 497 | `ACCESS_DENIED` | The query reads a table that your key cannot access, or it attempts to write data. | | 396 | `TOO_MANY_ROWS_OR_BYTES` | The result is more than one million rows. | | 164 | `READONLY` | The query tries to change a setting or to write data. | | 158 | `TOO_MANY_ROWS` | The query reads more than 500 million rows. Decrease the time range. | | 241 | `MEMORY_LIMIT_EXCEEDED` | The query uses more than 4 GB of memory. Decrease the time range, or replace `uniqExact` with `uniq`. | The API returns an empty body if the key is missing or unknown. The gateway can refuse an answer at 4 MiB before ClickHouse returns a size error. If a query runs longer than 30 seconds, the API cancels it. The error shows the elapsed time and the maximum. ## See also * [MCP Server](https://docs.centinelanalytica.com/api/mcp.md) to give an AI assistant the same access * [OpenAPI document](https://docs.centinelanalytica.com/openapi.json), which describes this endpoint as `POST /query` for client generators * [Crawlers](https://docs.centinelanalytica.com/api/crawlers.md) for the crawler categories * [Glossary](https://docs.centinelanalytica.com/concepts/glossary.md) for the block reason categories * [Dashboard](https://docs.centinelanalytica.com/install/dashboard.md) to create and revoke API keys