Queries

Query System: RunReveal uses ClickHouse as its query engine, giving you access to powerful SQL capabilities for analyzing your log data. All queries support parameterized syntax for flexible, reusable query patterns.

Queries Overview

Quick Start

Every RunReveal query follows this basic structure with built-in time parameters:

SELECT *
FROM logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
LIMIT 100

Key Points:

Use `{from:DateTime}` and `{to:DateTime}` for time filtering (automatically set by the UI)
Always use receivedAt for time-based queries (it's indexed and handles delayed logs)
Include LIMIT to control result size
Use source-specific views like aws_cloudtrail_logs instead of logs when possible

Built-in Parameters

RunReveal automatically provides time-based parameters that are populated based on your time range selection in the UI.

Time Parameters

{from:DateTime} - Start time (inclusive)

{to:DateTime} - End time (exclusive)

{interval:UInt32} - Time bucket in seconds

{window:UInt32} - Window duration (default: 14400)

Custom Parameters

Define your own parameters using {paramName:Type} syntax

Supported types: String, DateTime, UInt32/64, Int32/64, Float32/64, Array(String), etc.

Example Query with Parameters

SELECT 
    eventName,
    srcIP,
    COUNT(*) as eventCount
FROM aws_cloudtrail_logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
  AND eventName = 'AssumeRole'
  AND srcIP NOT IN ({officeIPs:Array(String)})
GROUP BY eventName, srcIP
ORDER BY eventCount DESC
LIMIT 100

Timestamp Fields: receivedAt vs eventTime

Always use receivedAt for time filtering. It's indexed, handles delayed logs, and ensures consistent query performance across all sources.

✓ receivedAt (Recommended)

When RunReveal received the event

• Indexed (part of primary key) - fast queries

• Consistent across all sources

• Handles delayed log delivery

• Optimized for partitioning

Use for: Time filtering, detections, scheduled queries

⚠ eventTime

When the event occurred (from source)

• Not indexed - slower queries

• May be inconsistent across sources

• Can be missing for some sources

• Doesn't account for delivery delays

Use for: Displaying original event time, source timestamp analysis

Best Practice Example

-- ✅ Good: Uses indexed receivedAt
SELECT *
FROM logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
  AND eventName = 'Login'

-- ❌ Avoid: Uses unindexed eventTime
SELECT *
FROM logs
WHERE eventTime >= {from:DateTime}
  AND eventTime < {to:DateTime}
  AND eventName = 'Login'

Query Optimization

Follow these best practices to write fast, efficient queries:

Use Source-Specific Views

Query optimized views like aws_cloudtrail_logs instead of the generic logs table.

Available: aws_cloudtrail_logs, okta_logs, github_logs, aws_vpc_flow_logs, and more

Filter Early

Apply filters on indexed fields (receivedAt, sourceType, sourceID) first to reduce dataset size before processing.

Use Smaller Time Ranges

Start with 1 hour to test, then expand. Shorter windows process much faster than 30-day ranges.

Always Use LIMIT

Prevent processing excessive data by including LIMIT in every query.

Use Aggregations

Prefer COUNT, GROUP BY over returning all individual events.

Filter by sourceType

When querying the logs table, always include sourceType filter.

Optimizing WHERE Clause Order

Order your WHERE clause filters to match the table's organization. Start with the most specific filters (source type), then narrow by time, and finally add other conditions.

Primary Key Fields (use in this order):

sourceType - Filter by source type first (e.g., 'okta', 'box', 'github')
sourceID - Include if filtering by a specific source instance
receivedAt - Always include your time range filter

Additional Indexed Fields (use after primary key):

eventTime - When the event occurred
srcIP or dstIP - IP addresses (efficient for filtering)
actor map values - User information
rawLog - For searching inside raw log data

Example - Optimized Query:

SELECT
  eventName,
  srcIP,
  actor['email'] AS actor_email,
  *
FROM runreveal.logs
WHERE 
  -- Primary key fields first (in order)
  sourceType = 'okta'
  AND receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
  -- Then indexed fields
  AND eventName = 'user.session.start'
  AND srcIP != ''
  -- Then other filters
  AND actor['email'] != ''

Why This Order Works:

ClickHouse uses the primary key to efficiently prune data. Filtering by sourceType first narrows the dataset to a single source, then receivedAt uses the time-ordered index to skip irrelevant time ranges. Other filters can leverage their indexes after the primary key filters have reduced the dataset size.

Common Pattern:

Most queries should follow this pattern:

WHERE sourceType = 'box'
  AND receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
  AND eventType = 'ADD_LOGIN_ACTIVITY_DEVICE'

Always include sourceType and receivedAt first, then add your specific filters. This pattern ensures optimal query performance.

Common Query Patterns

View Common Query Patterns

Basic Time-Range Query

SELECT *
FROM logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
LIMIT 100

Filtered Search

SELECT eventName, srcIP, eventTime
FROM aws_cloudtrail_logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
  AND eventName = 'AssumeRole'
  AND srcIP != ''
ORDER BY receivedAt DESC
LIMIT 100

Aggregation with Grouping

SELECT 
    eventName,
    srcIP,
    COUNT(*) as eventCount,
    min(receivedAt) as firstSeen,
    max(receivedAt) as lastSeen
FROM aws_cloudtrail_logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
GROUP BY eventName, srcIP
HAVING eventCount > 10
ORDER BY eventCount DESC
LIMIT 100

Time-Series Aggregation

SELECT 
    toStartOfHour(receivedAt) as hour,
    eventName,
    COUNT(*) as count
FROM aws_cloudtrail_logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
GROUP BY hour, eventName
ORDER BY hour DESC, count DESC

Top N Analysis

SELECT 
    srcIP,
    COUNT(*) as eventCount,
    count(DISTINCT eventName) as uniqueEvents
FROM logs
WHERE receivedAt >= {from:DateTime}
  AND receivedAt < {to:DateTime}
  AND sourceType = 'aws_cloudtrail'
GROUP BY srcIP
ORDER BY eventCount DESC
LIMIT 10

ClickHouse Functions Reference

View ClickHouse Functions

Complete Reference: RunReveal uses ClickHouse as its query engine. For a complete list of all available functions, see the ClickHouse Functions Documentation.

Date & Time

toYear(), toMonth(), toHour()

toStartOfHour(), toStartOfDay()

dateDiff(), formatDateTime()

String Functions

match(), LIKE, extract()

splitByChar(), upper(), lower()

trim(), replace()

Array Functions

arrayJoin(), length(), has()

hasAny()

Map Functions

map['key'] - Access map values

mapKeys(), mapValues()

has(map, 'key')

Aggregations

COUNT(), sum(), avg()

quantile(), groupArray()

min(), max()

Conditionals & JSON

if(), multiIf(), coalesce()

JSONExtractString(), JSONHas()

Common Mistakes to Avoid

❌ Common Mistakes

• Using eventTime for time filtering

• Missing time range filters

• No LIMIT clause

• Querying logs without sourceType filter

• Using SELECT * with GROUP BY

✅ Best Practices

• Always use receivedAt for time filtering

• Include time range filters in every query

• Always include LIMIT

• Filter by sourceType when using logs table

• Select only needed columns in aggregations

Quick Performance Tips

Start Small: Test with 1-hour windows before expanding
Use EXPLAIN: Check execution plans when optimizing
Monitor Query Time: Watch execution times in the UI
Index Usage: Filter on indexed columns (receivedAt, sourceType, sourceID)
Avoid Full Scans: Always include time range filters
Limit Aggregations: Use LIMIT even with GROUP BY

Explore Logs - Learn how to use the query interface
Writing Detections - Create detection rules from queries
ClickHouse Documentation - Complete ClickHouse reference
ClickHouse Functions - All available functions

On this page