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