What permissions do I need?
To be able to view and use lookup operations when working in the search window, you need to have at least the View version of the Lookups permissions for upload lookups or the Query lookups permission for query lookups. Having the User resources permission is equivalent to having the Manage version of these two permissions.
These two permissions are hierarchy-based, meaning you need to have Lookups assigned in order to be able to have Query lookups. Notice at least the View version of the Finders permission is required to enable the Lookup permission (know more about permissions here).
Additionally, if you’re not the owner of the lookup, you need to receive at least View access to the lookup in question via sharing, otherwise, you will not be able to see it. Check Share lookups to know more.
Using lookup operations in the search window
You can run a search and correlate its content with values from a lookup table. If any value in the key field of the lookup table is also present in your query, the corresponding values of the selected lookup field will be added to a new field.
Following the previous example shown in the Upload a lookup table article, suppose you want to include two lookup fields in the demo.ecommerce.data table showing the street address and printer brand corresponding to the IPs in the data table.
Using lookup operations in LINQ
You can also add lookup values by using the corresponding LINQ syntax to correlate query content with values from a lookup table. If any value in the key field of the lookup table is also present in your query, the corresponding values of the specified lookup field will be added to a new field.
To do this using LINQ, you can either use the Free Text Query to open the search from the beginning or open the Query code editor if you have it already open. This is the syntax you need to use:
LOOKUPS: select `lu/lookupName/lookupfield`(tableField) as newfieldName
TIME RANGE LOOKUPS: select `lu/lookupName/lookupfield`(tableField, tableTimestampField) as newfieldName
Syntax special considerations
lookupfield→ this is the lookup field you want to use to enrich your data, and must not be one that corresponds to the lookup key field. When using the search window interface, this is the name of the lookup operation.tableField→ this must be the field in your data table that corresponds to the lookup key field, otherwise there will be no match to correlate your data and you will get null for all events.One-field lookups → If your lookup has only the key field because you are interested only in the correlation and not the enrichment, you need to use the syntax to add all values in a row (see the bottom section).
As an alternative, you can use lookups with two fields, the key field and a field with default values such as true, which you can use with the standard syntax to enrich your data with values that indicate only correlation.
This is the required LINQ query to add the same lookup as in the example shown above using the search window interface:
from demo.ecommerce.dataselect `lu/IP_list/StreetAddress`(clientIpAddress) as `IP street address`
Lookups in Activeboards and APIs
The syntax to use lookup operations in Activeboards and APIs is different. Visit the following articles to know more: LINQ syntax differences: Activeboards vs search window, LINQ syntax differences: Devo APIs vs search window.
Lookup operations result
When using a lookup operation, both the key field specified in the lookup when created and the field specified as an argument in the operation are searched for matching values. When they are found, the corresponding value of the lookup field selected as the operation will be added to the new field.
In the example above, both fields are created with the lookup values, showing the street and printer brand corresponding to the IPs of the clientIpAddress field that are also in the key field of the lookup table. These fields will show null for those IPs that are not included in the lookup table.
Add all the values in a row
When a match is found between the content of your query and a value in your lookup key field, you can select the Whole entry of... operation to add all the values in that lookup row as a JSON object in a new field.
This can be useful to check the values the lookup contains, including the values of the key field, or when using a lookup with only the key field, seeking only the correlation and not the enrichment.