76 posts tagged with "stackql"

We provide a set of useful actions that allow you use StackQL with GitHub Actions Workflow. These actions are available in the GitHub Marketplace.

setup-stackql​

This action installs the stackql CLI on Actions runners. It is used if you want to perform custom operations using StackQL queries.

Using the setup-stackql action allows you to perform StackQL CLI operations in the subsequent steps of a GitHub Actions job. The setup-stackql action installs the stackql CLI on the runner, making it available to all subsequent steps. This means you can use StackQL queries and commands directly in your GitHub Action Workflow Steps.

stackql-exec​

This action executes a StackQL query within an Actions workflow.

The stackql-exec action is a wrapper around setup-stackql that allows you to pass your StackQL query and authentication information as inputs. This makes it easy to execute StackQL queries directly within your GitHub Action Workflow Step.

stackql-assert​

This action is used to test assertions against the results of a StackQL query. You can use this action to validate the state of a resource after an IaC or lifecycle operation has been performed or to validate the system (e.g., CSPM or compliance queries).

The stackql-assert action is also a wrapper around setup-stackql that allows you to pass your StackQL query and authentication information as inputs. This makes it easy to test assertions against the results of your StackQL queries directly within your GitHub Action Workflow Step.

Use setup-stackql to run StackQL queries in your GitHub Workflow Job​

To run StackQL query with GitHub Actions, you need to install the stackql CLI on the runner. This can be done using the setup-stackql action. Once installed, the stackql CLI is available to all subsequent steps in the job via StackQL.

- name: setup StackQL
  uses: stackql/setup-stackql@v1.1.0
  with:
    use_wrapper: true

Run StackQL query​

After using the setup-stackql action, you can run the stackql CLI as a shell command in subsequent steps of the job. For example, you can run a dry run of a StackQL query as a shell command like this:

- name: Prep Google Creds (bash)
      if: ${{ matrix.os != 'windows-latest' }}
      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 -i ./examples/google-example.iql --auth='{ "google": { "type": "service_account",  "credentialsfilepath": "sa-key.json" }}'

You can check more example here.

Use stackql-exec to run StackQL queries in a single step​

stackql-exec action provides a simpler and more streamlined way to executestackql CLI queries in your GitHub Workflow. You can create a step like this:

- name: stop running instances using stackql-exec
  uses: stackql/stackql-exec@v1.0.1
  with:
    auth_obj_path: './stackql/auth.json'
    query_file_path: './stackql/scripts/stop-instances.iql'

The auth_obj_path parameter specifies the path to the authentication object that contains the credentials required to authenticate with the data source. The query_file_path parameter specifies the path to the StackQL query file that you want to execute.

Once you have specified the authentication object and query file, you can run the StackQL query by executing the stackql-exec action in a single step in your GitHub Workflow.

You can check more examples here.

Validating resource state with stackql-assert​

stackql-assert is another useful action that StackQL provides, allowing you to test assertions against the results of a StackQL query.

This action is particularly helpful for validating the state of a resource after an infrastructure as code (IaC) or lifecycle operation has been performed.

You can run an assert step like this example:

- name: check terraform deployment using stackql-assert - should fail
  uses: stackql/stackql-assert@v1.0.2
  with:
    auth_obj_path: './stackql/auth.json'
    test_query_file_path: './stackql/scripts/check-terraform-instances.iql'
    expected_results_str: '[{"name":"terraform-test-1","name":"terraform-test-2"}]'

Authentication​

setup-stackql​

if you use basic token, you can setup authentication with the following example

- name: Use GitHub Provider
      run: |
        stackql exec -i ./examples/github-example.iql --auth='{ "github": { "type": "basic", "credentialsenvvar": "STACKQL_GITHUB_CREDS" } }'
      env: 
        STACKQL_GITHUB_CREDS: ${{  secrets.STACKQL_GITHUB_CREDS }}

Meanwhile if you are using a credential file, you can create the file via this example bash command

    - 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" }}'

stackql-exec and stackql-assert​

