Using Athena with CloudTrail logs is a powerful way to enhance your analysis of AWS service activity. For example, you can use queries to identify trends and further isolate activity by attributes, such as source IP address or user.
Console Sign-in activity: Whether it’s to help meet a compliance standard such as PCI, adhering to a best practice security framework such as NIST, or just wanting to better understand who is accessing your assets, auditing your login activity is vital. The following query returns details such as user name, IP address, time of day, whether the login was from a mobile console version, and whether multi-factor authentication was used.
SELECT useridentity.username,
errormessage,
sourceipaddress,
eventtime,
additionaleventdata
FROM default.TABLE_NAME
WHERE eventname = 'ConsoleLogin'
This is how it looks like on Athena Console.
AWS event errors: Recurring error messages can be a sign of an incorrectly configured policy, the wrong permissions applied to an application, or an unknown change in your workloads. The following query shows the top 10 errors that have occurred from the start of the year.
SELECT count (*) AS TotalEvents,
eventname,
errorcode,
errormessage
FROM default.TABLE_NAME
WHERE errorcode is NOT null
AND eventtime >= '2020-01-01T00:00:00Z'
GROUP BY eventname, errorcode, errormessage
ORDER BY TotalEvents DESC limit 10
This is how it looks like on Athena Console.
End of Lab Exercises
Thank you for using this lab.