cql-best-practices

name	cql-best-practices
description	Expert guidance for CrowdStrike LogScale CQL queries. Use for threat hunting, detection rules, security analytics, and incident response. Provides syntax rules, optimization techniques, event schemas, and hunting patterns.

CrowdStrike LogScale Query Expert

This skill provides expert guidance for writing efficient CQL (CrowdStrike Query Language) queries for threat hunting, detection engineering, and security analytics in CrowdStrike Falcon's LogScale platform.

Critical Syntax Rules

NEVER mix Splunk SPL syntax with LogScale CQL. These are fundamentally different query languages.

Core Syntax

Pattern	LogScale CQL	NOT Splunk SPL
Field assignment	newField := value	eval newField=value
String concat	field1 + " " + field2	field1." ".field2
Rename	rename(old, as=new)	rename old AS new
Aggregation	groupBy(field, function=count())	stats count by field
Function params	function(param=value)	Named parameters required

Query Structure

// 1. Tag filters (most selective, always first)
#event_simpleName=ProcessRollup2

// 2. Field filters (exact matches before wildcards)
| ImageFileName="powershell.exe"
| ComputerName="*-SRV-*"

// 3. Complex filters (regex, comparisons)
| CommandLine=/encodedcommand|downloadstring/i
| BytesOut > 1000000

// 4. Transformations
| fileName := lower(ImageFileName)

// 5. Aggregations
| groupBy([ComputerName, UserName], function=count())

// 6. Post-processing
| sort(_count, order=desc)
| head(100)
| table([ComputerName, UserName, _count])

Field Types

#event_simpleName=ProcessRollup2  // Tag field (# prefix, indexed, fast)
@timestamp > now() - 24h          // Metadata field (@ prefix)
UserName = "admin*"               // User field (wildcards ok)
newField := oldField + "_suffix"  // Field assignment (:=)

Essential Operators

Type	Operators
Comparison	=, !=, <, >, <=, >=
Logical	AND, OR, NOT, !
Wildcards	field = "*pattern*"
Regex	field = /pattern/i or regex()
Existence	field = * (exists), field != * (not exists)
Membership	in(field, values=["val1", "val2"])

Regex Syntax

// Inline regex with flags
| CommandLine=/pattern/i          // i = case-insensitive

// Named capture groups
| ImageFileName=/(?<fileName>[^\\\/]+)$/

// regex() function
| regex("(?<hash>[a-fA-F0-9]{64})", field=CommandLine)

CrowdStrike Event Types

Process Events

• ProcessRollup2 - Process execution (primary)
• SyntheticProcessRollup2 - Synthetic process events
• ProcessBlocked - Blocked process attempts
• ProcessTerminated - Process termination

Network Events

• NetworkConnectIP4 / NetworkConnectIP6 - Network connections
• NetworkListenIP4 / NetworkListenIP6 - Listening ports
• DnsRequest - DNS queries
• SuspiciousDnsRequest - Flagged DNS queries

Authentication Events

• UserLogon / UserLogoff - Logon events
• UserLogonFailed - Failed authentication
• UserAccountCreated / UserAccountDeleted - Account management
• UserIdentity - User identity events

File Events

• FileWritten - File writes
• FileDeleted - File deletions
• NewExecutableRenamed - Executable renaming
• NewScriptWritten - Script file creation
• ImageHash - File hash events

Registry Events

• AsepKeyUpdate - ASEP registry changes
• RegValueSet - Registry value modifications
• RegKeyDeleted - Registry key deletion

Persistence Events

• ScheduledTaskRegistered / ScheduledTaskDeleted - Scheduled tasks
• ServiceStarted - Service events

Key CrowdStrike Fields

Process Fields

aid                        // Sensor ID (primary key)
ComputerName               // Hostname
ImageFileName              // Executable path
CommandLine                // Full command line
ParentBaseFileName         // Parent process name
TargetProcessId_decimal    // Process ID
SHA256HashData             // File hash
UserName                   // User context
UserSid                    // User SID

Network Fields

RemoteAddressIP4           // Remote IP
RemotePort_decimal         // Remote port
LocalAddressIP4            // Local IP
LocalPort_decimal          // Local port
BytesIn / BytesOut         // Data transfer
Protocol                   // Network protocol

Time Fields

@timestamp                 // Event timestamp
ContextTimeStamp_decimal   // Sensor time

Query Optimization

Performance Priority Order

1. Tag filters first - #event_simpleName= filters use indexes
2. Narrow timeframes - Smaller time ranges = faster queries
3. Specific field values - Exact matches before wildcards
4. Limit cardinality - Use limit= in groupBy()
5. Filter before transform - Reduce data before processing

Cardinality Management

// Limit groupBy cardinality
| groupBy([field1, field2], function=count(), limit=10000)

// Use limit=max only when necessary
| groupBy(aid, function=collect([field1, field2]), limit=max)

