Skip to main content

10 posts tagged with "multicloud"

View All Tags

StackQL Provider for AWS Released

· 4 min read
Jeffrey Aven
Technologist and Cloud Consultant

Pleased to announce the initial release of the AWS provider for StackQL.

StackQL allows you to query, provision, and manage cloud and SaaS resources using a simple, SQL-based framework.

The initial release of the AWS provider covers EC2, S3, and the Cloud Control API - with support for other services to be released soon. The documentation for the StackQL AWS provider is available here.

Follow the steps below to get started querying AWS in the StackQL interactive command shell:

Authenticate and Connect​

Connect to an authenticated shell using the syntax shown below:

# AWS_SECRET_ACCESS_KEY and AWS_ACCESS_KEY_ID should be set as environment variables
AUTH="{ \"aws\": { \"type\": \"aws_signing_v4\", \"credentialsenvvar\": \"AWS_SECRET_ACCESS_KEY\", \"keyID\": \"${AWS_ACCESS_KEY_ID}\" }}"
stackql shell --auth="${AUTH}"

Download the AWS provider​

Download the AWS provider from the StackQL Provider Registry:

REGISTRY PULL aws v0.1.3;

Explore the AWS provider​

Explore the AWS provider using StackQL metacommands (such as SHOW and DESCRIBE), for example...

Show available services​

Show the services available in the StackQL AWS provider:

SHOW SERVICES IN aws;

Show available resources​

Show the resources available in the AWS EC2 service (filtered by a fuzzy match on instances):

SHOW RESOURCES IN aws.ec2 LIKE '%instances%';

Show 'selectable' fields​

Show the 'selectable' fields available in a resource:

DESCRIBE EXTENDED aws.ec2.instances;

Show operations available​

Show the available operations on a resource:

SHOW EXTENDED METHODS IN aws.ec2.instances;

Run some queries​

Now that you've identified the available resources and fields let's run some queries!

Instances by region (across multiple regions)​

SELECT 'N. Virginia' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'us-east-1'
UNION
SELECT 'N. California' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'us-west-1'
UNION
SELECT 'Sydney' as region, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'ap-southeast-2';

Instances grouped by instanceType​

SELECT instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'ap-southeast-2'
GROUP BY instanceType;

Instances grouped by instanceState​

SELECT instanceState, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = 'ap-southeast-2'
GROUP BY instanceState;

Enjoy!

StackQL provider for Azure is now available

· One min read
Jeffrey Aven
Technologist and Cloud Consultant

Proud to announce the release of the Microsoft Azure provider for StackQL.

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework

The StackQL provider for Azure provides key visibility across the Azure estate for CSPM, asset inventory and analysis, finops and more, as well as our IaC and ops (lifecycle management) functionality.

Created using the Autorest project using Azure specification docs from the azure-rest-api-specs repository, the StackQL azure provider exposes 230 services, 2,450 resources (of which 1,985 or 81% are available using SELECT statements) and 10,140 methods in total.

Core services are available in the azure provider, all other services are available using the azure_extras provider.

We will be adding integrated interactive authentication, for now this is cli/sdk based, all of the documentation is here.

Give it a test run and let us know what you think!

Kubernetes Provider for StackQL Released

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

Excited to announce the release of the Kubernetes provider for StackQL.

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework

The k8s provider can be used to query and interact with events, namespaces, nodes, persistent volumes, pvcs, pods, services, service accounts and more.

Complete provider documentation for all of the Kubernetes resources, properties and methods is available here.

Here are the steps to get started with the Kubernetes provider:

Setup​

If you are using a proxy (kubectl proxy), follow these instructions:

Using a proxy​

  1. Open an interactive shell (authentication will be handled using the proxy and your .kube/config):
AUTH='{ "k8s": { "type": "null_auth" } }'
./stackql shell --auth="${AUTH}"
  1. Pull the latest k8s provider for StackQL:
REGISTRY PULL k8s v0.1.1;
  1. Query away adding the following expressions to WHERE clauses in your k8s queries:
  • protocol = 'http'
  • cluster_addr = 'localhost:8080' (or whatever port your proxy is listening on)
select name, namespace, uid, creationTimestamp 
from k8s.core_v1.pod 
where protocol = 'http' 
and cluster_addr = 'localhost:8080'  
order by name asc limit 3;

Direct cluster access​

  1. Generate an access token for your cluster, see Access Clusters Using the Kubernetes API.

  2. Generate a certificate bundle for your cluster using the following code (for MacOS or Linux):

kubectl get secret -o jsonpath="{.items[?(@.type==\"kubernetes.io/service-account-token\")].data['ca\.crt']}" | base64 -i --decode > k8s_cert_bundle.pem
note

Alternatively, you could add the --tls.allowInsecure=true argument to the stackql command, it is not recommended however.

  1. Export the token to a variable and supply this as the provider authentication for StackQL:
export K8S_TOKEN='eyJhbGciOi...'
AUTH='{ "k8s": { "type": "api_key", "valuePrefix": "Bearer ", "credentialsenvvar": "K8S_TOKEN" } }'
./stackql shell --auth="${AUTH}" --tls.CABundle k8s_cert_bundle.pem
  1. Pull the latest k8s provider for StackQL:
