This is a plugin to run queries against etcd snapshots and raw database folders. This plugin is compatible with the key layout of Kubernetes and OpenShift.
The very basic example is listing all keys:
$ octosql "SELECT * FROM etcd.snapshot"where "etcd.snapshot" is an etcd snapshot in the current folder that was generated with etcdctl snapshot save.
Alternatively, you can supply a direct path:
$ octosql "SELECT * FROM ./etcdsnapshot/data/basic.snapshot"When you don't have the snapshot extension, you can also directly supply the plugin source via:
$ octosql "SELECT * FROM etcdsnapshot.etcddump"This is also required when loading from a "dataDir" directly:
$ octosql "SELECT * FROM etcdsnapshot. /var/lib/etcd/"Mind the space and note that the database must be closed beforehand (i.e. etcd was properly shut down).
The table schema currently looks like that:
$ octosql "SELECT * FROM etcd.snapshot" --describe
+-------------------+-----------------+------------+
| name | type | time_field |
+-------------------+-----------------+------------+
| 'apigroup' | 'NULL | String' | false |
| 'apiserverPrefix' | 'NULL | String' | false |
| 'createRevision' | 'Int' | false |
| 'key' | 'String' | false |
| 'lease' | 'Int' | false |
| 'modRevision' | 'Int' | false |
| 'name' | 'NULL | String' | false |
| 'namespace' | 'NULL | String' | false |
| 'resourceType' | 'NULL | String' | false |
| 'value' | 'String' | false |
| 'valueSize' | 'Int' | false |
| 'version' | 'Int' | false |
+-------------------+-----------------+------------+ keyis the actual key in etcd, all others can be NULL.apiserverPrefixis the prefix defined in the apiserver, for example openshift.io, kubernetes.io or registryapigroupare specified groups, eg. cloudcredential.openshift.ioresourceTypeare the usual k8s resources like "pod", "service", "deployment"namespaceis the namespace of that resourcenameis the resource namevalueis the value as a string (usually JSON in K8s/CRDs)valueSizeis the amount of bytes needed to store the valuecreateRevisionis the revision of last creation on this keymodRevisionis the revision of last modification on this keyversionis the version of the key, a deletion resets it to zero and a modification increments its valueleasecontains the lease id, if a lease is attached to that key, a value of zero means no lease
In addition to the content, you can also find meta information about that snapshot. This allows you to look into various database sizes, page usage and other otherwise hidden information in the underlying bbolt database.
$ octosql "SELECT * FROM etcd.snapshot?meta=true" --describe
+------------------------------+---------+------------+
| name | type | time_field |
+------------------------------+---------+------------+
| 'activeLeases' | 'Int' | false |
| 'averageValueSize' | 'Int' | false |
| 'avgRevisionsPerKey' | 'Float' | false |
| 'defaultQuota' | 'Int' | false |
| 'estimatedCompactionSavings' | 'Int' | false |
| 'fragmentationBytes' | 'Int' | false |
| 'fragmentationRatio' | 'Float' | false |
| 'keysWithLeases' | 'Int' | false |
| 'keysWithMultipleRevisions' | 'Int' | false |
| 'largestValueSize' | 'Int' | false |
| 'maxRevision' | 'Int' | false |
| 'minRevision' | 'Int' | false |
| 'quotaRemaining' | 'Int' | false |
| 'quotaUsagePercent' | 'Float' | false |
| 'quotaUsageRatio' | 'Float' | false |
| 'revisionRange' | 'Int' | false |
| 'size' | 'Int' | false |
| 'sizeFree' | 'Int' | false |
| 'sizeInUse' | 'Int' | false |
| 'smallestValueSize' | 'Int' | false |
| 'totalKeys' | 'Int' | false |
| 'totalRevisions' | 'Int' | false |
| 'totalValueSize' | 'Int' | false |
| 'uniqueKeys' | 'Int' | false |
+------------------------------+---------+------------+sizeis the total size of the entire database file in bytessizeInUseis the number of bytes actually used in the databasesizeFreeis the free space in the database (size - sizeInUse)fragmentationRatiois the ratio of fragmented space (sizeFree/size, between 0.0-1.0)fragmentationBytesis the total fragmented space in bytes (same as sizeFree)totalKeysis the total number of key-value pairs in the databasetotalRevisionsis the total number of unique revision numbersmaxRevisionis the highest revision number in the databaseminRevisionis the lowest revision number in the databaserevisionRangeis the difference between max and min revision numbersavgRevisionsPerKeyis the average number of revisions per unique keydefaultQuotais the default etcd storage quota (8GB)quotaUsageRatiois the ratio of current size to quota (0.0-1.0)quotaUsagePercentis the percentage of quota used (quotaUsageRatio * 100)quotaRemainingis the remaining quota space in bytestotalValueSizeis the sum of all value sizes in bytesaverageValueSizeis the average size of values in byteslargestValueSizeis the size of the largest value in bytessmallestValueSizeis the size of the smallest value in byteskeysWithMultipleRevisionsis the number of keys that have multiple revisionsuniqueKeysis the number of unique keys in the databasekeysWithLeasesis the number of keys that have leases attachedactiveLeasesis the number of unique lease IDs in useestimatedCompactionSavingsis the estimated bytes that could be saved by compaction
Awesome queries you can run against your etcd (snapshots):
$ octosql "SELECT COUNT(*) FROM etcd.snapshot"
+-------+
| count |
+-------+
| 10953 |
+-------+$ octosql "SELECT * FROM etcd.snapshot WHERE name='console'"
+----------------------------------------------------------------------------------+-----------------+-------------------------+------------------------+---------------------+-----------+
| key | apiserverPrefix | apigroup | resourceType | namespace | name |
+----------------------------------------------------------------------------------+-----------------+-------------------------+------------------------+---------------------+-----------+
| '/kubernetes.io/clusterrolebindings/console' | 'kubernetes.io' | <null> | 'clusterrolebindings' | <null> | 'console' |
| '/kubernetes.io/clusterroles/console' | 'kubernetes.io' | <null> | 'clusterroles' | <null> | 'console' |
| '/kubernetes.io/config.openshift.io/clusteroperators/console' | 'kubernetes.io' | <null> | 'config.openshift.io' | 'clusteroperators' | 'console' |
| '/kubernetes.io/deployments/openshift-console/console' | 'kubernetes.io' | <null> | 'deployments' | 'openshift-console' | 'console' |
| '/kubernetes.io/monitoring.coreos.com/servicemonitors/openshift-console/console' | 'kubernetes.io' | 'monitoring.coreos.com' | 'servicemonitors' | 'openshift-console' | 'console' |
| '/kubernetes.io/poddisruptionbudgets/openshift-console/console' | 'kubernetes.io' | <null> | 'poddisruptionbudgets' | 'openshift-console' | 'console' |
| '/kubernetes.io/rolebindings/kube-system/console' | 'kubernetes.io' | <null> | 'rolebindings' | 'kube-system' | 'console' |
| '/kubernetes.io/serviceaccounts/openshift-console/console' | 'kubernetes.io' | <null> | 'serviceaccounts' | 'openshift-console' | 'console' |
| '/kubernetes.io/services/endpoints/openshift-console/console' | 'kubernetes.io' | 'services' | 'endpoints' | 'openshift-console' | 'console' |
| '/kubernetes.io/services/specs/openshift-console/console' | 'kubernetes.io' | 'services' | 'specs' | 'openshift-console' | 'console' |
| '/openshift.io/oauth/clients/console' | 'openshift.io' | <null> | 'oauth' | 'clients' | 'console' |
| '/openshift.io/routes/openshift-console/console' | 'openshift.io' | <null> | 'routes' | 'openshift-console' | 'console' |
+----------------------------------------------------------------------------------+-----------------+-------------------------+------------------------+---------------------+-----------+$ octosql "SELECT namespace, COUNT(*) AS CNT FROM etcd.snapshot where resourceType='events' GROUP BY namespace ORDER BY CNT DESC"
+----------------------------------------------------+-----+
| namespace | CNT |
+----------------------------------------------------+-----+
| 'openshift-monitoring' | 460 |
| 'openshift-kube-apiserver-operator' | 371 |
| 'openshift-etcd-operator' | 353 |
| 'openshift-multus' | 347 |
| 'openshift-etcd' | 340 |
| 'openshift-cluster-csi-drivers' | 285 |
| 'openshift-kube-controller-manager-operator' | 278 |
| 'openshift-kube-controller-manager' | 261 |
| 'openshift-apiserver' | 234 |
| 'openshift-authentication-operator' | 227 |
| 'openshift-kube-apiserver' | 222 |
.... $ octosql "SELECT COUNT(*) AS CNT FROM etcd.snapshot where resourceType='imagestreams' ORDER BY CNT DESC"
+-----+
| CNT |
+-----+
| 60 |
+-----+$ octosql "SELECT l.key FROM etcd.snapshot l LEFT JOIN etcd_later.snapshot r ON l.key = r.key WHERE r.key IS NULL"
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| key |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| '/kubernetes.io/certificatesigningrequests/csr-2sxqs' |
| '/kubernetes.io/certificatesigningrequests/csr-2zmsm' |
| '/kubernetes.io/certificatesigningrequests/csr-66szn' |
| '/kubernetes.io/certificatesigningrequests/csr-7zpfn' |
| '/kubernetes.io/certificatesigningrequests/csr-dwg5r' |
| '/kubernetes.io/certificatesigningrequests/csr-fqfcr' |
| '/kubernetes.io/certificatesigningrequests/csr-h6kzp' |
| '/kubernetes.io/certificatesigningrequests/csr-jz5vr' |
| '/kubernetes.io/certificatesigningrequests/csr-n7qzt' |
....$ octosql "SELECT namespace, SUM(valueSize) AS S from etcd.snapshot GROUP BY namespace ORDER BY S DESC"
----------------------------------------------------+---------+
| namespace | S |
+----------------------------------------------------+---------+
| 'openshift-monitoring' | 3635823 |
| 'customresourcedefinitions' | 2752055 |
| 'apirequestcounts' | 2138028 |
| <null> | 1737472 |
| 'openshift-config-managed' | 1278729 |
....$ octosql "SELECT d.key, SUBSTR(d.value, 0, 10) FROM etcd.snapshot d WHERE name='version'"
+--------------------------------------------------------------+--------------+
| key | col_1 |
+--------------------------------------------------------------+--------------+
| '/kubernetes.io/config.openshift.io/clusterversions/version' | '{"apiVersi' |
| '/kubernetes.io/leases/openshift-cluster-version/version' | '' |
+--------------------------------------------------------------+--------------+Note that "key" seems to be a reserved keyword, so when querying the key you will need to qualify with its table name.
$ octosql "SELECT MAX(createRevision) FROM etcd.snapshot"
+-----------+
| max |
+-----------+
| 612442603 |
+-----------+ $octosql "SELECT COUNT(modRevision) FROM etcd.snapshot WHERE key='/kubernetes.io/operators.coreos.com/installplans/openshift-whatever/install-xyz'"
+----------------------+
| count_modRevision |
+----------------------+
| 3486 |
+----------------------+$ octosql "SELECT d.key, COUNT(modRevision) AS CNT FROM etcd.snapshot d GROUP BY d.key ORDER BY CNT DESC LIMIT 5"
+-------------------------------------------------------------------------------------------+------+
| key | CNT |
+-------------------------------------------------------------------------------------------+------+
| '/kubernetes.io/operators.coreos.com/operators/xxx-operator.openshift-storage' | 3611 |
| '/kubernetes.io/operators.coreos.com/installplans/openshift-storage/install-abcsd' | 3486 |
| '/kubernetes.io/operators.coreos.com/operators/xxx-operator.openshift-storage' | 1341 |
| '/kubernetes.io/imageregistry.operator.openshift.io/configs/cluster' | 320 |
| '/kubernetes.io/leases/cert-manager/trust-manager-leader-election' | 240 |
+-------------------------------------------------------------------------------------------+------+$ octosql "SELECT d.key, SUM(valueSize) AS SZ FROM etcd.snapshot d GROUP BY d.key ORDER BY SZ DESC LIMIT 5"
+--------------------------------------------------------------------------------------------------+------------+
| key | SZ |
+--------------------------------------------------------------------------------------------------+------------+
| '/kubernetes.io/operators.coreos.com/installplans/openshift-storage/install-abcsd' | 1650272400 |
| '/kubernetes.io/operators.coreos.com/operators/xxx-operator.openshift-storage' | 30435788 |
| '/kubernetes.io/operators.coreos.com/operators/xyz-operator.openshift-storage' | 28948038 |
| '/kubernetes.io/apiextensions.k8s.io/customresourcedefinitions/storageclusters.xxx.openshift.io' | 3637390 |
| '/kubernetes.io/apiserver.openshift.io/apirequestcounts/configmaps.v1' | 1405726 |
+--------------------------------------------------------------------------------------------------+------------+
This repository now includes an MCP (Model Context Protocol) server that allows AI assistants to analyze etcd snapshots using natural language!
MCP enables AI assistants to interact with external tools and data sources. Our MCP server transforms this octosql plugin into a powerful cluster analysis tool that AI assistants can use directly.
- Natural Language Queries: Ask questions like "Show me all pods in production namespace"
- Intelligent Analysis: Get cluster overviews, security scans, and performance insights
- Snapshot Comparison: Compare different cluster states over time
- Security Assessment: Identify potential security issues and misconfigurations
# Build the MCP server
make build-mcp
# Run the server (no environment variables needed)
./etcdsnapshot-mcp-serverSee the MCP Server Documentation for detailed usage examples and integration instructions.
- Follow the instructions on OctoSQL to install the query binary.
- Register the etcdsnapshot with the "snapshot" extension like that:
$ mkdir -p ~/.octosql/ && echo "{\"snapshot\": \"etcdsnapshot\"}" > ~/.octosql/file_extension_handlers.json
- Add this repository as a plugin repo:
$ octosql plugin repository add https://raw.githubusercontent.com/tjungblu/octosql-plugin-etcdsnapshot/main/plugin_repository.json
- Install the plugin:
$ octosql plugin install etcdsnapshot/etcdsnapshot
Try it out with a snapshot file named "etcd.snapshot" in the current folder:
octosql "SELECT * FROM etcd.snapshot"
In order to get a build directly from the source, you can leverage the makefile to build:
make build
and install it directly in the plugin directory with:
make install