// Sample large datasets
| sample(0.1)  // 10% sample
| groupBy(field)

Avoid These Patterns

// ❌ BAD: No tag filter
"powershell.exe" | CommandLine=/pattern/

// ✅ GOOD: Tag filter first
#event_simpleName=ProcessRollup2 | ImageFileName="powershell.exe"

// ❌ BAD: Format all events then filter
| formatTime(field=@timestamp) | fieldFilter=value

// ✅ GOOD: Filter first, then format
| fieldFilter=value | formatTime(field=@timestamp)

Common Query Patterns

Threat Hunting - Suspicious PowerShell

#event_simpleName=ProcessRollup2
| ImageFileName=/powershell\.exe$/i
| CommandLine=/(downloadstring|invoke-expression|encodedcommand|iex|webclient|bitstransfer)/i
| table([ComputerName, UserName, CommandLine, ParentBaseFileName, SHA256HashData])

Lateral Movement Detection

#event_simpleName=UserLogon
| LogonType=3  // Network logon
| groupBy(UserName, function=[count(), collect(ComputerName)])
| _count > 5
| table([UserName, _count, ComputerName])

Data Exfiltration Baseline

#event_simpleName=NetworkConnectIP4
| !cidr(RemoteAddressIP4, subnet=["10.0.0.0/8", "172.16.0.0/12", "192.168.0.0/16"])
| groupBy([ComputerName, RemoteAddressIP4], function=sum(BytesOut, as=total_bytes))
| total_bytes > 100000000  // 100MB threshold
| sort(total_bytes, order=desc)

Process Execution from Suspicious Paths

#event_simpleName=ProcessRollup2
| ImageFileName=/\\(temp|tmp|downloads|appdata|public|recycle\.bin)\\/i
| groupBy([ComputerName, ImageFileName, SHA256HashData], function=count())
| table([ComputerName, ImageFileName, SHA256HashData, _count])

IOC Hash Hunting

#event_simpleName=ProcessRollup2
| in(SHA256HashData, values=[
    "hash1abc...",
    "hash2def...",
    "hash3ghi..."
  ])
| table([ComputerName, UserName, ImageFileName, SHA256HashData, CommandLine])

Timeline Analysis for Incident Response

