nathanwallace

100% - automation assists us to stay on top of it. But, human connection is the key! (In hindsight, our subhead text "automation with a human touch" got it the wrong way around.)

Use SQL to run shell commands against any host - local or remote, Linux or Windows, through proxies & bastions. Aggregates results from multiple hosts.

Steampipe - https://steampipe.io

exec plugin - https://hub.steampipe.io/plugins/turbot/exec

It's a Postgres FDW - https://www.postgresql.org/docs/14/ddl-foreign-data.html

Uses Terraform provisioner code - https://developer.hashicorp.com/terraform/language/resources...

See also osquery, which runs on the host & has more structure - https://www.osquery.io/

In a similar vein, Steampipe [1] has a Google Sheets plugin [2]. It uses Postgres Foreign Data Wrappers instead of SQLite as the virtual table engine. (Disclaimer: I'm a lead on this open source project.)

1 - https://github.com/turbot/steampipe 2 - https://github.com/turbot/steampipe-plugin-googlesheets

Steampipe runs an embedded Postgres database [1]. One approach would be to connect that Postgres database with other databases using the usual (advanced) techniques like Foreign Data Wrappers [2]? With that connection in place, joins would work as normal.

1 - https://steampipe.io/docs/develop/overview 2 - https://wiki.postgresql.org/wiki/Foreign_data_wrappers

Correct - it's just Postgres, so all your favorite clients / tools work as usual. We have docs for a number of example integrations [1] that work with Steampipe CLI (or cloud).

1 - https://steampipe.io/docs/cloud/integrations/overview

Readers who like SQL may also enjoy Steampipe [1], an open source tool to live query 99+ services with SQL (e.g. AWS, GitHub, CSV, Kubernetes, etc). It uses Postgres Foreign Data Wrappers under the hood and supports joins etc across the services. (Disclaimer - I'm a lead on the project.)

1 - https://github.com/turbot/steampipe

Graph DB == connect the dots. Relational DB == join the dots. :-)

Steampipe [1] is an open source project [2] to live query all your cloud resources with SQL (via Postgres FDWs). It includes a dashboards as code layer written in HCL + SQL. We recently added support for relationship graphs & visualizations with hundreds of open source dashboards focused on cybersecurity [3].

In our experience, the hard part of these graphs was understanding which relationships / vectors are important to highlight and making them simple enough to browse in that way.

1 - https://steampipe.io 2 - https://github.com/turbot/steampipe 3 - https://steampipe.io/blog/release-0-18-0

Excited to see Steampipe shared here - thanks zJayv! I'm a lead on the project, so sharing some quick info below and happy to answer any questions.

Steampipe [1] is open source and uses Postgres foreign data wrappers under the hood [2]. We have 90+ plugins to SQL query AWS, GitHub, Slack, Kubernetes, etc [3]. Mods (written in HCL) provide dashboards as code and automated security & compliance benchmarks [3]. We'd love your help & feedback!

1 - https://steampipe.io 2 - https://steampipe.io/docs/develop/overview 3 - https://hub.steampipe.io/

Good idea - thanks, love the feedback :-)

Thanks atonse for the shout out!

Steampipe [1] is an open source CLI to query your cloud resources (e.g. GitHub, AWS, Splunk, etc) with SQL. The GitHub plugin has 44 tables to query [2].

The "GitHub Sherlock" mod includes 34 automated controls for organization, repo and issue best practices. The "GitHub Compliance" mod has 35 automated controls for supply chain security. Mods are written in HCL + SQL. [3]

1 - https://steampipe.io 2 - https://hub.steampipe.io/plugins/turbot/github 3 - https://hub.steampipe.io/mods?q=github

Sounds interesting - let us know how you go!

Please open GitHub issues (or PRs) for any tables you'd like to added! If there is a cost API then we'd definitely like to have a table to query it. In the meantime, you may find the "Thrifty" mods useful - they have many prebuilt queries to look for cost saving opportunities in AWS, Azure, GCP and DigitalOcean - https://hub.steampipe.io/mods?q=thrifty

hmmm ... I just tested in Incognito mode and it's working for me. Could you please try again? We are hiring for multiple roles!

100% - works great with Grafana, Metabase, Tableau, etc because it’s just Postgres :-)

If you are into the idea of "dashboards as code", you may also enjoy our HCL+SQL approach in mods [1] - runs in your cli, open source, composable, great DX [2]

[1] - https://hub.steampipe.io/mods [2] - https://steampipe.io/blog/dashboards-as-code

Awesome - thanks for the shout out in golangweekly!

We have work in progress to support go-getter [1] allowing use of files from many different sources. There is a WIP branch [2] building that into the Steampipe Plugin SDK, so it can be immediately enabled for all our file based plugins (CSV, Terraform, etc) [3]. Watch this space and hopefully you can help us test!

1 - https://github.com/hashicorp/go-getter 2 - https://github.com/turbot/steampipe-plugin-sdk/compare/main.... 3 - https://github.com/turbot/steampipe-plugin-terraform/tree/ad...

