I really really like Steampipe to do this kind of query: https://steampipe.io, which is essentially PostgreSQL (literally) to query many different kind of APIs, which means you have access to all PostgreSQL's SQL language can offer to request data.
* it's super easy to request multiple Kubernetes clusters transparently: define one Steampipe "connection" for each of your clusters + define an "aggregator" connection that aggregates all of them, then query the "aggregator" connection. You will get a "context" column that indicates which Kubernetes cluster the row came from.
* it's relatively fast in my experience, even for large result sets. It's also possible to configure a caching mechanism inside Steampipe to speed up your queries
* it also understands custom resource definitions, although you need to help Steampipe a bit (explained here: https://hub.steampipe.io/plugins/turbot/kubernetes/tables/ku...)
Last but not least: you can of course join multiple "plugins" together. I used it a couple of times to join content exposed only in GCP with content from Kubernetes, that was quite useful.
The things I don't like so much but can be lived with:
* Several columns are just exposed a plain JSON fields ; you need to get familiar with PostgreSQL JSON operators to get something useful. There's a page in Steampipe's doc to explain how to use them better.
* Be familiar also with PostgreSQL's common table expressions: there are not so difficult to use but makes the SQL code much easier to read
* It's SQL, so you have to know which columns you want to pick before selecting the table they come from ; not ideal from autocompletion
* the Steampipe "psql" client is good, but sometimes a bit counter intuitive ; I don't have specific examples but I have the feeling it behaves slightly differently than other CLI client I used.
All in all: I think Steampipe is a cool tool to know about, for Kubernetes but also other API systems.
Steampipe project lead here - thanks for the shout out & feedback multani!
I agree with your comment about JSON columns being more difficult to work with at times. On balance, we've found that approach more robust than creating new columns (names and formats) that effectively become Steampipe specific.
Our built-in SQL client is convenient, but it can definitely be better to run Steampipe in service mode and use any Postgres compatible SQL client you prefer [1].
You might also enjoy our open source mods for compliance scanning [2] and visualizing clusters [3]. They are Powerpipe [4] dashboards as code written in HCL + SQL that query Steampipe.
They have a Kubernetes plugin at https://hub.steampipe.io/plugins/turbot/kubernetes and there are a couple of things I really like:
* it's super easy to request multiple Kubernetes clusters transparently: define one Steampipe "connection" for each of your clusters + define an "aggregator" connection that aggregates all of them, then query the "aggregator" connection. You will get a "context" column that indicates which Kubernetes cluster the row came from. * it's relatively fast in my experience, even for large result sets. It's also possible to configure a caching mechanism inside Steampipe to speed up your queries * it also understands custom resource definitions, although you need to help Steampipe a bit (explained here: https://hub.steampipe.io/plugins/turbot/kubernetes/tables/ku...)
Last but not least: you can of course join multiple "plugins" together. I used it a couple of times to join content exposed only in GCP with content from Kubernetes, that was quite useful.
The things I don't like so much but can be lived with:
* Several columns are just exposed a plain JSON fields ; you need to get familiar with PostgreSQL JSON operators to get something useful. There's a page in Steampipe's doc to explain how to use them better. * Be familiar also with PostgreSQL's common table expressions: there are not so difficult to use but makes the SQL code much easier to read * It's SQL, so you have to know which columns you want to pick before selecting the table they come from ; not ideal from autocompletion * the Steampipe "psql" client is good, but sometimes a bit counter intuitive ; I don't have specific examples but I have the feeling it behaves slightly differently than other CLI client I used.
All in all: I think Steampipe is a cool tool to know about, for Kubernetes but also other API systems.