Skip to main content
Sumo Logic

Create and Manage Lookup Tables

Learn about lookup tables and how to create and manage them.

This page has instructions for creating and and managing lookup tables using the Sumo Logic UI.

Introduction to lookup tables

A lookup table is a table of data hosted on Sumo Logic that you can use to enrich the log data received by Sumo Logic. For example, in a Sumo Logic log search, you could refer to a lookup table of user account data to map the user ID in an incoming log to a row in the lookup table, and return other attributes of that user, for instance, email address or phone number. The fields you look up appear as part of your search results. 

Lookup table size limit

Lookup files can be up to 100 MB in size. 

Create a lookup table

This section has instructions for creating a lookup table using the Sumo Logic UI.

Create a lookup table schema

Follow these instructions to create a new lookup table and define its schema without populating the table.

  1. Go to the Sumo Logic Library.
  2. Navigate to the folder where you want to create the lookup table.
  3. Click Add New and then select New Lookup.
    lookup-list.png
  4. The Create Lookup Table page appears.
    new-lookup.png
  5. Lookup Name. Enter a name for the lookup table.
  6. Description. (Optional.)  Enter a description of the lookup table.
  7. Do you want a TTL for table entries? A TTL specifies a time limit beyond which an unchanged row in the table will be unavailable for reads. For example, if you set a TTL of 5 minutes for a lookup table, when 5 minutes pass without a row being updated, that row will no longer returned by lookups. 
    1. Click Yes if you want to set a TTL.
    2. Enter an integer value in the Duration field, and select a unit of time from the pulldown:  Seconds, Minutes (default), Hours, or Days.
  8. How do you want to create lookup? Click Create Schema only.
  9. The page displays a Schema section. 
    schema.png
  10. Schema. For the first column in the table, enter:
    1. Field. Enter a name for the field.
    2. Value Type. Choose the value type: boolean, int, long, double, or string (default).
    3. Primary Key. Click the Yes checkbox if the field is part of the primary key for the table. If your table's primary key is a composite key, you'll check this checkbox for each field that is part of the key.
  11. To add another column to the table, click the plus sign to the right of the first column and repeat the previous step.
  12. To remove a column from the table, select Delete Column from the three-dot more options menu.
  13. When you are done adding columns, click Create Lookup

Create a lookup table from a .csv file

Follow these instructions to create and populate a lookup table with the contents of a .csv file.

Before you start, create a .csv file containing the rows you want to put in the new lookup table. The file should have a .csv extension, and not be larger than 100 MB. The first row of the table should contain the names of the fields in the table. For example:

username,IPAddress,region

  1. Go to the Sumo Logic Library.
  2. Click Add New and then select New Lookup.
    new-lookup-button.png
  3. The Create Lookup Table page appears.
  4. Lookup Name. Enter a name for the lookup table.
  5. Description. (Optional.)  Enter a description of the lookup table.
  6. Do you want a TTL for table entries? A TTL specifies a time limit beyond which an unchanged row in the table will be unavailable for reads. For example, if you set a TTL of 5 minutes for a lookup table, when 5 minutes pass without a row being updated, that row will no longer returned by lookups. 
    1. Click Yes if you want to set a TTL.
    2. Enter an integer value in the Duration field, and select a unit of time from the pulldown:  Seconds, Minutes (default), Hours, or Days.
  7. How do you want to create lookup? Click Upload File.
  8. The Upload File section appears.
    upload-file.png
  9. Click Upload.
  10. Navigate to the file you want to upload and click Open.
  11. The Schema section of the page refreshes, and displays up to 10 rows from the .csv file you uploaded.
    schema-with-fields.png
  12. For each column in the table:
    1. Value Type. Choose the value type: boolean, int, long, double, or string (default).
    2. Primary Key. Click the Yes checkbox if the field is part of the primary key for the table. 
  13. Click Create Lookup in the upper right of the page.

View the contents of a lookup table

  1. Go to the Sumo Logic Library.
    library-icon.png
  2. Click in the search bar, and select Lookups from the dropdown.
    lookups-type-in-library.png
  3. Hover over the lookup table you want to view, and select Open from the three-dot more options menu.
  4. The view page for the lookup table appears. It displays a preview of the contents of the lookup table, up to 10 rows.
    edit-lookup-table-view-data.png
  5. To view the complete contents of the lookup table, click View Data.
  6. A Sumo Logic search tab opens and a cat command is run on your table.
    view-lookup-table.png

Update the contents of a lookup table

