Lookups
- Juan Tomás Alonso Nieto (Deactivated)
- Borja Moro Moreno
About lookup tables
Lookup tables are used to enrich the information in raw data tables by correlating values in the 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 containing IP addresses during a query.Â
Lookup values are added to the virtual data table at query time, as new fields. The original data tables are never modified.Â
A key value must be selected. This is the field in the lookup table that has values that correspond to values in the data table. In our example, the key field will be the field containing the IP addresses, which exists both in the lookup table and original data table.
Lookup tables can be edited to add, change or delete information.
Types of lookup tables
In Devo, lookup tables are grouped into four different categories and can be created by uploading a .csv file or using query data.
Source | Lookup table type | Description |
|---|---|---|
External file | Upload | External lookup tables uploaded as a .csv file. External sources may include lists of values, geo-localizations, or extracts from a database. Learn here how to upload external data as a lookup table. |
Query data | Static query | These lookup tables are created using query data from a specified period of time. See Create a lookup table from a query to learn more. |
Dynamic query | These lookup tables are fed with new data events every 5 minutes. Rows with duplicated key values will be overwritten. See Create a lookup table from a query to learn more. | |
Time range lookup | Both static and dynamic query lookups can be created as a time range lookup. To create these lookups, you must choose a timestamp type field that will dictate the lookup values to be inserted. That is to say, the same entry of your key field must be matched with different results depending on the specified date. Learn more here. |
Use cases
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
Lookup tables can be used categorize events according to their IPs.
Associate an IP to known threats.
Group IP by types of devices: servers, portable computers, printers.
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.
Working with lookups
Lookup status codes
Due to the complex nature and technical implications of lookups, working with them involves encountering a variety of situations. To help you understand them and deal with them, we have compiled some of the most common situations in the table below:
Status code | Description | Retrievable |
|---|---|---|
200 | The lookup was created and deployed correctly. | - |
999 | Multiple errors have occurred, listed for reference. | Depend on each error |
1098 | Error signing the query to delete a lookup. | Manually |
1099 | Error executing the query to delete a lookup. | Manually |
1100 | Connection problem with the query component. | Manually |
1101 | Query component error caused by an external component, such as client’s code. | Manually |
1102 | Query component internal error, usually due to a programming defect. | Manually |
1103 | Query component error related to system-wide resources, such as RAM or file handlers. | Manually |
1104 | Query component error related to resource operation that requires additional security checks, such as signed-queries. | Manually |
1105 | Query component error caused by the cancellation of the query by an external entity. | Manually |
1106 | Query component error indicating wrong format within the input resources. | No |
1107 | Query component error indicating the absence of the required resource to execute the query. User may need support to add these resources. | Manually |
1108 | Query component error when a query limit is reached (e.g. max result rows, time, RAM, etc.). | No |
1109 | Query component error indicating wrong query syntax, possibly a missing or misspelled token. | No |
1110 | Query component error indicating unsupported feature. It is syntactically correct but it is not implemented. | No |
1199 | Query component error referring to a general catch-all error. | No |
1200 | Error that indicates lookup creation failure due to the presence of at least one null value in the key field. If the index of the row is inferable, it is included in the row attribute, otherwise, the row attribute is null. | No |
1201 | Error that indicates lookup creation failure due to the presence of at least one unsupported field type. | No |
1202 | Error that indicates lookup creation failure due to the presence of at least one cell value that is incompatible with the field data type. If the problematic value is inferable, it is included in the value attribute. | No |
1203 | Error that indicates lookup creation failure due to the use of an invalid CSV file. | No |
1204 | Error that indicates lookup creation failure due to input incompatibility. An example is updating an existing lookup with a new version that does not contain the same fields. | No |
1205 | Error that indicates lookup creation failure due to an excessive number of rows. | No |
1206 | Error that indicates lookup creation failure due to a timeout. | No |
1207 | Error that indicates lookup creation failure due to the excessive size of the input (in bytes). | No |
1208 | Error that indicates lookup creation failure due to the inability of the lookup manager to find the field selected as key field. | No |
1209 | Error that indicates lookup creation failure due to the inability of the lookup manager to find at least one of the fields explicitly projected by the user. | No |
1210 | Error that indicates lookup creation failure due to a field projected by the user twice. | No |
1300 | Error that indicates lookup creation failure due to the misconfiguration of the repository containing the CSV file to be used by the lookup manager. | Manually |
1301 | Error that indicates lookup creation failure due to unauthorized access to the S3 bucket with the required objects. | Manually |
1302 | Error that indicates lookup creation failure due to the inability to find the required objects in the S3 bucket. | No |
1400 | Synchronizing component request timed out without response. | Manually |
1401 | Error sending a request to the synchronizing component. | Manually |
1402 | Error handling a response from the synchronizing component. | No |
1403 | Connection with synchronizing component was terminated. | Manually |
1404 | The response from the synchronizing component has an unknown format or type. | No |
1405 | Unexpected error from the synchronizing component. | Manually |
1406 | Error in the notification received from the synchronizing component. Lookup deployment will be reattempted automatically. | Automatically |
1407 | Error in the notification received from the synchronizing component after several attempts. Lookup deployment have been attempted several times without success and will be halted. User may need support. | Manually |
1408 | Synchronizing component library used to publish has a programming defect. | No |
1409 | Synchronizing component library used to publish has an implementation error. | No |
1500 | Rsync deployment failed in at least one data node and manual sync is needed. | Manually |
1999 | Unknown error. | No |