Forem: Lars Kamp

How to integrate cloud data into your analytics stack with Cloud2SQL

Lars Kamp — Wed, 18 Jan 2023 04:49:08 +0000

Cloud infrastructure can be really complex, and finding resources that match certain criteria can be like searching for the proverbial needle in the haystack.

Why would you want to search for a specific resource? For example, to find:

vulnerabilities
misconfigurations
dependencies

Usually, that requires direct access to your cloud environment, and that privilege is reserved to a few select people.

But what if you could just query your cloud infrastructure with SQL? For example, this query would return the name of an AWS ELB and the name of the VPC it is connected to.

SELECT aws_elb.name, aws_vpc.name
 FROM aws_elb
 INNER JOIN link_aws_vpc_aws_elb ON aws_elb._id = link_aws_vpc_aws_elb.to_id
 INNER JOIN aws_vpc ON aws_vpc._id = link_aws_vpc_aws_elb.from_id
 LIMIT 1;

With raw cloud data, you could use your existing analytics toolchain to transform and visualize your cloud data - think Airflow, dbt, Metabase, etc. By extracting resource metadata, and loading it into a separate SQL database (say a cloud warehouse like Snowflake) for analysis - we're widening the number of people who can ask smart questions about your cloud.

The first step for that is to build the "EL" part of "ELT" - extract, load, and transform. Extracting data from the clouds like AWS and GCP can be quite cumbersome - the APIs are all fragmented, and use different data models, even within a single cloud.

And that's why the vast majority of infrastructure and security engineers always fall back to an existing tool with a UI. That tool acquires and stores data in a proprietary format, in yet another infrastructure data silo, in yet another dashboard. And then charges and arm and a leg for it.

We think that data integration for infrastructure engineers should be a commodity. Cloud data should be easily accessible and separated from the underlying infrastructure, so that data professionals like analytics engineers can work with it.

That's why we've created Cloud2SQL. Cloud2SQL is an open source tool that extracts resource metadata from your cloud (currently with support for AWS, GCP and DigitalOcean) and syncs it to a destination database. Cloud2SQL flattens that data into tables, complete with foreign keys and link tables.

The image below shows what the fields in a table for an AWS ELB look like (screenshot taken from Metabase).

A link table is a special type of table that allows you to easily find relationships between different resources.

Dependencies matter in the cloud, you need to understand how your resources are connected. The relationships among your resources are 1st class citizens, and as important as the resources themselves. Link tables capture those dependencies.

Each link table is prefixed with link_ followed by the two resource kind names. For example, a link table connecting an AWS VPC to an AWS ELB would be named link_aws_vpc_aws_elb.

Link tables only have two fields: from_id and to_id, which can be easily JOINed on.

By using link tables, you can find dependent resources without needing to know the specific details of each resource's API or how the resources are related. Much like when working with a graph you can also find resources based on the state of another resource.

All of this allows users who are familiar with SQL to easily work with the data collected by Cloud2SQL, using the analytics toolchain and apps they are already familiar. You can now build your own transformations, and write your own queries - custom to your business. We think about it as the first step to replace your "XOps" tools, which only give you 80% of the data you need to start with.

Cloud2SQL already has support for SQLite, MySQL, MariaDB, and PostgreSQL, and Snowflake, as well as Parquet columnar structure files.

To install Cloud2SQL, all you need is Python 3.9 or newer. Create a new virtual environment and install the cloud2sql[all] package:

$ pip3 install --user cloud2sql[all]

See the full installation instructions for Cloud2SQL here.

In the next few posts, we'll publish example queries and transformations. To stay informed, best is to star and follow the GitHub repo for Cloud2SQL.

How to search your AWS accounts for any resource

Lars Kamp — Tue, 23 Aug 2022 08:31:34 +0000

Retrieving information about resources you have deployed in your Amazon Web Services (AWS) infrastructure means tediously navigating the AWS Management Console or using the AWS Command Line Interface.

This approach works well in a single account setup. Yet the best practice proposed by AWS is to set up a multi-account environment to separate your workloads and users. As the number of accounts grows, navigating your infrastructure and finding resources via the Console or the CLI becomes increasingly difficult.

The number of resources in these accounts just keeps growing. Developers create resources using tools such as Terraform, CDK, or CloudFormation… or sometimes even the console or CLI.

It's not just the resources themselves, it's also the relationships between your resources that are relevant: an EBS volume is mounted to an EC2 instance running in a VPC and reachable via an ALB load balancer, for example.

So how can you see everything that is running in your cloud, including the dependencies between your resources?

Graph-based Search

We created Resoto to allow the user to effortlessly search resources and automate workflows. Resoto gathers data about your infrastructure and builds a directed acyclic graph, where resources are vertices and their relationships/dependencies edges.

This graph is what makes Resoto so powerful. But we also needed a way to allow users to query this data.

Graph data is not relational, so SQL was not a good fit. And existing graph query languages like Cypher, Gremlin, or GSQL have steep learning curves and are unnecessarily complex for this use case.

And so we developed our own search syntax tailored specifically to Resoto. The Resoto Shell allows you to interact with your Resoto installation. In particular, it provides a search command.

