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 management permission on Query lookups.

Take into account that 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.

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 column 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 column value has different row values, the old ones will be overwritten.

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.

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 column turns green. The Type column 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 column will be matched with different results depending on the corresponding time range between the dates specified in a timestamp column 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 column dropdown menu will appear, and you must choose the timestamp-type column you want to use among the ones added into the field on the left.

Open

Time range lookup example

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

As you can see in the first picture below, Mike has been talking with colleagues in Madrid from 08:00 to 13:00; with users in New York from 13:00 to 17:00; and with users in Paris from 17:00.

After defining a time range lookup based on this data, we want to define a new column in a query that contains a user column. For every Mike value in our query, we want to include the corresponding city depending on the time range specified in our lookup.

As you can see in the second picture below, the new column shows the corresponding city according to the time in the eventdate column. For example, you can see the value New York in events sent at 16:00, since the time range lookup said that New York corresponds to the time range that goes from 13:00 to 17:00, as explained above.