SQL Queries

APXY stores all traffic in a SQLite database. You can run arbitrary read-only SQL queries for advanced analysis that goes beyond what the search and filter tools provide.

Web UI

SQL queries are available through the CLI. The Web UI does not have a SQL console.

CLI


apxy sql query "SELECT host, COUNT(*) as cnt FROM traffic_logs GROUP BY host ORDER BY cnt DESC LIMIT 10"

Available Tables

`traffic_logs`

Column	Type	Description
`id`	TEXT	Record ID
`timestamp`	TEXT	ISO 8601 timestamp
`method`	TEXT	HTTP method
`url`	TEXT	Full URL
`host`	TEXT	Host header
`path`	TEXT	URL path
`status_code`	INTEGER	Response status
`duration_ms`	INTEGER	Response time in ms
`tls`	INTEGER	1 if HTTPS
`mocked`	INTEGER	1 if mocked
`request_headers`	TEXT	JSON object
`response_headers`	TEXT	JSON object
`request_body`	BLOB	Raw request body
`response_body`	BLOB	Raw response body
`request_content_type`	TEXT	Request Content-Type
`response_content_type`	TEXT	Response Content-Type

`mock_rules`

Column	Type	Description
`id`	TEXT	Rule ID
`name`	TEXT	Rule name
`priority`	INTEGER	Priority (lower = higher)
`active`	INTEGER	1 if active
`url_pattern`	TEXT	URL match pattern
`match_type`	TEXT	exact, wildcard, regex
`method`	TEXT	HTTP method filter
`response_status`	INTEGER	Mock response status
`response_headers`	TEXT	JSON object
`response_body`	TEXT	Mock response body
`delay_ms`	INTEGER	Response delay

Example Queries

Top hosts by request count


SELECT host, COUNT(*) as cnt
FROM traffic_logs
GROUP BY host
ORDER BY cnt DESC
LIMIT 10

Find slow requests


SELECT method, url, duration_ms
FROM traffic_logs
WHERE duration_ms > 2000
ORDER BY duration_ms DESC
LIMIT 10

Error rate by host


SELECT host,
  COUNT(*) as total,
  SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) as errors,
  ROUND(100.0 * SUM(CASE WHEN status_code >= 400 THEN 1 ELSE 0 END) / COUNT(*), 1) as error_pct
FROM traffic_logs
GROUP BY host
ORDER BY errors DESC

5xx errors in the last hour


SELECT host, COUNT(*) as errors
FROM traffic_logs
WHERE status_code >= 500
  AND timestamp > datetime('now', '-1 hour')
GROUP BY host
ORDER BY errors DESC

Mocked vs real traffic


SELECT
  CASE WHEN mocked = 1 THEN 'mocked' ELSE 'real' END as type,
  COUNT(*) as count
FROM traffic_logs
GROUP BY mocked

Average response time by endpoint


SELECT method, path,
  COUNT(*) as requests,
  ROUND(AVG(duration_ms), 0) as avg_ms,
  MAX(duration_ms) as max_ms
FROM traffic_logs
GROUP BY method, path
ORDER BY avg_ms DESC
LIMIT 10