Correlates security events in IBM QRadar SIEM using AQL (Ariel Query Language), custom rules, building blocks, and offense management to detect multi-stage attacks across network, endpoint, and application log sources. Use when SOC analysts need to investigate QRadar offenses, build correlation rules, or tune detection logic for reducing false positives.
Instrucciones de origen · Vista previa de solo lectura
name
crrltn-sec-events-qradar
description
Correlates security events in IBM QRadar SIEM using AQL (Ariel Query Language), custom rules, building blocks, and offense management to detect multi-stage attacks across network, endpoint, and application log sources. Use when SOC analysts need to investigate QRadar offenses, build correlation rules, or tune detection logic for reducing false positives.
User role with offense management, rule creation, and AQL search permissions
Reference sets/maps configured for whitelist and watchlist management
Workflow
Step 1: Investigate an Offense with AQL
Open an offense in QRadar and query contributing events using AQL (Ariel Query Language):
SELECT DATEFORMAT(startTime, 'yyyy-MM-dd HH:mm:ss') AS event_time,
sourceIP, destinationIP, username,
LOGSOURCENAME(logSourceId) AS log_source,
QIDNAME(qid) AS event_name,
category, magnitude
FROM events
WHERE INOFFENSE(12345)
ORDERBY startTime ASC
LIMIT 500
Pivot on the source IP to find all activity:
SELECT DATEFORMAT(startTime, 'yyyy-MM-dd HH:mm:ss') AS event_time,
destinationIP, destinationPort, username,
QIDNAME(qid) AS event_name,
eventCount, category
FROM events
WHERE sourceIP ='192.168.1.105'AND startTime > NOW() -
startTime
LIMIT
24
*
60
*
60
*
1000
ORDER
BY
ASC
1000
Step 2: Build a Custom Correlation Rule
Create a multi-condition rule detecting brute force followed by successful login:
Rule 1 — Brute Force Detection (Building Block):
Rule Type: Event
Rule Name: BB: Multiple Failed Logins from Same Source
Tests:
- When the event(s) were detected by one or more of [Local]
- AND when the event QID is one of [Authentication Failure (5000001)]
- AND when at least 10 events are seen with the same Source IP
in 5 minutes
Rule Action: Dispatch new event (Category: Authentication, QID: Custom_BruteForce)
Rule 2 — Brute Force Succeeded (Correlation Rule):
Rule Type: Offense
Rule Name: COR: Brute Force with Subsequent Successful Login
Tests:
- When an event matches the building block BB: Multiple Failed Logins from Same Source
- AND when an event with QID [Authentication Success (5000000)] is detected
from the same Source IP within 10 minutes
- AND the Destination IP is the same for both events
Rule Action: Create offense, set severity to High, set relevance to 8
Step 3: Use AQL for Cross-Source Correlation
Correlate authentication failures with network flows to detect lateral movement:
SELECT e.sourceIP, e.destinationIP, e.username,
QIDNAME(e.qid) AS event_name,
e.eventCount,
f.sourceBytes, f.destinationBytes
FROM events e
LEFTJOIN flows f ON e.sourceIP = f.sourceIP
AND e.destinationIP = f.destinationIP
AND f.startTime BETWEEN e.startTime AND e.startTime +300000WHERE e.category ='Authentication'AND e.sourceIP IN (
SELECT sourceIP FROM events
WHERE QIDNAME(qid) ='Authentication Failure'AND startTime > NOW() -3600000GROUPBY sourceIP
HAVINGCOUNT(*) >20
)
AND e.startTime > NOW() -3600000ORDERBY e.startTime ASC
Detect data exfiltration by correlating DNS queries with large outbound flows:
SELECT sourceIP, destinationIP,
SUM(sourceBytes) AS total_bytes_out,
COUNT(*) AS flow_count
FROM flows
WHERE sourceIP IN (
SELECT sourceIP FROM events
WHERE QIDNAME(qid) ILIKE '%DNS%'AND destinationIP NOTIN (
SELECT ip FROM reference_data.sets('Internal_DNS_Servers')
)
AND startTime > NOW() -86400000GROUPBY sourceIP
HAVINGCOUNT(*) >500
)
AND destinationPort NOTIN (80, 443, 53)
AND startTime > NOW() -86400000GROUPBY sourceIP, destinationIP
HAVINGSUM(sourceBytes) >104857600ORDERBY total_bytes_out DESC
Step 4: Configure Reference Sets for Context Enrichment
Create reference sets for dynamic whitelists and watchlists:
# Create reference set via QRadar API
curl -X POST "https://qradar.example.com/api/reference_data/sets" \
-H "SEC: YOUR_API_TOKEN" \
-H "Content-Type: application/json" \
-d '{
"name": "Known_Pen_Test_IPs",
"element_type": "IP",
"timeout_type": "LAST_SEEN",
"time_to_live": "30 days"
}'# Add entries
curl -X POST "https://qradar.example.com/api/reference_data/sets/Known_Pen_Test_IPs" \
-H "SEC: YOUR_API_TOKEN" \
-d "value=10.0.5.100"
Use reference sets in rule conditions to exclude known benign activity:
Test: AND when the Source IP is NOT contained in any of [Known_Pen_Test_IPs]
Test: AND when the Destination IP is contained in any of [Critical_Asset_IPs]
Step 5: Tune Offense Generation
Reduce false positives by adding building block filters:
-- Find top false positive generatorsSELECT QIDNAME(qid) AS event_name,
LOGSOURCENAME(logSourceId) AS log_source,
COUNT(*) AS event_count,
COUNT(DISTINCT sourceIP) AS unique_sources
FROM events
WHERE INOFFENSE(
SELECT offenseId FROM offenses
WHERE status ='CLOSED'AND closeReason ='False Positive'AND startTime > NOW() -30*24*60*60*1000
)
GROUPBY qid, logSourceId
ORDERBY event_count DESC
LIMIT 20
Apply tuning:
Add high-frequency false positive sources to reference set exclusions
Increase event thresholds on noisy rules (e.g., 10 failed logins -> 25 for service accounts)
Set offense coalescing to group related events under a single offense
Step 6: Build Custom Dashboard for Correlation Monitoring
Create a QRadar Pulse dashboard with key correlation metrics:
-- Active offenses by categorySELECT offenseType, status, COUNT(*) AS offense_count,
AVG(magnitude) AS avg_magnitude
FROM offenses
WHERE status ='OPEN'GROUPBY offenseType, status
ORDERBY offense_count DESC-- Mean time to close offensesSELECT DATEFORMAT(startTime, 'yyyy-MM-dd') ASday,
AVG(closeTime - startTime) /60000AS avg_close_minutes,
COUNT(*) AS closed_count
FROM offenses
WHERE status ='CLOSED'AND startTime > NOW() -30*24*60*60*1000GROUPBY DATEFORMAT(startTime, 'yyyy-MM-dd')
ORDERBYday
Key Concepts
Term
Definition
AQL
Ariel Query Language — QRadar's SQL-like query language for searching events, flows, and offenses
Offense
QRadar's correlated incident grouping multiple events/flows under a single investigation unit
Building Block
Reusable rule component that categorizes events without generating offenses, used as input to correlation rules
