I configured the Administrative Dashboard by referring to the blog below.
In the Dashboard Demo, I checked that the Active Status of all users was displayed as Inactive in the “User Analysis” > “User Information and Activity Details” Visual.
This problem was caused by the null value of the lastlogin column value when the status was calculated by joining the Athena Table in the cloudtrail logs.
When I directly queried the Athena table of CloudTrail, it was determined that the problem was caused by the fact that the user registered by synchronizing from the Okta user did not trigger the AssumeRoleWithSAML event when logging in. I looked through the entire CloudTrail Log, but the AssumeRoleWithSAML event was not triggered. (The user successfully logs in to QuickSight through Okta.)
Is it possible that the part is not working properly because the CloudTrail event method has recently been changed, even though it worked in the demo? Or is there another way to solve this problem? I would like to ask other people for guidance.
Admin-Console-CFN-Main DataSet - SQL Query Part
…
…
FULL JOIN (
SELECT
“username”,
“accountid”,
“min”(“eventtime”) “firstlogin”,
“max”(“eventtime”) “lastlogin”
FROM (
SELECT
“eventtime”,
“awsregion”,
“sourceipaddress”,
“concat”(“split_part”(“split_part”(“resources”[1].“arn”, ‘:’, 6), ‘/’, 2), ‘/’,
“useridentity”.“username”) “username”,
“resources”[1].“accountid” “accountid”
FROM “kalqs-admin-console”.“cloudtrail_logs_pp”
WHERE
“eventname” = ‘AssumeRoleWithSAML’ AND
“date_trunc”(‘day’, “date_parse”(“timestamp”, ‘%Y/%m/%d’)) >
CAST((current_date - INTERVAL ‘99’ DAY) AS date)
GROUP BY 1, 2, 3, 4, 5
)
GROUP BY 1, 2
) l ON “d”.“user_name” = “l”.“username” AND “d”.“accountid” = “l”."accountid