For stackql-exec and stackql-assert, you can use the same authentication method as setup-stackql, except the authentication object is passed via the auth_obj_path or auth_strparameter

• auth_obj_path parameter to specify the path to the authentication object that contains the credentials required to authenticate with the data source.
• auth_str parameter to specify the authentication object as a string.

Example Authentication Object file (auth.json):​

{
  "github": {
    "type": "basic",
    "credentialsenvvar": "STACKQL_GITHUB_CREDS"
  }
}

Example Usage of auth_obj_path:​

  - name: Prep Google Creds (bash)
  if: ${{ matrix.os != 'windows-latest' }}
  shell: bash
  run: | ## use the base64 encoded secret to create json file
    sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json
    
  - name: stop running instances using stackql-exec
    uses: stackql/stackql-exec@v1.0.1
    with:
      auth_obj_path: './stackql/auth.json'
      query_file_path: './stackql/scripts/stop-instances.iql'

  - name: check if we have 4 instances using stackql-assert
    uses: stackql/stackql-assert@v1.0.2
    with:
      auth_obj_path: './stackql/auth.json'
      test_query_file_path: './stackql/scripts/check-instances.iql'
      expected_rows: 4

Example Usage of auth_str:​

  - name: Prep Google Creds (bash)
    if: ${{ matrix.os != 'windows-latest' }}
    shell: bash
    run: | ## use the base64 encoded secret to create json file
      sudo echo ${{ secrets.GOOGLE_CREDS }} | base64 -d > sa-key.json
      
  - name: exec github example
    uses: ./
    with:
      auth_str: '{ "github": { "type": "basic", "credentialsenvvar": "STACKQL_GITHUB_CREDS" } }'
      query: "REGISTRY PULL github v23.01.00104;
              SHOW PROVIDERS;
              select total_private_repos
              from github.orgs.orgs
              where org = 'stackql';"
    env: 
      STACKQL_GITHUB_CREDS: ${{ secrets.STACKQL_GITHUB_CREDS }}

  - name: Use test query string and expected rows, with auth string
    uses: ./
    with:
      auth_str: '{ "google": { "type": "service_account",  "credentialsfilepath": "sa-key.json" }}'
      test_query_file_path: './.github/workflows/workflow_scripts/google-example.iql'
      expected_rows: 1

Read More​

Check the full demo​

• stackql-actions-demo

Check the GitHub Repos​

• setup-stackql
• stackql-exec
• stackql-assert

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

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)

  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!

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!

This exercise will show you how to run a real-time query across your AWS and Google cloud environments. You may do this for inventory analysis, security analysis, or any other reason you can think of. We will use stackql to query the state of your cloud resources across your AWS and Google environments. You can also use stackql to provision, de-provision or manage resources across different cloud and SaaS providers.

The steps we will take are:

1. Prepare your environment for stackql usage.
2. Use stackql to provision some resources in cloud. optional
3. Use stackql to query resources present in the cloud.
4. Use stackql to tear down resources created in step (2), if any. Important: you must destroy any resources created through this exercise, or you will incur ongoing charges.

Preparation​

For this exercise, credentials with privileges against google and aws are required. It is outside the scope of this document to go into great detail on the various topics and options relevant to this. Instead, the below steps provide both: (i) reference to vendor documentation and (ii) suggestions for workarounds to get yourself going.

for old hands​

All the materials required for this exercise are:

1. A current stackql executable.
2. A Google Service Account Key JSON file, where the corresponding Service Account possesses permissions sufficient to create, interrogate and delete compute block storage.
3. AWS credentials stored in the traditional AWS_ACCESS_KEY_ID and AWS_SECRET_ACCESS_KEY environment variables, where the corresponding Service Account possesses permissions sufficient to create, interrogate and delete ec2 block storage.

step by step​

First, please do the following:

1. Download and install stackql from our website.
2. For google:
   • (i) Create and download a Google Service Account Key as per Google documentation. Remember the location of your key file.
   • (ii) You will need to grant the Service Account at least read, list, create, and delete privileges. For more information about google iam and Service Accounts in particular, please consult the documentation. For this exercise, grant your service account the roles/compute.storageAdmin role would be adequate.
3. For AWS:
   • (i) Create and download AWS user credentials as per AWS documentation. We will require long-lived credentials. In keeping with vendor advice, we strongly recommend against using root user credentials. We have created a dedicated CICD user for this exercise.
   • (ii) Set up the AWS CLI environment variables as per the documentation.
   • (iii) The user will need create / read / delete privileges against ec2 volumes. This can be done though the AWS IAM console in various ways. For example, one can use groups and permission policies. Adding your user to a group with AmazonEC2FullAccess will certainly work, although lesser privileges may be adequate.

Then, create some shell variables:

# you will need to edit the file path as appropriate

GOOGLE_DOWNLOADED_KEY_FILE_PATH="/path/to/your/downloaded/key.json"

AWS_AUTH_FRAGMENT='{ "type": "aws_signing_v4", "credentialsenvvar": "AWS_SECRET_ACCESS_KEY", "keyIDenvvar": "AWS_ACCESS_KEY_ID" }'

GOOGLE_AUTH_FRAGMENT='{ "credentialsfilepath": "'"${GOOGLE_DOWNLOADED_KEY_FILE_PATH}"'", "type": "service_account" }'

export STACKQL_AUTH_CTX='{ "aws":  '"${AWS_AUTH_FRAGMENT}"', "google": '"${GOOGLE_AUTH_FRAGMENT}"'  }'

$GOOGLE_DOWNLOADED_KEY_FILE_PATH = "C:\path\to\your\downloaded\key.json"

$AWS_AUTH_FRAGMENT = '{ "type": "aws_signing_v4", "credentialsenvvar": "AWS_SECRET_ACCESS_KEY", "keyIDenvvar": "AWS_ACCESS_KEY_ID" }'

$GOOGLE_AUTH_FRAGMENT = '{ "credentialsfilepath": "' + $GOOGLE_DOWNLOADED_KEY_FILE_PATH + '", "type": "service_account" }'

$env:STACKQL_AUTH_CTX = '{ "aws": ' + $AWS_AUTH_FRAGMENT + ', "google": ' + $GOOGLE_AUTH_FRAGMENT + ' }'

Start a stackql shell session​

To start an interactive shell session, in the same shell you setup your envrioment variables, run:

stackql --auth="${STACKQL_AUTH_CTX}" shell

You can exit at any time with ctrl + C.

Setup and meta queries to get started​

StackQL providers are installed from the StackQL Provider Registry using the REGISTRY command. StackQL supports meta queries such as SHOW and DESCRIBE which can be used to explore the available services, resources, fields, and operations available in a given cloud or SaaS provider.

-- see available providers
registry pull list;

-- pull the required providers
registry pull google;

registry pull aws;

-- some the installed providers
show providers;

-- some meta queries
show services in google;

show resources in google.compute;

describe google.compute.disks;

Create block storage (optional)​

You will need to replace the items in <ANGLE_BRACKETS>.

-- create a google volume, await and verify creation completes successfully
insert /*+ AWAIT */ into google.compute.disks(
  project, 
  zone, 
  data__name, 
  data__sizeGb
) 
select 
  '<YOUR_GCP_PROJECT>', 
  'australia-southeast1-a', 
  'my-stackql-demo-disk-01', 
  '10' ;

-- create an aws volume, operation despatched on a BEST EFFORT basis
insert into aws.ec2.volumes(
  AvailabilityZone, 
  Size, 
  region) 
select 
  'ap-southeast-2a', 
  10, 
  'ap-southeast-2';

Interrogate cloud block storage​

-- query one resource from google
select 
 name, 
 split_part(split_part(type, '/', 11), '-', 2) as type, 
 status, 
 sizeGb as size 
from google.compute.disks 
 where project = '<YOUR_GCP_PROJECT>' 
 and zone = 'australia-southeast1-a';

-- query the equivalent from aws
select 
 volumeId as name, 
 volumeType as type, 
 status, 
 size 
