Perform a Left Join Query to Combine Two Datasets

Query

logscale

defineTable(name="users_table",query={orgId=1},include=[username, name])
| operation=createdFile
| match(table=users_table, field=username, strict=false)
| select([username, name])

Introduction

In this example, the defineTable() function is used as a left join query to extract and combine information from two different datasets.

The event set for the query is in one repository, but the event set for each query is shown separately to identify the two sets of information. The first event set is:

username	name	orgId
user1	John Doe	1
user2	Jane Doe	1
user3	Bob Smith	2

and the other event set:

username	operation
user1	createdFile
user2	deletedFile
user3	createdFile

Step-by-Step

Starting with the source repository events.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1[/Filter/] 2[/Filter/] 3[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 0 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
defineTable(name="users_table",query={orgId=1},include=[username, name])
```
Generates an ad-hoc table named users_table that has the fields username and name and includes users where orgId field equals 1.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1[/Filter/] 2[/Filter/] 3[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 1 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| operation=createdFile
```
Filters on the field operation for users who performed the action of creating a file by looking for the value createdFile.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1[/Filter/] 2[/Filter/] 3[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 2 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| match(table=users_table, field=username, strict=false)
```
Joins with users_table table.
flowchart LR; %%{init: {"flowchart": {"defaultRenderer": "elk"}} }%% repo{{Events}} 0@{ shape: win-pane, label: "Table" } 1[/Filter/] 2[/Filter/] 3[/Filter/] result{{Result Set}} repo --> 0 0 --> 1 1 --> 2 2 --> 3 3 --> result style 3 fill:#ff0000,stroke-width:4px,stroke:#000;
logscale
```
| select([username, name])
```
Selects the username and name fields to be displayed from the event set.
Event Result set.