​
Query a Case Management Table

Query a Case Management Table by filling in the following parameters in the step.

ParameterDescription
Table NameType of Table: Alerts, Attachments, Cases, Custom Table, Observables, Tasks
FieldsField Types
Condition (Optional)Condition that compares two Case Management table field values.
Advanced-Limit (Optional)Query Limit

​
Query a Case Management Table using SQL

Query a Case Management Table using SQL by filling in the following parameters in the step.

ParameterDescription
SQL QueryThe SQL Query
Output FormatOutput Format Types: Table, CSV or JSON

NOTE

Please note that you need to use table and column names and not display names. Down below, is the full list of table and column names mapped to their display names.

  • To query associated cases linked to fields across different tables, use the following SQL query:

To fetch related record objects (e.g., full rows like id, name) for each case:

SELECT
    c.id AS case_id,
    json_agg(
        json_build_object(
            'id', a.id,
            'name', a.name
        )
    ) AS <inserted_table_name>
FROM cases c
JOIN records_relations rr
    ON (
        (rr.record_id_a = c.id AND rr.table_name_a = 'cases' AND rr.table_name_b = '<inserted_table_name>') OR
        (rr.record_id_b = c.id AND rr.table_name_b = 'cases' AND rr.table_name_a = '<inserted_table_name>')
    )
JOIN <inserted_table_name>
    ON (
        (<inserted_table_name>.id = rr.record_id_a AND rr.table_name_a = '<inserted_table_name>') OR
        (<inserted_table_name>.id = rr.record_id_b AND rr.table_name_b = '<inserted_table_name>')
    )
GROUP BY c.id;

To fetch only the related record IDs instead of full objects:

SELECT
    c.id AS case_id,
    json_agg(
        CASE
            WHEN rr.table_name_a = '<inserted_table_name>' THEN rr.record_id_a
            ELSE rr.record_id_b
        END
    ) AS <inserted_table_name>_ids
FROM cases c
JOIN records_relations rr
    ON (
        (rr.table_name_a = 'cases' AND rr.record_id_a = c.id AND rr.table_name_b = '<inserted_table_name>') OR
        (rr.table_name_b = 'cases' AND rr.record_id_b = c.id AND rr.table_name_a = '<inserted_table_name>')
    )
GROUP BY c.id;

NOTE

Replace <inserted_table_name> with the actual table name that is associated with the cases.

For example, to fetch alert records with selected fields attached to each case, you can use the following SQL query:

SELECT
    c.id AS case_id,
    json_agg(
        json_build_object(
            'id', a.id,
            'name', a.name
        )
    ) AS alerts
FROM cases c
JOIN records_relations rr
    ON (
        (rr.record_id_a = c.id AND rr.table_name_a = 'cases' AND rr.table_name_b = 'alerts') OR
        (rr.record_id_b = c.id AND rr.table_name_b = 'cases' AND rr.table_name_a = 'alerts')
    )
JOIN alerts a
    ON (
        (a.id = rr.record_id_a AND rr.table_name_a = 'alerts') OR
        (a.id = rr.record_id_b AND rr.table_name_b = 'alerts')
    )
GROUP BY c.id;

To fetch only the alert IDs attached to each case, you can use the following SQL query:

SELECT
    c.id AS case_id,
    json_agg(
        CASE
            WHEN rr.table_name_a = 'alerts' THEN rr.record_id_a
            ELSE rr.record_id_b
        END
    ) AS alert_ids
FROM cases c
JOIN records_relations rr
    ON (
        (rr.table_name_a = 'cases' AND rr.record_id_a = c.id AND rr.table_name_b = 'alerts') OR
        (rr.table_name_b = 'cases' AND rr.record_id_b = c.id AND rr.table_name_a = 'alerts')
    )
GROUP BY c.id;

​
List of Tables

​
Cases

NameDisplay Name
case_idCase ID
typeCase Type
severitySeverity
nameName
summarySummary
created_atCreated At
case_managerCase Manager
statusStatus
linked_observablesLinked Observables
close_reasonClose Reason
closed_atClosed At
closed_byClosed By
closed_by_workflowClosed By Workflow
collaboratorsCollaborators
created_byCreated By
linked_alertsLinked Alerts
linked_attachmentsLinked Attachments
linked_casesLinked Cases
linked_tasksLinked Tasks
mitre_attackMitre Attack
responseResponse
slaSLA
sla_expirySLA Expiry
case_tagsTags
created_atCreated At
vendorsVendors

​
Observables

NameDisplay Name
created_byCreated By
contentContent
enrichment_dataEnrichment Data
updated_atUpdated At
case_idsLinked Cases
attachment_idsLinked Attachments
task_idsLinked Tasks
nameName
typeObservable Type
descriptionDescription
verdictVerdict
alert_idsLinked Alerts
idID
updated_byUpdated By
observable_idObservable ID
auto_idObservable Number
observable_idsLinked Observables

​
Alerts

NameDisplay Name
processedProcessed
idID
updated_atUpdated At
created_byCreated By
updated_byUpdated By
observable_idsLinked Observables
template_existsTemplate Exists
severitySeverity
responseResponse
alert_idAlert ID
case_idsLinked Cases
typeAlert Type
nameName
vendorVendor
eventEvent
descriptionDescription
attachment_idsLinked Attachments

​
Attachments

NameDisplay Name
updated_atUpdated At
created_byCreated By
updated_byUpdated By
idID
ioc_idsLinked IOCs
alert_idsLinked Alerts
attachmentAttachment
attachment_idAttachment ID
case_idsLinked Cases
descriptionDescription
nameName
responseResponse
task_idsLinked Tasks
typeAttachment Type
menuMenu
observable_idsLinked Observables
created_atCreated At

​
Tasks

NameDisplay Name
updated_atUpdated At
idID
created_byCreated By
updated_byUpdated By
created_atCreated At
observable_idsLinked Observables
is_blockingBlock closing case until done
descriptionDescription
closed_atClosed At
case_idsLinked Cases
task_idTask ID
due_dateDue date
task_idsLinked Tasks
nameName
statusStatus
alert_idsLinked Alerts
priorityPriority

​
Custom Tables

If you’re using custom tables, please follow the Query a Table Using SQL docs to fetch the correct table scheme. You can get the full table name via the Copy Table ID button: