Logflare | Cloudflare, Vercel & Elixir Logging

~/ endpoints /usage.api-counts

id: 8c6cde36-aac5-4cf1-96fb-f1a1a0545683

BigQuery SQL


with 
dates as (
  select (case
    when @interval = 'hourly' then timestamp_sub(current_timestamp(), interval 1 hour)
    when @interval = 'daily' then timestamp_sub(current_timestamp(), interval 7 day)
    when @interval = 'minutely' then timestamp_sub(current_timestamp(), interval 60 minute)
  end) as start
),
chart_counts as (
select
  (case
    when @interval = 'hourly' then timestamp_trunc(f0.timestamp,  hour)
    when @interval = 'daily' then timestamp_trunc(f0.timestamp,  day)
    when @interval = 'minutely' then timestamp_trunc(f0.timestamp,  minute)
  end
  ) as timestamp,
  COUNTIF(REGEXP_CONTAINS(f2.path, '/rest')) as total_rest_requests,
  COUNTIF(REGEXP_CONTAINS(f2.path, '/storage')) as total_storage_requests,
  COUNTIF(REGEXP_CONTAINS(f2.path, '/auth')) as total_auth_requests,
  COUNTIF(REGEXP_CONTAINS(f2.path, '/realtime')) as total_realtime_requests,
FROM
  dates, 
  `cloudflare.logs.prod` as f0
  LEFT JOIN UNNEST(metadata) AS f1 ON TRUE
  LEFT JOIN UNNEST(f1.request) AS f2 ON TRUE
where
  REGEXP_CONTAINS(f2.url, @project) AND f0.timestamp >= dates[0]
  -- project = @project
GROUP BY
    timestamp
)
SELECT
    datetime(chart_counts.timestamp, 'UTC') as timestamp,
    COALESCE(SUM(chart_counts.total_rest_requests), 0) as total_rest_requests,
    COALESCE(SUM(chart_counts.total_storage_requests), 0) as total_storage_requests,
    COALESCE(SUM(chart_counts.total_auth_requests), 0) as total_auth_requests,
    COALESCE(SUM(chart_counts.total_realtime_requests), 0) as total_realtime_requests,
FROM  
  chart_counts
GROUP BY
    timestamp
ORDER BY
    timestamp asc;

max rows: 1000
caching: 900 seconds
cache warming: 300 seconds
query sandboxing: disabled


WITH dates AS (SELECT (CASE WHEN @interval = 'hourly' THEN timestamp_sub(current_timestamp(), INTERVAL 1 HOUR) WHEN @interval = 'daily' THEN timestamp_sub(current_timestamp(), INTERVAL 7 DAY) WHEN @interval = 'minutely' THEN timestamp_sub(current_timestamp(), INTERVAL 60 MINUTE) END) AS start), chart_counts AS (SELECT (CASE WHEN @interval = 'hourly' THEN timestamp_trunc(f0.timestamp, hour) WHEN @interval = 'daily' THEN timestamp_trunc(f0.timestamp, day) WHEN @interval = 'minutely' THEN timestamp_trunc(f0.timestamp, minute) END) AS timestamp, COUNTIF(REGEXP_CONTAINS(f2.path, '/rest')) AS total_rest_requests, COUNTIF(REGEXP_CONTAINS(f2.path, '/storage')) AS total_storage_requests, COUNTIF(REGEXP_CONTAINS(f2.path, '/auth')) AS total_auth_requests, COUNTIF(REGEXP_CONTAINS(f2.path, '/realtime')) AS total_realtime_requests FROM dates, `cloudflare.logs.prod` AS f0 LEFT JOIN UNNEST(metadata) AS f1 ON true LEFT JOIN UNNEST(f1.request) AS f2 ON true WHERE REGEXP_CONTAINS(f2.url, @project) AND f0.timestamp >= dates[0] GROUP BY timestamp) SELECT datetime(chart_counts.timestamp, 'UTC') AS timestamp, COALESCE(SUM(chart_counts.total_rest_requests), 0) AS total_rest_requests, COALESCE(SUM(chart_counts.total_storage_requests), 0) AS total_storage_requests, COALESCE(SUM(chart_counts.total_auth_requests), 0) AS total_auth_requests, COALESCE(SUM(chart_counts.total_realtime_requests), 0) AS total_realtime_requests FROM chart_counts GROUP BY timestamp ORDER BY timestamp ASC

Call your endpoint


      # By UUID
      curl "https://api.logflare.app/api/endpoints/query/8c6cde36-aac5-4cf1-96fb-f1a1a0545683" \
      -H 'X-API-KEY: YOUR-ACCESS-TOKEN' \
      -H 'Content-Type: application/json; charset=utf-8' \
      -G -d "project=VALUE" -d "interval=VALUE"


      # By name
      curl "https://api.logflare.app/api/endpoints/query/usage.api-counts" \
      -H 'X-API-KEY: YOUR-ACCESS-TOKEN' \
      -H 'Content-Type: application/json; charset=utf-8' \
      -G -d "project=VALUE" -d "interval=VALUE"