nano SIEM
Search Commands

join

Supported in:Self-hosted

Merge fields from a subsearch onto your results by matching one or more key fields.

Use join to add fields from a subsearch to your current results. nano merges the rows where your chosen key fields match.

Syntax

... | join [type=inner|left|outer] [maxout=<n>] [max=<n>] [overwrite=<bool>] <field>[, <field> ...] [maxout=<n>] [max=<n>] [ <subsearch> ]
ArgumentDefaultDescription
fieldrequiredOne or more key fields. The field must have the same name on both sides; use rename inside the subsearch when it doesn't.
typeinnerinner keeps only rows with a match. left keeps every main row and fills subsearch fields where a match exists. outer keeps rows from both sides.
max1Subsearch rows joined per key value.
maxout10000Total rows the subsearch may return. The maximum is 100000.
overwritetrueWhether subsearch fields replace main-search fields of the same name. Set it before the key fields.
subsearchrequiredA search in square brackets, for example [search class_uid=1007 | stats count by actor.user.name].

How it works

  • You can set max and maxout together. Before the key fields, type, maxout, max, and overwrite can appear in any order. After the key fields, you can set maxout and max in either order. A later value wins.
  • The subsearch runs over the main search's time range, is capped at maxout rows, and is joined on the key fields. earliest= and latest= inside the brackets are rejected. Use where filters on time to narrow the subsearch within the main range.
  • With the default max=1, each key value matches at most one subsearch row. Aggregate in the subsearch (stats ... by <key>) so that one row per key carries everything you need.
  • In a left join, rows with no match get null for the subsearch's fields. Filter unmatched rows with where isnull(<field>).
  • If the subsearch returns more than maxout rows, the extra rows are dropped before the join, so some keys can go unmatched. Filter or aggregate the subsearch so it stays under the cap.

A subsearch can start with dataset=spans or dataset=metrics to join your logs against OpenTelemetry traces or metrics.

Examples

Failed and successful logon counts per user, side by side:

class_uid=3002 status_id=2 | stats count as failures by user.name | join type=left user.name [search class_uid=3002 status_id=1 | stats count as successes by user.name]

Users with failed logons, with how many distinct processes they ran. Process events name the user actor.user.name, so rename it to match the key:

class_uid=3002 status_id=2 | stats count as failures by user.name | join user.name [search class_uid=1007 | stats dc(process.name) as process_count by actor.user.name | rename actor.user.name as user.name]

Hosts that made outbound RDP connections, with their failed-logon count:

class_uid=4001 dst_endpoint.port=3389 | stats count as rdp_out by device.hostname | join type=left device.hostname [search class_uid=3002 status_id=2 | stats count as failed_logons by device.hostname]

Up to three process names per user who failed to log on (one joined row per process):

class_uid=3002 status_id=2 | stats count by user.name | join max=3 user.name [search class_uid=1007 | stats count as runs by actor.user.name, process.name | rename actor.user.name as user.name]

Raise the subsearch cap when the subsearch has many keys:

class_uid=4001 | stats count by dst_endpoint.ip | join type=left maxout=50000 dst_endpoint.ip [search class_uid=4001 | stats dc(device.hostname) as internal_hosts by dst_endpoint.ip]

Filter instead of merge

When you only need the main-search rows whose key appears in another search, use an IN [ ] subsearch with return. It filters without merging columns and is cheaper than join:

class_uid=1007 actor.user.name IN [search class_uid=3002 status_id=2 | return 1000 user.name] | stats count by actor.user.name, process.name

An IN [ ] subsearch may contain only a search and an optional return. Put aggregations in a join instead. See return.

What's next

  • append: add subsearch rows below your results without matching
  • lookup: enrich from a lookup table
  • return: feed values into an IN [ ] filter
  • stats: aggregate a subsearch to one row per key

On this page