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 advanced analytics /#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 clickhouse logs 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 cloudflare logs v2 full detailed logs real time metrics cf http realtime metrics 1 minute aggregations short term analytics cf http 5min metrics 5 minute aggregations medium term analytics cf http hourly metrics hourly aggregations long term analytics cf http daily metrics daily aggregations table details 1\ raw data table table cloudflare logs v2 purpose stores complete cloudflare logs with full detail for deep analysis and debugging best for detailed investigation, debugging specific requests, custom analytics cloudflare logs v2 table schema table default cloudflare logs v2 column name description cache fields cachecachestatus cache status (hit, miss, etc ) cachereserveused whether cache reserve was used cacheresponsebytes number of bytes returned from cache cacheresponsestatus cache response status code cachetieredfill whether tiered cache fill was used client information clientasn client autonomous system number clientcity client city location clientcountry client country code clientdevicetype type of client device clientip client ip address clientipclass client ip classification clientlatitude client latitude coordinate clientlongitude client longitude coordinate clientmtlsauthcertfingerprint mtls authentication certificate fingerprint clientmtlsauthstatus mtls authentication status clientregioncode client region/state code clientrequestbytes number of bytes in client request clientrequesthost host header from client request clientrequestmethod http method (get, post, etc ) clientrequestpath url path requested clientrequestprotocol http protocol version clientrequestreferer http referer header clientrequestscheme request scheme (http/https) clientrequestsource source of the request clientrequesturi complete request uri clientrequestuseragent user agent string clientsslcipher ssl cipher used clientsslprotocol ssl/tls protocol version clientsrcport client source port clienttcprttms tcp round trip time in milliseconds clientxrequestedwith x requested with header content scanning contentscanobjresults content scan results contentscanobjsizes sizes of scanned objects contentscanobjtypes types of scanned objects cookies cookie header content edge information edgecfconnectingo2o cloudflare connecting o2o edgecolocode edge datacenter code edgecoloid edge datacenter id edgeendtimestamp edge response end timestamp (nanoseconds) edgepathingop edge pathing operation edgepathingsrc edge pathing source edgepathingstatus edge pathing status edgerequesthost host header at edge edgeresponsebodybytes response body size in bytes edgeresponsebytes total response size in bytes edgeresponsecompressionratio response compression ratio edgeresponsecontenttype response content type edgeresponsestatus http response status code edgeserverip edge server ip address edgestarttimestamp edge request start timestamp (nanoseconds) edgetimetofirstbytems time to first byte in milliseconds security & authentication leakedcredentialcheckresult result of leaked credential check origin server origindnsresponsetimems origin dns response time originip origin server ip address originrequestheadersenddurationms time to send headers to origin originresponsebytes origin response size in bytes originresponsedurationms origin response duration originresponsehttpexpires origin http expires header originresponsehttplastmodified origin http last modified header originresponseheaderreceivedurationms time to receive headers from origin originresponsestatus origin response status code originresponsetime origin response time originsslprotocol ssl protocol used with origin origintcphandshakedurationms tcp handshake duration with origin origintlshandshakedurationms tls handshake duration with origin request tracking parentrayid parent ray id for subrequests rayid unique request identifier headers requestheaders request headers tuple responseheaders response headers tuple with cors and onetrust fields security & waf securityaction security action taken securityactions array of security actions securityruledescription description of triggered security rule securityruleid id of triggered security rule securityruleids array of security rule ids securitysources security rule sources smartroutecoloid smart routing datacenter id uppertiercoloid upper tier datacenter id verifiedbotcategory verified bot category wafattackscore waf attack score wafflags waf flags wafmatchedvar waf matched variable wafrceattackscore waf rce attack score wafsqliattackscore waf sql injection attack score wafxssattackscore waf xss attack score workers workercputime worker cpu time used workerscriptname name of worker script workerstatus worker execution status workersubrequest whether this is a worker subrequest workersubrequestcount number of worker subrequests workerwalltimeus worker wall time in microseconds zone & file metadata zonename cloudflare zone name path file path in storage file filename size file size time file timestamp materialized columns (computed fields) column name data type formula description edgeendtimestamp dt datetime64(3) fromunixtimestamp64nano(edgeendtimestamp) edge end timestamp converted to datetime edgestarttimestamp dt datetime64(3) fromunixtimestamp64nano(edgestarttimestamp) edge start timestamp converted to datetime tenant id string requestheaders tenantid extracted tenant id from request headers message string concat( ) formatted log message for grafana display response headers tuple structure the responseheaders tuple contains the following fields access control allow origin cors allow origin header x onetrust anonymous onetrust anonymous identifier x onetrust cpt onetrust consent preference token x onetrust receiptid onetrust receipt id x onetrust tid onetrust tenant id 2\ 1 minute aggregations table cf http realtime 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(total requests) as requests from cf http realtime metrics where $ timefilter(timestamp) uses the grafana time picker group by time order by time error rate percentage select tostartofminute(timestamp) as time, (countmerge(error 4xx requests) + countmerge(error 5xx requests)) 100 0 / countmerge(total requests) as error rate percent from cf http realtime metrics where $ timefilter(timestamp) group by time order by time performance analysis response time percentiles select tostartofminute(timestamp) as time, avgmerge(avg client duration ms) as avg response time, quantilemerge(0 9)(p90 client duration ms) as p90 response time, quantilemerge(0 95)(p95 client duration ms) as p95 response time from cf http realtime metrics where $ timefilter(timestamp) group by time order by time cache hit rate select tostartofminute(timestamp) as time, countmerge(cache hits) 100 0 / (summerge(cache hits) + summerge(cache misses)) as cache hit rate percent from cf http realtime 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 normalized path, summerge(total requests) as request count from cf http 5min metrics where $ timefilter(timestamp) group by normalized path 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(total requests) as requests, summerge(total bytes served) as bytes served from cf http hourly metrics where $ timefilter(timestamp) group by country order by requests desc limit 10 security monitoring waf attack trends select tostartofhour(timestamp) as time, summerge(waf high attack requests) as attack requests, avgmerge(avg waf attack score) as avg attack score from cf http hourly metrics where $ timefilter(timestamp) group by time order by time high risk request details (raw data) select timestamp, clientip, clientrequestpath, wafattackscore, securityaction from clickhouse logs where timestamp >= now() interval 1 hour and wafattackscore > 80 order by timestamp desc limit 100 multi tenant analysis per tenant request volume select tenantid, summerge(total requests) as requests, avgmerge(avg client duration ms) as avg response time from cf http hourly 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(total requests) / 60 as requests per second from cf http realtime metrics where $ timefilter(timestamp) and tenantid = '$tenant id' group by time order by time stat panel (cache hit rate) select summerge(cache hits) 100 0 / (summerge(cache hits) + summerge(cache misses)) as cache hit rate from cf http realtime 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