Skip to main content
Solved

Stats query using a regex reference list

  • June 10, 2025
  • 6 replies
  • 12 views
donkos
Forum|alt.badge.img+8
metadata.event_type = "EMAIL_TRANSACTION"
$email = network.email.from
$email in %SAE_Regex_Test_List
match: $email
outcome: $total_count = count_distinct(network.email.mail_id)
order: $total_count desc
limit: 100

The above query works great if I'm using a string reference list - it will return counts based on the items in that reference list if they're present in the logs (user1@example.com, user2@example.com).

The reason I'm using reference lists is because there is the potential to search hundreds of items in one go, this way my query string does not exceed the length limit. However I also need to searches using wildcards for domains, e.g. .*@example.com - is there any way to still use reference lists to do those searches and return per item results?

Best answer by chrisd2

Hello, you could use a second Reference List, typed as 'regex' instead of string, and update your query to look for results matching (string) your first RefList OR matching (regex) your sedond RefList :

( $email in %SAE_string_Test_List or $email in regex %SAE_pattern_test_list )

6 replies

chrisd2
Forum|alt.badge.img+9
  • chrisd2Bronze 5
  • Bronze 5
  • Answer
  • June 10, 2025

Hello, you could use a second Reference List, typed as 'regex' instead of string, and update your query to look for results matching (string) your first RefList OR matching (regex) your sedond RefList :

( $email in %SAE_string_Test_List or $email in regex %SAE_pattern_test_list )
chrisd2
Forum|alt.badge.img+9
  • chrisd2Bronze 5
  • Bronze 5
  • June 10, 2025

You could even use a DataTable with 2 columns, one for your exact email addresses (typed as STRING) and one for your email patterns (typed as REGEX) so you have all your reference data at the same place !
And then use it in your query :

metadata.event_type = "EMAIL_TRANSACTION" $email = network.email.from ( $email in %SAE_test.email_addr or $email in %SAE_test.email_pattern )
donkos
Forum|alt.badge.img+8
  • donkos
  • Author
  • New Member
  • June 17, 2025

You could even use a DataTable with 2 columns, one for your exact email addresses (typed as STRING) and one for your email patterns (typed as REGEX) so you have all your reference data at the same place !
And then use it in your query :

metadata.event_type = "EMAIL_TRANSACTION" $email = network.email.from ( $email in %SAE_test.email_addr or $email in %SAE_test.email_pattern )

@chrisd2 Imagine a scenario where my reference lists (simple string and regex) have items that are not present in my logs. Is there some way to have those items still returned as row items (with a count of 0) if I want to run a count command for each entity?

chrisd2
Forum|alt.badge.img+9
  • chrisd2Bronze 5
  • Bronze 5
  • June 17, 2025

Hello, I had a similar UC and could not find any solution. From my understanding, everything starts by the logs, so no logs = no results = no stats row.

I found a workaround though, which is to look for larger duration in the logs and perform your aggregation on your window of interest. e.g. if you are interested in the past day only, query logs for 7 days and perform your count on the logs of last day only (timestamps functions will be handy).

This way, if one of the value of your reflist is not seen in the last day but was here a bit earlier, you'll have a count of 0. However, if it was not seen for 7 days, we still have the  issue

chrisd2
Forum|alt.badge.img+9
  • chrisd2Bronze 5
  • Bronze 5
  • June 17, 2025

Hello, I had a similar UC and could not find any solution. From my understanding, everything starts by the logs, so no logs = no results = no stats row.

I found a workaround though, which is to look for larger duration in the logs and perform your aggregation on your window of interest. e.g. if you are interested in the past day only, query logs for 7 days and perform your count on the logs of last day only (timestamps functions will be handy).

This way, if one of the value of your reflist is not seen in the last day but was here a bit earlier, you'll have a count of 0. However, if it was not seen for 7 days, we still have the  issue


But honestly this is a dirty workaround, uses more resources than needed to compensate for the current lack of functionality 😞

donkos
Forum|alt.badge.img+8
  • donkos
  • Author
  • New Member
  • June 20, 2025

@chrisd2 Would I be able to search for hash values in two different udm fields:

 

metadata.event_type = "EMAIL_TRANSACTION" and network.direction = "INBOUND"
$custom_action = if(security_result.action != "BLOCK", "ALLOW", "BLOCK")
$hash256 = about.file.sha256
$hashmd5 = about.file.md5
($hash256 in %SAE_string_Test_List OR $hashmd5 in %SAE_string_Test_List)
match: $hash256,$hashmd5, $custom_action
outcome: $total_count = count_distinct(network.email.mail_id)
order: $total_count desc
 
The aim would be to return counts by hash if the hash in the string reference list is present in one udm field or the other.
Terms & ConditionsCookie settingsAccessibility statement
Privacy Policy Terms of Service Gen AI Policy Community Guidelines Cookie Settings

© 2025 Google. All rights reserved.