Query gateway request analytics

The POST /v2/analytics.getGatewayRequests endpoint runs SQL queries on the requests that Unkey routed to your deployments. Use it from a trusted backend with a root key. Never put a root key in browser code. For the request and response schemas, see the API reference.

Authenticate the request

Use a root key with the project.*.read_gateway_requests permission. This permission gives access to the gateway request data of all projects in the workspace. Unkey limits each query to the workspace of the root key. A query cannot read the data of a different workspace, and it cannot remove this filter.

Send a query

Send a JSON object with a query string. The response contains meta.requestId, which identifies the API request, and data, which contains an array of result objects.

SELECT path, response_status, total_latency
FROM gateway_requests_v1
ORDER BY time DESC
LIMIT 10

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT path, response_status, total_latency FROM gateway_requests_v1 ORDER BY time DESC LIMIT 10"}'

Response

{
  "meta": {
    "requestId": "req_1234"
  },
  "data": [
    {
      "path": "/v1/orders",
      "response_status": 200,
      "total_latency": 42
    }
  ]
}

Queries follow the SQL limits and the resource limits in Query restrictions. Only SELECT queries are permitted. CTEs, subqueries, UNION, and EXCEPT are also permitted.

Select the data of one project, app, or environment

Each row contains project_id, app_id, and environment_id. Add a filter on one of these columns to get the data of one deployment target.

SELECT count() AS total
FROM gateway_requests_v1
WHERE app_id = 'app_1234'
  AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT count() AS total FROM gateway_requests_v1 WHERE app_id = '\''app_1234'\'' AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)"}'

Use IN to select more than one target.

SELECT
  environment_id,
  count() AS total
FROM gateway_requests_v1
WHERE environment_id IN ('env_1234', 'env_5678')
  AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)
GROUP BY environment_id

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT environment_id, count() AS total FROM gateway_requests_v1 WHERE environment_id IN ('\''env_1234'\'', '\''env_5678'\'') AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR) GROUP BY environment_id"}'

If you do not add one of these filters, the query reads all projects in the workspace.

Understand the time range

Two limits control the time range of a query: the 7 day history of gateway_requests_v1 and the retention setting of your workspace plan. A query gets only the rows that satisfy both limits. If you do not add a time filter, Unkey limits the results to your workspace retention range.

Reference available columns

The table contains one row for each request. Unkey filters workspace_id automatically, thus you do not need to add it to a query. A query must name the columns that it needs. SELECT * fails, because the table also contains columns of the Unkey infrastructure that a workspace cannot read.

Column	Type	Description
`request_id`	String	Unique request ID
`time`	Int64	Request time as a Unix timestamp in milliseconds
`project_id`	String	Project ID
`app_id`	String	App ID
`environment_id`	String	Environment ID
`deployment_id`	String	Deployment that received the request
`instance_id`	String	Instance that processed the request
`region`	String	Region that served the request
`method`	String	HTTP method in upper case
`host`	String	Host header of the request
`path`	String	Request path
`query_string`	String	Raw query string
`query_params`	Map(String, Array(String))	Parsed query parameters
`request_headers`	Array(String)	Request headers as `Key: Value` pairs
`request_body`	String	Request body
`response_status`	Int32	HTTP status code of the response
`response_headers`	Array(String)	Response headers as `Key: Value` pairs
`response_body`	String	Response body
`user_agent`	String	User agent of the caller
`ip_address`	String	IP address of the caller
`total_latency`	Int64	Full end to end time in milliseconds
`instance_latency`	Int64	Time your instance used, in milliseconds
`gateway_latency`	Int64	Time the Unkey gateway added, in milliseconds

Read a latency percentile

The table keeps total_latency, instance_latency, and gateway_latency as plain numbers. Use quantile on these columns.

SELECT quantile(0.95)(total_latency) AS p95
FROM gateway_requests_v1
WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 1 HOUR)

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT quantile(0.95)(total_latency) AS p95 FROM gateway_requests_v1 WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 1 HOUR)"}'

