About lookup tables
Lookup tables are used to correlate values in a data table with corresponding values in the lookup table. For example, a lookup table containing IP addresses with their geographical addresses may be used to add geographical addresses to a data table during a query. Therefore, lookup tables are used to enrich the information in raw data tables.
- Lookup tables must be uploaded in .csv format.
- Lookup values are added to the virtual data table at query time. The original data tables are never modified.
- A key value must be selected. This is the column in the data table that has values that correspond to values in the lookup table.
- Once uploaded to Devo, a lookup table can be edited to add or change information.
The sources of lookup tables may be external or internal:
External sources include lists of values, geo-localizations, or extracts from a database. Learn below how to upload external data as a lookup table.
Internal sources based on loaded data tables whose data is used to create a lookup table. For example, robot activity logs contain data that measure the precision of the robot. The values in this table could be used in a lookup table that associates a quality rating based on measured values. Check the dynamic lookups article to learn more about this.
Here are some common use cases that demonstrate how lookup tables can be used.
Converting codes into names
- Convert an IP address into a machine name.
- Convert an IP address into a geo-localization.
Add values to classify or filter events
- Associate an IP to known threats.
- Group IP by types of devices: servers, portable computers, printers.
For example, lookup tables can be used to enrich a data table containing information about a manufacturing company's robots.
- They can associate robot IDs to factory locations.
- They can categorize types of robots but their functions.
- They can assign rankings to robots based on measurements in the data table.
Upload a lookup table
Suppose you have the following CSV file containing printers' information and their corresponding IPs and addresses:
|22.214.171.124,Mainstreet 15,A,5,23,HP,Laserjet 11|
If you want to upload the file as a lookup:
- Go to Data Search → Lookup Management and click New Lookup.
- Enter a name for the lookup (in this case, IP_list), then click Choose file and select the CSV file. Check the Contains headers box.
- Choose a key column selecting the Is key option. In this example, the key will be the IP column.
- Click Upload. You will receive a notification when the file is uploaded.
Once uploaded, you can find the lookup in:
- Data Search → Lookup Management, where you can manage and edit your lookup tables.
- Data Search → Finder if you choose my → lookuplist, where you can open lookups as data tables and apply operations.
Add lookup values to a data table
You can run a search and correlate its content with values from an uploaded lookup. If any value in the lookup key column is also present in the data table, the values in those lookup rows can be added to the data table.
Following the previous example, suppose you want to include two columns in the demo.ecommerce.demo table showing the street address and printer brand corresponding to the IPs in the data table. To do it:
- Go to Data Search and open the data.ecommerce.demo table.
- Click Create column in the data table toolbar.
- Select custom and look for the name of the lookup (IP_list). Select the StreetAddress column. Give the column a meaningful name (IP street address).
- Add the clientIpAddress column as an argument and click Create column.
- Repeat the process to add the printer brand column. In this case, add the Brand column of the lookup and choose again the clientIpAddress data table column as argument (you can name it IP Brand).
Both columns are created, showing the street and printer brand corresponding to the IPs of the clientIpAddress column that are also in the lookup key column. These columns will show null for those IPs that are not included in the lookup.
Manage lookup tables
You can manage your lookup tables in Data Search → Lookup Management. Click the ellipsis icon to the right of any lookup table row to access the following options:
|More info||This offers a quick view of the table's column names, value types, and primary key.|
|Download CSV||Download the lookup table as a CSV file.|
Edit the content of the selected lookup table. You can add new rows or modify the already existing data. Click Save Changes once you finish. If you edit a dynamic lookup, you will be taken to the query it was created from. Note that this option is only available for lookup tables up to 100MB.
|Load new lookup version||
Upload an updated version of the selected lookup table. Click Choose file, select the file including the updated version and click Upload.
This opens the Restrictions to tables screen. Select the required lookup table in the left area and use the toggle buttons to enable or disable them in the different data tables.
|Delete||Delete the lookup table.|
Have we answered your question?
If not, please contact our technical support team via email by clicking the button below.CONTACT US