Skip to main content

Working With Lookups

Do you know how they say less is more? Well "they" are wrong, more is more. When you find yourself in a situation where you need additional context in your data, the lookup operator will be your go-to tool. Before we see this in action, we first need to create a lookup to use. This is a great opportunity to introduce the export operator.

Export takes search results and... exports ...them to a lookup that can be used in searches.

important
  1. Clear the query box.
  2. Enter the following query: 
    dataset="cribl_search_sample" dataSource="access_combined"
    | limit 100000
    | summarize by status
    | extend outcome = iif(status=="200","SUCCESS","FAIL")
    | project-away count_
    | export description="HTTP Status Outcomes" fieldMapping="status:status,outcome:outcome" to lookup sbx_http_outcomes
  3. Click SEARCH.

To validate that our lookup has been created, we'll search for it in the $vt_lookups dataset. $vt_lookups is a special class of datasets referred to as a virtual table (more to come on virtual tables in a later sandbox).

important
  1. Clear the query box.
  2. Enter the following query: 
    dataset="$vt_lookups"
  3. Click SEARCH.

You should now be able to see an entry showing the details of your newly created lookup. To see the contents of the lookup you can search the cribl_lookups dataset.

important
  1. Clear the query box.
  2. Enter the following query: 
    dataset="cribl_lookups" lookup_table="sbx_http_outcomes"
  3. Click SEARCH.

Now you can see every lookup entry within the results pane. Great, now we'll use this lookup to add information to our original access_combined search.

important
  1. Clear the query box.
  2. Enter the following query: 
    dataset="cribl_search_sample" dataSource="access_combined"
    | limit 100000
    | lookup sbx_http_outcomes on status
  3. Click SEARCH.

Boom, just like that, we've added a field called outcome to every single event based on the status of the request. To verify, take a look at the field browser.

important
  1. Click outcome in the field browser left of the events.
    1. Verify there are both SUCCESS and FAIL values.
  2. Clear the query box.
  3. Enter the following query: 
    dataset="cribl_search_sample" dataSource="access_combined"
    | limit 100000
    | lookup sbx_http_outcomes on status
    | summarize by outcome
  4. Click SEARCH.
tip

summarize by <field> is equivalent to summarize count() by <field>