This section has instructions for updating a lookup table. You can:

  • Merge data—Use this option to update existing rows with new values, or to add new rows to the lookup table. 
  • Replace data—Use this option to completely replace the data in the lookup table with the data in the .csv file. 
  • Delete data—Use this option to remove all the data in the lookup table. 

Merge data into a lookup table

You can use the Merge Data option to update existing lookup tables rows with new values, or to add new rows to a lookup table. 

Before you start, create a .csv file that contains the data you want to merge. Note that:

  • If a primary key value in the .csv file matches an existing primary key value in the lookup table, the corresponding row in the table will be overwritten.
  • If a primary key value in the .csv file does not match a primary key value in any row in the table, a new row will be added to the table.
  • Any rows that exist in the lookup table, but not in the .csv file, will remain unchanged.

The file should have a .csv extension, and not be larger than 100 MB. The first row of the table should contain the names of the fields defined in the table schema. 

For example:

username,IPAddress,region

  1. Go to the Sumo Logic Library.
  2. Mouse over the lookup table you want to view, and select Edit from the three-dot more options menu.
  3. The edit page for the lookup table appears.
    edit-lookup-table.png
  4. Click Merge Data.
  5. The Merge Lookup Data popup appears.
    merge-lookup-data.png
  6. Click Upload.
  7. Navigate to the .csv file and click Open.
  8. Click Done.

Replace all the rows in a lookup table with new rows

You can use the Replace Data option to completely replace the data in a lookup table with the data in a .csv file.

Before you start, create a .csv file that contains the rows you overwrite the lookup table with.  

The file should have a .csv extension, and not be larger than 100 MB. The first row of the table should contain the names of the fields defined in the table schema. For example:

username,IPAddress,region

  1. Go to the Sumo Logic Library.
  2. Mouse over the lookup table you want to view, and select Edit from the three-dot more options menu.
  3. The edit page for the lookup table appears.
    edit-lookup-table.png
  4. Click Replace Data.
  5. The Replace All Lookup Data popup appears.
    replace-all-lookup-data.png
  6. Click Upload.
  7. Navigate to the .csv file and click Open.
  8. Click Done.

Delete the contents of a lookup table

You can use the Delete Data option to remove all the data in a lookup table.

Follow the steps below to delete all of the contents of a lookup table:

  1. Go to the Sumo Logic Library.
  2. Mouse over the lookup table you want to delete, and select Edit from the three-dot more options menu.
  3. The edit page for the lookup table appears.
    edit-lookup-table.png
  4. Click Delete Data.
  5. You are prompted to confirm that you want to delete the contents of the lookup file.
    delete-lookup-data.png
  6. Enter Delete, and click Delete.

Update a lookup table with the save operator

You can use the save operator to save the results of a Sumo log query to a lookup table you created using the Lookup UI or API. For more information, see save Operator.

Export a lookup table schema

If you want to replicate a lookup table schema in a different folder in the Library, the process is to export it, and then import it into the desired folder. (When you export a lookup table, the data it contains is not exported.) 

  1. Go to the Sumo Logic Library.
  2. Mouse over the lookup table you want to export, and select Export from the three-dot more options menu.
  3. The export popup presents the contents of the lookup table in JSON format.
    export-lookup-table.png
  4. Click Copy to copy the JSON to the clipboard, or Download to download a JSON file.
  5. Click Done
  6. To create new lookup table with the JSON, follow the instructions in Import Content in the Library.

Share a lookup table

You can share a lookup table with other users, a role, or a combination of the two. 

  1. Go to the Sumo Logic Library.
  2. Mouse over the row for a lookup table you want to share, and click the sharing icon towards the right side of the row. You’ll be prompted to enter the user and roles with whom you want to share the table, the level of access you want to grant, and other sharing options. For information about sharing , see Share Content.

Delete a lookup table

Follow the steps below to completely delete a lookup table:

  1. Go to the Sumo Logic Library.
  2. Mouse over the lookup table you want to delete, and select Delete from the three-dot more options menu.

Operators you use with lookup tables

You can use the following operators with lookup tables in Sumo Logic log searches and in Cloud SIEM rules:

  • cat—You can view the contents of a lookup table using the cat operator in a Sumo Logic log search tab. For more information, see cat.
  • lookup—You can use the lookup operator to return one or more fields from a lookup table. For more information, see lookup.
  • lookupContains—You can use the lookupContains operator to see whether a key exists in a lookup table. For more information, see lookupContains.
  • save—You can use the save operator to save the results of a Sumo log query to a lookup table you created using the Lookup UI or API. For more information, see save.