soc operations

Correlating Security Events in QRadar

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.

aqlcorrelationibmoffense-managementqradarsiemsoc
Install this skill
npx skills add mukul975/Anthropic-Cybersecurity-Skills
Framework mappings

When to Use

Use this skill when:

  • SOC analysts need to investigate QRadar offenses and correlate events across multiple log sources
  • Detection engineers build custom correlation rules to identify multi-stage attacks
  • Alert tuning is required to reduce false positive offenses and improve signal quality
  • The team migrates from basic event monitoring to behavior-based correlation

Do not use for log source onboarding or parsing — that requires QRadar administrator access and DSM editor knowledge.

Prerequisites

  • IBM QRadar SIEM 7.5+ with offense management enabled
  • AQL knowledge for ad-hoc event and flow queries
  • Log sources normalized with proper QID mappings (Windows, firewall, proxy, endpoint)
  • 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)
ORDER BY 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() - 24*60*60*1000
ORDER BY startTime ASC
LIMIT 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
LEFT JOIN flows f ON e.sourceIP = f.sourceIP
  AND e.destinationIP = f.destinationIP
  AND f.startTime BETWEEN e.startTime AND e.startTime + 300000
WHERE e.category = 'Authentication'
  AND e.sourceIP IN (
    SELECT sourceIP FROM events
    WHERE QIDNAME(qid) = 'Authentication Failure'
      AND startTime > NOW() - 3600000
    GROUP BY sourceIP
    HAVING COUNT(*) > 20
  )
  AND e.startTime > NOW() - 3600000
ORDER BY 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 NOT IN (
        SELECT ip FROM reference_data.sets('Internal_DNS_Servers')
      )
      AND startTime > NOW() - 86400000
    GROUP BY sourceIP
    HAVING COUNT(*) > 500
  )
  AND destinationPort NOT IN (80, 443, 53)
  AND startTime > NOW() - 86400000
GROUP BY sourceIP, destinationIP
HAVING SUM(sourceBytes) > 104857600
ORDER BY 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 generators
SELECT 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
  )
GROUP BY qid, logSourceId
ORDER BY 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 category
SELECT offenseType, status, COUNT(*) AS offense_count,
       AVG(magnitude) AS avg_magnitude
FROM offenses
WHERE status = 'OPEN'
GROUP BY offenseType, status
ORDER BY offense_count DESC
 
-- Mean time to close offenses
SELECT DATEFORMAT(startTime, 'yyyy-MM-dd') AS day,
       AVG(closeTime - startTime) / 60000 AS avg_close_minutes,
       COUNT(*) AS closed_count
FROM offenses
WHERE status = 'CLOSED'
  AND startTime > NOW() - 30*24*60*60*1000
GROUP BY DATEFORMAT(startTime, 'yyyy-MM-dd')
ORDER BY day

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
Magnitude QRadar's calculated offense severity combining relevance, severity, and credibility scores (1-10)
Reference Set Dynamic lookup table in QRadar for whitelists, watchlists, and enrichment data used in rules
QID QRadar Identifier — unique numeric ID mapping vendor-specific events to normalized categories
Coalescing QRadar's mechanism for grouping related events into a single offense to reduce analyst workload

Tools & Systems

  • IBM QRadar SIEM: Enterprise SIEM platform with event correlation, offense management, and AQL query engine
  • QRadar Pulse: Dashboard framework for building custom visualizations of offense and event metrics
  • QRadar API: RESTful API for automating reference set management, offense operations, and rule deployment
  • QRadar Use Case Manager: App for mapping detection rules to MITRE ATT&CK framework coverage
  • QRadar Assistant: AI-powered analysis tool helping analysts investigate offenses with natural language

Common Scenarios

  • Brute Force to Compromise: Correlate failed auth events with subsequent successful login from same source
  • Lateral Movement Chain: Track authentication events across multiple internal hosts from a single source
  • C2 Beaconing: Correlate periodic DNS queries with low-entropy payloads to unusual domains
  • Privilege Escalation: Correlate user account changes (group additions) with prior suspicious authentication
  • Data Exfiltration: Correlate large outbound flow volumes with prior internal reconnaissance activity

Output Format

QRADAR OFFENSE INVESTIGATION — Offense #12345
━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━
Offense Type:   Brute Force with Subsequent Access
Magnitude:      8/10 (Severity: 8, Relevance: 9, Credibility: 7)
Created:        2024-03-15 14:23:07 UTC
Contributing:   247 events from 3 log sources
 
Correlation Chain:
  14:10-14:22  — 234 Authentication Failures (EventCode 4625) from 192.168.1.105 to DC-01
  14:23:07     — Authentication Success (EventCode 4624) from 192.168.1.105 to DC-01 (user: admin)
  14:25:33     — New Process: cmd.exe spawned by admin on DC-01
  14:26:01     — Net.exe user /add detected on DC-01
 
