Logflare - Bigquery Aggregation Formula

PHOTO EMBED

Mon Dec 20 2021 07:34:20 GMT+0000 (Coordinated Universal Time)

Saved by @admariner

SELECT
  (
    `5518_prod`.LF_TIMESTAMP_TRUNC(t0.timestamp, 'MINUTE')
  ) as timestamp,
  COUNTIF(
    f4.status_code <= 99
    OR f4.status_code >= 601
    OR f4.status_code IS NULL
  ) as other,
  COUNTIF(
    f4.status_code BETWEEN 100
    AND 199
  ) as status_1xx,
  COUNTIF(
    f4.status_code BETWEEN 200
    AND 299
  ) as status_2xx,
  COUNTIF(
    f4.status_code BETWEEN 300
    AND 399
  ) as status_3xx,
  COUNTIF(
    f4.status_code BETWEEN 400
    AND 499
  ) as status_4xx,
  COUNTIF(
    f4.status_code BETWEEN 500
    AND 599
  ) as status_5xx,
  COUNT(t0.timestamp) as total
FROM
  `logflare-232118`.5518_prod.05d223cf_10ed_4ae3_8ca6_fba1b93d0ba3 AS t0
  INNER JOIN UNNEST(t0.metadata) AS f1 ON TRUE
  INNER JOIN UNNEST(f1.response) AS f2 ON TRUE
  INNER JOIN UNNEST(t0.metadata) AS f3 ON TRUE
  INNER JOIN UNNEST(f3.response) AS f4 ON TRUE
WHERE
  (
    t0.timestamp >= (
      `5518_prod`.LF_TIMESTAMP_SUB('2021-12-20 07:32:59.865119Z', 120, 'MINUTE')
    )
  )
  AND (f2.status_code = 404)
GROUP BY
  1
ORDER BY
  1 DESC
LIMIT
  120
content_copyCOPY

https://logflare.app/sources/17493/search?querystring