I have a table which has S3 access logs.
For example,
| Bucketname | Rolename | Operation | Requesttime |
|------------|----------|-----------|--------------------|
| bucket1 | role1 | write | 2022-10-01 10:15:00|
| bucket2 | role2 | read | 2022-10-01 12:30:00|
| bucket1 | role3 | put | 2022-10-02 14:45:00|
| bucket1 | role1 | write | 2022-10-02 15:30:00|
| bucket2 | role2 | read | 2022-10-03 08:45:00|
| bucket3 | role1 | put | 2022-10-03 09:30:00|
| bucket1 | role1 | read | 2022-10-03 11:15:00|
| bucket2 | role2 | write | 2022-10-03 12:30:00|
| bucket3 | role1 | read | 2022-10-04 13:45:00|
| bucket3 | role2 | put | 2022-10-05 16:00:00|
| bucket3 | role1 | write | 2022-10-05 17:30:00|
I want to create a table visualization where i display the count of the number of times a particular role accessed a particular bucket in the span of 30 days.
There can be multiple times in a day where a role might have accessed a bucket, but it will boiled to 1 transaction.
The output will look like:
| bucket_name | role_name | No.of time accessed |
|-------------|-----------|---------------------|
| bucket1 | role1 | 3 |
| bucket1 | role3 | 1 |
| bucket2 | role2 | 2 |
| bucket3 | role1 | 3 |
| bucket3 | role2 | 2 |
The SQL to achieve this would look something like this:
SELECT
Bucketname AS bucket_name,
Rolename AS role_name,
COUNT(DISTINCT DATE_TRUNC('day', Requesttime)) AS "No.of time accessed"
FROM
your_table_name
GROUP BY
Bucketname,
Rolename
Can someone help me to achieve the same in quicksight.