"Error updating data cell filter: ... . Row filter expression provided cannot be parsed." with Security Lake struct

Question

I have Security Lake enabled with my org level Cloud Trail. Events are coming into the Cloud Trail Management table, `amazon_security_lake_table_us_west_2_cloud_trail_mgmt_1_0`,  in the underlying Lake Formation.

I'd like to filter rows by account id and then share the filtered rows to the associated account. There is something preventing me from creating a data filter for a specific part of a column.

I can create a filter as `actor.idp.name = 'foo'`, but I get the error in the post title when I try to create one as `actor.user.accountuid = 'foo'` or `actor.user.name = 'foo'`. In fact I can't create a data filter on any part of the `actor.user` struct.

I noticed that the actor column's struct differs between Glue and Lake Formation. Specifically, Glue has `actor.user.account_uid` and Lake Formation has `actor.user.accountuid`. Neither form allows me to create a Lake Formation data filter.

This is the column struct as shown in Lake Formation:

![Enter image description here](/media/postImages/original/IMYdm2o67PTKOuD9fnzmLG8Q)

This is the column struct as shown in Glue:

```json
{
  "actor": {
    "user": {
      "type": "string",
      "name": "string",
      "uid": "string",
      "uuid": "string",
      "account_uid": "string",
      "credential_uid": "string"
    },
    "session": {
      "created_time": "bigint",
      "mfa": "boolean",
      "issuer": "string"
    },
    "invoked_by": "string",
    "idp": {
      "name": "string"
    }
  }
}
```

Why can't I create a data filter on props of the `actor.user` struct?

Accepted Answer

Hello Matt,

I have replied to the Lake Formation support case that you have opened for the issue.

To summarise, "user" is one of the PartiQL reserved keywords as can be seen here: https://partiql.org/syntax/reserved-keywords-spec-version-2019.html

As per the Lake Formation documentation here: https://docs.aws.amazon.com/lake-formation/latest/dg/partiql-support.html#partiql-reserved-keywords

> If your row filter expression contains PartiQL keywords, you will receive a parsing error as column names may conflict with the keywords. When this happens, escape the column names by using double quotes. Some examples of reserved keywords are “first”, “last”, “asc”, “missing”. See PartiQL specification for a list of reserved keywords.

To resolve the issue, please ensure that the column names are enclosed in double quotes such as below when creating the data filter in Lake Formation:
```
"actor"."user"."accountuid" = 'some-uid'
```

Have a great day!

Answer

The actor column in the CloudTrail management table contains sensitive user identity information, which is why you cannot create a filter on parts of that column like the account ID.

Try these instead.
* Create a filter on another column that uniquely identifies the account, such as the eventSource or eventName fields.
* Export the data to S3 and process it there to filter by account ID. You would extract just the rows you want and share that data.
* Consider using AWS Config instead of CloudTrail management events if you need to track changes by account. Config delivers configuration changes in a format that allows easier filtering and sharing of findings.
* The structural differences you noticed between Glue and Lake Formation for the actor column are likely due to how each service parses the JSON event data. But neither allows filtering on sensitive user identity fields for security and privacy reasons. You'll need to filter the data in another way before sharing it with other accounts.

"Error updating data cell filter: ... . Row filter expression provided cannot be parsed." with Security Lake struct

Relevant content