Example: Search for EC2 instances

Let's try searching for all available EC2 instances. is() will match a specific or abstract type in a polymorphic fashion, checking all types and subtypes of the provided type.

The instance_cores filter will limit results to only those instances with more than two cores. The query below will automagically search your entire infrastructure, regardless of account or region!

search is(aws_ec2_instance) and instance_cores > 2

and here the (abbreviated) result:

id=i-a..., name=crmsec, age=2y2M, account=dev, region=us-east-1
id=i-0..., name=airgap, age=2M, account=staging, region=eu-central-1
id=i-0..., name=flixer, age=1M3w, account=sales, region=us-west-2

The query found three instances in three accounts and three regions. The default output is a condensed list view, but it is also possible to get all collected properties of any resource using the dump command:

search is(aws_ec2_instance) and instance_cores > 2 limit 1 | dump

In the case of EC2, these properties for example are the number of cores, memory and the actual instance type and its status:

reported:
  kind: aws_ec2_instance
  id: i-a...
  tags:
    aws:cloudformation:stack-name: lk-build-server
    aws:cloudformation:stack-id: arn:aws:cloudformation:...
    owner: team-proto
  name: LKbuild
  instance_cores: 4
  instance_memory: 16
  instance_type: t3.xlarge
  instance_status: stopped
  age: 1y10M

We can refine and group the results. Let's group our instances by instance_type using the count command:

search is(aws_ec2_instance) and instance_cores > 2 | count instance_type

t3.2xlarge: 1
m5.4xlarge: 15
total matched: 16
total unmatched: 0

The search returns sixteen EC2 instances, including fifteen m5 and one t3 xlarge.

Using graph search to understand dependencies

Now, let's say we want to find all ELB load balancers attached to the EC2 instances returned above. We first need to understand Resoto's graph data structure to tackle this problem.

When Resoto collects data on your cloud infrastructure, it creates an edge between ELB and EC2 instances if the ELB balances the traffic of the related EC2 instance. In the image below, you can see the how the graph captures the entire set of dependencies in the account:

search is(aws_ec2_instance) and instance_cores > 2 --> is(aws_elb)

name=a5..., age=1y1M, account=sales, region=eu-central-1
name=a3..., age=6M2w, account=staging, region=us-west-2

The -->arrow will take all matching EC2 instances and walk the graph "outbound," moving precisely one step. The list of matching items is not limited only to ELB load balancers, so with is(aws_elb) we filter the list again to return only ELB results.

It's also possible to reverse the last query to output all EC2 instances behind an ELB:

search is(aws_elb) <-- is(aws_ec2_instance) and instance_cores > 2

id=i-0..., name=airgap, age=2M, account=staging, region=eu-central-1
id=i-0..., name=flixer, age=1M3w, account=sales, region=us-west-2

The arrow is now mirrored and traverses the graph "inbound," walking edges in the opposite direction.

Use cases

Graph-based search becomes useful when you're trying to solve for problems that require understanding of how resources are connected to each other.

An example is the "blast radius" of a resource.

When you're looking at cleaning up an EC2 instance, what other resources are you taking down as well? Or, the other way around, what resources do you need to clean up first before you want to clean up an unused EC2 instance?

Try it yourself!

These examples only scratch the surface of Resoto's search syntax. While this post is AWS specific, we also support GCP and DigitalOcean.

Resoto is open source, self-hosted and free to use! Check out our documentation and give Resoto a spin!

This post was originally published by my colleague Matthias Veit at https://resoto.com/blog/2022/02/04/resoto-search-101 on February 4, 2022.

Cloud Inventory: The High Interest Credit Card of Technical Debt

Lars Kamp — Sat, 06 Aug 2022 10:35:59 +0000

Does the title sound familiar? Probably yes, because ... 👇

... it was the title of a 2014 Google paper, except the paper was about Machine Learning.

But the title also easily applies to today's cloud-native infrastructure.

👀 Today, developers face pressure to ship new products and services.

As a result, they run lots of experiments. They adopt new cloud services that drive forward innovation, combined with accelerated deployment through infrastructure-as-code and CI/CD pipelines.

😱 The flip side of that innovation is that companies now have an inventory problem

👉 It's much easier to deploy new resources than figuring out which ones are running and why. The result is a growing number of resources that run in your cloud.

It's a new type of technical debt, where you lose track of the assets running in your infrastructure and how they relate to your business.

Ward Cunningham first defined the metaphor of technical debt in 1992.

💸 Just like financial debt, inventory debt compounds.

💰With modern cloud-native infrastructure, it's remarkably easy to incur massive recurring cloud spend - without understanding what you're actually spending it on.

A Cloud Asset Inventory has a lot of the answers. It's also a forward-looking tool that allows platform teams to stay in control while giving developers liberal permissions.

In a post "What is Cloud Asset Inventory?", I summarize the challenges that come along with adoption of cloud-native infrastructure, and how a cloud asset inventory is a strategic tool to

📉 pay off you inventory debt,

🚀 increase development velocity, and

📈 grow infrastructure's contribution to profitability.

💪 ✌️ 👌

cloud #infrastructure #shiftleft