Example: Using Unified IPv4 Threatlists

In this example, you explore using the Unified IPv4 Threatlist against data that contains IPv4 addresses.

Shaping the Data

Assume that you have a Dataset with a FIELDS column with the following data:

{"action":"failure","dest":"www.your-website.com","source":"weblogs","src":"167.86.122.9","timestamp":"1684767232.746","user":"your.name"}

  1. Click on Untitled Worksheet and rename it to Example/IPv4 IOC Matches.

  2. Click on Stage 1 and rename it to Form Data.

Extracting Fields from the JSON Payload

3. Open the menu for the FIELDS column and choose Extract from JSON.

4. In the right menu, select the following fields. Some fields may be in nested objects:

  • action

  • source

  • dest

  • src

  • timestamp

  • user

5. Click Apply.

The extraction creates the OPAL:

// Extract fields from JSON
make_col action:string(FIELDS.action),
    source:string(FIELDS.source),
    dest:string(FIELDS.dest),
    src:string(FIELDS.src),
    timestamp:string(FIELDS.timestamp),
    user:string(FIELDS.user)

Forming the timestamp from Event Data

6. Default time is likely to be set from the time when the data was ingested. In this section, you will set the Event timestamp from the data. Because JSON extraction does not force a type, you will need to force this column to a valid timestamp object. Additionally, the src field should be forced to an ipv4 type, in order to ensure valid IPv4 address values are used.

Use the following OPAL to force proper object types.

// Extract fields from JSON
make_col action:string(FIELDS.action),
    source:string(FIELDS.source),
    dest:string(FIELDS.dest),
    src:ipv4(FIELDS.src),
    timestamp:from_seconds(FIELDS.timestamp),
    user:string(FIELDS.user)

7. Add the following OPAL to set a set_valid_from. For better performance, don’t use a new value that deviates too much from the original ingest time replaced.

// Set valid from for the data
set_valid_from options(max_time_diff:4h), timestamp

Another potential performance improvement is to use timechart on the source data. The typical use case for this data is to create Monitors or Dashboards to alert on contact with known bad addresses. If you are presuming that any amount of contact is bad, then there is no need to track every single event in this dataset (noting that every event is preserved as long as you choose in Observe). You can optionally use the following OPAL to increase performance:

// Bucket events to 15 minute frames
timechart 15m, count:count(1), group_by(source, dest, src, user)

8. To improve the performance of matching addresses against large network blocks, we can use numeric representations of the first part of the network and the address as a key. This OPAL constructs that key from an INT64 version of the IPv4 address.

// make a src_64 of the IPv4 Address
make_col src_64:int64(src)
// Make an INT64 representation of the first 16 bits for a lookup key
make_col src_prefix_mask:floor(src_64/pow(2, 16),0)

9. Add the following OPAL to reduce the columns to only the fields of interest.

// Keep fields of interest
pick_col timestamp,
    src,
    src_64,
    dest,
    user,
    action

10. Click RUN.

11. Click Save Worksheet.

Filtering Data to Public IP Addresses

  1. Minimize the OPAL window and select Link New Stage.

  2. Rename the new Stage Filter to Public IPs.

  3. Open the OPAL console.

  4. Update the OPAL using the following code:

// Filter out reserved IPv4 Ranges
filter not ipv4_address_in_network(src,"0.0.0.0/8") and not ipv4_address_in_network(src,"10.0.0.0/8") and not ipv4_address_in_network(src,"100.64.0.0/10") and not ipv4_address_in_network(src,"127.0.0.0/8") and not ipv4_address_in_network(src,"169.254.0.0/16") and not ipv4_address_in_network(src,"172.16.0.0/12") and not ipv4_address_in_network(src,"192.0.0.0/24") and not ipv4_address_in_network(src,"192.0.2.0/24") and not ipv4_address_in_network(src,"192.88.99.0/24") and not ipv4_address_in_network(src,"192.168.0.0/16") and not ipv4_address_in_network(src,"198.18.0.0/15") and not ipv4_address_in_network(src,"198.51.100.0/24") and not ipv4_address_in_network(src,"203.0.113.0/24") and not ipv4_address_in_network(src,"224.0.0.0/4") and not ipv4_address_in_network(src,"233.252.0.0/24") and not ipv4_address_in_network(src,"240.0.0.0/4") and not ipv4_address_in_network(src,"255.255.255.255/32")

You may also find it useful to enrich the data with lookup_ip_info at this time, in order to use geographic information in a later dashboard or monitor.

5. Click Run then Save worksheet.

Match Data to Unified IP IOCs

  1. Minimize the OPAL console so you can select Link New Stage.

  2. Rename the new Stage to IP Address IOC Matches.

  3. Open the OPAL console.

  4. Click Inputs.

  5. Search for and add the Unified IPv4 Threatlist.

  6. Note the name of the added Input as you use in the join section.

7. Click the OPAL tab and update using the following code:

// Match IP in the Unified IP IOC Threatlist

// Pick just the columns of interest from the dataset
pick_col timestamp,
      src,
      src_64,
      dest,
      user,
      action
      
// Make an integer64 representation of the first 16 bits for a lookup key
make_col src_prefix_mask:floor(int64(ipv4(IP))/pow(2, 16),0)

//Join on checking the first 16 bits of the IP is equal to the IOC first 16 bits. This lookup key ensures performance.
//Then check if the IP Address is within the range of the IOC to match

join on (src_prefix_mask = @"Threat Intel Basic/Unified IPv4 Threatlist".tip_ip_prefix_mask and ipv4_address_in_network(src,@"Threat Intel Basic/Unified IPv4 Threatlist".tip_ip)),
    tip_provider:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_provider,
    tip_match:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_ip,
    tip_category:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_category,
    tip_source:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_source,
    tip_provider_id:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_provider_id,
    tip_provider_url:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_provider_url,
    tip_ip_version:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_ip_version,
    tip_match_field_range_start:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_ip_range_start,
    tip_match_range_end:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_ip_range_end,
    tip_severity:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_severity,
    tip_tlp:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_tlp,
    tip_tags:@"Threat Intel Basic/Unified IPv4 Threatlist".tip_tags,
    tip_match_field:"src"

8. Click Run.

9. Click Save worksheet.

You have now completed the usage example. You could also use this Worksheet to make a Dataset and subsequent Monitor.