---
title: Advanced Analytics
slug: advanced-analytics
docTags: 
createdAt: 2025-09-19T18:36:03.053Z
---

## 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](docId:83omxFYNdb5nMfkDZfAEA)  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:

::Image[]{src="https://app.archbee.com/api/optimize/xpy-ZuNXAextve6S5Tto2/el2kl3lLUQlHQE8-uRoZZ-20250919-192804.png" size="50" width="848" height="884" position="center" showCaption="false"}

Select `CloudflareObserve` as the datasource:

::Image[]{src="https://app.archbee.com/api/optimize/xpy-ZuNXAextve6S5Tto2/IQaQX7dDL6oNzm8HtGj50-20251121-183939.png" size="60" width="932" height="1130" position="center" showCaption="false"}

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:

::Image[]{src="https://app.archbee.com/api/optimize/xpy-ZuNXAextve6S5Tto2/dHt-G9s78A0BwIO4uicMZ-20250919-193304.png" size="70" width="1514" height="612" position="center" showCaption="false"}

## 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`



```java
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**

:::BlockQuote
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**

:::BlockQuote
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**

:::BlockQuote
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**

:::BlockQuote
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.

:::BlockQuote
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.

:::BlockQuote
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**

:::BlockQuote
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**

:::BlockQuote
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)**

:::BlockQuote
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)**

:::BlockQuote
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

1. **Choose the Right Table**: Use the most aggregated table that meets your time granularity needs
2. **Filter Early**: Always include time filters and tenant/zone filters when possible
3. **Use Indexes**: The tables are optimized for queries filtered by timestamp, zone\_name, host, and tenantid
4. **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

1. **Start with Aggregated Tables**: Use the most appropriate aggregation level for your use case
2. **Use Raw Data Sparingly**: Only query clickhouse\_logs for detailed investigation or when aggregated data isn't sufficient
3. **Filter by Time/Tenant/Host**: Always include tenant filtering for multi-tenant environments

##