Create a lookup table from a query

What permissions do I need?

To be able to create lookup tables from queries, you need to have the Manage version of the Query lookups permission. Having the User resources permission is equivalent to having the Manage version of the Query lookups permission.

This permission is part of a hierarchy with the Lookups permission in a higher level, 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).

Open

Query lookup types

Users with the required permissions can use the content of a query to create a lookup directly from the search window. You can create two different types of lookup tables using query data: static query lookups and dynamic query lookups.

Static query lookup

These lookup tables are created using query data from a specific period of time. You cannot create this type of lookup if you enable real-time in your query –you must always choose a period of time using the time range selector in the query toolbar. These lookups work exactly the same as uploaded lookup tables, since both contain a fixed set of data that you can use to enrich a raw data table.

The lookup will include the last events in the specified time range containing all the unique values in the key field selected.

Dynamic query lookup

These lookup tables are also created with query data, but they differ from static query lookups in that they are constantly populated with new sets of data. By default, they are updated every 5 minutes since the creation date, but this time might increase depending on the load of the system. If a new event arrives where the key field value has different row values, the old ones will be overwritten. Be aware that events remain unaffected if there are not any updates.

If your query groups data, the dynamic query lookup will be updated according to the grouping time indicated. For example, if your query groups data every 1 hour, the lookup will be populated with a new set of data every 1 hour. Note that if the grouping period is less than 5 minutes, the lookup will be updated every 5 minutes.

You can create a dynamic query lookup with the real-time option activated, but you can also define a specific time range. If you do this, the first data set until it is updated will be the data in the time frame indicated. For example, if you set Last week as the time range, the data in the last seven days will be used to populate the lookup, and then will be populated with new data following the rules explained above.

Query lookup size limits

Before start creating your lookups, there are some limitations you must take into account.

• Creation duration: if it exceeds 1 hour, a timeout error appears and the creation process is canceled. Whether it reaches the time limit or not is closely related to the other two restrictions described below.

• Lookup size: in dynamic lookups, each iteration of new data must not exceed 8 GB downloaded from the server, otherwise, the lookup is aborted. Note that this limit does not apply to the total size of the dynamic lookup, which can exceed 8 GB.

• Number of rows: in dynamic lookups, each iteration of new data must not exceed a certain number of rows downloaded from the server, which may vary depending on your environment. The number of rows downloaded is determined by the number of changes of a single key in each petition, which can be up to a maximum of 33,554,432 by default.

Create a query lookup

Query lookup tables are shown along with uploaded lookup tables, in Data search → Lookup management. The query lookup will be ready when the indicator in the Status field turns green. The Type field shows Upload, Static Query or Dynamic Query to indicate the lookup table type. Learn more in Manage and edit lookup tables.

Time range lookups

Both static and dynamic query lookups can be created as a time range lookup. When using this option, the same entry of your key field will be matched with different results depending on the corresponding time range between the dates specified in a timestamp field of your query. This way, you can match the same value in your query with different values in your lookup, which may come in handy in different situations.

To create a time range lookup, you must check the Time range lookup checkbox in the creation process of the query lookup, as explained above. Once you do this, the Time range field dropdown menu will appear, and you must choose the timestamp-type field you want to use among the ones added into the field on the left.

Open

Time range lookup example

For example, imagine you have a query that shows the cities to which a user (Mike) has been calling in different time ranges.

As you can see in the picture below, Mike talked to colleagues in different cities at different times. Let’s say we want to consider the time from one call to the next as a time slot assigned to that specific city. For that purpose, we will define a time range lookup as described above.

After defining a time range lookup based on this data, we want to define a new field in another query to match the user with the city that corresponds to them according to the time slot mentioned before and recorded in the lookup.

As you can see in the picture below, the new field shows the corresponding city according to the time in the eventdate field. For example, you can see the value New York in events sent at 16:00 because the time range lookup matches New York to any event received from 13:00 to 17:00.