~ 3 min read

How to use Steampipe to extract information for DigitalOcean

Once configured to work with DigitalOcean, Steampipe pulls metadata about resources running in the cloud which we can then query using SQL queries.


In previous posts, we have seen how to set up Steampipe and configure it to work with DigitalOcean. In this post we will see how we can run specific SQL queries to fetch the information we need.

As we have mentioned earlier, due to its data collection features and the ability to query for specific information, Steampipe can be used to perform security posture evaluation of your cloud infrastructure. However, it is important to remember that Steampipe is not a security tool but a data aggregation software. Any security inferences about missing policies, user, cloud and resource misconfigurations, service privileges, RBAC, Internet exposure, transport security etc. all have to be deduced using custom queries that you need to write.

Let’s take a look at some examples of SQL queries to use to fetch information that we need to draw possible inferences from a security point of view.

Steampipe and DigitalOcean

Here are some examples of queries you can run to find metadata information about resources in the cloud and draw specific security conclusions from them.

Inspect command to understand table structure

  1. Start Steampipe in query mode using the following command

    steampipe query
  2. Run .tables to get a list of tables for different connections. The connection name will be visible at the top of the output

    Steampipe tables

  3. To obtain column names for the tables, you can run .inspect {connection}.{table}

  4. For example, to inspect the columns of the table “digitalocean_droplet”, run .inspect digitalocean_droplet

    Steampipe inspect tables

  5. The interface has autocomplete enabled that allows you to easily navigate around

    Steampipe inspect digitalocean tables

Common queries to get you started

  • Get a list of all droplets and show id, name of the droplet, public IPv4 address, public IPv6, IDs of any backups, snapshot IDs and enabled features.

    select id,name,public_ipv4,public_ipv6,backup_ids,snapshot_ids,features from digitalocean_droplet;

    Select data from droplet for DigitalOcean

  • Get a list of all kubernetes clusters, their names and node pools

    select id,name,endpoint,node_pools from digitalocean_kubernetes_cluster;

    Select data from kubernetes clusters

  • Get a list of all load_balancers and their HTTPS/SSL redirect status

    select name,redirect_http_to_https from digitalocean_load_balancer

    Select data from load balancer

  • Get a list of domain names and their respective zone files to identify if SPF or DKIM records have been created or not

    select name, ttl, zone_file from digitalocean_domain

    Select data from digitalocean domain

  • Get a list of all databases, their names and version numbers

    select name, version from digitalocean_database

    Select data from databases