Login Attempts of email sign-in attempts in Cloudtrail?

Question & Answer
,
1

I’m looking for a way to find out first and last login times for users that login using their email, and not through an IAM account.

For the latter, I can filter Cloudtrail events using this query:

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

This query only works for IAM accounts.

Are non-iam login attempts visible in Cloudtrail ? and can I thus do something similar for non IAM users?

Do I need to look somewhere else?

2

This might cover some of what you’re looking for: Centrally tracking dashboard lineage, permissions, and more with Amazon QuickSight administrative dashboards | AWS Big Data Blog

3

Hi Lillie,

No it does not, since your answer does nothing with users.
By the way the question springs from implementing this dashboard: https://aws.amazon.com/blogs/big-data/building-an-administrative-console-in-amazon-quicksight-to-analyze-usage-metrics/. The User part does not work for us, since we use Quicksight users and not IAM users.

Thanks anyway,
Bart

4

Solved it so far by adding a UNION that queries for the GetDashboard :slight_smile:

UNION 
   SELECT
        "username"
      , "accountid"
      , "min"("eventtime") "firstlogin"
      , "max"("eventtime") "lastlogin"
      FROM
      (SELECT
     "eventtime"
   , "awsregion"
   , "sourceipaddress"
   , "useridentity"."username" "username"
   , "useridentity"."accountid" "accountid"
   FROM
     "admin-console"."cloudtrail_logs"
   WHERE ("eventname" = 'GetDashboard')
   GROUP BY 1, 2, 3, 4, 5)  
      GROUP BY 1, 2