AWS Admin console first and last login query question

Question & Answer
, ,
1

I have a question about the AWS admin console. I have it set up and working, but I noticed the query for the main dataset isn’t capturing the first and last login for users. Essentially, this query is not capturing it as intended.:
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
“admin-console”.“cloudtrail_logs”
WHERE (“eventname” = ‘AssumeRoleWithSAML’)
GROUP BY 1, 2, 3, 4, 5)
GROUP BY 1, 2
) l ON ((“d”.“user_name” = “l”.“username”) AND (“d”.“accountid” = “l”.“accountid”)))

If anyone has experience with the console, I can provide more information.

1 Like
2

Hello @ineedqshelp, this may be a question that would be better suited for the AWS re:Post website rather than the QuickSight Community. Someone here may have some information about this, but since this question is more focused on CloudTrail and accessing specific data from it, you may want to try posting your question there instead. I hope you can get the information you need!

3

Okay thanks, I had gotten help here for the admin console before, but I will check there.

1 Like