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 clickhouse logs 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 clickhouse logs purpose stores complete cloudflare logs with full detail for deep analysis and debugging best for detailed investigation, debugging specific requests, custom analytics column name description timestamp request timestamp with nanosecond precision message full log message content labels key value metadata map ingested at when the log was ingested into clickhouse cachecachestatus cache status (hit, miss, expired, etc ) cachereserveused whether cache reserve was used cacheresponsebytes bytes served from cache cacheresponsestatus http status from cache cachetieredfill whether tiered cache fill was used clientasn client autonomous system number clientcity client city location clientcountry client country code clientdevicetype client device type (desktop, mobile, etc ) clientip client ip address clientipclass client ip classification clientlatitude client geographic latitude clientlongitude client geographic longitude clientmtlsauthcertfingerprint mtls certificate fingerprint clientmtlsauthstatus mtls authentication status clientregioncode client region/state code clientrequestbytes bytes in client request clientrequesthost host header from client request clientrequestmethod http method (get, post, etc ) clientrequestpath request path normalizedclientrequestpath normalized/cleaned request path clientrequestprotocol http protocol version clientrequestreferer request referer header clientrequestscheme request scheme (http/https) clientrequestsource request source classification clientrequesturi full 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 contentscanobjresults content scanning results contentscanobjsizes scanned object sizes contentscanobjtypes scanned object types cookies request cookies requestheaders all request headers responseheaders all response headers edgecfconnectingo2o cloudflare connecting origin to origin edgecolocode edge location code edgecoloid edge location id edgeendtimestamp edge processing end time edgepathingop edge pathing operation edgepathingsrc edge pathing source edgepathingstatus edge pathing status edgerequesthost host as seen by edge edgeresponsebodybytes response body bytes edgeresponsebytes total response bytes edgeresponsecompressionratio response compression ratio edgeresponsecontenttype response content type edgeresponsestatus http response status code edgeserverip edge server ip edgestarttimestamp edge processing start time edgetimetofirstbytems time to first byte in milliseconds leakedcredentialcheckresult leaked credential check result origindnsresponsetimems origin dns lookup time originip origin server ip originrequestheadersenddurationms origin request header send duration originresponsebytes bytes received from origin originresponsedurationms origin response duration originresponsehttpexpires origin expires header originresponsehttplastmodified origin last modified header originresponseheaderreceivedurationms origin response header receive duration originresponsestatus origin http status code originresponsetime origin response time originsslprotocol ssl protocol to origin origintcphandshakedurationms origin tcp handshake duration origintlshandshakedurationms origin tls handshake duration parentrayid parent request ray id rayid unique request identifier securityaction security action taken securityactions all security actions securityruledescription triggered security rule description securityruleid triggered security rule id securityruleids all triggered security rule ids securitysources security rule sources smartroutecoloid smart routing colo id uppertiercoloid upper tier colo id verifiedbotcategory verified bot category wafattackscore waf attack score (0 100) wafflags waf flags wafmatchedvar waf matched variable wafrceattackscore waf rce attack score wafsqliattackscore waf sql injection attack score wafxssattackscore waf xss attack score workercputime worker cpu time used workerscriptname worker script name workerstatus worker execution status workersubrequest whether this is a worker subrequest workersubrequestcount number of worker subrequests workerwalltimeus worker wall time in microseconds zonename cloudflare zone name request header tenantid tenant id from request header 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 column name description timestamp aggregation timestamp (minute boundary) zone name cloudflare zone name host request host tenantid tenant identifier normalized path normalized request path cache status cache status (hit, miss, expired) response status http response status code client request source request source classification edge location edge server location country client country total requests total number of requests requests per minute average requests per minute total bytes served total bytes served avg client duration ms average response time p90 client duration ms 90th percentile response time p95 client duration ms 95th percentile response time p99 client duration ms 99th percentile response time avg origin response ms average origin response time p90 origin response ms 90th percentile origin response time p95 origin response ms 95th percentile origin response time success requests count of 2xx responses error 4xx requests count of 4xx responses error 5xx requests count of 5xx responses cache hits count of cache hits cache misses count of cache misses cache expired count of cache expired waf high attack requests count of high waf attack score requests waf high rce requests count of high rce attack score requests waf high sqli requests count of high sqli attack score requests waf high xss requests count of high xss attack score requests avg waf attack score average waf attack score avg waf rce score average waf rce score avg waf sqli score average waf sqli score avg waf xss score average waf xss score verified bot requests count of verified bot requests avg compression ratio average compression ratio 3\ 5 minute aggregations table cf http 5min metrics purpose short term trend analysis with reduced data volume best for short term trending, reducing dashboard load column name description timestamp aggregation timestamp (5 minute boundary) zone name cloudflare zone name host request host tenantid tenant identifier normalized path normalized request path cache status cache status (hit, miss, expired) response status http response status code client request source request source classification edge location edge server location country client country total requests total number of requests requests per minute average requests per minute total bytes served total bytes served avg client duration ms average response time p90 client duration ms 90th percentile response time p95 client duration ms 95th percentile response time p99 client duration ms 99th percentile response time avg origin response ms average origin response time p90 origin response ms 90th percentile origin response time p95 origin response ms 95th percentile origin response time success requests count of 2xx responses error 4xx requests count of 4xx responses error 5xx requests count of 5xx responses cache hits count of cache hits cache misses count of cache misses cache expired count of cache expired waf high attack requests count of high waf attack score requests waf high rce requests count of high rce attack score requests waf high sqli requests count of high sqli attack score requests waf high xss requests count of high xss attack score requests avg waf attack score average waf attack score avg waf rce score average waf rce score avg waf sqli score average waf sqli score avg waf xss score average waf xss score verified bot requests count of verified bot requests avg compression ratio average compression ratio 4\ hourly aggregations table cf http hourly metrics purpose medium term analysis and reporting best for daily reports, capacity planning, trend analysis columns same as those of the cf http 5min metrics table 5\ daily aggregations table cf http daily metrics purpose medium term analysis and reporting best for monthly reports, long term trend analysis, capacity planning columns same as those of the cf http 5min metrics table common query patterns real time monitoring request rate error rate percentage performance analysis response time percentiles cache hit rate traffic analysis top requested paths use the table option to view the results of queries without a time field traffic by country use the table option to view the results of queries without a time field security monitoring waf attack trends high risk request details (raw data) multi tenant analysis per tenant request volume 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) stat panel (cache hit rate) 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