How to Display Site Counts from a Data Table Using Matched IP Addresses?

Hi Sanzz,

The error joining case with other datasources is not supported occurs because Google SecOps (Chronicle) Dashboard and Search engines do not currently support direct JOIN operations between Case Management entities (case.*) and Reference Data Tables (%DATA_TABLE). Data Tables are primarily designed to join against UDM events (udm.*) rather than case objects.

To achieve your goal of grouping cases by Site based on IP ranges, here are two supported approaches you can take:

Option 1: Enrich UDM Events at Ingestion / Parser Level (Recommended for Dashboards)

Instead of looking up the Data Table at the Case level, enrich the UDM events using the Data Table during log parsing or via rule outcomes.

1. Map your %IP_RANGES Data Table against incoming IP fields in your Parsers / Unified Data Model (UDM).

2. Populate the corresponding site name into a UDM field (for example, principal.location.name or additional.fields["site"]).

3. Build your Dashboard widget directly using UDM Search, where joining with Data Tables or using pre-enriched UDM fields is fully supported:

Code snippet

$ip = target.ip
$ip in %IP_RANGES
$Site = %IP_RANGES.Site

match:
    $Site

outcome:
    $Count = count_distinct(metadata.id)

order:
    $Count desc
limit: 100

Option 2: Enrich Cases via SecOps SOAR Playbooks (If Case-Level Dashboard is Required)

If this must be driven from the SOAR / Case Management side:

1. SOAR Playbook Enrichment: Create a playbook triggered on new Case creation.

2. Data Table Lookup Action: Extract case.alerts.entities.identifier (IPs), query the Data Table or an external IP/Asset mapping KV store, and retrieve the Site value.

3. Populate Custom Field: Write the Site value directly into a Custom Case Field (e.g., case.custom_fields.site).

4. Query Custom Case Field in Dashboard: Once saved as a case field, your Case Dashboard query becomes a simple aggregation without needing a Data Table join:

Code snippet

case.alerts.entities.type = "ADDRESS"
$Site = case.custom_fields.site

match:
    $Site

outcome:
    $Count = count_distinct(case.id)

order:
    $Count desc
limit: 100

Summary

Since direct joins between case.* and Data Tables are restricted in Chronicle Search, Option 2 (SOAR Playbook Enrichment) is the most seamless way to keep this within Case Management, while Option 1 (UDM Enrichment) gives the best real-time performance for SIEM Dashboards.

Hope this helps resolve the error! Let us know which approach fits your workflow best.

Thank you ​@thineth_dasun , but again the compilation error thrown while executing the query.  

Hi Sanzz,

The compilation error is expected here because Case Search syntax does not allow variable assignments ($var = field) or joins with Data Tables. In Case queries you need to reference fields directly, without $ assignment.

Try simplifying your query like this:

Code

case.alerts.entities.type = "ADDRESS"
match:
    case.custom_fields.site
outcome:
    $Count = count_distinct(case.id)
order:
    $Count desc
limit: 100

👉 Key points:

• Remove $ip = ... and $Site = ... assignments — Case Search compiler rejects those.

• Use case.custom_fields.site (populated via playbook enrichment) directly in the match block.

• In outcome, use count_distinct(case.id) instead of case.alerts.metadata.id.

If you run a simpler query first (just case.alerts.entities.type = "ADDRESS" outcome: count(case.id)), it should compile. Then gradually add match and order blocks.

Since Case Search cannot join with %IP_RANGES, the supported path is:

• SOAR Playbook enrichment → write Site into a custom case field.

• UDM enrichment → if you want dashboard-level joins.

That way, your query compiles cleanly without hitting the join limitation.

Hope this clears up the compilation error!