Lookup (lu)

Description

This operation returns the values of a given lookup field upon successful key matching.

How does it work in the search window?

Select Create field in the search window toolbar, then select the Lookups category, and choose the Lookup (lu) operation from the dropdown (more info here). You need to specify three arguments:

Argument	Description	Data type
Lookup name ^mandatory	Choose the lookup you want to use to enrich your table.	string
Lookup field ^mandatory	Choose the lookup field you want to use to enrich your table.	string
Key ^mandatory	Choose the table field you want to use to find matches with the lookup key field.	same as lookup key field
Existing lookups required To perform these operations, it is necessary to have existing lookups ready for use (visit this article to get help uploading lookups and this article to get help creating query lookups).

Once you specify the adequate arguments and click the Create field button, the new field is added to your table.

When the values of the lookup key field match the values of the Key argument, the new field displays the corresponding value from the Lookup field argument. If there is no match, it displays null.

The data type of the values in the new column will be the same as the original field brought from the lookup.

How does it work in LINQ?

Use the create field operator select... as new_field and add the operation syntax to create the new column. This is the syntax for the Lookup (lu) operation:

lu("Lookup_name", "Lookup_field", Key_field)

The complete syntax with both the create field operator and the operation syntax is:

select lu("Lookup_name", "Lookup_field", Key_field) as new_field

Existing lookups required

To perform these operations, it is necessary to have existing lookups ready for use (visit this article to get help uploading lookups and this article to get help creating query lookups).

Syntax considerations

"LOOKUP_NAME" → This must be the name of lookup that contains the data you want to use to enrich your data.
"LOOKUP_FIELD" → This must be the lookup field you want to use to enrich your data, which must not be the same as the lookup key field.
KEY_FIELD → This must be the table field that will be used to correlate with the lookup key. The name can be different than the lookup key field as long as the data type coincide and the values it contains are potential matches (username-user). The absence of matches will return null and a different data type will return an error when running the query .

Example

We want to enrich the siem.logtrust.web.activity table with information about the working model in each city. If we want to work more comfortably, we can isolate the data we’re interested in by using filter and grouping operations. Then, we will use the Lookup (lu) operation with this upload lookup that contains info about company offices.

These are the arguments needed when using the interface :

Lookup name: Company_offices
Lookup field: Office_type
Key: city

This is the syntax needed when using LINQ free-text query:

from siem.logtrust.web.activity
where isnotnull(city)
where not isempty(city)
where result = "OK"
group every 1h by city, result, region
select lu("Company_offices", "Office_type", city) as OfficeType

The values in the Office_type lookup field will be brought into our table when the values in the city field and those in the lookup key field match. When they do not match, null will be returned.