Advanced Analytics

Overview

Cloudflare logs data is ingested, stored, and intelligently aggregated by ObserveNow using ClickHouse as the high-performance storage and query engine. This comprehensive analytics solution automatically processes raw Cloudflare log events into multiple aggregation layers, enabling advanced analytics, real-time monitoring, and historical trend analysis. This document provides a guide on querying the Cloudflare analytics tables using Grafana.

Jump to the Common Query Patterns section for example queries.

Querying Using Grafana

The Cloudflare logs data is accessible in Grafana through the data source ClickHouse Logs.

In your Grafana, navigate to the Explore section:

Select CloudflareObserve as the datasource:

Use either the Query Buiilder or the SQL Editor views and enter your query. Select the Table or Time Series option based on how you want to view the results:

Table Architecture

ObserveNow uses a multi-tier approach with one raw table and several pre-aggregated tables for optimal query performance:

Raw Data: events - Full detailed logs

Aggregated Metrics:

cf_http_aggregated_metrics (1 min aggregations)

cf_http_aggregated_hourly

Table Details

1. Raw Data Table

Table: events

Purpose: Stores complete Cloudflare logs with full details for deep analysis and debugging.

Best For: Detailed investigation, debugging specific requests, custom analytics

Events Table Schema

Table: events

timestamp - Event timestamp with nanosecond precision
service_name - Name of the service generating the event
id - Unique event identifier
TenantID - Tenant identifier
ClientRequestPath - Client request path
RayID - RayID
ZoneName - Zone name
ClientRequestHost - Client request host
EdgeResponseStatus - HTTP response status code
ResponseTimeMs - Response time in milliseconds
attributes_json - JSON attributes stored as key-value map
indexed_attributes - Indexed attributes for faster querying

2. Aggregated Table

Table: cf_http_aggregated_metrics

Purpose: Near real-time monitoring and alerting with 1-minute granularity.

Best For: Real-time dashboards, alerting, performance monitoring

Dimension Columns (Grouping Keys)