Sources Correlated:
  Windows Security Logs (DC-01)
  Sysmon (DC-01)
  Firewall (Palo Alto PA-5260)
 
Disposition:    TRUE POSITIVE — Escalated to Incident Response
Ticket:         IR-2024-0432
Source materials

References and resources

Everything below is rendered for inspection. Script files are read-only and never run.

References 1

api-reference.md2.2 KB

QRadar SIEM API Reference

QRadar REST API Base

Base URL: https://<qradar_host>/api/
Auth Header: SEC: <api_token>
Content-Type: application/json

AQL (Ariel Query Language)

-- Search events by offense
SELECT DATEFORMAT(startTime, 'yyyy-MM-dd HH:mm:ss') AS event_time,
       sourceIP, destinationIP, username,
       QIDNAME(qid) AS event_name
FROM events
WHERE INOFFENSE(12345)
ORDER BY startTime ASC LIMIT 500
 
-- Brute force detection
SELECT sourceIP, COUNT(*) AS failures
FROM events
WHERE QIDNAME(qid) ILIKE '%Authentication Fail%'
  AND startTime > NOW() - 3600000
GROUP BY sourceIP HAVING COUNT(*) > 10
 
-- Cross-source correlation (events + flows)
SELECT e.sourceIP, e.destinationIP, f.sourceBytes
FROM events e LEFT JOIN flows f
  ON e.sourceIP = f.sourceIP AND e.destinationIP = f.destinationIP
WHERE e.category = 'Authentication'

Offense Management API

# List open offenses
curl -s "https://qradar/api/siem/offenses?filter=status%3DOPEN" -H "SEC: $TOKEN"
 
# Get offense details
curl -s "https://qradar/api/siem/offenses/12345" -H "SEC: $TOKEN"
 
# Close offense
curl -X POST "https://qradar/api/siem/offenses/12345?closing_reason_id=1&status=CLOSED" \
  -H "SEC: $TOKEN"
 
# Add note to offense
curl -X POST "https://qradar/api/siem/offenses/12345/notes" \
  -H "SEC: $TOKEN" -H "Content-Type: application/json" \
  -d '{"note_text": "Investigation completed"}'

Reference Data API

# Create reference set
curl -X POST "https://qradar/api/reference_data/sets" \
  -H "SEC: $TOKEN" -H "Content-Type: application/json" \
  -d '{"name":"Watchlist_IPs","element_type":"IP","timeout_type":"LAST_SEEN","time_to_live":"30 days"}'
 
# Add value to set
curl -X POST "https://qradar/api/reference_data/sets/Watchlist_IPs?value=10.0.5.100" \
  -H "SEC: $TOKEN"
 
# Get set contents
curl -s "https://qradar/api/reference_data/sets/Watchlist_IPs" -H "SEC: $TOKEN"

AQL Functions

Function Description
QIDNAME(qid) Resolve QID to event name
LOGSOURCENAME(id) Resolve log source ID to name
INOFFENSE(id) Filter events belonging to offense
DATEFORMAT(ts, fmt) Format timestamp
NOW() Current time in milliseconds
CATEGORYNAME(cat) Resolve category ID to name

Scripts 1

agent.py6.5 KB
Display-only source. This catalog never executes bundled scripts.
#!/usr/bin/env python3
"""IBM QRadar SIEM correlation and offense management agent."""

import json
import sys
import urllib.request
import urllib.parse
import ssl
from datetime import datetime


