Clarification on data tables syntax and example shown on the most recent Tuesday's Tip of the Week

Hello! This is, I guess, aimed more at Google people (especially ​@dnehoda as the original writer, and I’m really sorry if directly mentioning users is rude) since it’s a question on a Google post, but please feel free to answer if you have relevant information.

On the most recent Tip of the Week (on which I can’t reply, hence why I’m creating this new topic)

 I read that, apparently, “Data tables use $table_name[column_name] syntax”

Is that a new thing? From what I’ve read and used, it’s always been %a.b rather than $a[b]. If that’s a new thing, is it different in any way from the old %a.b syntax? I’ve attempted to test the syntax used in the post, and it throws syntax errors

---

Also, in the same post, there’s an example rule for unauthorized secret access, which has the following in the events block:

$secret.principal.user.email_addresses = $sa_email
$secret.target.resource.name = $resource
not $sa_email in $approved_service_accounts[email]
not $resource in $approved_secrets[secret_name]

That uses two separate data tables, “list of emails that can access secrets” and “list of secrets that can be accessed”. However, that doesn’t limit which secrets a particular user can access, so that all approved users can read all approved secrets.

What if we wanted to have a single data table with (user, secret) pairs such that different users can read a different set of secrets (and, conversely, each secret can be read by a different set of users)? From what I’ve seen, simply using two clauses, both of the form not $field in %table.column (with the same table name in both, but different column names), doesn’t work as I’d expect. I’d expect it to unify to the same row on the data table, so that it’ll only find events that do *not* have a matching row in the data table. However, per https://docs.cloud.google.com/chronicle/docs/investigation/data-tables#row-based_and_column-based_comparisons_in_data_tables, using $field IN %table.column works like “Value must exist ANYWHERE in the column”, as opposed to $field = %table.column which works like “All conditions must match within the SAME row”

In our particular case, we’re trying to have an allowlist of foreign VPN connections (user A can connect from country B between times C1 and C2, anything else from outside our country should raise an alert). We have a data table with (user, country, start_time, end_time) columns, but the following query

$e.principal.user.userid != %allowlist.user or
$e.principal.location.country_or_region != %allowlist.country

which I’d expect to be interpreted as (userid NOT in table) OR (country NOT in table) which per De Morgan’s rules is == NOT (userid in table and country in table) does *not* work, it errors with “event variables are not all joined by equalities”

I believe 

 is asking something similar, but it hasn’t really received a response.

Does anyone know of a way to express multi-column allowlist logic, i.e. alert exclusions/suppressions that involve multiple columns of a data table, and not all columns are = operations? In our case, we’d also have regex matches (e.g. allow all users with the username auto_.* from a particular country) and range matches (exclusions should have a start and end time, so that would be something like event_timestamp > %exclusions.start_time AND event_timestamp < %exclusions.end_time

Thanks for any help!