Log ManagementQueries

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.

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.

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 eventTime DESC
LIMIT 100
Aggregation with Grouping
SELECT 
  eventName,
  srcIP,
  COUNT(*) as eventCount,
  min(eventTime) as firstSeen,
  max(eventTime) 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

Related Documentation

Explore LogsPipelines