Skip to main content

· One min read

A new version of the Azure provider for StackQL plus additional built-in functions are available now.

Version 0.3.0 of the Azure provider for StackQL is available now. This update includes support for extended resource properties, along with support for Hybrid Azure Kubernetes Services. The Azure provider allows you to query across your Azure estate for cloud security posture, asset inventory, analysis and reporting, finops, sysops, and more - all using a natural query language (SQL) and a natural object mapping.

In addition, we have announced the release of several new built-in functions, including SPLIT_PART() - to split a string by a delimiter and extract a single element, additional unicode functions, and expanded regular expression support, including REGEXP_REPLACE() and more.

An example StackQL query using the split_part() function with the azure v0.3.0 provider is shown here: 

SELECT name,  
split_part(id, '/', 3) as subscription,
split_part(id, '/', 5) as resource_group,
json_extract(properties, '$.hardwareProfile.vmSize') as vm_size
FROM azure.compute.virtual_machines WHERE resourceGroupName = 'stackql-ops-cicd-dev-01' AND subscriptionId = '242c6a2d-16f9-4912-90f6-59b1cf85509d';

You can find more information on the latest Azure provider here.

· 5 min read

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!

· One min read

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!

· 2 min read

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!

· 3 min read

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

Understanding entitlements across a GCP org with a complex hierarchy is a challenge. I have taken and data-centric approach to this in this article.

Prerequisites include setting up a Jupyter environment with StackQL (done here using Docker): stackql-jupyter-demo. You will also need a service account and associated key with the roles/iam.securityReviewer role.

I've broken the notebook bits down to explain...

Setup​

This step includes importing the required libraries (pandas etc.) and instantiating a StackQL client with the service account creds you created before. You will supply your root node here using the org_id and org_name variables.

Next we will create some helper functions; these will help us enumerate nodes in the GCP org resource hierarchy and fetch and unnest IAM policies.

Get all nodes in the resource hierarchy​

Create a dataframe containing all nodes in the resource hierarchy, including the root node (the organization), each folder with its subfolders, and projects. The functions used will search each folder in the hierarchy to find its subfolders and projects using a depth-first search approach.

Inspecting the output, it looks like this:

GCP Nodes

Create a dataset including each node and its associated IAM policies​

This step will fetch all of the policies applied at each node in the data structure we created in the previous step.

The IAM policies response from SELECT role, members FROM google.cloudresourcemanager.project_iam_policies ... presents some challenges as members is a nested list which we need to unnest (or explode) along with the associated role and conditions (if they exist).

This bit of massaging will give us a SQL-friendly model we can use for analysis and join with another data source (such as a list of identities from an identity provider).

Inspecting the Final Output

We can now peek at the final data set, which looks like this:

GCP Nodes with IAM Policies

What's next? You could now join this with data from your IdP, or other SaaS services to correlate entitlements across your entire estate. You could also drill into specific service accounts, users, or groups. Queries are run in real-time, so you can refresh the data by simply rerunning the cells.

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

Enjoy!