Column Name	Description
timestamp	Time bucket for metric aggregation
ZoneName	Cloudflare zone/domain name
ClientRequestHost	Host header from client request
TenantId	Tenant identifier for multi-tenant applications
NormalizedPath	Normalized URL path (e.g., /api/users/*)
CacheCacheStatus	Cache status (HIT, MISS, EXPIRED, BYPASS)
ResponseStatus	HTTP response status code group
ClientRequestSource	Source of the client request
EdgeColoCode	Cloudflare edge datacenter code
ClientCountry	Client country code

Aggregate Function Columns (Metrics)

Core Request Metrics

Column Name	Description
TotalRequests	Total number of requests
RequestsPerMinute	Average requests per minute
TotalBytesServed	Total bytes served to clients

Performance Metrics

Column Name	Description
AvgClientDurationMs	Average client request duration in milliseconds
P90ClientDurationMs	90th percentile client request duration
P95ClientDurationMs	95th percentile client request duration
P99ClientDurationMs	99th percentile client request duration

HTTP Status Code Metrics

Column Name	Description
SuccessRequests	Count of successful requests (2xx status codes)
Error4xxRequests	Count of client error requests (4xx status codes)
Error5xxRequests	Count of server error requests (5xx status codes)

Cache Performance Metrics

Column Name	Description
CacheHits	Count of cache hit requests
CacheMisses	Count of cache miss requests
CacheExpired	Count of expired cache requests
CacheBypass	Count of cache bypass requests

Security & WAF Metrics

Column Name	Description
WAFHighAttackRequests	Count of requests with high WAF attack scores
WAFHighRCERequests	Count of requests with high RCE attack scores
WAFHighSQLiRequests	Count of requests with high SQL injection scores
WAFHighXSSRequests	Count of requests with high XSS attack scores
AvgWAFAttackScore	Average overall WAF attack score
AvgWAFRCEAttackScore	Average RCE (Remote Code Execution) attack score
AvgWAFSQLiAttackScore	Average SQL injection attack score
AvgWAFXSSAttackScore	Average XSS (Cross-Site Scripting) attack score

Bot Detection Metrics

Column Name	Description
VerifiedBotRequests	Count of requests from verified bots

Compression & Size Metrics

Column Name	Description
AvgCompressionRatio	Average response compression ratio
AvgRequestBytes	Average request size in bytes
AvgResponseBytes	Average response size in bytes

Common Query Patterns

Real-Time Monitoring

Request Rate

SELECT toStartOfMinute(timestamp) as time, countMerge(TotalRequests) as requests FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) -- Uses the Grafana time picker GROUP BY time ORDER BY time

Error Rate Percentage

SELECT toStartOfMinute(timestamp) as time, (countMerge(Error4xxRequests) + countMerge(Error5xxRequests)) * 100.0 / countMerge(TotalRequests) as error_rate_percent FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) GROUP BY time ORDER BY time

Performance Analysis

Response Time Percentiles

SELECT toStartOfMinute(timestamp) as time, avgMerge(AvgClientDurationMs) as avg_response_time, quantileMerge(0.9)(P90ClientDurationMs) as p90_response_time, quantileMerge(0.95)(P95ClientDurationMs) as p95_response_time FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) GROUP BY time ORDER BY time

Cache Hit Rate

SELECT toStartOfMinute(timestamp) as time, countMerge(CacheHits) * 100.0 / (sumMerge(CacheHits) + sumMerge(CacheMisses)) as cache_hit_rate_percent FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) GROUP BY time ORDER BY time

Traffic Analysis

Top Requested Paths

Use the Table option to view the results of queries without a time field.

SELECT NormalizedPath, sumMerge(TotalRequests) as request_count FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) GROUP BY NormalizedPath ORDER BY request_count DESC LIMIT 10

Traffic by Country

Use the Table option to view the results of queries without a time field.

SELECT country, sumMerge(TotalRequests) as requests, sumMerge(TotalBytesServed) as bytes_served FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) GROUP BY country ORDER BY requests DESC LIMIT 10

Security Monitoring

WAF Attack Trends

SELECT toStartOfHour(timestamp) as time, sumMerge(WAFHighAttackRequests) as attack_requests, avgMerge(AvgWAFAttackScore) as avg_attack_score FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) GROUP BY time ORDER BY time

Multi-Tenant Analysis

Per-Tenant Request Volume

SELECT tenantid, sumMerge(TotalRequests) as requests, avgMerge(AvgClientDurationMs) as avg_response_time FROM cf_http_aggregated_metrics WHERE timestamp >= now() - INTERVAL 24 HOUR GROUP BY tenantid ORDER BY requests DESC

Grafana Dashboarding Tips

Variable Setup

Create dashboard variables for dynamic filtering:

$tenant_id - Tenant selection

$zone_name - Zone/domain selection

$time_range - Time range picker

Panel Queries

Use these patterns in your Grafana panels:

Time Series Panel (Request Rate)

SELECT $__timeInterval(timestamp) as time, sumMerge(TotalRequests) / 60 as requests_per_second FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) AND tenantid = '$tenant_id' GROUP BY time ORDER BY time

Stat Panel (Cache Hit Rate)

SELECT sumMerge(CacheHits) * 100.0 / (sumMerge(CacheHits) + sumMerge(CacheMisses)) as cache_hit_rate FROM cf_http_aggregated_metrics WHERE $__timeFilter(timestamp) AND tenantid = '$tenant_id'

Performance Optimization

Choose the Right Table: Use the most aggregated table that meets your time granularity needs

Filter Early: Always include time filters and tenant/zone filters when possible

Use Indexes: The tables are optimized for queries filtered by timestamp, zone_name, host, and tenantid

Limit Results: Use LIMIT clauses for top-N queries

Common Grafana Functions

$__timeFilter(timestamp) - Automatic time range filtering

$__timeInterval(timestamp) - Dynamic time grouping based on dashboard time range

The clickhouse functions sumMerge(), avgMerge(), quantileMerge() are required for querying from aggregated columns

Best Practices

Start with Aggregated Tables: Use the most appropriate aggregation level for your use case

Use Raw Data Sparingly: Only query clickhouse_logs for detailed investigation or when aggregated data isn't sufficient

Filter by Time/Tenant/Host: Always include tenant filtering for multi-tenant environments