Postgres FDWs are extensions installed in Postgres. Multicorn provides an easy way to implement Postgres FDW extensions in Python, leading to many FDW implementations. Steampipe runs as a Postgres FDW extension with a pluggable interface to easily build and manage data sources. Multicorn-based FDWs might be installed as part of your existing Postgres server, while Steampipe is optimized to run more standalone / alongside your existing Postgres server.

Steampipe computes the SQL part in a single Postgres instance (not distributed) - we've not found this to be a significant limit (so far). A difference in this case is that the query is really combining and organizing results that may been processed at the source. In 2022 most services are API first and offer great searching and filtering, so we can use that to do much of the query processing in many cases.

I feel your pain - but Steampipe won't solve this (yet). We have AWS Thrifty [1] to scan your env for cost savings and other tables to query your spend [2], but haven't tackled the 1.8GB of pricing JSON data into a table yet.

1 - https://hub.steampipe.io/mods/turbot/aws_thrifty 2 - https://hub.steampipe.io/plugins/turbot/aws/tables?filter=co...

The premise of virtual tables (Postgres FDW) is to not store the data but instead query it from the original source. So, the primary performance challenge is API throttling. Steampipe optimizes these API calls with smart caching, massive parallelization through goroutines and calling the minimum set of APIs to hydrate the exact columns requested. For example, "select instance_id from aws_ec2_instance" will do 2 API calls get 100 instances in 2 pages, while "select instance_id, tags from aws_ec2_instance" would do 2 calls (instance paging) + 100 tag API calls (one per instance). We've also implemented support for qualifiers (i.e. where clauses) so API calls can be reduced even further - e.g. get 1 EC2 instance without pagination etc.

The Postgres planner is not really optimized for foreign tables, but we can give it hints to indicate optimal paths. We've gradually ironed out many cases here in our FDW implementation particularly for joins etc.

If you can tolerate my Aussie accent, I explain many of the details in this Yow Data talk - https://www.youtube.com/watch?v=2BNzIU5SFaw

While it's extensible, the primary use case for Steampipe so far is to query DevOps data (e.g. AWS, GitHub, Kubernetes, Terraform files, etc) - so often they are working within the Steampipe DB itself and combining with CSV data etc.

But, because it's just Postgres, it can be integrated into many different data infrastructure strategies. Many users query Steampipe from standard BI tools, others use it to extract data into S3, it has also been connected with BigQuery - https://briansuk.medium.com/connecting-steampipe-with-google...

As opposed to a lake or a warehouse, we think of it as a "Data Rainbow" - structured, ephemeral queries on live API data. Because it doesn't have to import the data it works uniquely well for small, wide data and joining large data sets (e.g. search, querying logs). I spoke about this in detail at the Yow Data conference - https://www.youtube.com/watch?v=2BNzIU5SFaw

Sorry you didn't have a smooth start there :-(. Not sure how the behavior you are describing is happening. We'd be happy to help get things going if you want to open an issue [1] or discuss on Slack [2].

1 - https://github.com/turbot/steampipe/issues 2 - https://steampipe.io/community/join

osquery is awesome. It was definitely part of our inspiration!

Python and boto3 are very powerful in many cases for sure - glad they work well for you. No doubt that multi-region for 1k+ accounts is a large surface area, so we'd love to learn from your experience! Performance is an ongoing focus area for us, including radical improvements in memory usage and startup time in our most recent version - https://steampipe.io/blog/release-0-16-0

Yes - joins work across tables and schemas! This (toy) example connects IAM user records with Slack user data:

    select u.name, s.id, s.display_name
    from aws_iam_user as u, slack_user as s
    where u.name = s.email

Because it's just Postgres, connecting Jupyter notebooks to Steampipe via SQL works really well :-)

We see users running Steampipe on their desktop, in pipelines (e.g. to scan Terraform files) and also as a central service. See "service mode" in the CLI for a Postgres endpoint and a dashboard web server [1] or try docker / kubernetes [2]. We also have a cloud hosted option with dashboard hosting, multi-user support, etc [3].

1 - https://steampipe.io/docs/managing/service 2 - https://steampipe.io/docs/managing/containers 3 - https://steampipe.io/docs/cloud/overview

I'm biased, but writing plugins is really fun - and then you can create mods / dashboards for your users as well! Please give it a go - we have a guide [1], 84+ open source plugins to copy [2], many authors in our Slack community [3] and we're happy to get your plugin added to the Steampipe Hub [4].

1 - https://steampipe.io/docs/develop/writing-plugins 2 - https://github.com/topics/steampipe-plugin 3 - https://steampipe.io/community/join 4 - https://hub.steampipe.io/plugins

Because it's just PostgreSQL, Steampipe works out of the box with SQL clients, BI tools and other integrations [1]. Please let us know how you go!

1 - https://steampipe.io/docs/query/third-party

Glad AWS worked. Sorry you hit a bump with tailscale - we'd love feedback / contributions for that plugin [1], we just released it this month so may be edges for more complex configurations :-(

1 - https://github.com/turbot/steampipe-plugin-tailscale