Queries

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

Query History

Every query you run in Explore is saved to Query History so you can revisit SQL, reload results, and share runs with teammates.

Open the panel from the Explore toolbar: click the history (circular arrow) icon next to Run. The panel opens beside the editor and lists recent runs.

Tabs

Search and filters

• Search queries… — Filter the list by text in the SQL (or AI / PQL text when present).
• Status filter — Narrow to Any, Running, Completed, Error, or Cancelled.

Use the refresh control in the panel header to reload the list. Scroll to load more entries (history pages in batches).

Each history entry

Collapsed rows show a one-line SQL preview, how long ago the query started, status (when not completed), and row count for successful runs.

Click a row to expand the full query with syntax highlighting. From the row actions (visible on hover) you can:

Built-in Parameters

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

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

For how RunReveal sets these fields and infrastructure NTP, see Explore Logs — Timestamps and clock synchronization.

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:

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

1. sourceType - Filter by source type first (e.g., 'okta', 'box', 'github')
2. sourceID - Include if filtering by a specific source instance
3. 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

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

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

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

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

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

Common Mistakes to Avoid

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 Logs - Learn how to use the query interface and saved queries
• Writing Detections - Create detection rules from queries
• ClickHouse Documentation - Complete ClickHouse reference
• ClickHouse Functions - All available functions