Skip to main content

11 posts tagged with "asset management"

View All Tags

· 3 min read

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework. Use cases include CSPM, asset inventory and analysis, finops and more, as well as our IaC and ops (lifecycle management).

The three major cloud providers all offer a built-in Linux shell for executing commands using their respective CLIs; in some cases, they come with tools like terraform pre-installed. They are pre-authorized with your credentials in the cloud console for the user you authenticated with.

Now you can easily use stackql - a unified analytics and IaC dev tool - in all major cloud providers' built-in shells, using cloud shell scripts packaged with the stackql Linux binary (available from v0.5.587 onwards).

StackQL is particularly useful for asynchronously querying across regions in AWS, projects in Google, or resource groups in Azure, which is challenging to do via the CLIs. For example:

SELECT region, COUNT(*) as num_functions
FROM aws.lambda.functions
WHERE region IN (
'us-east-1','us-east-2','us-west-1','us-west-2',
'ap-south-1','ap-northeast-3','ap-northeast-2',
'ap-southeast-1','ap-southeast-2','ap-northeast-1',
'ca-central-1','eu-central-1','eu-west-1',
'eu-west-2','eu-west-3','eu-north-1','sa-east-1')
GROUP BY region;

Additionally, you could authenticate to another provider from one cloud shell simultaneously and run multi-cloud inventory commands. For example:

SELECT 
name,
SPLIT_PART(machineType, '/', -1) as instance_type,
'google' as provider
FROM google.compute.instances
WHERE project IN ('myproject1','myproject2')
UNION
SELECT
instanceId as name,
instanceType as instance_type,
'aws' as provider
FROM aws.ec2.instances
WHERE region IN (
'us-east-1','us-east-2','us-west-1','us-west-2',
'ap-south-1','ap-northeast-3','ap-northeast-2',
'ap-southeast-1','ap-southeast-2','ap-northeast-1',
'ca-central-1','eu-central-1','eu-west-1',
'eu-west-2','eu-west-3','eu-north-1','sa-east-1');

Getting Started

To get started with StackQL in your preferred cloud shell environment, download the StackQL package using the following command:

curl -L https://bit.ly/stackql-zip -O \
&& unzip stackql-zip

This command downloads the StackQL package, unzips it, and sets the appropriate permissions. From there, you can use our tailored scripts for AWS, Google Cloud, or Azure to integrate StackQL seamlessly into your cloud shell environment.

Using StackQL in the AWS Cloud Shell

Run the stackql-aws-cloud-shell.sh as follows to use the StackQL command shell within the AWS cloud shell:

sh stackql-aws-cloud-shell.sh

An example is shown here:

aws-cloud-shell-example

You can also run stackql exec commands using the stackql-aws-cloud-shell.sh script; for instance, this command will write a CSV file for the results of a query that could be downloaded from the Cloud Shell.

sh stackql-aws-cloud-shell.sh exec \
--output csv --outfile instances.csv \
"SELECT region, instanceType FROM aws.ec2.instances WHERE region IN ('us-east-1')"

Additionally, you can supply an IAM role using the --role-arn argument to assume another role for your query or mutation operation, an example is shown here:

sh stackql-aws-cloud-shell.sh \
--role-arn arn:aws:iam::824532806693:role/SecurityReviewerRole exec \
--infile query.iql \
--output csv --outfile output.csv

Using StackQL in the Azure Cloud Shell

Run the stackql-azure-cloud-shell.sh as follows to open a StackQL command shell from the Azure Cloud Shell:

sh stackql-azure-cloud-shell.sh

An example is shown here:

azure-cloud-shell-example

Similar to the AWS script, you can also invoke stackql exec as well, an example is shown here:

sh stackql-azure-cloud-shell.sh exec \
--output csv --outfile instances_by_location.csv \
"SELECT location, COUNT(*) as num_instances FROM azure.compute.virtual_machines WHERE resourceGroupName = 'stackql-ops-cicd-dev-01' AND subscriptionId = '631d1c6d-2a65-43e7-93c2-688bfe4e1468' GROUP BY location"

Using StackQL in the Google Cloud Shell

