Transform AWS CloudTrail data using AWS Data Wrangler
Tags: aws, bigdata, cloud, dataengineering
Published: 2020-09-20 18:27:01

<!-- wp:paragraph -->
<p>AWS CloudTrail service captures actions taken by an IAM user, IAM role, APIs, SDKs and other AWS services. By default, AWS CloudTrail is enabled in your AWS account. You can create "trail" to record ongoing events which will be delivered in JSON format to an Amazon S3 Bucket of your choice.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"left","id":4235,"width":161,"height":194,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="alignleft size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-1.03.45-pm.png?w=218" alt="" class="wp-image-4235" width="161" height="194"/><figcaption>CloudTrail Dashboard</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:image {"align":"left","id":4236,"width":378,"height":204,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="alignleft size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-1.08.54-pm.png?w=1024" alt="" class="wp-image-4236" width="378" height="204"/><figcaption>Create Trail</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:image {"align":"center","id":4238,"width":491,"height":389,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="aligncenter size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-1.13.30-pm.png?w=1024" alt="" class="wp-image-4238" width="491" height="389"/><figcaption>Choose events to capture</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>You can configure the trail to log read-write, read-only, write-only data events for all current and future S3 buckets</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"left","id":4242,"width":354,"height":147,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="alignleft size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-1.32.56-pm-1.png?w=846" alt="" class="wp-image-4242" width="354" height="147"/></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Also, you have the option to log data events for Lambda functions. You can select all region, all functions or specify any specific Lambda ARN or region.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>The trail creates small, mostly KB size gzipped json files in the S3 Bucket.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":4246,"width":580,"height":179,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="aligncenter size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-1.22.46-pm.png?w=1024" alt="" class="wp-image-4246" width="580" height="179"/><figcaption>Trail Log files in S3 Bucket</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>You can select the file and use "Select from" tab to view the content of the file.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"align":"center","id":4302,"width":523,"height":494,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="aligncenter size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-17-at-6.20.28-pm.png?w=1024" alt="" class="wp-image-4302" width="523" height="494"/><figcaption>Select from S3 file</figcaption></figure></div>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Below is an example of "PutObject" event to S3 bucket.</p>
<!-- /wp:paragraph -->

<!-- wp:preformatted -->
<pre class="wp-block-preformatted">{
    "eventVersion": "1.07",
    "userIdentity": {
        "type": "AWSService",
        "invokedBy": "s3.amazonaws.com"
    },
    "eventTime": "2020-09-12T23:53:22Z",
    "eventSource": "s3.amazonaws.com",
    "eventName": "PutObject",
    "awsRegion": "us-east-1",
    "sourceIPAddress": "s3.amazonaws.com",
    "userAgent": "s3.amazonaws.com",
    "requestParameters": {
        "bucketName": "my-data-bucket",
        "Host": "s3.us-east-1.amazonaws.com",
        "key": "mydatabase/mytable/data-content.snappy.parquet"
    },
    "responseElements": null,
    "additionalEventData": {
        "SignatureVersion": "SigV4",
        "CipherSuite": "ECDHE-RSA-AES128-SHA",
        "bytesTransferredIn": 107886,
        "AuthenticationMethod": "AuthHeader",
        "x-amz-id-2": "Dg9gelyiPojDT00UJ+CI7MmmEyUhPRe1EAUtzQSs3kJAZ8JxMe+2IQ4f6wT2Kpd+Czih1Dc2SI8=",
        "bytesTransferredOut": 0
    },
    "requestID": "29C76F4BC75743BF",
    "eventID": "6973f9b1-1a7d-46d4-a48f-f2d91c80b2d3",
    "readOnly": false,
    "resources": [
        {
            "type": "AWS::S3::Object",
            "ARN": "arn:aws:s3:::my-data-bucket/mydatabase/mytable/data-content.snappy.parquet"
        },
        {
            "accountId": "xxxxxxxxxxxx",
            "type": "AWS::S3::Bucket",
            "ARN": "arn:aws:s3:::my-data-bucket"
        }
    ],
    "eventType": "AwsApiCall",
    "managementEvent": false,
    "recipientAccountId": "xxxxxxxxxxxx",
    "sharedEventID": "eb37214b-623b-43e6-876b-7088c7d0e0ee",
    "vpcEndpointId": "vpce-xxxxxxx",
    "eventCategory": "Data"
}</pre>
<!-- /wp:preformatted -->

<!-- wp:paragraph -->
<p>CloudTrail provides a useful feature under Event history to create Athena table over the trail's Amazon S3 bucket which you can use to query the data using standard SQL.</p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4293,"sizeSlug":"large","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-17-at-5.31.33-pm.png?w=1024" alt="" class="wp-image-4293"/></figure>
<!-- /wp:image -->

<!-- wp:image {"id":4256,"sizeSlug":"large","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-2.18.23-pm.png?w=824" alt="" class="wp-image-4256"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>Now, depending on the duration and events captured, CloudTrail would create lots of small files in S3, which can impact execution time, when  queried from Athena. </p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>Moving ahead, I will show you how you can use <a rel="noreferrer noopener" href="https://aws-data-wrangler.readthedocs.io/en/latest/what.html" target="_blank">AWS Data Wrangler</a> and Pandas to perform the following:</p>
<!-- /wp:paragraph -->

<!-- wp:list {"ordered":true} -->
<ol><li>Query data from Athena into Pandas dataframe using AWS Data Wrangler.</li><li>Transform eventtime string datatype to datetime datatype.</li><li>Extract and add year, month, and day columns from eventtime to dataframe.</li><li>Write dataframe to S3 in Parquet format with hive partition using AWS Data Wrangler.</li><li>Along with writing the dataframe, how you can create the table in Glue catalog using AWS Data Wrangler.</li></ol>
<!-- /wp:list -->

<!-- wp:paragraph -->
<p>For this example, I have setup a Sagemaker Notebook with <a rel="noreferrer noopener" href="https://aws-data-wrangler.readthedocs.io/en/latest/install.html" target="_blank">Lifecycle configuration</a>. Once you have the notebook open, you can use conda_python3 kernel to work using AWS Data Wrangler.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>Import the required libraries</u></span></p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>import awswrangler as wr
import pandas as pd
pd.set_option('display.width', None)
pd.set_option('display.max_columns', None)
pd.set_option('display.notebook_repr_html', True)
pd.set_option('display.max_rows', None)</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>Python function to execute the SQL in Athena using AWS Data Wrangler</u></span></p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>def execute_sql(sql, database, ctas=False):
    return wr.athena.read_sql_query(sql, database, ctas_approach=ctas)</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>SQL query to get details related to S3 Events</u></span></p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>s3ObjectSql = """
SELECT 
    useridentity.sessioncontext.sessionissuer.username as username,
    useridentity.sessioncontext.sessionissuer.type as type,
    useridentity.principalid as principalid,
    useridentity.invokedby as invokedby,
    eventname as event_name,
    eventtime,
    eventsource as event_source,
    awsregion as aws_region,
    sourceipaddress,
    eventtype as event_type,
    readonly as read_only,
    requestparameters
FROM cloudtrail_logs_cloudtrail_logs_traillogs
WHERE eventname in ('ListObjects', 'PutObject', 'GetObject') and eventtime > '2020-08-23'
"""</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>Execute the sql and have results in Pandas dataframe</u></span></p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>data = execute_sql(sql=s3GObjectSql, database='default')</code></pre>
<!-- /wp:code -->

<!-- wp:image {"id":4264,"sizeSlug":"large","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-2.40.38-pm.png?w=384" alt="" class="wp-image-4264"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>Find unique username</u></span>(just for fun)</p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>data&#91;'username'].value_counts()</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p>If you observe, eventtime column is "string" datatype so performing any date transformations will be difficult. So here we will create a new column with datetime datatype and drop eventtime.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>String to Datetime conversion for eventtime column</u></span></p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>data&#91;'event_time'] = pd.to_datetime(data&#91;'eventtime'], errors='coerce')</code></pre>
<!-- /wp:code -->

<!-- wp:group -->
<div class="wp-block-group"><div class="wp-block-group__inner-container"><!-- wp:image {"align":"left","id":4267,"width":157,"height":338,"sizeSlug":"large","className":"is-style-default"} -->
<div class="wp-block-image is-style-default"><figure class="alignleft size-large is-resized"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-2.45.16-pm.png" alt="" class="wp-image-4267" width="157" height="338"/></figure></div>
<!-- /wp:image -->

<!-- wp:image {"id":4268,"width":134,"height":355,"sizeSlug":"large","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-resized is-style-default"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-13-at-2.45.24-pm.png" alt="" class="wp-image-4268" width="134" height="355"/></figure>
</div></div>
<!-- /wp:group -->

<!-- wp:code -->
<pre class="wp-block-code"><code>data.drop('eventtime', axis=1, inplace=True)</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p>Lets extract and add year, month, day columns from event_time column. With this change you can write the data back to S3 as Hive partitions.</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><span style="text-decoration:underline;"><u>Extract and add new fields to dataframe</u></span></p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>data&#91;'year'] = data&#91;'event_time'].dt.year
data&#91;'month'] = data&#91;'event_time'].dt.month
data&#91;'day'] = data&#91;'event_time'].dt.day</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p>Now using AWS Data Wrangler s3.to_parquet API you can write the data back to S3 partitioned by year, month, day, and in parquet format. You can also add database and table parameters to it, to write the metadata on Athena/Glue catalog. Note that the database must exists to be command to be successful.</p>
<!-- /wp:paragraph -->

<!-- wp:code -->
<pre class="wp-block-code"><code>wr.s3.to_parquet(
    df=data,
    path='s3://my-bucket/s3_access_analyzer/cloudtrail/',
    dataset=True,
    partition_cols=&#91;'year', 'month', 'day'],
    database='default',  # Athena/Glue database
    table='cloudtrail' # Athena/Glue table
)</code></pre>
<!-- /wp:code -->

<!-- wp:paragraph -->
<p>You can query the Athena to view the results </p>
<!-- /wp:paragraph -->

<!-- wp:image {"id":4296,"sizeSlug":"large","className":"is-style-default"} -->
<figure class="wp-block-image size-large is-style-default"><img src="https://aprakash.files.wordpress.com/2020/09/screen-shot-2020-09-17-at-5.56.32-pm.png?w=544" alt="" class="wp-image-4296"/></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p>The query took just 1.74 seconds to complete with 0 KB of data scanned. Now why 0 KB? Well, I will leave that for you to think and answer :)</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>To conclude, with AWS Data Wrangler you can easily and efficiently perform extract, transform and load (ETL) task as shown above. It is well integrated with other AWS services and is actively being updated with new features and enhancements. </p>
<!-- /wp:paragraph -->