ComputerName="COMPROMISED-HOST"
| @timestamp >= "2024-01-01T00:00:00Z"
| @timestamp <= "2024-01-01T23:59:59Z"
| sort(@timestamp, order=asc)
| table([@timestamp, #event_simpleName, UserName, ImageFileName, CommandLine, RemoteAddressIP4])

Quick Navigation

📚 Essential Resources

• Master Index - Complete navigation across 184 documentation files
• Common Patterns - 20+ ready-to-use threat hunting queries mapped to MITRE ATT&CK
• Event Dictionary - CrowdStrike event types quick reference
• CQL Functions - Complete function reference with syntax

📘 Comprehensive Event Schemas

943 CrowdStrike events documented across 8 platform-specific files:

• [Master Dictionary](CrowdStrike CQL Knowledgebase/Event Dictionary/CrowdStrike_Events_Dictionary.md) - All 943 events with complete field documentation
• [Windows Events](CrowdStrike CQL Knowledgebase/Event Dictionary/Windows_Events.md) - 565 Windows event types
• [Linux Events](CrowdStrike CQL Knowledgebase/Event Dictionary/Linux_Events.md) - 269 Linux event types
• [macOS Events](CrowdStrike CQL Knowledgebase/Event Dictionary/macOS_Events.md) - macOS event types
• [Android Events](CrowdStrike CQL Knowledgebase/Event Dictionary/Android_Events.md) - 93 Android event types
• [iOS Events](CrowdStrike CQL Knowledgebase/Event Dictionary/iOS_Events.md) - iOS event types
• [Container Events](CrowdStrike CQL Knowledgebase/Event Dictionary/Container_Events.md) - 94 container event types
• [Kubernetes Events](CrowdStrike CQL Knowledgebase/Event Dictionary/Kubernetes_Events.md) - Kubernetes event types

📦 Consolidated Function References

For efficiency, related functions have been consolidated into comprehensive guides:

• [Math Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-math-all.md) - All mathematical operations (trigonometric, logarithmic, etc.)
• [Time Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-time-all.md) - Time extraction functions (hour, day, month, year, etc.)
• [Array Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-array-all.md) - Complete array manipulation reference
• [Crypto Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-crypto-all.md) - Hash functions (MD5, SHA1, SHA256)
• [Bitfield Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-bitfield-all.md) - Flag extraction and decoding
• [Text Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-text-all.md) - String matching (contains, startswith, endswith)
• [Parsing Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-parsing-all.md) - All parsing operations (JSON, CSV, XML, URI, etc.)
• [ObjectArray Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-objectarray-all.md) - Nested array operations

📖 Learning Paths

• Beginners: [Query Fundamentals](CrowdStrike CQL Knowledgebase/Writing Queries/01_query_fundamentals.md) → [Frequent Operations](CrowdStrike CQL Knowledgebase/Writing Queries/02_frequent_operations.md)
• Intermediate: [Join Operations Overview](CrowdStrike CQL Knowledgebase/Query Joins and Lookups/01_joins_overview.md) → [Aggregation Functions](CrowdStrike CQL Knowledgebase/Query Functions/functions-groupby.md)
• Advanced: [Query Optimization](CrowdStrike CQL Knowledgebase/Writing Queries/04_statement_ordering.md) → [Join Performance](CrowdStrike CQL Knowledgebase/Query Joins and Lookups/08_join_performance.md)

🎯 By Task

• Threat Hunting: Common Patterns | Hunting Queries
• Detection Engineering: [Best Practices](CrowdStrike CQL Knowledgebase/Best Practices/)
• Dashboards: [Dashboard Guide](CrowdStrike CQL Knowledgebase/Dashboards/README.md) | [Widget Reference](CrowdStrike CQL Knowledgebase/Widgets/README.md)

⚡ Core Functions (Tier 1 - Enhanced with Examples)

Aggregation: [groupBy](CrowdStrike CQL Knowledgebase/Query Functions/functions-groupby.md) • [count](CrowdStrike CQL Knowledgebase/Query Functions/functions-count.md) • [stats](CrowdStrike CQL Knowledgebase/Query Functions/functions-stats.md) • [collect](CrowdStrike CQL Knowledgebase/Query Functions/functions-collect.md)

Time: [bucket](CrowdStrike CQL Knowledgebase/Query Functions/functions-bucket.md) • [timeChart](CrowdStrike CQL Knowledgebase/Query Functions/functions-timechart.md) • [formatTime](CrowdStrike CQL Knowledgebase/Query Functions/functions-formattime.md)

String: [regex](CrowdStrike CQL Knowledgebase/Query Functions/functions-regex.md) • [format](CrowdStrike CQL Knowledgebase/Query Functions/functions-format.md) • [concat](CrowdStrike CQL Knowledgebase/Query Functions/functions-concat.md) • [split](CrowdStrike CQL Knowledgebase/Query Functions/functions-split.md)

Network: [cidr](CrowdStrike CQL Knowledgebase/Query Functions/functions-cidr.md) • [ipLocation](CrowdStrike CQL Knowledgebase/Query Functions/functions-iplocation.md) • [asn](CrowdStrike CQL Knowledgebase/Query Functions/functions-asn.md)

Joins: [join](CrowdStrike CQL Knowledgebase/Query Functions/functions-join.md) • [selfJoin](CrowdStrike CQL Knowledgebase/Query Functions/functions-selfjoin.md) • [readFile](CrowdStrike CQL Knowledgebase/Query Functions/functions-readfile.md)

🔍 Syntax Reference

• [Operators](CrowdStrike CQL Knowledgebase/Query Language Syntax/cql_syntax_operators.md) - Arithmetic, logical, comparison
• [Regular Expressions](CrowdStrike CQL Knowledgebase/Query Language Syntax/cql_syntax_regex.md) - Pattern matching
• [Time Syntax](CrowdStrike CQL Knowledgebase/Query Language Syntax/cql_syntax_time.md) - Time ranges and formatting
• [Conditional Evaluation](CrowdStrike CQL Knowledgebase/Query Language Syntax/cql_syntax_conditional.md) - case/match statements

Integration with Threat Reports

When generating detection queries from threat intelligence:

1. Map threat actor TTPs to CrowdStrike event types
2. Identify observable artifacts (processes, network, files, registry)
3. Build behavioral queries, not just IOC matching
4. Include confidence scoring and false positive guidance
5. Test query performance before production deployment

Example: Building Detection from Intelligence

// Threat Intel: Actor uses mshta.exe to execute HTA files from web
// MITRE ATT&CK: T1218.005 - Signed Binary Proxy Execution: Mshta

#event_simpleName=ProcessRollup2
| ImageFileName=/mshta\.exe$/i
| CommandLine=/(http|https|ftp):\/\//i
| table([ComputerName, UserName, CommandLine, ParentBaseFileName])

Query Development Workflow

1. Start with tag filter - Identify the right #event_simpleName
2. Add time constraints - Use appropriate time windows
3. Filter progressively - Most selective filters first
4. Test on limited scope - Add aid=?aid for single-host testing
5. Validate fields - Check field names against event dictionary
6. Optimize cardinality - Add limits to aggregations
7. Format output - Add table() for readable results

Tips for Detection Engineering

• Use ?parameter syntax for user-configurable inputs
• Include // comments explaining detection logic
• Build multi-variant queries (high/medium/low confidence)
• Document expected false positive sources
• Test with sample() on large datasets first
• Use timechart() for temporal pattern analysis