Run the stackql-google-cloud-shell.sh as shown below to launch a StackQL command shell from within the google cloud shell:

sh stackql-google-cloud-shell.sh

An example is shown here:

google-cloud-shell-example

As with the other two providers, you can run exec commands following the example below:

sh stackql-google-cloud-shell.sh exec \
--output csv --outfile instances.csv \
"SELECT name, status FROM google.compute.instances WHERE project = 'stackql-demo'"

Please give us your feedback! Star us at github.com/stackql.

· One min read

StackQL allows you to query and interact with your cloud and SaaS assets using a simple SQL framework. Use cases include CSPM, asset inventory and analysis, finops and more, as well as our IaC and ops (lifecycle management).

Excited to announce the general availability of the latest StackQL providers for Azure. Includes expanded resource and method coverage including all of the latest Resource Manager services. The StackQL Azure provider catalog now includes:

  • azure - core Azure RM services
  • azure_extras - additional Azure services
  • azure_isv - Azure Native ISV software and services (like Databricks, Datadog, Confluent, Astro and more)
  • azure_stack - Azure Hybrid app framework

by the numbers...

ProviderTotal ServicesTotal MethodsTotal Resources
azure195138413920
azure_extras381164339
azure_isv20906253
azure18470142

More Data Plane services like Azure Container Registry coming as well, stay tuned!

· One min read

StackQL is an intelligent API client which uses SQL as a front-end language with support for multi-cloud and SaaS provider environments, you can find more information at github.com/stackql/stackql

StackQL can provide valuable insights into your cloud and SaaS estates, whether for security posture management, cross-cloud entitlements reporting, cost optimization, or asset/inventory management.

As an interactive analysis tool, Jupyter notebooks can leverage StackQL to provide sources for cloud and SaaS provider data.

GCP Nodes

We've recently added magic function support for running StackQL queries in Jupyter notebooks, making the integration between StackQL and Jupyter more seamless. StackQL magic and be used on a line in a cell or the entire cell itself, as shown here:

The stackql-jupyter-demo Docker image is available from Docker Hub. You can find instructions to run using the Docker Hub image and instructions to run using docker-compose at github.com/stackql/stackql-jupyter-demo.

Give us your feedback!

· 2 min read

Proud to announce the release of the first version of our middleware server and playground for StackQL.

StackQL allows you to query and interact with cloud services and APIs using SQL grammar and an ORM which is a direct reflection of a provider API, no database is required or implemented

StackQL Middleware Server

Our middleware solution allows you to use StackQL as a query language to interact with APIs, much like GraphQL - however, the query DSL is SQL, providing a friendlier, more data-centric experience for developers. As shown in the example below, developers can POST queries to a /stackql endpoint; the queries are parsed and executed by a StackQL runner via the middleware server.

As the StackQL middleware API server and runner are stateless, the solution is horizontally scalable. With cache and authorization to be implemented, the solution provides a flexible, robust, scalable, performant back end for applications.

Furthermore, using SQL semantics, developers can JOIN data between API providers and perform projections, filtering, aggregation, windowing operations, and more on simple or complex data types.

StackQL Playground

The StackQL Playground is a TypeScript app that connects to a StackQL Middleware Server, which provides access to backend APIs using SQL. Features of the playground include...

Explore API Providers

The StackQL Playground allows you to explore and query providers, services, resources, fields, and methods.

Submit Queries

Using the Playground you can submit queries to the /stackql endpoint of the StackQL Middleware Server.

View, Sort, and Filter Results

Query results can be sorted or filtered in the grid result set in the StackQL Playground app, JSON results can also be viewed or copied from the JSON results tab.

Save Results as CSV or JSON

Users can save results to local CSV or JSON files as well.

Generate Types

Furthermore, after modeling a query you can export the TypeScript types using the Get Types button.

Huge thanks to Yuncheng Yang for the work he put in on this!

You can find the complete code here to launch an environment using docker-compose, which includes the StackQL Middleware Server, a StackQL runner (runs the queries on the back end), and the StackQL Playground app.

Let us know what you think.

· 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.