- Welcome, in this demonstration we are going
to create an Athena table that will allow us
to query cloudtrail logs that are located in an S3 bucket.
And there are a number of different pieces
that have to be in place for this to work properly.
The first of these is that we are going to
need an S3 bucket created for our query results.
And so we're gonna go ahead and do that first.
We're gonna go to the S3 service
and we're gonna go ahead and create a bucket.
And we are going to make sure that the bucket
is in the same region as the cloudtrail logs,
which are in US East one.
And we're gonna pick a bucket name
and I will call this "Live Lessons Athena Query Results"
and we're gonna accept most of the defaults.
We're gonna leave object ownership, the defaults,
block public accesses, the defaults,
bucket versioning off.
We'll add a tag to the bucket.
We'll give it a cost center tag.
Default encryption we'll enable
and just allow Amazon Managed Keys
and we'll create the bucket.
So now we have a destination for our query's results.
Now we need to be able to create an Athena table
that will be placed in the data catalog.
We could go to the Athena service and do this directly.
We could go to explore the query editor
and we could scroll down here on the left hand side,
under data catalog,
and realize that we don't have any databases.
We don't have any tables.
And so we could click here on create a table
from a data source,
but then it would require us
to define all of the information that was part
of that data set, including the data format.
But there's an easier way to do this for cloudtrail logs.
So we can cancel out of that entirely.
Go back to our Athena Query Editor main page
and leave that there.
We are now going to skip over to the cloudtrail dashboard.
Now, assuming that this has been enabled,
you can check in your own account
by going to the dashboard
and see if you have a trail created.
I have one called cloudtrail Write Demo.
Now, assuming that you do have one,
and if not you can enable that
by creating an account wide trail pretty easily.
We're actually gonna talk about this in a later lesson.
We can then go to event history,
and in the event dashboard
we have the ability to do some basic searching here,
but it's not very sophisticated.
And if you notice at the top right, look at that.
Create Athena table.
This is a great example
of how AWS is supposed to be used as an ecosystem
rather than just a bunch of independent parts.
So when we click here,
it's gonna ask us for the storage location.
So the S3 bucket that contains the actual cloudtrail files.
So that's easy, right there in the dropdown.
And then it's going to fill out all of that information
for creating the table on our behalf.
So it's gonna create the external table.
We have all of the different fields
that have been listed in there.
And now all we have to do is click, Create Table.
And now that that's done,
we can now go back to the Athena dashboard.
We'll do a quick refresh.
And from here, we can then scroll down a little bit further.
We see that we have a default database
that did not exist before.
We can go down a little bit further
and see we have tables and views
and under tables, we have one right here
called, cloudtrail Logs
brightkey_cloudtrail_write_demo.
We have all of the information there in the table
and the schema so that we can then perform an actual query.
But first says here, before you run your first query,
you need to set up a query result location in S3.
We created the bucket.
Now we need to change our settings.
So we can go ahead and click here.
We can see that there's no query result location.
And so we click on Manage,
and we can browse S3 to find,
let's look for Athena.
There we go.
Live lessons, Athena query results.
So we can then choose that.
We already know that it's the same account
so we don't need to make any changes there.
We can enable query result encryption, if we like,
as well as bucket owner, full control.
But since it's all in the same account,
we can just go ahead and save.
So now we can go back to our query editor
and we can create a query.
Now I'm gonna provide a couple of example queries.
And let me go ahead and copy the text.
And we'll paste this in.
We'll see here, select user identity, username,
source IP event time and additional event data.
What we're gonna do is we're gonna query all AWS logins.
Says where event name equals console login,
starting in January of 2022.
The only piece that we have to change
is right here from your table name.
So we can grab the name of this table right here
and paste that in instead.
And all we have to do at this point is click run.
And it is now scanning
all of the various cloudtrail logs
to locate those console login events.
And you can see that it is taking a little bit of time.
And because I didn't identify anything farther in
to the S3 bucket than just the name of the bucket
it's gonna scan all of my cloudtrail logs.
And that might take a little bit of time.
And you can see that as it's going along here
it's updating the run time.
It's also updating the data scanned
and you can see that that number keeps going up.
Athena as a service is relatively expensive,
if you're not optimizing your queries.
And so while this runs, I'm gonna pull up
another example of a query that we can execute.
And this one's a little bit different.
This one is going to select all failed operations
based on permissions from the beginning of 2021.
Now with this query that's executing
we could just choose to cancel, oh, there we go.
So it finished and we have 64 different logins
that have happened since the beginning of 2022.
And it gives all the information on these.
So let's try one more query.
For this one, we're gonna grab all the failed operations.
See this, where error code like denied
or error code like unauthorized.
The only thing we need to do is change the table name again
Paste that in and execute.
Now notice that previous one scanned 1.49 gigabytes of data.
That might not sound like a lot,
but what if you have an S3 bucket that has petabytes of data
and you're scanning the entire thing for every query
that's gonna be extraordinarily expensive.
So with this one, when we click run,
this one would take a little bit longer
because it's going all the way back
to the beginning of 2021.
But notice the data scanned going up pretty quickly.
So we could let this sit until it completes
but when it's done, it's gonna give us the results
of all of the various failed operations
since the beginning of January 1st, 2021.
And that completes this demonstration.