Data Collection

Data Collection

Data Scope

Item Detail

Data window

April 5-8, 2026 (3 days — full Ariel retention available)

Raw grouped records

1,176,902

Total events (3-day)

2,239,727

Projected monthly events

~22.4 million

Projected monthly ingest

~11.2 GB (estimated at 500 bytes/event)

Financial projections

NOT included — pending Sentinel workspace tier and Monad contract confirmation

Environment Setup

# Load CHLA credentials — sources $QRADAR_TOKEN, $QRADAR_HOST, $ISE_HOST
dsec d001 dev/network

# Verify QRadar API access
curl -k \
  -H "SEC: $QRADAR_TOKEN" \
  -H "Accept: application/json" \
  "https://$QRADAR_HOST/api/help/capabilities"
dsec sources secrets from the d001 domain registry using age encryption. No secrets touch the filesystem unencrypted.

AQL Query Development

Step 1 — Validate Data Window

Always run this first. QRadar 7.5.0 UP13 devicetime epoch values are non-standard (~1.775E12).

SELECT MIN(devicetime) AS earliest, MAX(devicetime) AS latest FROM events

Step 2 — Validate Total Event Volume

SELECT COUNT(*) AS TotalRawEvents, SUM(eventcount) AS TotalEventCount FROM events

Step 3 — Validate AQL Functions

-- Validate metric fields
SELECT severity, credibility, relevance, magnitude FROM events LIMIT 5

-- Validate category function
SELECT CATEGORYNAME(category) AS Category FROM events LIMIT 5

-- Validate log source functions
SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType
FROM events LIMIT 5
HIGHLEVELCATEGORYNAME() is not available in QRadar 7.5.0 UP13 — causes parse error. Use CATEGORYNAME() only.

Step 4 — Full Migration Inventory Query

SELECT
  LOGSOURCENAME(logsourceid)    AS LogSource,
  LOGSOURCETYPENAME(devicetype) AS LogSourceType,
  QIDNAME(qid)                  AS EventName,
  CATEGORYNAME(category)        AS Category,
  severity,
  credibility,
  relevance,
  magnitude,
  COUNT(*)                      AS EventCount
FROM events
GROUP BY logsourceid, devicetype, qid, category,
         severity, credibility, relevance, magnitude
ORDER BY LogSource, EventCount DESC

QRadar REST API Workflow

Submit → Poll → Fetch Pattern

# Step 1 — Submit AQL query (URL parameter format — JSON body POST returns error 1005)
curl -k -X POST \
  -H "SEC: $QRADAR_TOKEN" \
  -H "Accept: application/json" \
  "https://$QRADAR_HOST/api/ariel/searches?query_expression=<URL_ENCODED_QUERY>" \
  > /tmp/qradar_submit.json

# Extract search_id
SEARCH_ID=$(cat /tmp/qradar_submit.json | jq -r '.search_id')
echo "Search ID: $SEARCH_ID"
# Step 2 — Poll until completed
curl -k \
  -H "SEC: $QRADAR_TOKEN" \
  -H "Accept: application/json" \
  "https://$QRADAR_HOST/api/ariel/searches/$SEARCH_ID" \
  > /tmp/qradar_poll.json

cat /tmp/qradar_poll.json | jq '{status: .status, completed: .completed, record_count: .record_count}'
# Step 3 — Fetch results
curl -k \
  -H "SEC: $QRADAR_TOKEN" \
  -H "Accept: application/json" \
  "https://$QRADAR_HOST/api/ariel/searches/$SEARCH_ID/results" \
  > /tmp/qradar-migration-export.json

Automated Collection Script

Full 7-step collection script that runs the complete data extraction pipeline:

  1. Data window check

  2. Total event volume

  3. Full migration inventory

  4. Volume per log source (event count based)

  5. Offense data (/api/siem/offenses)

  6. Asset inventory (/api/asset_model/assets)

  7. Log source list (/api/config/event_sources/log_source_management/log_sources)

Script: siem-migration.adoc workstream 1a (to be promoted to scripts/ when stabilized).

jq Validation Patterns

# Count records
cat /tmp/qradar-migration-export.json | jq '.events | length'

# Pretty print first event
cat /tmp/qradar-migration-export.json | jq '.events[0]'

# Filter high severity
cat /tmp/qradar-migration-export.json | jq '.events[] | select(.severity >= 7)'

# Extract two fields
cat /tmp/qradar-migration-export.json | jq '.events[] | {source: .LogSource, count: .EventCount}'

# Export to CSV
cat /tmp/qradar-migration-export.json | jq -r '.events[] | [.LogSource, .EventCount] | @csv'

Python Report Generation

Excel report with 13 sheets including Glossary, Executive Summary, Source Summary, Migration Plan, Risk Scores, and 12 embedded charts (Catppuccin Mocha themed).

Output: migration_report.xlsx

Key computed metrics:

  • ReadinessScore (0-100) — Volume(30%) + Diversity(30%) + Risk(40%)

  • WeightedRisk — sum(magnitude x EventCount) / TotalEvents

  • MigrationPhase — Phase 1 (High + score>=50), Phase 2 (High/Medium), Phase 3 (Low)

  • SuggestedParserType — static (1 event type), simple (2-5), structured (6-20), complex (20+)

Script: siem-migration.adoc Python Report section (to be promoted to scripts/ when stabilized).

AQL Gotchas — QRadar 7.5.0 UP13

Gotcha Detail

LAST N DAYS not supported

Causes parse error. Use devicetime BETWEEN or omit time filter.

STARTTIME BETWEEN returns no results

Field unreliable. Use devicetime instead.

devicetime epoch non-standard

Values ~1.775E12 — not standard Unix epoch. Always run MIN/MAX first.

HIGHLEVELCATEGORYNAME invalid

Not available. Use CATEGORYNAME only.

JSON body POST returns error 1005

Use ?query_expression= URL parameter instead.

Default AQL window = 5 minutes

Always specify time range explicitly.

offenses not an AQL table

Use REST API GET /api/siem/offenses instead.

SEC header not Bearer

QRadar uses SEC: token — not Authorization: Bearer.