How can I search p_any_ip_addresses for one or more of a set of IP addresses in Panther?

QUESTION

I'd like to see if any of a list of IP addresses appear in the p_any_ip_addresses field in my logs. How can I do this concisely?

ANSWER

The simplest (and cleanest) way to search p_any_ip_addresses for a set of IP addresses is to use the arrays_overlap command. For example, to look for any Cloudtrail logs over the past day which included the addresses 1.1.1.1 or 2.2.2.2, we can use the following query:

SELECT *
FROM aws_cloudtrail
WHERE
  ARRAYS_OVERLAP(p_any_ip_addresses, [
    '1.1.1.1',
    '2.2.2.2'
  ])
AND
  p_occurs_since('24 hours')
LIMIT 100;