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_latencyFROM gateway_requests_v1ORDER BY time DESCLIMIT 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 totalFROM gateway_requests_v1WHERE 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 totalFROM gateway_requests_v1WHERE 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 p95FROM gateway_requests_v1WHERE 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 totalFROM gateway_requests_v1WHERE response_status >= 500  AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)GROUP BY pathORDER BY total DESCLIMIT 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_rateFROM gateway_requests_v1WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 24 HOUR)GROUP BY deployment_idORDER 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 requestsFROM gateway_requests_v1WHERE app_id = 'app_1234'  AND time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 6 HOUR)GROUP BY minuteORDER 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 totalFROM gateway_requests_v1WHERE time >= toUnixTimestamp64Milli(now64(3) - INTERVAL 7 DAY)GROUP BY app_id, response_statusORDER BY app_id, total DESC