Verify Response Codes

[trieloff]

Use case: you want to verify that the CDN is delivering the correct status code for a given URL consistently.

The below query gets the last 1000 requests for a URL, groups by status code and lists for each status code the first and last time it has been seen.

SELECT status_code, COUNT(time) AS requests, MIN(time) AS first, MAX(time) AS last FROM (
    SELECT TIMESTAMP_MICROS(CAST(time_start_usec AS INT64)) AS time, status_code FROM `helix-225321.helix_logging_6v0sHgrPTGUGS5PHOXZ0H1.requests202101*`
    WHERE req_http_X_URL = "/en/publish/2020/12/15/the-future-of-government-keeping-government-business-moving-with-fast-frictionless-digital-procurement.html"
    ORDER BY  time ASC
    LIMIT 1000
)
GROUP BY status_code

[
  {
    "status_code": "200",
    "requests": "133",
    "first": "2021-01-01 15:24:13.489343 UTC",
    "last": "2021-01-20 17:55:48.802735 UTC"
  },
  {
    "status_code": "301",
    "requests": "397",
    "first": "2021-01-20 17:30:33.084799 UTC",
    "last": "2021-01-22 06:02:22.176486 UTC"
  },
  {
    "status_code": "503",
    "requests": "1",
    "first": "2021-01-20 18:05:51.324393 UTC",
    "last": "2021-01-20 18:05:51.324393 UTC"
  }
]