Skip to main content

14 posts tagged with "asset management"

View All Tags

Cloud security and inventory analysis with StackQL and Jupyter

· One min read
Jeffrey Aven
Technologist and Cloud Consultant

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!

StackQL Middleware and Playground Available

· 3 min read
Jeffrey Aven
Technologist and Cloud Consultant

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.

New Functions and Azure Provider Available

· One min read
Jeffrey Aven
Technologist and Cloud Consultant

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.

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!