Lookup Files

Security Requirements and Controls

  • Change files permission

Lookup files are used to add additional context to data, enabling you to attach or replace fields from events recorded in a repository when searched. Lookup files can also be used to filter data by calling the lookup file in a query function.

To add a lookup file, create or import a CSV (comma-separated value) or JSON file and upload it to the repository or view.

The files can be used together with query functions to provide lookups and matching using the match() function.

The feature also works with the readFile() function for reading a file which is used as data input for your query.

Once uploaded, files are synchronized across all the nodes within the cluster. Depending on the size of the file, and what queries may be being executed, there may be a delay before the updated file is available to queries.

The following operations are available:

For information on how Lookup files interact with the rest of the system, see Lookup files operations.

Supported file types and formats

LogScale supports two different file formats for uploaded lookup files: JSON and CSV.

  CSV Files JSON Files
Viewable within LogScale UI Yes No
Editable within LogScale UI Yes No
File Size Limit

Variable default: 2,147,483,647 bytes (2048 MB)

(MaxCsvFileUploadSizeBytes)

Variable default: 2,147,483,647 bytes (2048 MB)

(MaxJsonFileUploadSizeBytes)

Each file format has some specific rules that are described in the section for each file format type. For both formats, the following apply:

  • Individual keys and values should be quoted, even as a number.

Important

Nested objects, that is an object within the returned object, are not supported. For example:

Invalid Example for Demonstration - DO NOT USE
json
{
  "1": { "name": "chr", "roles": { "user" : true }},
  "2": { "name": "krab" },
  "4": { "name": "pmm" },
  "7": { "name": "mgr" }
}

would return only the simple field, name when used with match(); the remainder of the embedded object would be not be returned or included in the events. LogScale does not reject files in this format.

Lookup files using CSV format

When using CSV for lookup files, the following rules apply:

  • Individual fields should be separated by a comma (,)

  • Whitespace is always included in the imported fields, the input takes the literal contents split by the comma character.

  • Fields can optionally be quoted by double quotes, for example to include commas in the imported values.

  • The first line of the CSV is interpreted as the column header and can be used as the field name when looking up values with functions like match().

For example, the CSV file:

csv
number,code,description
17,udp,UDP
80,http,HTTP Service
ip,"Internet Protocol, pseudo protocol"

Would be interpreted as:

numbercodedescription
17udpUDP
80httpHTTP Service
ipInternet Protocol, pseudo protocol 

CSV files can be viewed within the Lookup files interface to confirm how the information has been interpreted.

Lookup files using JSON format

When using JSON files, two different formats are supported: object-based and array-based.

JSON must be formatted in strict notation format. This requires no trailing commas (where there is no additional value),

Important

Once uploaded, JSON files cannot be viewed or updated. They can be exported to confirm the file format.

  • Object-based

    In the object-based format, format the JSON as a hash or associative array, with a single key and corresponding object. For example:

    json
    {
  "1": { "name": "chr" },
  "2": { "name": "krab" },
  "4": { "name": "pmm" },
  "7": { "name": "mgr" }
}

    When performing a lookup, match() will return the object (as an event with multiple fields), based on the matching key.

  • Array-based

    In the array-based format, format the JSON as an array of objects. In this model, the keys for each individual object become fields that can be matched when performing a lookup. For example, in the file:

    json
    [
  { "userid": "1", "name": "chr" },
  { "userid": "2", "name": "krab" },
  { "userid": "4", "name": "pmm" },
  { "userid": "7", "name": "mgr" }
]

    The userid and name fields in the JSON object can be used to lookup and return other key/value pairs as event fields/values. For example, the fragment:

    logscale Syntax
    ...
| match(file="long.json",field=codename,column="name")

    would return the userid field for objects within the lookup file array.

Create a lookup file

The methods to create lookup files are:

  • Create the lookup file in the Files interface (CSV only)

  • Create the lookup file externally and upload it to LogScale (CSV or JSON)

  • Create the lookup file through a trigger action (CSV only)

Create a lookup file in the Files menu

You can create a new lookup file from the LogScale user interface, and then populate the content manually or from a package.

  1. In the Resources menu, click Files → + New File.

  2. Specify a name for the file and then select either + Empty File to create an empty file to populate or From Package to use a template from a previously installed package.

    Dialog showing options available when creating a new lookup file. You can choose between tabs for empty file, import file for lookup files made in other tools, or from package if importing a lookup file from a package.

    Figure 32. Options when creating a new lookup file


  3. Click Create file.

  4. If you've created an empty file, click + to add rows and columns.

    Create New CSV File

    Figure 33. Create New CSV File


  5. Click Save to save the changes.

