Perform a Right Join Query to Combine Two Datasets

Query

logscale
defineTable(name="users",query={orgId=1},include=[username, name])
| defineTable(name="operations",query={*},include=[username, operation])
| readFile(users)
| match(operations, field=username, strict=false)
| select([username, operation])

Introduction

In this example, the defineTable() function is used as a right 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:

usernamenameorgId
user1John Doe1
user2Jane Doe1
user3Bob Smith2

and the other event set:

usernameoperation
user1createdFile
user3createdFile

Step-by-Step

  1. Starting with the source repository events.

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

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

  3. logscale
    | defineTable(name="operations",query={*},include=[username, operation])

    Defines a new ad-hoc table that uses all the fields (username and operation) in a table named operations.

  4. logscale
    | readFile(users)

    Reads the users ad-hoc table as events using readFile().

  5. logscale
    | match(operations, field=username, strict=false)

    Matches the events that have a matching operation from the operations table with the users table using the username as the common field. Events are not filtered if the events do not match, (implying a right join), by using strict=false

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

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

  7. Event Result set.

Summary and Results

The result will output two events:

usernameoperation
user1createdFile
user2no value

Note that in the event set all operations have been included even when there is no match between the username field, resulting in the no value for user2. If strict=true had been used to the match() function, then the event for user2 would not have been outputted.