Perform a Nested Join Query to Combine Two Datasets and Two Tables

Query

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

Introduction

Similar to the inner join example, defineTable() first creates a separate table for organizations belonging to a different view, which is then matched against a users' table as a nested-like join.

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:

usernameorgId
user11
user21
user32

and the other event set:

usernamename
user1John Doe
user2Jane Doe
user3Bob Smith

Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    defineTable(name="organization_table",query={orgId=1},include=[username, orgId],view=organizations)

    Generates an ad-hoc table named organization_table that has the fields username and orgId and includes users where orgId field equals 1 from the organizations view.

  3. logscale
    | defineTable(name="users_table",query={match(table=organization_table, field=username)},include=[username, name])

    Generates an ad-hoc table named users_table that has the fields username and name and enriches rows with orgId=1 from organization_table

  4. logscale
    | operation=createdFile

    Filters on the field operation for users who performed the action of creating a file by looking for the value createdFile.

  5. logscale
    | match(table=users_table, field=username)

    Joins with users_table table, to filter out users who are not from orgId=1 and to enrich with the users' names.

  6. logscale
    | select([username, name])

    Selects the username and name fields to be displayed from the event set.

  7. Event Result set.

Summary and Results

The result will output one event:

usernamename
user1John Doe