Document toolboxDocument toolbox

Lookups

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

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

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