4 posts tagged with "cpsm"

We're excited to announce the release of two new StackQL providers: datadog and pagerduty. The daatdog provider includes 41 services and 405 methods at your disposal, you can query and manage everything from APM retention filters, audit logs, to cloud workload security and more. More information on the dataog provider can be found here.

The pagerduty provider includes an array of services like events, metrics, monitors, and users to fully leverage the operational prowess of these platforms. Whether it's maintaining the security posture with cloud_workload_security and security_monitoring or managing resources with containers and incidents, StackQL gives you the visibility and control over pagerduty, datadog or numerous other XaaS platforms. More information on the pagerduty provider can be found here.

Let us know your thoughts! Visit us and give us a ⭐ on GitHub

An exciting new feature to cap off 2023! Parallel query execution in StackQL. With the latest release of StackQL, parameters in WHERE IN clauses are fetched asyncronously.

This query for example, queries lambda functions across 17 AWS regions in under 1.5 sec, technically these are 17 parallel queries to 17 different endpoints.

SELECT region, function_name
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'
)

You could do something similar for other hyperscalars, for example querying resources across projects in GCP asynchronously, or querying across resource groups in Azure asynchronously.

This capability was previously available using the pystackql package, as discussed in the Query Resources Across AWS Regions Asynchronously blog post, but is now available natively in the StackQL query optimizer.

You just need to add --execution.concurrency.limit=-1 to your stackql exec or stackql shell commands or when starting a StackQL Server using stackql srv. More query optimizations coming! Happy New Year! 🎉 🎉 🎉

Let us know your thoughts! Visit us and give us a ⭐ on GitHub

You can leverage the powerful combination of StackQL and PowerBI to create comprehensive dashboard interfaces. These dashboards are perfect for reporting on various aspects such as cloud security, inventory, and configuration.

Quick Start Guide​

Set Up StackQL Server​

To get started, you can run a StackQL server container on port 7432. Use the following project for easy setup: StackQL Server on GitHub.

Create an ODBC Connection​

Next, set up an ODBC connection using the PostgreSQL ODBC driver. You can download the latest driver from the PostgreSQL ODBC Driver Versions. Install this driver on your local machine to proceed.

Integrating with PowerBI​

Once your ODBC connection is ready, you can move on to PowerBI. Here’s how you can integrate StackQL queries into PowerBI:

1. Create Data Sources in PowerBI: For each StackQL query that you want to visualize, create a new data source in PowerBI.

2. Test Queries Locally: Before integrating with PowerBI, you can test your StackQL queries locally using psql. For example:

   $ psql -h localhost -p 7432 -U stackql -d stackql
   psql (14.9 (Ubuntu 14.9-0ubuntu0.22.04.1), server 0.0.0)
   Type "help" for help.
   
   stackql=> select name, stargazers FROM
   (select name, stargazers_count as stargazers
   from github.repos.repos 
   where org = 'stackql' 
   and visibility = 'public'
   order by stargazers_count desc) t
   limit 3;
                  name                | stargazers 
   -----------------------------------+------------
    stackql                           | 179
    stackql-provider-registry         | 21
    google-discovery-to-openapi       | 18
   (3 rows)
   

3. Visualize with PowerBI: With your named data sources created, you can now visualize the result sets in PowerBI. Use various visualization tools like bar charts, pie charts, and line charts to create rich and insightful dashboards.

Let us know your thoughts! Visit us and give us a ⭐ on GitHub

Most AWS services and resources are regionally scoped, meaning the UI, CLI, SDKs, and all other methods of querying the aws provider give you a regional view (us-east-1 or ap-southeast-2, for instance). Many customer AWS estates span multiple regions - for multinational organizations, for example, or organizations with numerous dispersed locations within the US.

Sure, you could write custom scripts wrapping the CLI or SDKs - which would require development effort (not reusable for other providers); or get an abstract view with tools like AWS Config or Systems Manager, which requires these services to be enabled and configured (not flexible and not extendible to other providers). In either case:

1. You can't write and run customized queries and generate custom reports - as you can do in SQL
2. Any solutions you build will have to be rebuilt entirely for other providers

Using the latest (AWS provider for StackQL - which leverages the AWS Cloud Control API) and the executeQueriesAsync method in the pystackql Python package, I've put together an example here which runs a query to bring back attributes from all AWS Lambda functions deployed across 17 different AWS regions asynchronously. Results can be returned as a list of Python dictionaries or a Pandas dataframe. I am doing the former here, which took less than 10s.

from pystackql import StackQL
from pprint import pprint
from asyncio import run
stackql = StackQL()
stackql.executeStmt("REGISTRY PULL aws") # not required if the aws provider is already installed

async def stackql_async_queries(queries):
    return await stackql.executeQueriesAsync(queries)

regions= ["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"]

# list functions from all regions asynchronously
get_fns = [
    f"""
    SELECT *
    FROM aws.lambda.functions
    WHERE region = '{region}'
    """
    for region in regions
]

functions = run(stackql_async_queries(get_fns))

# get function details for all functions across all regions asynchronously
get_fn_details = [
    f"""
    SELECT 
    function_name,
    region,
    arn,
    description,
    architectures,
    memory_size,
    runtime
    FROM aws.lambda.function
    WHERE region = '{function['region']}'
    AND data__Identifier = '{function['function_name']}'
    """
    for function in functions
]

function_details = run(stackql_async_queries(get_fn_details))
pprint(function_details)

which returns...

[{'architectures': '["x86_64"]',
  'arn': 'arn:aws:lambda:us-east-1:824532806693:function:stackql-helloworld-fn',
  'description': '',
  'function_name': 'stackql-helloworld-fn',
  'memory_size': '128',
  'region': 'us-east-1',
  'runtime': 'nodejs18.x'},
 {'architectures': '["x86_64"]',
  'arn': 'arn:aws:lambda:us-east-2:824532806693:function:stackql-helloworld-fn',
  'description': '',
  'function_name': 'stackql-helloworld-fn',
  'memory_size': '128',
  'region': 'us-east-2',
  'runtime': 'nodejs18.x'},
 {'architectures': '["x86_64"]',
  'arn': 'arn:aws:lambda:us-west-1:824532806693:function:stackql-helloworld-fn',
  'description': '',
  'function_name': 'stackql-helloworld-fn',
  'memory_size': '128',
  'region': 'us-west-1',
  'runtime': 'nodejs18.x'},
...

You could customize the StackQL query to run specific reports and visualize the results in a Jupyter notebook, for example:

• Functions by runtimes
• Function by memory size
• Functions by tags
• etc...

You could do something similar for other hyperscalars, for example, GCP, which scopes resources by projects, or Azure, which scopes resources by resource groups.

Let us know your thoughts! Visit us and give us a ⭐ on GitHub