from aws.ec2.volumes 
 where region = 'ap-southeast-2';

-- union the equivalent resources across clouds
select 
 'google' as vendor, 
 name, 
 split_part(split_part(type, '/', 11), '-', 2) as type, 
 status, 
 sizeGb as size 
from google.compute.disks 
 where project = '<YOUR_GCP_PROJECT>' 
 and zone = 'australia-southeast1-a'
union
select 
 'aws' as vendor, 
 volumeId as name, 
 volumeType as type, 
 status, 
 size 
from aws.ec2.volumes 
 where region = 'ap-southeast-2';

-- create a view for convenience
create view dual_cloud_block_storage as
select 
 'google' as vendor, 
 name, 
 split_part(split_part(type, '/', 11), '-', 2) as type, 
 status, 
 sizeGb as size 
from google.compute.disks 
 where project = '<YOUR_GCP_PROJECT>' 
 and zone = 'australia-southeast1-a'
union
select 
 'aws' as vendor, 
 volumeId as name, 
 volumeType as type, 
 status, 
 size 
from aws.ec2.volumes 
 where region = 'ap-southeast-2';

-- select from the newly created view, with ordering
select * from dual_cloud_block_storage order by name desc;

Delete block storage (if required)​

This will only work if the disks are deletable. For example, aws.ec2.volumes must have status = available; you can check this with the view we created above.

/* delete a google volume, await and verify creation completes successfully.
One at a time only... */
delete /*+ AWAIT */ from google.compute.disks
 where project = '<YOUR_GCP_PROJECT>' 
 and zone = 'australia-southeast1-a' 
 and disk = 'my-stackql-demo-disk-01';

-- delete an aws volume, operation despatched on a BEST EFFORT basis
delete from aws.ec2.volumes 
 where VolumeId = 'vol-049ee07b31aff451a'  
 and region = 'ap-southeast-2';

Verify the cleanup was successful​

select * from dual_cloud_block_storage order by name desc;

That's it for the scripted demo!

Get involved​

The StackQL Sumologic provider is now available in the public StackQL Provider Registry. Docs are available at sumologic provider docs.

StackQL is an intelligent API client which uses SQL as a front-end language. StackQL can be used for querying cloud and SaaS providers, as well as provisioning and lifecycle operations.

The StackQL Sumo provider can query, create, update and delete Sumologic collectors and sources, view and manage ingest budgets, health events, dashboards, user and account access and activity, and more.

Some example queries include:

SELECT id, name FROM sumologic.collectors.collectors WHERE region = 'au';

or using built-in functions to simplify and format query outputs, such as:

SELECT alive, datetime(lastSeenAlive/1000, 'unixepoch') AS lastSeenAliveUtc,
datetime(lastSeenAlive/1000, 'unixepoch', 'localtime') AS lastSeenAliveLocal
FROM sumologic.collectors.collectors
WHERE region = 'au' AND id = 116208196;

another example...

SELECT id, email,
firstName || ' ' || lastName AS fullName,
isMfaEnabled,
lastLoginTimestamp,
round(julianday('now') - julianday(lastLoginTimestamp), 0) as daysSinceLastLogin
FROM sumologic.users.users WHERE region = 'au';

An example using StackQL with the Sumologic provider to query users and roles and join the results to get a list of users and their roles:

SELECT u.email as email, r.name AS role
FROM sumologic.users.users u
JOIN sumologic.roles.roles r 
ON JSON_EXTRACT(u.roleIds, '$[0]') = r.id
WHERE u.region = 'au' AND r.region = 'au';

An example using StackQL and Jupyter is shown here (see stackql/stackql-jupyter-demo):

StackQL can also be used to provision objects in Sumologic, the following query can be used to create a collector for instance:

INSERT INTO sumologic.collectors.collectors(region, data__collector)
SELECT 'au',
'{ "collectorType":"Hosted", "name":"My Hosted Collector", "description":"An example Hosted Collector", "category":"HTTP Collection" }';

Let us know what you think!