AWS Athena Analysis Log

AWS can use Athena to analyze the logs saved in S3. He converts the logs into the format of database tables so that they can be queried through the sql statement. This function is similar to using logparser to analyze Exchange or IIS logs on a windows server.

Let's do a demonstration, record the administrative log through Cloudtrail, and then query the log content through Athena.

First of all, choose CloudTrail. CloudTrail is a logging service. The difference between CloudTrail and cloudwatch is that this service focuses more on auditing. Its content is all about when, what account, and what operations have been performed on what IP.

Click Create Trail

Take a name and create a new S3 bucket to save the log

After it is created, you can see that he is automatically recording the latest log.

Then select Athena

Skip the wizard and go directly to the editor of the querier, where you can edit the SQL statement. Here I create a database directly.

Let's create a table to get the data from the specified S3 Bucket.

We can create it through the wizard, but it's cumbersome.

It is easier to create through a script, pay attention to the address of the last line of S3 bucket

CREATE EXTERNAL TABLE cloudtrail_logs (eventversion STRING,useridentity STRUCT

< type:STRING, principalid:STRING, arn:STRING, accountid:STRING, invokedby:STRING, accesskeyid:STRING, userName:STRING,sessioncontext:STRUCT,sessionissuer:STRUCT< type:STRING, principalId:STRING, arn:STRING, accountId:STRING, userName:STRING>

> >, eventtime STRING,eventsource STRING,eventname STRING,awsregion STRING,sourceipaddress STRING,useragent STRING,errorcode STRING,errormessage STRING,requestparameters STRING,responseelements STRING,additionaleventdata STRING,requestid STRING,eventid STRING,resources ARRAY >, eventtype STRING,apiversion STRING,readonly STRING,recipientaccountid STRING,serviceeventdetails STRING,sharedeventid STRING,vpcendpointid STRING) ROW FORMAT SERDE 'com.amazon.emr.hive.serde.CloudTrailSerde'STORED AS INPUTFORMAT' com.amazon.emr.cloudtrail.CloudTrailInputFormat'OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION' s 3 purse Unix

How successful it is to create a table

Below we can make a simple query, the results are as follows.