ποΈ Part 3: SOAR BigQuery Schema Reference
Β
(siemplify_search_everything_db)
TheΒ siemplify_search_everything_dbΒ dataset contains tables capturing the lifecycle of incident management, playbook automation, and analyst workflows.
Synchronization & Guardrails
- Automated Sync:Β Handled continuously in the background by the SOAR data synchronization engine.
- Simulated Cases Excluded:Β Cases created in simulation/test mode are deliberately excluded from BigQuery export to prevent corrupting production KPIs and SLA metrics.
- Field Truncation:Β To adhere to BigQuery performance boundaries, field values exceedingΒ 10,000 charactersΒ are truncated.
- Exact Table Naming:Β Keep in mind that some tables contain specific legacy naming conventions (e.g.,Β
AlertProductsDistribuations). Use the exact literal table names in your SQL queries.
Key SOAR Tables Overview
Β
βββββββββββββββββββββ
β DashboardCases β
βββββββββββ¬ββββββββββ
β
ββββββββββββββββΌβββββββββββββββ
βΌ βΌ βΌ
ββββββββββββββββββ βββββββββββββ ββββββββββββββββββββ
β DashboardAlertsβ β CaseTags β β CaseStageEntries β
ββββββββββ¬ββββββββ βββββββββββββ ββββββββββββββββββββ
β
ββββββββββββββΌβββββββββββββ
βΌ βΌ βΌ
βββββββββββββββββββββββββββββββββββββββ
βAlertEntityββPlaybooks ββActionRslt β
βββββββββββββββββββββββββββββββββββββββ
| Table Category | Key Tables | Purpose / Data Captured |
|---|---|---|
| Cases & Lifecycle | DashboardCases,Β CaseStageEntries,Β CaseAssignActivities,Β CaseMergeHistories | Case status, priority, assigned analyst, stage transitions, closure reasons, merge records, and SLA status. |
| Alerts & Ingestion | DashboardAlerts,Β AlertsDistribuations,Β AlertOntologyFamilies,Β AlertProductsDistribuations | Source alerts grouped into cases, vendor products, alert rules, ontology families, and severity. |
| Playbooks & Actions | DashboardAlertPlaybooks,Β WorkflowStepIndexRecords,Β SystemActionResults | Automated playbook executions, individual action step runtimes, success/failure statuses, and script outputs. |
| Entities & Enrichments | DashboardAlertEntities,Β InvolvedEntityRelations,Β AlertUsersDistribuations | Extracted entities (IPs, hostnames, users), relations, whether entities are flagged as suspicious or internal. |
| SLA & Performance | SystemCaseSlas,Β SystemAlertSlas,Β DashboardAlertCategoryOutcomes | Target vs. actual SLA thresholds, resolution turnaround times, and alert outcome distributions. |
| Configuration Metadata | MetadataCaseStages,Β MetadataSocRoles,Β MetadataUserProfiles,Β CustomFields,Β CustomFieldValues | Definitions of case stages, team roles, analyst profiles, and custom case/closure field schemas. |
π‘ High-Value SOAR BigQuery Use Cases & Sample Queries
Use Case 1: SOC Performance & MTTR by Case Root Cause
Measure how quickly cases are closed based on their root cause category.
Β
sql
SELECT
RootCause,
COUNT(1) AS total_cases,
ROUND(AVG(TIMESTAMP_DIFF(ClosedTime, CreatedTime, MINUTE)), 2) AS avg_mttr_minutes,
ROUND(AVG(TIMESTAMP_DIFF(FirstAssignedTime, CreatedTime, MINUTE)), 2) AS avg_mtta_minutes
FROM
`YOUR_PROJECT_ID.siemplify_search_everything_db.DashboardCases`
WHERE
Status = 'Closed'
AND CreatedTime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
GROUP BY
RootCause
ORDER BY
total_cases DESC;
Use Case 2: Playbook Automation Health & Failure Rates
Identify playbooks or automated integration actions experiencing frequent failures or slow runtimes.
Β
sql
SELECT
PlaybookName,
ActionName,
Status,
COUNT(1) AS execution_count,
ROUND(AVG(ExecutionDurationSeconds), 2) AS avg_duration_sec
FROM
`YOUR_PROJECT_ID.siemplify_search_everything_db.SystemActionResults`
WHERE
CreatedTime >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
GROUP BY
PlaybookName,
ActionName,
Status
HAVING
Status IN ('Failed', 'Timeout', 'Error')
ORDER BY
execution_count DESC;
Use Case 3: Analyst Workload and Case Stage Progression
Track how cases move through stages and which analysts handle the highest volumes.
Β
sql
SELECT
c.AssignedUser,
s.StageName,
COUNT(DISTINCT c.Id) AS active_cases,
ROUND(AVG(TIMESTAMP_DIFF(CURRENT_TIMESTAMP(), s.StartTime, HOUR)), 1) AS avg_hours_in_stage
FROM
`YOUR_PROJECT_ID.siemplify_search_everything_db.DashboardCases` c
JOIN
`YOUR_PROJECT_ID.siemplify_search_everything_db.CaseStageEntries` s
ON c.Id = s.CaseId
WHERE
c.Status = 'Open'
GROUP BY
c.AssignedUser,
s.StageName
ORDER BY
active_cases DESC;
βοΈ Summary Comparison: Advanced BQ Export vs. SOAR BQ
| Feature | Advanced BigQuery Export (SIEM) | SOAR BigQuery (search_everything_db) |
|---|---|---|
| Primary Data Scope | UDM Events, Rules/Detections, IoCs, Entity Graph | Cases, Alerts, Playbooks, SOC SLAs, Users/Roles |
| Architecture | Managed Tenant + Analytics Hub Linked Dataset | Direct Managed / BYOBQ Dataset |
| Data Freshness | Near real-time (< 5β10 mins) | Synchronized continuous background sync |
| License Tier | Enterprise Plus | Requires SOAR Advanced Reporting |
| Dataset Name in BQ | secops_linked_datalake | siemplify_search_everything_db |
| Access Method | Project IAM (roles/bigquery.dataViewer) | Support Case or BackstoryΒ bigqueryAccessΒ API |
| Deduplication | Automatic Fine-Grained DML (FGDML) Merges | Managed backend synchronization |
