Available: defineTable() v1.163.0

The defineTable() function is available from version 1.163

Executes a sub-query that generates an in-memory, ad-hoc table based on its results. The ad-hoc table can be joined with the results of the primary query using the match() function.

defineTable() is the recommended alternative to the join() function, allowing for easier query writing of complex joins. For more explanations on the benefits of using ad-hoc tables with defineTable() instead of join(), see Ad-hoc Tables vs. join()

Combined with match() and readFile() query functions, defineTable() can be used to create several types of join-like queries — see defineTable() Examples.

ParameterTypeRequiredDefault ValueDescription
queryfunctionrequired  Sub-query used to generate the ad-hoc table.
name[a]stringrequired  Name of the ad-hoc table that is generated. Used to reference the table in other functions within the primary query.
includeArray of stringsrequired  Fields to include as columns in the temporary table. If set to * all fields will be included.
viewstringoptional[b]same as primary query View in which to perform the sub-query.
startstringoptional[b]same as primary query Start of time interval of sub-query: milliseconds since UNIX epoch, or time interval using Relative Time Syntax (e.g., 2d, 24h, 10sec).
endstringoptional[b]same as primary query End of time interval of sub-query: milliseconds since UNIX epoch, or time interval using Relative Time Syntax (e.g., 2d, 24h, 10sec).

[a] The argument name name can be omitted.

[b] Optional parameters use their default value unless explicitly set.

Hide omitted argument names for this function

Show omitted argument names for this function

The function's signature combined with match():

logscale
defineTable(query={a=hello}, name="tablename", include=[col1,col2,col3])
| match(table="tablename",field=fieldname, column=col1)

For example, to match email data within the same view: 

email=bob@example.com firstname=bob lastname=thomas
loginemail=bob@example.com action=register

perform a sub-query and a primary query:

logscale
defineTable(query={email=*}, name="emails", include=[email,firstname, lastname])
| match(table="emails", field=loginemail, column=email)

The first query in the pipeline is the sub-query used for table definition. The second query in the pipeline is the primary query that uses match().

The following example query combines information about the ProcessRollUp2 and NetworkListenIP4 to find processes that have created listeners on a port.

  1. This is the full query:

    logscale
    defineTable(query={#event_simpleName=NetworkListenIP4 LocalPort<1024 LocalPort!=0}, name="network_listener", include=[ContextProcessId,LocalAddressIP4, LocalPort])
   
| #event_simpleName=ProcessRollup2         
   
| match(table="network_listener",field=TargetProcessId,column=ContextProcessId)

  2. The sub-query with defineTable() generates a result table called network_listener:

    ContextProcessId LocalAddressIP4 LocalPort
    123 172.16.254.1 1010
    456 172.19.254.1 2020
    789 190.16.254.1 3030

  3. The second item in the pipeline filters #event_simpleName field to only take the ProcessRollUp2 value.

  4. match() joins the results of the network_listener ad-hoc table with the primary query, by matching:

    • TargetProcessId field from the primary query

    • ContextProcessId column field from the ad-hoc, generated table.

Note

To ensure optimal performance when using defineTable(), follow the best practice described at Ad-hoc Tables Optimization.

For more information on the different methods of creating join queries — including ad-hoc tables with the defineTable() function — see Join Methods.

Important

When using defineTable(), be aware that ad-hoc tables are not supported in Alerts, for the reasons explained at Ad-hoc Tables in Live Queries. Use Scheduled Searches instead.

defineTable() Examples

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.

This is one event dataset:

Raw Events
username,name,orgId
user1,"John Doe",1
user2,"Jane Doe",1
user3,"Bob Smith",2

and this is the other dataset:

Raw Events
username,operation
user1,createdFile
user2,deletedFile
user3,createdFile
Step-by-Step

  1. Starting with the source repository events.

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

    Generate a temporary table called users_table that has username and name as its field columns and selects ony users from orgId=1.

  3. logscale
    | operation=createdFile

    Take only users who created a file

  4. logscale
    | match(table=users_table, field=username, strict=false)

    Join with user_table table.

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

    Specify username and name fields to select from the event.

  6. Event Result set.

Summary and Results

The result will output two events:

usernamename
user1John Doe
user3no value

where user3 has no value since there this user is not included in the in users_table table user2 (not belonging to orgId=1).

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.

This is one event dataset:

Raw Events
username,orgId
user1,1
user2,1
user3,2

and this is the other dataset:

Raw Events
username,name
user1,"John Doe"
user2,"Jane Doe"
user3,"Bob 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)

    Generate a temporary table called organization_table that has username and orgId as its field columns and takes ony users from orgId=1, from the organizations view.

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

    Generate a temporary table called users_table that has username and name as its field columns and enrich rows with orgId=1 from organization_table

  4. logscale
    | operation=createdFile

    Take only users who created a file

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

    Join with users_table table, to filter out users who aren't from orgId=1 and to enrich with the users' names

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

    Specify username and name fields to select from the event.

  7. Event Result set.

Summary and Results

The result will output one event:

usernamename
user1John Doe

where user3 has no value since there this user is not included in the in users_table table user2 (not belonging to orgId=1).

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:

Raw Events
username,name,orgId
user1,"John Doe",1
user2,"Jane Doe",1
user3,"Bob Smith",2

and the other event set:

Raw Events
username,operation
user1,createdFile
user3,createdFile
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])

    Select 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.

Perform an Inner Join Query to Combine Two Datasets

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

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

This is one event dataset:

Raw Events
username,name,orgId
user1,"John Doe",1
user2,Jane Doe",1
user3,"Bob Smith",2

and this is the other dataset:

Raw Events
username,operation
user1,createdFile
user2,deletedFile
user3,createdFile
Step-by-Step

  1. Starting with the source repository events.

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

    Generate a temporary table called users_table that has the fields username and name and filters used where the orgId field equals 1.

  3. logscale
    | orgId=1

    Select only users from the orgId where the value equals 1.

  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 the filtered events (users that created a file in organization 1) using the username field with the users_table table.

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

    Select the username and name fields to be displayed in the result set.

  7. Event Result set.

Summary and Results

The result will output one event:

usernamename
user1John Doe

Using ad-hoc table with CSV file

Query
logscale
defineTable(name="users_table",query={match(file=organizations.csv, field=username) 
query={match(file=organisations.csv, field=username)  
| orgId=1 },include=[username, name])
| operation=createdFile
| match(table=users_table, field=username)
| select([username, name])
Introduction

In this example, the defineTable() is used to extract and combine information from two different datasets, with the mapping between username and orgId stored in a CSV file. The example file organizations.csv has the following content:

CSV
username,orgId
user1,1
user2,1
user3,2

This is one event dataset:

Raw Events
username,name
user1,"John Doe"
user2,"Jane Doe"
user3,"Bob Smith"

and this is the other dataset:

Raw Events
username,operation
user1,createdFile
user2,deletedFile
user3,createdFile
Step-by-Step

  1. Starting with the source repository events.

  2. logscale
    defineTable(name="users_table",query={match(file=organizations.csv, field=username) 
query={match(file=organisations.csv, field=username)  
| orgId=1 },include=[username, name])

    Generate a temporary table called users_table that has username and name as its field columns and takes only users from orgId=1. Then use match() to enrich rows with orgId from organizations.csv file.

  3. logscale
    | operation=createdFile

    Select only users who created a file.

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

    Join the username field with the users_table table, to filter out users who aren't from orgId=1 and to enrich with the users' names.

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

    Specify username and name fields to select from the event.

  6. Event Result set.

Summary and Results

The result will output one event:

usernamename
user1John Doe