> For the complete documentation index, see [llms.txt](https://docs.panther.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://docs.panther.com/pantherflow/operators/join.md). # Join Operator ## Overview Enrich data with `join`. ```kusto | join kind= =() on ``` When a `join` is performed, rows from the pipe are matched up with rows from the `` based on the ``. The resulting data contains all of the original fields from the pipe, with the fields from the `` added to the `` field. The rows returned are controlled with ``. ### `` The `target_query` is the data that will be joined. It can be any valid query, such as simply a table name `(some_table)` or a more complex statement `(some_table | extend id = a + b)`. ### `` The `` is an expression that is used to control which rows in the pipe are joined to rows in the ``. It is required for every join `` except `cross` joins. Joins have a `$left` side (the pipe) and a `$right` side (the ``). For example, a condition of `$left.id == $right.id` will match rows that contain the same value for `id`. ### `` Control which rows are emitted with `kind`.
kind valueDescription
innerReturn only rows that match on condition.
leftouterReturn every row from the pipe enriched with the fields from the target_query, rows with no match have null for the columns from target_query.
rightouterReturn every row from the target_query enriched with the fields from the pipe, rows with no match have null for the columns from the pipe.
fullouterReturn rows from both the pipe and the target_query, even those with no match.
crossReturn every row in the pipe combined with every row from the target_query .
## Examples ### Join on IP addresses Let's say our pipe contains IP addresses in the `sourceIP` field and we want to enrich it with data from a table named `ip_location` containing IP addresses in a field named `ip`. We can use a `` of `$left.sourceIp == $right.ip` to match rows based on IP addresses and `kind=leftouter` to return all rows from the pipe—even those that might be missing from `ip_location`. ```kusto | join kind=leftouter ip=(ip_location) on $left.sourceIP == $right.ip ``` ### Join with `tor_exit_nodes` table This example uses an `inner` join to find `aws_alb` logs that have a `clientIp` that is listed in the Panther-managed [`tor_exit_node` Enrichment Provider](/enrichment/tor-exit-nodes.md) table. ```kusto aws_alb | join kind=inner tor=(panther_lookups.public.tor_exit_nodes) on $left.clientIp == $right.ip | limit 10 ``` ### Join with `ipinfo_location_datalake` table using UDFs Here is a more complex example, in which we'll enrich ALB logs with the Panther-managed [`ipinfo_location_datalake` Enrichment Provider](/enrichment/ipinfo.md) table. Note that this query uses the [`snowflake.func`](https://docs.panther.com/pantherflow/operators/pages/Mz0eDn0w5rGDFC4wqoGp#snowflake.func) function to call the SQL user-defined functions (UDFs) `panther_lookups.public.ipinfo_to_join_key()` and `panther_lookups.public.ipinfo_to_int()`, which take an IP address as an argument. Learn more about these UDFs on [IPinfo](/enrichment/ipinfo.md#using-a-joinkey). ```kusto aws_alb | where p_event_time > time.ago(1d) | join kind=leftouter ip=(panther_lookups.public.ipinfo_location_datalake) on snowflake.func('panther_lookups.public.ipinfo_to_join_key', $right.joinKey) == snowflake.func('panther_lookups.public.ipinfo_to_join_key', $left.clientIp) and snowflake.func('panther_lookups.public.ipinfo_to_int', $left.clientIp) between snowflake.func('panther_lookups.public.ipinfo_to_int', $right.startIP) .. snowflake.func('panther_lookups.public.ipinfo_to_int', $right.endIP) | project p_event_time, clientIp, city=ip.city, country=ip.country | limit 10 ``` ### Join using `datatable` It's possible to inject sample data into a `join` using the [`datatable`](/pantherflow/operators/datatable.md) operator: ```kusto aws_alb | join kind=inner system_info=(datatable [{"ip":"192.168.1.1", "hostname":"fluffy"}, {"ip":"192.168.1.2", "hostname":"squishy"}]) on $left.clientIp == $right.ip | limit 10 ``` --- # Agent Instructions This documentation is published with GitBook. GitBook is the documentation platform designed so that both humans and AI agents can read, navigate, and reason over technical content effectively. Learn more at gitbook.com. ## Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter, and the optional `goal` query parameter: ``` GET https://docs.panther.com/pantherflow/operators/join.md?ask=&goal= ``` `ask` is the immediate question: it should be specific, self-contained, and written in natural language. `goal` is optional and describes the broader end goal you are ultimately trying to accomplish on behalf of the user. GitBook uses it to tailor the answer towards what is most useful for that goal. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.