AWS Redshift Audit Logging UserActivityLog CloudWatch Queries with multiple lines

Question

I have enabled logging for my AWS Redshift cluster and am able to see the UserActivityLogs in CloudWatch. The problem that I am having is that when a query has multiple lines, it is split into separate log records and only the initial log entry contains the below information from [the documentation](https://docs.aws.amazon.com/redshift/latest/mgmt/db-auditing.html#db-auditing-logs).

1. recordtime	Time the event occurred.
2. db	Database name.
3. user	User name.
4. pid	Process ID associated with the statement.
5. userid	User ID.
6. xid	Transaction ID.
7. query	A prefix of LOG: followed by the text of the query, including newlines.

How can I tie the log entries together to get the necessary information in the first log message and the full query text?

I have been able to use the event id to link together multiple entries. However I have not found anything documented to backup this assumption. Ideally I would like to see each log entry have the information within the brackets or at least the xid to link everything together. I am running the below query in AWS RedShift Query Editor v2.

```
select 1 AS TestMarker_8, * 
from 
mydb 
AS multiline_query_8;
```

And here are the resulting log entries

```
timestamp,ingestionTime,message,eventId,logStreamName
1704399566000,1704399604068,'2024-01-04T20:19:26Z UTC [ db=mydb user=user pid=1234567890 userid=123 xid=234209736 ]' LOG: /* RQEV2-LnCUnrDyYD */,00000000000000000000000000000000000000000000000000000010,myLogStream
1704399566000,1704399604068,"select 1 AS TestMarker_8, * ",00000000000000000000000000000000000000000000000000000011,myLogStream
1704399566000,1704399604068,from ,00000000000000000000000000000000000000000000000000000012,myLogStream
1704399566000,1704399604068,mydb ,00000000000000000000000000000000000000000000000000000013,myLogStream
1704399566000,1704399604068,AS multiline_query_8 limit 100,00000000000000000000000000000000000000000000000000000014,myLogStream
```
I can see that the event id goes 10, 11, 12, 13 and 14. If this is a valid way to group the messages, how will I know when I have reached the last message?

Accepted Answer

Hello Ryan,

Your method of grouping the UserActivityLog message using EventId to relate group of query messages ran by the user in Redshift Editor is correct.

This is by design that when a user runs a query like:

```
select 1 AS TestMarker_8, * 
from 
mydb 
AS multiline_query_8;
```

Then the Redshift Service would log 4 messages in CloudWatch as you could observe in your case, this occurred as the query is structured across 4 lines, when the query is executed by the user the logging service will dispatch/publish each line as a separate event to CloudWatch, with EventId in ascending order as they are triggered sequentially during query execution.

Also, if the same query is re-structured as:

```
select 1 AS TestMarker_8, * from mydb AS multiline_query_8;
```

Then is such case there will be just one Log event pushed to CloudWatch as the entire query is structured in 1 line.

AWS Redshift Audit Logging UserActivityLog CloudWatch Queries with multiple lines

Relevant content