Query Action

Query a Case Management Table

Query a Case Management Table- Exception Rule

When querying a case management table using a condition, and you are using both the in operator, it is essential to use the arr() function when working with arrays. Failing to do so will cause the workflow action to fail.For Example:

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

Parameter	Description
Table Name	Type of Table: Alerts, Attachments, Cases, Custom Table, Observables, Tasks
Fields	Field 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.

Parameter	Description
SQL Query	The SQL Query
Output Format	Output Format Types: Table, CSV or JSON

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:

SQL Query- Use Case Examples

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

Name	Display Name
case_id	Case ID
type	Case Type
severity	Severity
name	Name
summary	Summary
created_at	Created At
case_manager	Case Manager
status	Status
linked_observables	Linked Observables
close_reason	Close Reason
closed_at	Closed At
closed_by	Closed By
closed_by_workflow	Closed By Workflow
collaborators	Collaborators
created_by	Created By
linked_alerts	Linked Alerts
linked_attachments	Linked Attachments
linked_cases	Linked Cases
linked_tasks	Linked Tasks
mitre_attack	Mitre Attack
response	Response
sla	SLA
sla_expiry	SLA Expiry
case_tags	Tags
created_at	Created At
vendors	Vendors

Observables

Name	Display Name
created_by	Created By
content	Content
enrichment_data	Enrichment Data
updated_at	Updated At
case_ids	Linked Cases
attachment_ids	Linked Attachments
task_ids	Linked Tasks
name	Name
type	Observable Type
description	Description
verdict	Verdict
alert_ids	Linked Alerts
id	ID
updated_by	Updated By
observable_id	Observable ID
auto_id	Observable Number
observable_ids	Linked Observables

Alerts

Name	Display Name
processed	Processed
id	ID
updated_at	Updated At
created_by	Created By
updated_by	Updated By
observable_ids	Linked Observables
template_exists	Template Exists
severity	Severity
response	Response
alert_id	Alert ID
case_ids	Linked Cases
type	Alert Type
name	Name
vendor	Vendor
event	Event
description	Description
attachment_ids	Linked Attachments

Attachments

Name	Display Name
updated_at	Updated At
created_by	Created By
updated_by	Updated By
id	ID
alert_ids	Linked Alerts
attachment	Attachment
attachment_id	Attachment ID
case_ids	Linked Cases
description	Description
name	Name
response	Response
task_ids	Linked Tasks
type	Attachment Type
menu	Menu
observable_ids	Linked Observables
created_at	Created At

Tasks

Name	Display Name
updated_at	Updated At
id	ID
created_by	Created By
updated_by	Updated By
created_at	Created At
observable_ids	Linked Observables
is_blocking	Block closing case until done
description	Description
closed_at	Closed At
case_ids	Linked Cases
task_id	Task ID
due_date	Due date
task_ids	Linked Tasks
name	Name
status	Status
alert_ids	Linked Alerts
priority	Priority

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:

Case Management

Automated Case Management

Case Management Interface

Agent Blink

Triggers & Actions

Case Management Settings

Query a Case Management Table

Query a Case Management Table using SQL

List of Tables

Cases

Observables

Alerts

Attachments

Tasks

Custom Tables

​Query a Case Management Table

​Query a Case Management Table using SQL

​List of Tables

​Cases

​Observables

​Alerts

​Attachments

​Tasks

​Custom Tables

Query a Case Management Table

Query a Case Management Table using SQL

List of Tables

Cases

Observables

Alerts

Attachments

Tasks

Custom Tables