Thus, we can envision the Code plugin wrapping every secret checker and authentication tester in its own span, to help users diagnose abnormally slow queries. Which regex is taking too much time? With which input data? Or is it a login call to a remote API? Is the remote service silently throttling login attempts, since they are being made too frequently and with already-expired keys?

So, we have verified that custom metrics are supported by the Steampipe SDK. You can register new metrics, and they will be exported to the OpenTelemetry collector.This should open up huge opportunities for custom plugin monitoring. With no changes required to the Steampipe core, any plugin author can expose whatever metrics make sense for the specific plugin, usecases and remote service that the plugin interfaces with.

Metrics are useful for both Ops (i.e., the people that manage the servers, deploy the applications, and get pestered in the middle of the night when the service is down) and Business (i.e., the people that stare at dashboards, can be heard muttering “KPIs, KPIs” under their breaths, dream of high conversion rates and have nightmares of high churn rates).

So, to summarize: Steampipe caches query results and attempts to serve them before calling the remote API. The cache is aware of both row and column subsets:If you request a subset of columns (such as calling SELECT * and later SELECT one_column), while keeping the WHERE conditions constant, the cache will look for the original results (which have more columns) and then return only the required columns.If you request a subset of rows/records (such as calling SELECT * and later SELECT * WHERE some_condition), the results will be served from the cache too.A combination of the above also works.

And now, for a small story. While browsing the Interwebs for solutions to this problem, I found this thread in the Postgres mailing list. The title seems promising: “the results of a nested query/join not being passed as qual to the outer query”. That’s precisely our problem. The first post starts with “I am developing an FDW which allows various data sources to act as virtual tables, allowing various different APIs to be queried using a consistent SQL interface.” “We often have virtual tables where a list operation is not viable/possible without providing quals.” “The problem we have is that the results of nested subqueries/joins are not being passed as quals to the outer query.” That’s a direct description of the problem.

If you need more, grab your BI tool of choice and plug it into Steampipe. That, I believe, is the secret superpower of Steampipe: by making a true PostgreSQL server, you inherit the entire SQL ecosystem.

Hopefully, it will get merged soon!

We saw what happens when you don’t echo your search query into the output table (namely, you get no data returned), why it happens and how to prevent it (namely, use transform.FromQual or do it manually, but the built-in mechanism promotes laziness, which is good)

Later, I reviewed the Github plugin for Steampipe, that also implements a search for repositories. It exposes a table called github_search_repository, in which you can fill a query column. That column is defined in the code with Transform: transform.FromQual("query"), which takes its value from the incoming query and reflects it back on the result table. That’s a really clean way of handling precisely that requirement, and I assume that the developers added it just for that. It’s not mentioned in the docs (at least, not that I could find it, nor Google), but some example repositories (or the code autocompletion, if using an IDE that supports it) will reveal to you the hidden secret of the FromQual transform. Thus, when you review the plugin code, you’ll see that there is no Wallet field in the response struct, since it’s not required: the plugin scaffolding will add it.

So, KeyColumns cannot be used for fuzzy-searching with LIKE. That’s a shame, since it would be nice to be able to write the following queries:

Steampipe, on the other hand, takes APIs that were developed with no idea of mutual cooperation, and makes them… mutually cooperate.

glob

Also, give up now on the idea of trying to get everything tagged. If you can set a baseline of 80% or better, you’re outperforming almost everyone. And, in most cases, you’re not an organization where you’re required to spend thousands of dollars to allocate that last 20 cents. Lastly, never ever expect people to tag things by hand. They won’t. If you want decent tag coverage, tagging absolutely must be automated.

With Tag Editor, you build a query to find resources in one or more AWS Regions that are available for tagging. You can choose up to 20 individual resource types, or build a query on All resource types. Your query can include resources that already have tags, or resources that have no tags.

Including id and resource in the result set means we can query multiple services using some sql magic

Steampipe's standardized schemas allow you to approach tagging queries with common patterns across resources and clouds.

Automation and proactive tag management are important, but are not always effective. Many customers also employ reactive tag governance approaches to identify resources that are not properly tagged and correct them.

Because the policies are standardized, we can now use the policy_std columns to evaluate and analyze our IAM policies without having to convert case or account for optional use of scalar values vs array values!

AWS IAM policies can have multiple representations that mean the same thing. Converting to a standard, machine-readable format makes them easier to search, analyze, join with other data and to calculate differences.