class QRadarClient:
    """Client for QRadar REST API operations."""

    def __init__(self, host, api_token, verify_ssl=False):
        self.base_url = f"https://{host}/api"
        self.headers = {
            "SEC": api_token,
            "Content-Type": "application/json",
            "Accept": "application/json",
        }
        self.ctx = ssl.create_default_context()
        if not verify_ssl:
            self.ctx.check_hostname = False
            self.ctx.verify_mode = ssl.CERT_NONE

    def _request(self, method, endpoint, data=None):
        url = f"{self.base_url}/{endpoint}"
        body = json.dumps(data).encode() if data else None
        req = urllib.request.Request(url, data=body, headers=self.headers, method=method)
        try:
            with urllib.request.urlopen(req, context=self.ctx, timeout=60) as resp:
                return json.loads(resp.read().decode())
        except urllib.error.HTTPError as e:
            return {"error": e.code, "message": e.read().decode()}
        except Exception as e:
            return {"error": str(e)}

    def search_aql(self, query):
        """Execute an AQL query and return results."""
        encoded = urllib.parse.quote(query)
        result = self._request("POST", f"ariel/searches?query_expression={encoded}")
        if "error" in result:
            return result
        search_id = result.get("search_id")
        if not search_id:
            return result
        import time
        for _ in range(30):
            status = self._request("GET", f"ariel/searches/{search_id}")
            if status.get("status") == "COMPLETED":
                return self._request("GET", f"ariel/searches/{search_id}/results")
            time.sleep(2)
        return {"error": "AQL query timed out"}

    def get_offenses(self, status_filter="OPEN", limit=50):
        """Retrieve offenses filtered by status."""
        params = f"?filter=status%3D%22{status_filter}%22&Range=items%3D0-{limit-1}"
        return self._request("GET", f"siem/offenses{params}")

    def get_offense_details(self, offense_id):
        """Get detailed information about a specific offense."""
        return self._request("GET", f"siem/offenses/{offense_id}")

    def close_offense(self, offense_id, closing_reason_id, note="Closed by automation"):
        """Close an offense with a reason and note."""
        params = f"?closing_reason_id={closing_reason_id}&status=CLOSED"
        result = self._request("POST", f"siem/offenses/{offense_id}{params}")
        if "error" not in result:
            self.add_note(offense_id, note)
        return result

    def add_note(self, offense_id, note_text):
        """Add an investigation note to an offense."""
        data = {"note_text": note_text}
        return self._request("POST", f"siem/offenses/{offense_id}/notes", data)

    def get_reference_set(self, name):
        """Retrieve a reference set and its entries."""
        encoded = urllib.parse.quote(name)
        return self._request("GET", f"reference_data/sets/{encoded}")

    def add_to_reference_set(self, name, value):
        """Add a value to a reference set."""
        encoded = urllib.parse.quote(name)
        return self._request("POST", f"reference_data/sets/{encoded}?value={urllib.parse.quote(value)}")

    def create_reference_set(self, name, element_type="IP", ttl="30 days"):
        """Create a new reference set."""
        data = {
            "name": name,
            "element_type": element_type,
            "timeout_type": "LAST_SEEN",
            "time_to_live": ttl,
        }
        return self._request("POST", "reference_data/sets", data)

    def get_rules(self, limit=50):
        """List custom rules."""
        return self._request("GET", f"analytics/rules?Range=items%3D0-{limit-1}")


def brute_force_aql(client, hours=24):
    """AQL query to detect brute force followed by success."""
    query = f"""
    SELECT sourceIP, destinationIP, username,
           QIDNAME(qid) AS event_name, COUNT(*) as cnt
    FROM events
    WHERE category = 'Authentication'
      AND QIDNAME(qid) ILIKE '%fail%'
      AND startTime > NOW() - {hours}*60*60*1000
    GROUP BY sourceIP, destinationIP, username, qid
    HAVING COUNT(*) > 10
    ORDER BY cnt DESC
    LIMIT 50
    """
    return client.search_aql(query)


def lateral_movement_aql(client, hours=24):
    """AQL query to detect lateral movement patterns."""
    query = f"""
    SELECT sourceIP, COUNT(DISTINCT destinationIP) as dest_count,
           COUNT(*) as event_count, username
    FROM events
    WHERE category = 'Authentication'
      AND sourceIP NOT IN (SELECT ip FROM reference_data.sets('Domain_Controllers'))
      AND startTime > NOW() - {hours}*60*60*1000
    GROUP BY sourceIP, username
    HAVING COUNT(DISTINCT destinationIP) > 5
    ORDER BY dest_count DESC
    LIMIT 30
    """
    return client.search_aql(query)


def generate_report(client):
    """Generate a QRadar offense summary report."""
    offenses = client.get_offenses("OPEN", 100)
    if isinstance(offenses, dict) and "error" in offenses:
        return offenses
    return {
        "timestamp": datetime.utcnow().isoformat() + "Z",
        "open_offenses": len(offenses) if isinstance(offenses, list) else 0,
        "offenses": offenses[:20] if isinstance(offenses, list) else offenses,
    }


if __name__ == "__main__":
    import os
    host = os.environ.get("QRADAR_HOST", "qradar.example.com")
    token = os.environ.get("QRADAR_TOKEN", "")
    if not token:
        print("Set QRADAR_HOST and QRADAR_TOKEN environment variables")
        sys.exit(1)

    client = QRadarClient(host, token)
    action = sys.argv[1] if len(sys.argv) > 1 else "report"

    if action == "report":
        print(json.dumps(generate_report(client), indent=2))
    elif action == "offenses":
        print(json.dumps(client.get_offenses(), indent=2))
    elif action == "offense" and len(sys.argv) > 2:
        print(json.dumps(client.get_offense_details(sys.argv[2]), indent=2))
    elif action == "brute-force":
        print(json.dumps(brute_force_aql(client), indent=2))
    elif action == "lateral-movement":
        print(json.dumps(lateral_movement_aql(client), indent=2))
    elif action == "aql" and len(sys.argv) > 2:
        print(json.dumps(client.search_aql(" ".join(sys.argv[2:])), indent=2))
    else:
        print("Usage: agent.py [report|offenses|offense <id>|brute-force|lateral-movement|aql <query>]")
Keep exploring