Available: defineTable() v1.163.0

The defineTable() function is available from version 1.163

Executes a subquery 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  Subquery 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 subquery.
startstringoptional[b]same as primary query Start of time interval of subquery: milliseconds since UNIX epoch, or time interval using Relative Time Syntax (for example, 2d, 24h, 10sec).
endstringoptional[b]same as primary query End of time interval of subquery: milliseconds since UNIX epoch, or time interval using Relative Time Syntax (for example, 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 subquery 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 subquery 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 subquery with defineTable() generates a result table named 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.

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
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])

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

  3. logscale
    | operation=createdFile

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

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

    Joins with users_table table.

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

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

  6. Event Result set.

Summary and Results

The result will output two events:

usernamename
user1John Doe
user3no value

where user3 has no value since this user is not included in the 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.

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,orgId
user1,1
user2,1
user3,2

and the other event set:

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)

    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

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])

    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.

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.

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
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])

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

  3. logscale
    | orgId=1

    Selects 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])

    Selects 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)  
| 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

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
user1,"John Doe"
user2,"Jane Doe"
user3,"Bob Smith"

and the other event set:

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)  
| 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. Then uses match() to enrich rows with orgId from organizations.csv file.

  3. logscale
    | operation=createdFile

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

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

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

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

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

  6. Event Result set.

Summary and Results

The result will output one event:

usernamename
user1John Doe