Skip to main content

Introducing Table Valued Functions in StackQL

· 2 min read
Kieran Rimmer
Technologist and Cloud Consultant

Many provider query responses include columns which are arrays, the iam policy related resources in google are a classic example of this. for example, this query:

select * 
from google.cloudresourcemanager.projects_iam_policies 
where projectsId = 'stackql';

produces..

|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| condition | members                                                                                                                                                                                    | role         |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| null      | ["serviceAccount:1234567890-compute@developer.gserviceaccount.com","serviceAccount:1234567890@cloudservices.gserviceaccount.com","serviceAccount:stackql@appspot.gserviceaccount.com"] | roles/editor |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| null      | ["serviceAccount:1234567890-compute@developer.gserviceaccount.com","serviceAccount:1234567890@cloudservices.gserviceaccount.com","serviceAccount:stackql@appspot.gserviceaccount.com"] | roles/editor |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|                               
| null      | ["serviceAccount:1234567890-compute@developer.gserviceaccount.com","serviceAccount:1234567890@cloudservices.gserviceaccount.com","serviceAccount:stackql@appspot.gserviceaccount.com"] | roles/editor |                               
|-----------|--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|--------------|

What you want to do is unnest each member in members for each role binding (and condition if applicable)

Enter the table valued function json_each.

The json_each function accepts a field (optionally with a json path expression) and returns a table object with fields that can be projected in your result set, for example (querying the same underlying resource as above), this...

select 
iam.role, 
SPLIT_PART(json_each.value, ':', 1) as member_type,
SPLIT_PART(json_each.value, ':', 2) as member 
from google.cloudresourcemanager.projects_iam_policies iam, json_each(members) 
where projectsId = 'stackql';

now provides something much more useful from an analytic perspective:

|--------------------------------------|----------------|-----------------------------------------------------------------------------|
|                 role                 |  member_type   |                                   member                                    |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/appengine.serviceAgent         | serviceAccount | service-1234567890@gcp-gae-service.iam.gserviceaccount.com                |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/cloudbuild.builds.builder      | serviceAccount | 1234567890@cloudbuild.gserviceaccount.com                                 |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/cloudbuild.serviceAgent        | serviceAccount | service-1234567890@gcp-sa-cloudbuild.iam.gserviceaccount.com              |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/compute.serviceAgent           | serviceAccount | service-1234567890@compute-system.iam.gserviceaccount.com                 |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/container.serviceAgent         | serviceAccount | service-1234567890@container-engine-robot.iam.gserviceaccount.com         |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|
| roles/containerregistry.ServiceAgent | serviceAccount | service-1234567890@containerregistry.iam.gserviceaccount.com              |
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/dataflow.serviceAgent          | serviceAccount | service-1234567890@dataflow-service-producer-prod.iam.gserviceaccount.com |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/dataproc.serviceAgent          | serviceAccount | service-1234567890@dataproc-accounts.iam.gserviceaccount.com              |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/editor                         | serviceAccount | 1234567890-compute@developer.gserviceaccount.com                          |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/editor                         | serviceAccount | 1234567890@cloudservices.gserviceaccount.com                              |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/editor                         | serviceAccount | stackql@appspot.gserviceaccount.com                                         |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/firebaserules.system           | serviceAccount | service-1234567890@firebase-rules.iam.gserviceaccount.com                 |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/firestore.serviceAgent         | serviceAccount | service-1234567890@gcp-sa-firestore.iam.gserviceaccount.com               |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | serviceAccount | stackql-provisioner@stackql.iam.gserviceaccount.com                         |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | serviceAccount | t1-804@stackql.iam.gserviceaccount.com                                      |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | user           | javen@stackql.io                                                            |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/owner                          | user           | krimmer@stackql.io                                                          |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/pubsub.serviceAgent            | serviceAccount | service-1234567890@gcp-sa-pubsub.iam.gserviceaccount.com                  |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|                                                                                    
| roles/viewer                         | serviceAccount | testing-admin@stackql.iam.gserviceaccount.com                               |                                                                                    
|--------------------------------------|----------------|-----------------------------------------------------------------------------|

__json_each__is available from version 0.5.418 or stackql onwards, this function can also be used in StackQL GitHub Actions such as stackql-exec or stackql-assert and in Python and Pandas using pystackql.

Support for Outer Joins Released for StackQL

· 3 min read
Kieran Rimmer
Technologist and Cloud Consultant
info

stackql is a dev tool that allows you to query, manage, and perform analytics against cloud and SaaS resources in real time using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

We are pleased to announce the addition of support for OUTER JOIN operations in StackQL queries. This is a significant addition to the language, and we are excited to see what our users will do with it!

info

An OUTER JOIN is a type of JOIN operation that returns all records from one table (or StackQL resource) and only those records from a second table or resource where the joined fields are equal (i.e. the JOIN condition is met). If there is no match, the missing side of the JOIN is filled with NULL values.

OUTER JOIN operations are important because they allow you to combine data from two or more resources (within a StackQL provider or across StackQL providers), even when there is no match between the two resources. This is a common scenario when performing analytics and reporting on user access management (for example between an IdP (like Okta) and a resource provider like AWS or Google).

Using OUTER JOIN operations in StackQL​

If you wanted to find all users in your AWS account that have not logged in to their account in the last 20 days, and compare that to the same information for users in your Google Workspace account, you could use an OUTER JOIN operation to do this. The following query:

select 
   aws_users.UserName as aws_user_name
  ,aws_users.PasswordLastUsed as aws_last_Login_time
  ,CASE 
    WHEN aws_users.PasswordLastUsed = '' then 'false' 
    WHEN ( strftime('%Y-%m-%d %H:%M:%SZ', aws_users.PasswordLastUsed) > ( datetime('now', '-20 days' ) ) ) then 'true' 
    else 'false' end as aws_is_active
  ,json_extract(google_users.name, '$.fullName') as google_user_name
  ,google_users.lastLoginTime as google_last_Login_time 
  ,CASE 
    WHEN google_users.lastLoginTime is null then 'false' 
    WHEN google_users.lastLoginTime = '' then 'false' 
    WHEN ( strftime('%Y-%m-%d %H:%M:%SZ', google_users.lastLoginTime) > ( datetime('now', '-20 days' ) ) ) then 'true' 
    else 'false' end as google_is_active
from 
  aws.iam.users aws_users 
  LEFT OUTER JOIN 
  googleadmin.directory.users google_users 
  ON lower(substr(aws_users.UserName, 1, 5)) = lower(substr(json_extract(google_users.name, '$.fullName'), 1, 5)) 
WHERE aws_users.region = 'us-east-1' AND google_users.domain = 'stackql.io'
;

would produce a result like this:

|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
|     aws_user_name      | aws_last_Login_time  | aws_is_active | google_user_name |  google_last_Login_time  | google_is_active |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| demo-stackql-cicd-user | null                 | false         | null             | null                     | false            |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| github_actions         | null                 | false         | null             | null                     | false            |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| jeffrey.aven           | 2023-06-30T04:29:14Z | true          | null             | null                     | false            |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| kieran.rimmer          | 2023-06-03T08:40:49Z | false         | Kieran Rimmer    | 2023-06-23T06:01:46.000Z | true             |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|
| ...                    | ...                  | ...           | ...              | ...                      | ...              |
|------------------------|----------------------|---------------|------------------|--------------------------|------------------|

Currently only LEFT OUTER JOIN sppport is available, but we will be adding support for RIGHT OUTER JOIN and FULL OUTER JOIN in the near future. Stay tuned!

StackQL PyPI Package is Now Available

· 2 min read
Jeffrey Aven
Technologist and Cloud Consultant
What is StackQL?
info

stackql is a dev tool that allows you to query and manage cloud and SaaS resources using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

pystackql is now available in PyPI, and documentation for the package is available at Read the Docs. pystackql can be used with pandas, matplotlib, plotly, jupyter and more to run queries and visualize results.

The latest version of stackql for any platform can be installed using:

pip install pystackql

here is a complete example...

pystackql usage

the code used in this example is available here
from pystackql import StackQL
import pandas as pd
provider_auth =  {
    "aws": {
        "credentialsenvvar": "AWS_SECRET_ACCESS_KEY",
        "keyIDenvvar": "AWS_ACCESS_KEY_ID",
        "type": "aws_signing_v4"
    }
}
regions = ["ap-southeast-2", "us-east-1"]
stackql = StackQL(auth=provider_auth)

query = """
SELECT '%s' as region, instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = '%s'
GROUP BY instanceType
UNION
SELECT  '%s' as region, instanceType, COUNT(*) as num_instances
FROM aws.ec2.instances
WHERE region = '%s'
GROUP BY instanceType
""" % (regions[0], regions[0], regions[1], regions[1])

res = stackql.execute(query)
df = pd.read_json(res)
print(df)

which returns a pandas DataFrame like the following:

  instanceType  num_instances          region
0    t2.medium              2  ap-southeast-2
1     t2.micro              7  ap-southeast-2
2     t2.small              4  ap-southeast-2
3     t2.micro              6       us-east-1

Source code for pystackql can be found at stackql/pystackql

Let us know what you think!

Using the setup-stackql Github Action

· 2 min read
Yuncheng Yang
SRE at Sitemate and Cloud Enthusiast
What is StackQL?
info

stackql is a dev tool that allows you to query and manage cloud and SaaS resources using SQL, which developers and analysts can use for CSPM, assurance, user access management reporting, IaC, XOps and more.

The setup-stackql action is now available in the GitHub Marketplace. setup-stackql downloads and installs the latest stackql binary in your Actions runner. Different runner operating systems are fully tested and supported, including ubuntu-latest, windows-latest and darwin-latest.

Embedding stackql in an actions workflow can be done for assurance, compliance, and provisioning. An example workflow is shown here:

name: 'Setup StackQL Example'

on:
  pull_request:

defaults:
  run:
    shell: bash

jobs:
  stackql-test-matrix:
    name: Stackql local run on ubuntu-latest with wrapper
    runs-on: 'ubuntu-latest'
    steps:
    - name: Checkout
      uses: actions/checkout@v3

    - name: Setup Stackql
      uses: ./
      with:
        use_wrapper: true

    - name: Validate Stackql Version
      run: |
        stackql --version

    - name: Prep Google Creds (bash)
      run: | ## use the secret to create json file
        sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json

    - name: Use Google Provider
      run: | 
        stackql exec "SELECT name, status
        FROM google.compute.instances 
        WHERE project = 'stackql-demo' AND zone = 'australia-southeast1-a';" --auth='{ "google": { "type": "service_account",  "credentialsfilepath": "sa-key.json" }}'

the log outputs showing...

|-----------------------------------------------------|------------|
|                        name                         |   status   |
|-----------------------------------------------------|------------|
| stackql-demo-001                                    | TERMINATED |
|-----------------------------------------------------|------------|
| stackql-demo-002                                    | RUNNING    |
|-----------------------------------------------------|------------|
| stackql-demo-003                                    | RUNNING    |
|-----------------------------------------------------|------------|
| stackql-demo-004                                    | RUNNING    |
|-----------------------------------------------------|------------|

additional output types available through the --output argument include json and csv

Source code for the action can be found at stackql/setup-stackql

much more to come, stay tuned!

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.