Create a lookup table from a query
Admin users 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 lookup
These lookup tables are created using 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. They are updated every 5 minutes since the creation date. If a new event arrives where the key column value has different row values, the old ones will be overwritten.
Note that this feature is only enabled in certain domains by default. If you need to use it and is not enabled in your domain, contact the Devo support team.
To define a lookup table using the content of a query, follow these steps:
- Go to Data Search and access the required table.
- Choose the required period of time using the time range selector and click Apply Interval.
- In the data table toolbar, click the gear icon and then select Table Operations → New Query Lookup.
The New Query Lookup window appears. Enter the required information.
Name Give a name to the lookup table. Data columns Drag to this field the data columns you want to add to the lookup table. Mode Choose either Static query or Dynamic query. Key column Select one of the columns added as key column. Learn more about this here.
Click Save settings. You will receive a notification when the file is uploaded.
Query lookup tables are stored 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.