If you have many changes to make, editing a data table through the Files interface page can be tedious; click Export file and then save the file to a location to edit the it in a spreadsheet program or a simple text editor.

Note

Files larger than 100 MB cannot be viewed in the UI. Files with more than 1000 lines cannot be edited.

File Tab in Search View

Figure 34. File Tab in Search View


Upload lookup files

If you created or edited a lookup file in an external system, you can upload it to LogScale.

  1. In the Resources menu, click Files → + New file → Import files.

  2. Drag and drop your file or browse for the file to upload.

    You can upload a CSV file containing text like what you see below, which is essentially a lookup table that you can use for labels or value lookups.

    csv
    userid,ip,username,region
  1,"212.12.31.23","pete","EU"
  2,"212.12.31.231","bob","EU"
  3,"98.12.31.21","anders","EU"
  4,"121.12.31.23","jeff","US"
  5,"82.12.31.23","ted","AU"
  6,"62.12.31.23","annie","US"
  7,"122.12.31.23","joe","CH"
  8,"112.11.11.21","alice","CH"
  9,"212.112.131.22","admin","RU"
  10,"212.12.31.23","wendy","EU"

    Once it has been uploaded, it will look like what you see in the figure below.

    Import CSV File

    Figure 35. Import CSV File


    Typically, the content is used within the match() to lookup fixed reference information. Notice that the values are in quotes, except for the ones for userid, which are integers. See the Lookup API reference page for more information on this topic.

  3. Once created or uploaded, the file can be edited and updated within the user interface. Additional columns and rows can be added to the file using the + button. Clicking the tiny information icon next to the file name displays metadata info about the file (created by, time it was created, etc.)

    Important

    Only CSV files can be edited once uploaded within the user interface.

  4. Once you have finished editing, click Save, or click Export if you wish to download the edited file.

Create a lookup file upon a trigger action

Security Requirements and Controls

  • Change actions permission

  • Change files permission

It is possible to create and update a lookup file with an action connected to a trigger. This means that the first time the action triggers, a lookup file will be created. And then, the lookup file will be changed on subsequent action triggers.

For more information about how to create an action that creates a lookup file, see Action Type: Upload File.

Update lookup files

Sometimes it's necessary to update the content of lookup files. If you are running a trigger with a lookup file action, you can configure the action to update the file automatically based on the results of the trigger.

If you are not updating the lookup file automatically with a trigger as described above, the following methods are available to update lookup files:

  • Go to the Resources menu and select Lookup files. Click on the file to update. Update the file as needed, adding or removing columns and rows, updating contents, and so on.

  • Update the lookup file externally and upload it to LogScale. To do this, export the lookup file and open it in another tool, make the necessary changes, and upload the file again.

Export a lookup file

You can export lookup files from LogScale as needed, for example, to augment content in third-party tools where it is easier to manipulate large amounts of data.

To export a file click the ⋮ menu icon next to the file.

Options in the vertical dots for each lookup file. You can export, delete, or share the asset.

Figure 36. Lookup file management actions


You can also export the lookup file when the file is open by clicking Export file.

Screenshot showing a lookup file that is open with the options at the top of the screen to export or delete a file.

Figure 37. Export file


Delete a lookup file

Warning

Deleting a file that is actively used by live queries will stop those queries.

To delete a file click the ⋮ menu icon next to the file.

Options in the vertical dots for each lookup file. You can export, delete, or share the asset.

Figure 38. Lookup file management actions


You can also delete the lookup file when the file is open by clicking Delete file.

Screenshot showing a lookup file that is open with the options at the top of the screen to export or delete a file.

Figure 39. Delete file


Lookup files operations

When using Lookup files and match() functionality, consider the following:

  • Lookup files use server memory proportional to the size of the file on disk, at least as much and typically more. If you have a 1Gb lookup file it will take up at least 1Gb of memory on some, potentially all, hosts within the cluster. This requirement should be taken into account when uploading and sizing the nodes within the cluster.

  • From LogScale v1.108 on, content of the file is shared among all queries that uses match(), that is, the included columns that are common among match() functions can be reused among queries.

  • From 1.117 version on, whenever a file is updated, live queries and alert queries that use that file will seamlessly continue to run with the new updated file, thus making little difference if you have many small files to update or one large file. Since the file is swapped while the query is running, this also means that events can be queried with different versions of the file.

  • From LogScale v1.90, if you have large lookup files, wrap the uses of match() in saved queries rather than use them directly across multiple different queries to ensure you don't accidentally pass slightly different argument in different queries. However, due to an improved reuse of files introduced in LogScale v1.108, this practice is no longer necessary starting from that version.