Find the paths with the most errors

Group by path to find the endpoints that return the most server errors. The table keeps time as Unix milliseconds, thus the time filter uses toUnixTimestamp64Milli.

SELECT
  path,
  count() AS total
FROM gateway_requests_v1
WHERE response_status >= 500
  AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)
GROUP BY path
ORDER BY total DESC
LIMIT 10

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT path, count() AS total FROM gateway_requests_v1 WHERE response_status >= 500 AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR) GROUP BY path ORDER BY total DESC LIMIT 10"}'

Calculate an error rate for each deployment

Use countIf to count a subset of the rows in the same query. This example compares the failed requests with all requests of each deployment.

SELECT
  deployment_id,
  count() AS total,
  countIf(response_status >= 500) AS errors,
  round(countIf(response_status >= 500) / count() * 100, 2) AS error_rate
FROM gateway_requests_v1
WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)
GROUP BY deployment_id
ORDER BY error_rate DESC

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT deployment_id, count() AS total, countIf(response_status >= 500) AS errors, round(countIf(response_status >= 500) / count() * 100, 2) AS error_rate FROM gateway_requests_v1 WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR) GROUP BY deployment_id ORDER BY error_rate DESC"}'

Build a zero-filled time series

Use WITH FILL to get each minute, including the minutes with no requests. This query gives a dashboard chart the full range without gap filling in the client.

SELECT
  toStartOfMinute(fromUnixTimestamp64Milli(time)) AS minute,
  count() AS requests
FROM gateway_requests_v1
WHERE app_id = 'app_1234'
  AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 6 HOUR)
GROUP BY minute
ORDER BY minute WITH FILL
  FROM toStartOfMinute(now() - INTERVAL 6 HOUR)
  TO toStartOfMinute(now())
  STEP INTERVAL 1 MINUTE

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT toStartOfMinute(fromUnixTimestamp64Milli(time)) AS minute, count() AS requests FROM gateway_requests_v1 WHERE app_id = '\''app_1234'\'' AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 6 HOUR) GROUP BY minute ORDER BY minute WITH FILL FROM toStartOfMinute(now() - INTERVAL 6 HOUR) TO toStartOfMinute(now()) STEP INTERVAL 1 MINUTE"}'

Compare the status codes of each app

Group by app_id and response_status to see the response mix of each app.

SELECT
  app_id,
  response_status,
  count() AS total
FROM gateway_requests_v1
WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 7 DAY)
GROUP BY app_id, response_status
ORDER BY app_id, total DESC

curl --request POST \
  --url https://api.unkey.com/v2/analytics.getGatewayRequests \
  --header "Authorization: Bearer $UNKEY_ROOT_KEY" \
  --header "Content-Type: application/json" \
  --data '{"query":"SELECT app_id, response_status, count() AS total FROM gateway_requests_v1 WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 7 DAY) GROUP BY app_id, response_status ORDER BY app_id, total DESC"}'

Getting Started

Platform

Build & Deploy

Networking

Observability

CLI

Security

Keyspaces

AI Code Gen

Audit Logs

CLI

Errors

Query gateway request analytics

Authenticate the request

Send a query

Select the data of one project, app, or environment

Understand the time range

Reference available columns

Read a latency percentile

Find the paths with the most errors

Calculate an error rate for each deployment

Build a zero-filled time series

Compare the status codes of each app

​Authenticate the request

​Send a query

​Select the data of one project, app, or environment

​Understand the time range

​Reference available columns

​Read a latency percentile

​Find the paths with the most errors

​Calculate an error rate for each deployment

​Build a zero-filled time series

​Compare the status codes of each app

Authenticate the request

Send a query

Select the data of one project, app, or environment

Understand the time range

Reference available columns

Read a latency percentile

Find the paths with the most errors

Calculate an error rate for each deployment

Build a zero-filled time series

Compare the status codes of each app