Skip to main content

4 posts tagged with "amazon web services"

View All Tags

· 3 min read

Have you been sent one of these?

[Action Required] AWS Lambda end of support for Node.js 18 [AWS Account: 824123456789][EU-CENTRAL-1]

If you are like me and manage AWS accounts with numerous Lambda functions potentially deployed across multiple regions, you need to identify affected resources, in this case, Lambda node runtimes, which will be discontinued later this year.  

With stackql this task is easy...

  1. Open AWS cloud shell in your AWS account (any region - it doesn't matter)
  2. Download stackql
curl -L https://bit.ly/stackql-zip -O && unzip stackql-zip
  1. Open an authenticated stackql command shell
sh stackql-aws-cloud-shell.sh
  1. Run some analytic queries using stackql; here are some examples...

🔍 List all functions and runtimes across regions​

Run a stackql query to get the details about functions, runtimes, etc, deployed at any given time across one or more AWS regions.  You can include all 25 AWS regions; each query will be performed asynchronously - speeding up the results.

select 
  function_name, 
  region,
  runtime 
FROM aws.lambda.functions 
WHERE region IN ('us-east-1', 'eu-west-1');

📊 Group by runtime and region​

Perform an analytic query like a group by aggregate query such as...

select 
  runtime, 
  region, 
  count(*) as num_functions 
FROM aws.lambda.functions 
WHERE region IN ('us-east-1', 'eu-west-1', 'ap-southeast-2')
GROUP BY runtime, region;
tip

You can easily visualise this data using a notebook; see stackql-codespaces-notebook or stackql-jupyter-demo.

Using StackQL you can:

  • Quickly spot functions running on runtimes like nodejs18.x that are approaching end of support.
  • Plan your upgrades region-by-region with confidence.

⭐ us on GitHub and join our community!

· 2 min read
info

To get started with the aws provider for stackql, pull the provider from the registry as follows:  

registry pull aws;

for more detailed provider documentation, see here.

Happy New Year 🎉. The latest AWS provider for StackQL is now available.  The StackQL AWS Provider by the numbers:

  • 230 services
  • 3174 resources
  • 3917 methods

with additional new support for the following services:

  • amazonmq - Managed message broker service for Apache ActiveMQ and RabbitMQ that simplifies setup and operation of open-source message brokers on AWS.
  • applicationsignals - CloudWatch Application Signals automatically provides a correlated view of application performance that includes real user monitoring data and canaries.
  • apptest - AWS mainframe modernization ppplication Testing
  • connectcampaignsv2 - Amazon Connect Outbound Campaigns V2
  • invoicing - Deploy and query invoice units allowing you separate AWS account costs and configures your invoice for each business entity
  • launchwizard - Easily size, configure, and deploy third party applications on AWS
  • pcaconnectorscep - AWS Private CA Connector for SCEP
  • pcs - AWS Parallel Computing Service, easily run HPC workloads at virtually any scale
  • rbin - Recycle Bin is a resource recovery feature that enables you to restore accidentally deleted snapshots and EBS-backed AMIs.
  • s3tables - Amazon S3 Tables enabling Tabular Data Storage At Scale
  • ssmquicksetup - AWS Systems Manager Quick Setup

And 150 new resources with some notable additions including:

  • aws.apigateway.domain_name_access_associations
  • aws.appconfig.deployments, aws.appconfig.deployment_strategies
  • aws.batch.job_definitions
  • aws.bedrock.flows, aws.bedrock.prompts
  • aws.chatbot.custom_actions
  • aws.cloudformation.guard_hooks, aws.cloudformation.lambda_hooks
  • aws.cloudfront.anycast_ip_lists
  • aws.cloudtrail.dashboards, aws.cloudwatch.dashboards
  • aws.codepipeline.pipelines
  • aws.cognito.user_pool_identity_providers
  • aws.ec2.security_group_vpc_associations, aws.ec2.vpc_block_public_access_exclusions, aws.ec2.vpc_block_public_access_options
  • aws.glue.crawlers, aws.glue.databases, aws.glue.jobs, aws.glue.triggers
  • aws.guardduty.malware_protection_plans
  • aws.iot.commands
  • aws.memorydb.multi_region_clusters
  • aws.rds.db_shard_groups
  • aws.redshift.integrations
  • aws.sagemaker.clusters, aws.sagemaker.endpoints
  • aws.secretsmanager.resource_policies, aws.secretsmanager.rotation_schedules, aws.secretsmanager.secret_target_attachments
  • aws.workspaces.workspaces_pools
  • aws.wisdom.ai_agents, aws.wisdom.ai_prompts, aws.wisdom.ai_guardrails, aws.wisdom.message_templates
  • and much more!

⭐ us on GitHub and join our community!

· 5 min read

AWS creates default VPCs in each region for convenience. However, these default VPCs often contain noncompliant network ACLs and security group rules that do not align with best practices for AWS Config and Security Hub. Deleting these default VPCs is beneficial, especially for regions not used by your organization or architectures that do not utilize a VPC.  

This guide demonstrates how to use StackQL to enumerate and delete all default VPCs and their associated resources in all AWS regions.  

What you need​

All you need to do is to install the pystackql package using:

pip install pystackql

Deleting resources will require a privileged AWS IAM user. You can do this from a terminal with the AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY environment variables set (and optionally AWS_SESSION_TOKEN set if you are using sts assume-role).  

You can also use the StackQL Cloud Shell Scripts, from within AWS Cloud Shell, to run authenticated StackQL queries using:

sh stackql-aws-cloud-shell.sh

How it works​

Default VPCs in AWS regions have a CIDR block of 172.31.0.0/16, before deleting anything, the program ensures it is not in use by using:

SELECT
id
FROM aws.ec2.network_interfaces
WHERE region = '{region}'
AND vpc_id = '{vpc_id}'

If any resources are using the VPC (such as EC2, RDS, ELB/ALB, VPC attached Lambda functions, etc), it will skip these VPCs.  Once determined that the VPC is not in use, all of the resources associated with the VPC must be deleted before the VPC itself can be deleted; this includes:

  • External Routes (aws.ec2.routes)
  • Internet Gateways (aws.ec2.internet_gateways)
  • NACLs (aws.ec2.network_acls)
  • Subnets (aws.ec2.subnets)
  • and then finally, the VPC (aws.ec2.vpcs)

The default route table and default security group are deleted automatically when deleting the VPC

This is done by discovering the resources using StackQL SELECT queries and deleting them using StackQL DELETE queries, like:

DELETE FROM aws.ec2.network_acls
WHERE data__Identifier = '{nacl_id}'
AND region = '{region}'

Complete code​

The following Python program uses StackQL to list and delete default VPCs and their associated resources (subnets, route tables, internet gateways, security groups, and network ACLs) across all AWS regions.

Get the complete code here
from pystackql import StackQL
stackql = StackQL()
stackql.executeStmt("REGISTRY PULL aws")

def ensure_one_or_zero(resource_list, resource_name, region):
    if len(resource_list) > 1:
        raise RuntimeError(f"ah snap! multiple default {resource_name} resources found in {region}")
    elif len(resource_list) == 0:
        print(f"/* no default {resource_name} found in {region} */\n")
        return False
    return True

regions = [
    'us-east-1', 'us-east-2', 'us-west-1', 'us-west-2',
    'eu-central-1', 'eu-central-2', 'eu-west-1', 'eu-west-2', 'eu-west-3', 'eu-north-1', 'eu-south-1',
    'ap-east-1', 'ap-south-1', 'ap-south-2', 'ap-northeast-1', 'ap-northeast-2', 'ap-northeast-3',
    'ap-southeast-1', 'ap-southeast-2', 'ap-southeast-3', 'ap-southeast-4', 'af-south-1',
    'ca-central-1',  'me-south-1', 'me-central-1', 'sa-east-1'
]

for region in regions:
    vpc_ids = stackql.execute(
        f"""
        SELECT
        vpc_id
        FROM aws.ec2.vpcs
        WHERE region = '{region}'
        AND cidr_block = '172.31.0.0/16'
        AND JSON_ARRAY_LENGTH(tags) = 0
        """
    )
    if not ensure_one_or_zero(vpc_ids, 'VPC', region): continue 
    vpc_id = vpc_ids[0]['vpc_id']
    
    # check if vpc is in use
    network_interfaces = stackql.execute(
        f"""
        SELECT
        id
        FROM aws.ec2.network_interfaces
        WHERE region = '{region}'
        AND vpc_id = '{vpc_id}'
        """
    )
    if len(network_interfaces) > 0:
        print(f"/* skipping deletion of default VPC ({vpc_id}) in {region} because it is in use */\n")
        continue
        
    print(f"/* deleting resources for default VPC ({vpc_id}) in {region} */\n")

    # get route table
    route_table_ids = stackql.execute(
        f"""
        SELECT
        route_table_id
        FROM aws.ec2.route_tables
        WHERE region = '{region}'
        AND vpc_id = '{vpc_id}'
        """
    )
    ensure_one_or_zero(route_table_ids, 'route table', region)
    route_table_id = route_table_ids[0]['route_table_id']

    # get inet gateway id
    inet_gateway_ids = stackql.execute(
        f"""
        SELECT gateway_id 
        FROM aws.ec2.routes 
        WHERE data__Identifier = '{route_table_id}|0.0.0.0/0' 
        AND region = '{region}'
        """
    )
    ensure_one_or_zero(inet_gateway_ids, 'internet gateway', region)
    inet_gateway_id = inet_gateway_ids[0]['gateway_id']

    # delete routes
    print(f"/* deleting default VPC routes in route table ({route_table_id}) in {region} */")
    print(f"""
DELETE FROM aws.ec2.routes
WHERE data__Identifier = '{route_table_id}|0.0.0.0/0'
AND region = '{region}';
    """)

    # detatch inet gateway
    print(f"/* detaching default VPC internet gateway ({inet_gateway_id}) in {region} */")
    print(f"""
DELETE FROM aws.ec2.vpc_gateway_attachments
WHERE data__Identifier = 'IGW|{vpc_id}'
AND region = '{region}';
    """)

    # delete inet gateway
    print(f"/* deleting default VPC internet gateway ({inet_gateway_id}) in {region} */")
    print(f"""
DELETE FROM aws.ec2.internet_gateways
WHERE data__Identifier = '{inet_gateway_id}'
AND region = '{region}';
    """)

    # delete nacl
    nacl_ids = stackql.execute(
        f"""
        SELECT
        id
        FROM aws.ec2.network_acls
        WHERE vpc_id = '{vpc_id}'
        AND region = '{region}'
        """
    )
    ensure_one_or_zero(nacl_ids, 'network acl', region)
    nacl_id = nacl_ids[0]['id']
    print(f"/* deleting default VPC NACL ({nacl_id}) in {region} */")
    print(f"""
DELETE FROM aws.ec2.network_acls
WHERE data__Identifier = '{nacl_id}'
AND region = '{region}';
    """)

    # delete subnets
    subnet_ids = stackql.execute(
        f"""
        SELECT
        subnet_id
        FROM aws.ec2.subnets
        WHERE vpc_id = '{vpc_id}'
        AND region = '{region}'
        """
    )
    for subnet_id in subnet_ids:
        print(f"/* deleting default VPC subnet ({subnet_id['subnet_id']}) in {region} */")
        print(f"""
DELETE FROM aws.ec2.subnets
WHERE data__Identifier = '{subnet_id['subnet_id']}'
AND region = '{region}';
        """)

    # delete vpc
    print(f"/* deleting default VPC ({vpc_id}) in {region} */")
    print(f"""
DELETE FROM aws.ec2.vpcs
WHERE data__Identifier = '{vpc_id}'
AND region = '{region}';
    """)

run the program using:

python3 delete-all-default-vpcs.py > delete_default_vpcs.iql

to generate a StackQL script you can run as a batch using stackql exec or as individual statements in the stackql shell.

Conclusion​

Deleting default VPCs can help improve your AWS security posture by removing potentially noncompliant network configurations. This program leverages StackQL to automate the process, ensuring consistency across all regions.

Let us know what you think! ⭐ us on GitHub.

· 5 min read

Pleased to announce the initial release of the AWS provider for StackQL.

StackQL allows you to query, provision, and manage cloud and SaaS resources using a simple, SQL-based framework.

The initial release of the AWS provider covers EC2, S3, and the Cloud Control API - with support for other services to be released soon. The documentation for the StackQL AWS provider is available here.

Follow the steps below to get started querying AWS in the StackQL interactive command shell:

Authenticate and Connect​

Connect to an authenticated shell using the syntax shown below: 

# AWS_SECRET_ACCESS_KEY and AWS_ACCESS_KEY_ID should be set as environment variables
AUTH="{ \"aws\": { \"type\": \"aws_signing_v4\", \"credentialsenvvar\": \"AWS_SECRET_ACCESS_KEY\", \"keyID\": \"${AWS_ACCESS_KEY_ID}\" }}"
stackql shell --auth="${AUTH}"

Download the AWS provider​

Download the AWS provider from the StackQL Provider Registry: 

REGISTRY PULL aws v0.1.3;

Explore the AWS provider​

Explore the AWS provider using StackQL metacommands (such as SHOW and DESCRIBE), for example...

Show available services​

Show the services available in the StackQL AWS provider: 

SHOW SERVICES IN aws;

Show available resources​

Show the resources available in the AWS EC2 service (filtered by a fuzzy match on instances): 

SHOW RESOURCES IN aws.ec2 LIKE '%instances%';

Show 'selectable' fields​

Show the 'selectable' fields available in a resource: 

DESCRIBE EXTENDED aws.ec2.instances;

Show operations available​

Show the available operations on a resource: 

SHOW EXTENDED METHODS IN aws.ec2.instances;

Run some queries​

Now that you've identified the available resources and fields let's run some queries!

Instances by region (across multiple regions)​

SELECT 'N. Virginia' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'us-east-1'
UNION
SELECT 'N. California' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'us-west-1'
UNION
SELECT 'Sydney' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'ap-southeast-2';

Instances grouped by instanceType​

SELECT instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'ap-southeast-2'
GROUP BY instanceType;

Instances grouped by instanceState​

SELECT instanceState, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'ap-southeast-2'
GROUP BY instanceState;

Enjoy!