[Redshift] How to get number of queries executed by each IAM account

Question

Hi, I am trying to get the number of queries executed against Redshift but I found it very difficult to find it for each IAM user in our organization.

The problem is that most of the queries on Redshift are executed under the master user(awsuser) and the system tables only shows the count for the master user,  not for each individual IAM user.

I have looked all the Redshift system tables and checked cloudtrail/cloudwatch logs. But it seems like I am stuck.

One more question. On CloudTrail event history, whenever I execute a query using redshift query editor v2, I can see several "DriverExecute" events coming up at once. Is there anyother event that I should target to get the number of queries executed by each IAM user?

Thanks

Answer

To get the number of queries executed by each IAM account in Amazon Redshift, you can query the STL_QUERY table. This table contains information about queries, including the user ID of the account that executed the query. By using the STL_QUERY table, you can track the number of queries executed by each IAM account. Here is an example SQL query to retrieve the number of queries executed by each IAM account:

SELECT userid, COUNT(*) AS num_queries FROM stl_query GROUP BY userid;

This query will provide the count of queries executed by each IAM account.

SELECT userid, COUNT(*) AS num_queries
FROM stl_query
GROUP BY userid;

This query will provide the count of queries executed by each IAM account.

[Redshift] How to get number of queries executed by each IAM account

Contenido relevante