AWS Redshift Audit Logging UserActivityLog CloudWatch Queries with multiple lines


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.

  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, * 
AS multiline_query_8;

And here are the resulting log entries

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?

1 Answer
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, * 
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.

answered 4 months ago
  • Assuming that I'm looking at the log entries for a query with multiple lines and aggregating them by the event id, how will I know when I've reached the last line?

  • I got confirmation that the only way to find the last line was looking for the timestamp and the word log(e.g. [2024-01-04T20:19:26Z UTC][LOG] per support case 170440253001680).

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions