Top 10 outliers by DB time, request durations and allocations (Logs by BetterStack)

betterstack-logs-rails-lograge.sql

-- SQL: https://clickhouse.com/docs/en/sql-reference 
-- top db
SELECT endpoint, AVG(db) AS average_db, MIN(db) AS min_db, MAX(db) AS max_db, COUNT(*) AS count FROM (
  SELECT
      CONCAT(
        JSON_VALUE(json, '$.message.controller'),
        '#',
        JSON_VALUE(json, '$.message.action')
      ) AS endpoint,
      simpleJSONExtractFloat(message, 'db') / 1000 as db
  FROM $source
  WHERE
    (dt BETWEEN toDateTime64($from, 3) AND toDateTime64($to, 3))
    AND proc_id LIKE 'web.%'
    AND JSON_VALUE(json, '$.message.controller') != ''
)
GROUP BY endpoint
ORDER BY average_db DESC
LIMIT 10;

-- top duration
SELECT endpoint, AVG(duration) AS average_duration, MIN(duration) AS min_duration, MAX(duration) AS max_duration, COUNT(*) AS count FROM (
  SELECT
      CONCAT(
        JSON_VALUE(json, '$.message.controller'),
        '#',
        JSON_VALUE(json, '$.message.action')
      ) AS endpoint,
      simpleJSONExtractFloat(message, 'duration') / 1000 as duration
  FROM $source
  WHERE
    (dt BETWEEN toDateTime64($from, 3) AND toDateTime64($to, 3))
    AND proc_id LIKE 'web.%'
    AND JSON_VALUE(json, '$.message.controller') != ''

)
GROUP BY endpoint
ORDER BY average_duration DESC
LIMIT 10;

-- top allocations
SELECT endpoint, AVG(alloc) AS average_alloc, MIN(alloc) AS min_alloc, MAX(alloc) AS max_alloc, COUNT(*) AS count  FROM (
  SELECT
      CONCAT(
        JSON_VALUE(json, '$.message.controller'),
        '#',
        JSON_VALUE(json, '$.message.action')
      ) AS endpoint,
      simpleJSONExtractUInt(message, 'allocations') as alloc
  FROM $source
  WHERE
    (dt BETWEEN toDateTime64($from, 3) AND toDateTime64($to, 3))
    AND proc_id LIKE 'web.%'
    AND simpleJSONExtractUInt(message, 'allocations') > 0
)
GROUP BY endpoint
ORDER BY average_alloc DESC
LIMIT 10;