REGISTRY PULL k8s v0.1.1;
  1. Run some queries (provide the cluster_addr as a WHERE clause parameter):
select name, namespace, uid, creationTimestamp 
from k8s.core_v1.service_account 
where cluster_addr = '35.244.65.136' 
and namespace = 'kube-system' 
order by name asc;

Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, give us some ⭐️ love while you are there!

Netlify Provider for StackQL Released

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

Excited to announce the release of the Netlify provider for StackQL.

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework

The netlify provider can be used to query, provision, de-provision or update sites, builds, deploys, functions, identities, domain_names and more.

Here are the steps to get started with the Netlify provider:

Setup​

  1. Create a personal access token for Netlify (if you don't have one already), go to https://app.netlify.com/user/applications/personal.

  2. Export the token to a variable and supply this as the provider authentication for StackQL:

export NETLIFY_TOKEN=your_personal_access_token
AUTH='{ "netlify": { "type": "api_key", "valuePrefix": "Bearer ", "credentialsenvvar": "NETLIFY_TOKEN" } }'
./stackql shell --auth="${AUTH}"

Install the netlify provider​

  1. Pull the Netlify provider (you only need to do this the first time you use the provider or when you are updating), the following command can be run from the StackQL interactive shell (stackql shell) or using stackql exec:
registry pull netlify v0.1.0;

Show available services in netlify​

  1. (Optional) Show the available services in the Netlify provider:
stackql >> show services in netlify;

+----------------------+---------------+-----------------------+
|          id          |     name      |       title           |
|----------------------|---------------|-----------------------|
| builds:v0.1.0        | builds        | Netlify site builds   |
|----------------------|---------------|-----------------------|
| deploys:v0.1.0       | deploys       | Netlify site deploys  |
|----------------------|---------------|-----------------------|
| user_accounts:v0.1.0 | user_accounts | Netlify user accounts |
|----------------------|---------------|-----------------------|
| ...                  | ...           | ...                   |
+----------------------+---------------+-----------------------+

Explore resources in netlify​

  1. (Optional) Explore resources in a service in the Netlify provider:
stackql  >> show resources in netlify.deploys;

+----------------+--------------------------------+
|      name      |               id               |
|----------------|--------------------------------|
| deploy         | netlify.deploys.deploy         |
|----------------|--------------------------------|
| deployKey      | netlify.deploys.deployKey      |
|----------------|--------------------------------|
| deployedBranch | netlify.deploys.deployedBranch |
+----------------+--------------------------------+
  1. (Optional) List the available fields (or properties) of a resource:
stackql  >> describe netlify.deploys.deploy;

+--------------------+---------+
|        name        |  type   |
|--------------------|---------|
| id                 | string  |
|--------------------|---------|
| name               | string  |
|--------------------|---------|
| url                | string  |
|--------------------|---------|
| review_id          | number  |
|--------------------|---------|
| commit_url         | string  |
|--------------------|---------|
| commit_ref         | string  |
|--------------------|---------|
| ...                | ...     |
+--------------------+---------+
  1. (Optional) Show the available methods in a resource (the stuff you can do with a resource..):
stackql  >> show methods in netlify.deploys.deploy;

+--------------------+--------------------+
|     MethodName     |   RequiredParams   |
|--------------------|--------------------|
| cancelSiteDeploy   | deploy_id          |
|--------------------|--------------------|
| createSiteDeploy   | site_id            |
|--------------------|--------------------|
| getSiteDeploy      | deploy_id, site_id |
|--------------------|--------------------|
| listSiteDeploys    | site_id            |
|--------------------|--------------------|
| lockDeploy         | deploy_id          |
|--------------------|--------------------|
| ...                | ...                |
+--------------------+--------------------+

::: tip Methods prefixed by list and get are exposed via SELECT verbs, for example:

SELECT id, name FROM netlify.deploys.deploy
WHERE site_id = 'ad26d902-9cb1-43be-90d9-284e8c7ac687';

this query accesses the listSiteDeploys method :::

Run some queries!​

  1. Run some queries...
stackql  >> SELECT created_at, name, state, branch FROM netlify.deploys.deploy
>> WHERE site_id = 'ad26d902-9cb1-43be-90d9-284e8c7ac687'
>> ORDER BY created_at DESC LIMIT 2;

+--------------------------+------------+-------+-------------------------+
|        created_at        |    name    | state |         branch          |
|--------------------------|------------|-------|-------------------------|
| 2022-05-04T22:46:43.015Z | stackql-io | ready | main                    |
|--------------------------|------------|-------|-------------------------|
| 2022-05-04T22:39:34.958Z | stackql-io | ready | feature/content-updates |
+--------------------------+------------+-------+-------------------------+

You can also use StackQL to provision resources in Netlify, the methods you saw in step 7 that are prefixed by create or insert, can be accessed using INSERT statements in StackQL, similarly methods prefixed by delete or remove can be accessed using DELETE statements.

more providers coming soon, if there is anything you are interested in specifically, get in contact and let us know.

Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, give us some ⭐️ love while you are there!

enjoy!

GitHub Provider for StackQL Released

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

The GitHub provider for StackQL is now generally available. This can be used to query resources in GitHub Cloud or GitHub Enterprise, including orgs, teams, users, repositories, branches, pull requests, issues, workflows/actions and much more!

See available providers​

You can see the versions of GitHub Provider (and other providers) available using:

stackql registry list

or from the StackQL Command Shell (stackql shell) using:

REGISTRY LIST;

this would return a list of all the providers that are currently available, for example:

+----------+---------+
| provider | version |
+----------+---------+
| github   | v0.1.0  |
| google   | v0.1.0  |
| okta     | v0.1.0  |
+----------+---------+

Pull the github provider​

To pull v0.1.0 of the github provider use:

stackql registry pull github v0.1.0

or

REGSITRY PULL github v0.1.0;

to see what providers are installed use:

SHOW PROVIDERS;

this would return something like...

+--------+
|  name  |
+--------+
| github |
+--------+

Explore the github provider and query public resources​

The provider and public objects can be queried without authentication as shown here:

AUTH='{"github": { "type": "null_auth" }}'
stackql shell --auth="${AUTH}"

you can now enumerate services, resources, attributes and methods in the github provider using the SHOW and DESCRIBE meta commands, for instance:

show services in github from either the StackQL command shell or via stackql exec would return something like...

+----------------------------+---------------------+------------------------------------------+
|             id             |        name         |                  title                   |
+----------------------------+---------------------+------------------------------------------+
| actions_enterprises:v0.1.0 | actions_enterprises | GitHub v3 REST API - actions_enterprises |
| billing:v0.1.0             | billing             | GitHub v3 REST API - billing             |
| repos:v0.1.0               | repos               | GitHub v3 REST API - repos               |
| ...                        | ...                 | ...                                      |
+----------------------------+---------------------+------------------------------------------+
tip

Use the EXTENDED operator with the SHOW or DESCRIBE commands to get additional information about services, resources, attributes and methods, e.g. DESCRIBE EXTENDED github.repos.repos

show resources in github.repos would return something like...

+--------------+---------------------------+
|     name     |            id             |
+--------------+---------------------------+
| branches     | github.repos.branches     |
| commits      | github.repos.commits      |
| deployments  | github.repos.deployments  |
| environments | github.repos.environments |
| forks        | github.repos.forks        |
| releases     | github.repos.releases     |
| repos        | github.repos.repos        |
| statistics   | github.repos.statistics   |
| statuses     | github.repos.statuses     |
| traffic      | github.repos.traffic      |
| ...          | ...                       |
+--------------+---------------------------+

to see fields in a resource (which can be queried or updated) use DESCRIBE for example DESCRIBE github.repos.commits would return something like...

+--------------+--------+
|     name     |  type  |
+--------------+--------+
| files        | array  |
| stats        | object |
| commit       | object |
| url          | string |
| html_url     | string |
| parents      | array  |
| node_id      | string |
| comments_url | string |
| committer    | object |
| sha          | string |
| author       | object |
+--------------+--------+

to see methods available in a resource use the SHOW METHODS command for example SHOW METHODS IN github.repos.commits would return something like...

+-------------------------------------------+-------------------------+
|                MethodName                 |     RequiredParams      |
+-------------------------------------------+-------------------------+
| compare_commits                           | basehead, owner, repo   |
| get_commit                                | owner, ref, repo        |
| list_branches_for_head_commit             | commit_sha, owner, repo |
| list_commits                              | owner, repo             |
| list_pull_requests_associated_with_commit | commit_sha, owner, repo |
+-------------------------------------------+-------------------------+
tip

Methods beginning with list or get can usually be accessed via SELECT statements. For example,

SELECT github.repos.commits.sha 
FROM github.repos.commits 
WHERE owner='${owner}' AND repo='${repo}';

Other methods can be accessed using the EXEC command (for more information see EXEC)

Query protected resources​

Accessing protected resources requires authentication using a Personal Access token as shown here:

export GITHUB_CREDS=$(echo -n 'yourgithubusername:ghp_YOURPERSONALACCESSTOKEN' | base64)
AUTH='{ "github": { "type": "basic", "credentialsenvvar": "GITHUB_CREDS" } }'
stackql shell --auth="${AUTH}"

Now you are able to access protected resources, for example:

select id, name, private 
from github.repos.repos 
where org = 'stackql';

which would return something like...

+-----------+-------------------------+---------+
|    id     |          name           | private |
+-----------+-------------------------+---------+
| 443987542 | stackql                 | false   |
| 441087132 | stackqlproviderregistry | false   |
| 409393414 | fullstackchronicles.io  | false   |
| 435085734 | stackql.io              | true    |
| 443979486 | releases.stackql.io     | true    |
| 447890554 | stackqldevel            | true    |
|       ... | ...                     | ...     |
+-----------+-------------------------+---------+

Welcome your feedback by getting in touch or raising issues at stackql/stackql-provider-registry, ⭐️ us while you are there!