matwerber1

Amazon Athena SQL to create table for FOCUS 1.2 Cost Data Export

The AWS Billing & Cost Management (BCM) service provides AWS Data Exports, a feature that allows you to export cost data in one of several schemas (aka tables, such as CUR 2.0 vs. FOCUS 1.2) to an S3 bucket and prefix of your choice.

This gist provides an Amazon Athena SQL DDL statement to create an external table in the AWS Glue catalog so you can run SQL queries over a BCM data export of the FOCUS 1.2 table schema.

Note on AWS Data Export configuration variations

Beyond just selecting the type of BCM export (e.g. CUR 2.0 vs. FOCUS 1.2), there are a number of other options you can choose from that will impact which columns are present and whether and how the objects written to your S3 bucket are partitioned by path.

matwerber1

-- == 90% APPLICABLE TO ANY CLOUDTRAIL
-- == It's the unnested fields like api.resquest.data 
-- == and api.request.data that are contextual, depend on the service that called them

SELECT DISTINCT
    -- ===== Scalar top-level columns =====

    date_format(from_unixtime((time - (time % 600000)) / 1000) AT TIME ZONE
   'America/Los_Angeles', '%Y-%m-%d %h:%i %p') AS event_time_10min_local,

matwerber1

Terraform to create an AWS Systems Manager (SSM) RunCommand that can be used to configure an EC2 Windows instance as a bitbucket pipeline runner.

The installation uses WinSW to wrap the runner process as a Windows service and configures it to restart on failure / reboots as described in https://jira.atlassian.com/browse/BCLOUD-21928, also based on https://support.atlassian.com/bitbucket-cloud/docs/set-up-runners-for-windows/

This is for a workspace-level runner.

A repository-level runner requires an extra parameter in the XML file and the SSM document.

Not all of the install scripts are included as they're pretty straightforward (e.g. choco install xyz, apart from the initial install of chocolately (see powershell-scripts/winEc2-choco-install-git.ps1) and disabling of pagefile+swapfile (see powershell-scripts/winEc2-disable-pagefile-and-swapfile.ps1).

matwerber1

DDL for Athena Table

This could be improved, but does the trick for now. This is for a report configured as "daily" with resource IDs:

CREATE EXTERNAL TABLE IF NOT EXISTS `org_data`.`curv2` (
  `discount_bundled_discount` string,
  `discount_total_discount` string,
  `identity_line_item_id` string,
  `identity_time_interval` string,

matwerber1

#! /bin/bash -i
set -e

# Constants
PROFILE_NICKNAME_FILE=".awsprofile.env"
AWS_CONFIG_FILE="$AWS_CONFIG_FILE"

# Variables
original_request="$1"
requested_profile=""

matwerber1

version: "3.8"

services:
  AwsCLI:
    image: public.ecr.aws/aws-cli/aws-cli
    container_name: aws-cli
    platform: linux/x86_64
    volumes:
      - ~/.aws/:~/.aws/ # Mount local credentials into container to use AWS CLI/SDK
    environment:

matwerber1

CloudTrail Lake Snippets

CloudTrail Lake supports Presto SQL. This doc focuses less on SQL itself and more on snippets specific to CloudTrail Lake schema.

Example Query

This query ties together key concepts and, in one shape or another, is often the starting point for my analyses of events:

SELECT

matwerber1

# Example command
eksctl create iamidentitymapping \
  --cluster YOUR_CLUSTER_NAME \
  --arn arn:aws:iam::999999999999:role/AWSReservedSSO_YOUR-ROLE_NAME_xxxxxxxxxxxc \
  --username cluster-admin \
  --group system:masters

matwerber1

/* eslint-disable no-prototype-builtins */
var express = require('express');
var ip = require("ip");
var fetchTimeout = require('fetch-timeout');

/*
This app creates a simple HTTP listener and responds to GET / requests by
displaying basic info about the container (e.g. IP address) and, optionally, 
ECS metadata if the container is running on EC2 or Fargate via ECS.

matwerber1

astroid==2.15.6
autoflake==2.2.1
autopep8==2.0.4
boto3==1.28.52
botocore==1.31.52
certifi==2023.7.22
chardet==5.2.0
charset-normalizer==3.2.0
contourpy==1.1.1
coverage==7.3.1