Skip to main content

· 3 min read

Have you been sent one of these?

[Action Required] AWS Lambda end of support for Node.js 18 [AWS Account: 824123456789][EU-CENTRAL-1]

If you are like me and manage AWS accounts with numerous Lambda functions potentially deployed across multiple regions, you need to identify affected resources, in this case, Lambda node runtimes, which will be discontinued later this year.  

With stackql this task is easy...

  1. Open AWS cloud shell in your AWS account (any region - it doesn't matter)
  2. Download stackql
curl -L https://bit.ly/stackql-zip -O && unzip stackql-zip
  1. Open an authenticated stackql command shell
sh stackql-aws-cloud-shell.sh
  1. Run some analytic queries using stackql; here are some examples...

🔍 List all functions and runtimes across regions​

Run a stackql query to get the details about functions, runtimes, etc, deployed at any given time across one or more AWS regions.  You can include all 25 AWS regions; each query will be performed asynchronously - speeding up the results.

select 
  function_name, 
  region,
  runtime 
FROM aws.lambda.functions 
WHERE region IN ('us-east-1', 'eu-west-1');

📊 Group by runtime and region​

Perform an analytic query like a group by aggregate query such as...

select 
  runtime, 
  region, 
  count(*) as num_functions 
FROM aws.lambda.functions 
WHERE region IN ('us-east-1', 'eu-west-1', 'ap-southeast-2')
GROUP BY runtime, region;
tip

You can easily visualise this data using a notebook; see stackql-codespaces-notebook or stackql-jupyter-demo.

Using StackQL you can:

  • Quickly spot functions running on runtimes like nodejs18.x that are approaching end of support.
  • Plan your upgrades region-by-region with confidence.

⭐ us on GitHub and join our community!

· 2 min read
info

To get started with the aws provider for stackql, pull the provider from the registry as follows:  

registry pull aws;

for more detailed provider documentation, see here.

Happy New Year 🎉. The latest AWS provider for StackQL is now available.  The StackQL AWS Provider by the numbers:

  • 230 services
  • 3174 resources
  • 3917 methods

with additional new support for the following services:

  • amazonmq - Managed message broker service for Apache ActiveMQ and RabbitMQ that simplifies setup and operation of open-source message brokers on AWS.
  • applicationsignals - CloudWatch Application Signals automatically provides a correlated view of application performance that includes real user monitoring data and canaries.
  • apptest - AWS mainframe modernization ppplication Testing
  • connectcampaignsv2 - Amazon Connect Outbound Campaigns V2
  • invoicing - Deploy and query invoice units allowing you separate AWS account costs and configures your invoice for each business entity
  • launchwizard - Easily size, configure, and deploy third party applications on AWS
  • pcaconnectorscep - AWS Private CA Connector for SCEP
  • pcs - AWS Parallel Computing Service, easily run HPC workloads at virtually any scale
  • rbin - Recycle Bin is a resource recovery feature that enables you to restore accidentally deleted snapshots and EBS-backed AMIs.
  • s3tables - Amazon S3 Tables enabling Tabular Data Storage At Scale
  • ssmquicksetup - AWS Systems Manager Quick Setup

And 150 new resources with some notable additions including:

  • aws.apigateway.domain_name_access_associations
  • aws.appconfig.deployments, aws.appconfig.deployment_strategies
  • aws.batch.job_definitions
  • aws.bedrock.flows, aws.bedrock.prompts
  • aws.chatbot.custom_actions
  • aws.cloudformation.guard_hooks, aws.cloudformation.lambda_hooks
  • aws.cloudfront.anycast_ip_lists
  • aws.cloudtrail.dashboards, aws.cloudwatch.dashboards
  • aws.codepipeline.pipelines
  • aws.cognito.user_pool_identity_providers
  • aws.ec2.security_group_vpc_associations, aws.ec2.vpc_block_public_access_exclusions, aws.ec2.vpc_block_public_access_options
  • aws.glue.crawlers, aws.glue.databases, aws.glue.jobs, aws.glue.triggers
  • aws.guardduty.malware_protection_plans
  • aws.iot.commands
  • aws.memorydb.multi_region_clusters
  • aws.rds.db_shard_groups
  • aws.redshift.integrations
  • aws.sagemaker.clusters, aws.sagemaker.endpoints
  • aws.secretsmanager.resource_policies, aws.secretsmanager.rotation_schedules, aws.secretsmanager.secret_target_attachments
  • aws.workspaces.workspaces_pools
  • aws.wisdom.ai_agents, aws.wisdom.ai_prompts, aws.wisdom.ai_guardrails, aws.wisdom.message_templates
  • and much more!

⭐ us on GitHub and join our community!

· 6 min read

We are pleased to announce the release of the Databricks provider for StackQL today.  The Databricks provider is two different providers, databricks_account and databricks_workspace.

info

Check out the registry docs at databricks_account or databricks_workspace.

To get started, pull the providers from the registry as follows:  

registry pull databricks_account;
registry pull databricks_workspace;

databricks_account provider​

The databricks_account provider is used for account-level operations, including provisioning or managing users, groups, unity catalog metastores, workspaces, and account-level cloud resources used by workspaces (such as networking resources).  Services include:

stackql  >>show services in databricks_account;
|----------------------------|---------------|--------------------------------|
|             id             |     name      |             title              |
|----------------------------|---------------|--------------------------------|
| billing:v00.00.00000       | billing       | Account Billing                |
|----------------------------|---------------|--------------------------------|
| iam:v00.00.00000           | iam           | Identity and Access Management |
|----------------------------|---------------|--------------------------------|
| logging:v00.00.00000       | logging       | Log Delivery                   |
|----------------------------|---------------|--------------------------------|
| oauth:v00.00.00000         | oauth         | OAuth Integrations             |
|----------------------------|---------------|--------------------------------|
| provisioning:v00.00.00000  | provisioning  | Account Provisioning           |
|----------------------------|---------------|--------------------------------|
| settings:v00.00.00000      | settings      | Account Settings               |
|----------------------------|---------------|--------------------------------|
| unity_catalog:v00.00.00000 | unity_catalog | Unity Catalog                  |
|----------------------------|---------------|--------------------------------|

Some example databricks_account queries are shown here:

stackql  >>select *  from  databricks_account.iam.users where account_id = 'ebfcc5a9-9d49-4c93-b651-b3ee6cf1c9ce' and active = true;
|--------|--------------|-------------------------------------------------------------|------------|------------------|---------------------------------------------|---------------------------------------------|------------------|
| active | displayName  |                           emails                            | externalId |        id        |                    name                     |                    roles         
 |     userName     |
|--------|--------------|-------------------------------------------------------------|------------|------------------|---------------------------------------------|---------------------------------------------|------------------|
| true   | Jeffrey Aven | [{"primary":true,"type":"work","value":"javen@stackql.io"}] | null       | 5728205706991489 | {"familyName":"Aven","givenName":"Jeffrey"} | [{"type":"direct","value":"account_admin"}] | javen@stackql.io |
|--------|--------------|-------------------------------------------------------------|------------|------------------|---------------------------------------------|---------------------------------------------|------------------|

or..

stackql  >>SELECT applicationId,  displayName
stackql  >>FROM databricks_account.iam.service_principals, JSON_EACH(roles)
stackql  >>WHERE account_id = 'ebfcc5a9-9d49-4c93-b651-b3ee6cf1c9ce'
stackql  >>AND JSON_EXTRACT(json_each.value, '$.value') = 'account_admin';
|--------------------------------------|-------------|
|            applicationId             | displayName |
|--------------------------------------|-------------|
| 0b7b23de-3e7d-4432-812c-cf517e079a22 | stackql     |
|--------------------------------------|-------------|

or..

stackql  >>select
stackql  >>workspace_id,
stackql  >>workspace_name,
stackql  >>deployment_name,
stackql  >>workspace_status,
stackql  >>pricing_tier,
stackql  >>aws_region,
stackql  >>credentials_id,
stackql  >>storage_configuration_id
stackql  >>from
stackql  >>databricks_account.provisioning.workspaces where account_id = 'ebfcc5a9-9d49-4c93-b651-b3ee6cf1c9ce';
|------------------|----------------|-------------------|------------------|--------------|------------|--------------------------------------|--------------------------------------|
|   workspace_id   | workspace_name |  deployment_name  | workspace_status | pricing_tier | aws_region |            credentials_id            |       storage_configuration_id       |
|------------------|----------------|-------------------|------------------|--------------|------------|--------------------------------------|--------------------------------------|
| 1583879855205171 | stackql-test   | dbc-ddbc0f51-c9cf | RUNNING          | PREMIUM      | us-west-2  | dcacd875-c782-46ea-9d3e-8307975d758a | e52e029f-24bb-4a75-99c3-7796c202dd89 |
|------------------|----------------|-------------------|------------------|--------------|------------|--------------------------------------|--------------------------------------|

databricks_workspace provider​

The databricks_workspace provider is used for workspace-level operations, such as provisioning and managing clusters, dashboards, and workflow jobs (including delta live table pipelines).  Services include:  

stackql  >>show services in databricks_workspace;
|------------------------------|-----------------|-----------------|
|              id              |      name       |      title      |
|------------------------------|-----------------|-----------------|
| apps:v24.12.00279            | apps            | Apps            |
|------------------------------|-----------------|-----------------|
| cleanrooms:v24.12.00279      | cleanrooms      | Cleanrooms      |
|------------------------------|-----------------|-----------------|
| compute:v24.12.00279         | compute         | Compute         |
|------------------------------|-----------------|-----------------|
| dbsql:v24.12.00279           | dbsql           | Dbsql           |
|------------------------------|-----------------|-----------------|
| deltalivetables:v24.12.00279 | deltalivetables | Deltalivetables |
|------------------------------|-----------------|-----------------|
| deltasharing:v24.12.00279    | deltasharing    | Deltasharing    |
|------------------------------|-----------------|-----------------|
| filemanagement:v24.12.00279  | filemanagement  | Filemanagement  |
|------------------------------|-----------------|-----------------|
| iam:v24.12.00279             | iam             | Iam             |
|------------------------------|-----------------|-----------------|
| lakeview:v24.12.00279        | lakeview        | Lakeview        |
|------------------------------|-----------------|-----------------|
| machinelearning:v24.12.00279 | machinelearning | Machinelearning |
|------------------------------|-----------------|-----------------|
| marketplace:v24.12.00279     | marketplace     | Marketplace     |
|------------------------------|-----------------|-----------------|
| realtimeserving:v24.12.00279 | realtimeserving | Realtimeserving |
|------------------------------|-----------------|-----------------|
| repos:v24.12.00279           | repos           | Repos           |
|------------------------------|-----------------|-----------------|
| secrets:v24.12.00279         | secrets         | Secrets         |
|------------------------------|-----------------|-----------------|
| unitycatalog:v24.12.00279    | unitycatalog    | Unitycatalog    |
|------------------------------|-----------------|-----------------|
| vectorsearch:v24.12.00279    | vectorsearch    | Vectorsearch    |
|------------------------------|-----------------|-----------------|
| workflows:v24.12.00279       | workflows       | Workflows       |
|------------------------------|-----------------|-----------------|
| workspace:v24.12.00279       | workspace       | Workspace       |
|------------------------------|-----------------|-----------------|

An example query could be:

stackql  >>select
stackql  >>cluster_id,
stackql  >>aws_attributes,
stackql  >>node_type_id,
stackql  >>state
stackql  >>from
stackql  >>databricks_workspace.compute.clusters
stackql  >>where deployment_name = 'dbc-ddbc0f51-c9cf';
|----------------------|---------------------------------------------------------------------------------------------------------|--------------|------------|
|      cluster_id      |                                             aws_attributes                                              | node_type_id |   state    |
|----------------------|---------------------------------------------------------------------------------------------------------|--------------|------------|
| 1218-233957-q9v9oi86 | {"availability":"SPOT_WITH_FALLBACK","first_on_demand":1,"spot_bid_price_percent":100,"zone_id":"auto"} | m5d.large    | TERMINATED |
|----------------------|---------------------------------------------------------------------------------------------------------|--------------|------------|

To use either provider, set the following environment variables (either locally or as secrets in your preferred CI tool):

  • DATABRICKS_ACCOUNT_ID - a uuid representing your Databricks account id, you can get this from the Databricks UI
  • DATABRICKS_CLIENT_ID - obtained after creating a service principal through the Databricks UI
  • DATABRICKS_CLIENT_SECRET - obtained after creating a service principal secret through the Databricks UI, using the "Generate Secret" function

These are the same variables that Terraform, the Databricks SDKs, and CLI use.  

stackql-deploy examples coming soon, stay tuned!  

⭐ us on GitHub and join our community!

· 3 min read

We're excited to announce the release of the new Confluent provider for StackQL! With this new provider, users can now seamlessly query, manage, and integrate Confluent Cloud resources using familiar SQL syntax. The Confluent provider opens up possibilities for managing Kafka clusters, environments, organizations, and more, providing unparalleled flexibility for building data and event-driven architectures as infrastructure-as-code.

Quick Start Example​

To start, set the CONFLUENT_CLOUD_API_KEY and CONFLUENT_CLOUD_API_SECRET environment variables and then pull the Confluent provider from the StackQL registry:

registry pull confluent;

Querying Confluent Resources​

The Confluent provider includes access to a range of services covering resources like billing, catalog, managed Kafka clusters, environments, and more. Let’s look at a few examples of querying these resources.

Listing Organizations​

To view the organization associated with your Confluent account, use the following query:

stackql >> select * from confluent.org.vw_organizations;
|--------------------------------------|----------------|-------------------------------------------------------------------------|-----------------------------|-----------------------------|-------------|-------------|--------------|
|                  id                  |  display_name  |                              resource_name                              |         created_at          |         updated_at          | jit_enabled | api_version |     kind     |
|--------------------------------------|----------------|-------------------------------------------------------------------------|-----------------------------|-----------------------------|-------------|-------------|--------------|
| 73ea43f0-1685-4a78-bc90-fa63ef8102fe | Aven Solutions | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe | 2024-09-06T21:51:43.895116Z | 2024-09-08T09:23:53.147453Z | false       | org/v2      | Organization |
|--------------------------------------|----------------|-------------------------------------------------------------------------|-----------------------------|-----------------------------|-------------|-------------|--------------|

Listing Environments​

To list the available environments in your organization, use this query:

select * from confluent.org.vw_environments;
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|
|     id     | display_name | stream_governance_package |                                         resource_name                                          |         created_at          |         updated_at          |                            self                            | api_version |    kind     |
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|
| env-1wz7pv | default      | null                      | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-1wz7pv | 2024-09-06T21:51:43.901757Z | 2024-09-06T21:51:43.901757Z | https://api.confluent.cloud/org/v2/environments/env-1wz7pv | org/v2      | Environment |
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|
| env-216dqo | stackql      | ESSENTIALS                | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-216dqo | 2024-10-29T03:47:21.577972Z | 2024-10-29T03:47:21.577972Z | https://api.confluent.cloud/org/v2/environments/env-216dqo | org/v2      | Environment |
|------------|--------------|---------------------------|------------------------------------------------------------------------------------------------|-----------------------------|-----------------------------|------------------------------------------------------------|-------------|-------------|

Fetching Kafka Clusters in a Specific Environment​

To list Kafka clusters available within a specific environment, modify the WHERE clause to target your desired environment:

stackql >> select * 
stackql >> from confluent.managed_kafka_clusters.vw_clusters 
stackql >> where environment = 'env-216dqo';
|------------|-------------|--------------|--------------|------------------------------------------------------------------------------------------------------------------------------------------|--------------|--------------|-------|-------------|----------------|------------------------------------------------------------|------------------------------------------------------------------------------------------------|-----------------------------------------------------|---------------------------------------------------------|-----------|-----------------------------|-----------------------------|--------------------------------------------------------|-------------|---------|
|     id     | environment | display_name | status_phase |                                                              resource_name                                                   
            | api_endpoint | availability | cloud | config_kind | environment_id |                    environment_related                     |                                   environment_resource_name                                    |                    http_endpoint                    |                kafka_bootstrap_endpoint                 |  region   |         created_at          |         updated_at          |                          self                          | api_version |  kind   |
|------------|-------------|--------------|--------------|------------------------------------------------------------------------------------------------------------------------------------------|--------------|--------------|-------|-------------|----------------|------------------------------------------------------------|------------------------------------------------------------------------------------------------|-----------------------------------------------------|---------------------------------------------------------|-----------|-----------------------------|-----------------------------|--------------------------------------------------------|-------------|---------|
| lkc-ov720o | env-216dqo  | cluster_0    | PROVISIONED  | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-216dqo/cloud-cluster=lkc-ov720o/kafka=lkc-ov720o |              | LOW          | AWS   | Basic       | env-216dqo     | https://api.confluent.cloud/org/v2/environments/env-216dqo | crn://confluent.cloud/organization=73ea43f0-1685-4a78-bc90-fa63ef8102fe/environment=env-216dqo | https://pkc-p11xm.us-east-1.aws.confluent.cloud:443 | SASL_SSL://pkc-p11xm.us-east-1.aws.confluent.cloud:9092 | us-east-1 | 2024-10-29T03:48:00.562964Z | 2024-10-29T03:48:00.562964Z | https://api.confluent.cloud/cmk/v2/clusters/lkc-ov720o | cmk/v2      | Cluster |
|------------|-------------|--------------|--------------|------------------------------------------------------------------------------------------------------------------------------------------|--------------|--------------|-------|-------------|----------------|------------------------------------------------------------|------------------------------------------------------------------------------------------------|-----------------------------------------------------|---------------------------------------------------------|-----------|-----------------------------|-----------------------------|--------------------------------------------------------|-------------|---------|

With these examples, you can see how StackQL makes it easy to interact with Confluent Cloud resources directly through SQL.

Confluent Services Supported in StackQL​

The new Confluent provider for StackQL includes the following services:

  • Billing: Manage Confluent Cloud billing and view cost metrics.
  • Catalog: Explore available Confluent Cloud components.
  • Managed Kafka Clusters: Query and manage Kafka clusters.
  • Flink Artifacts and Compute Pools: Manage Flink environments and compute resources.
  • IAM: Configure access controls and permissions.
  • Networking: Set up and view networking configurations.
  • Schema Registry and Clusters: Register, manage, and monitor schemas and clusters.
  • Stream Sharing: Configure shared data streams.

See the full provider documentation at Confluent Provider for StackQL for more details on each service.

Building Composable Infrastructure Stacks​

The Confluent provider for StackQL allows you to compose infrastructure stacks with Confluent resources as part of a broader data infrastructure, integrating seamlessly with other cloud providers. With simple SQL queries, you can pull in resources, monitor usage, and manage configurations across Confluent and other clouds for a cohesive multi-cloud or hybrid cloud setup.

More examples to follow. Let us know what you think! ⭐ us on GitHub.

· 2 min read

Have you received one of these?

Azure TLS Deprecation Email

Microsoft Azure is retiring TLS 1.0 and 1.1 for its services, requiring customers to transition to TLS 1.2 or later to ensure uninterrupted connectivity. If you have workloads still using older TLS versions, you’ll need to update them.

Using StackQL to Identify Non-Compliant Resources​

With StackQL, you can quickly identify resources in your Azure environment that are still using older TLS versions. This article shows how to leverage StackQL queries to check various Azure services for compliance.

Prerequisites​

  1. Pull the latest StackQL provider for Azure using REGISTRY PULL azure.
  2. Authenticate with Azure using StackQL by setting up your credentials as environment variables (or using your existing az login system/session authentication).

Queries to Run​

Below are example queries you can use to identify resources affected by the TLS 1.2 requirement (use your subscriptionId of course):

1. Check Application Gateway Configurations​

Azure Application Gateways may support older TLS versions. Run the following query to get their configurations:

SELECT
    id,
    name,
    JSON_EXTRACT(properties, '$.sslPolicy') as ssl_policy,
    JSON_EXTRACT(properties, '$.defaultPredefinedSslPolicy') as default_predefined_ssl_policy
FROM
    azure.network.application_gateways
WHERE
    subscriptionId = '123e4567-e89b-12d3-a456-426614174000'
    AND ssl_policy IS NOT NULL
    AND JSON_EXTRACT(properties, '$.sslPolicy') NOT LIKE '%TLS12%';

This query lists all Application Gateways configured with TLS versions lower than 1.2.

2. Inspect App Service Configurations​

If you use Azure App Services (Web Apps), check their TLS configurations with this query:

SELECT
    id,
    name,
    JSON_EXTRACT(properties, '$.httpsOnly') as https_only,
    JSON_EXTRACT(properties, '$.siteConfig.minTlsVersion') as min_tls_version
FROM
    azure.app_service.web_apps
WHERE
    subscriptionId = '123e4567-e89b-12d3-a456-426614174000'
    AND JSON_EXTRACT(properties, '$.siteConfig.minTlsVersion') < '1.2';

This returns all web apps that allow connections using TLS versions older than 1.2.

3. Check SQL Server Instances​

Azure SQL Databases and SQL Managed Instances may also have TLS configurations that need checking:

SELECT
    location,
    fully_qualified_domain_name,
    minimal_tls_version,
    state
FROM 
    azure.sql.vw_servers
WHERE 
    subscriptionId = '123e4567-e89b-12d3-a456-426614174000'
    AND minimal_tls_version < '1.2';

This shows all SQL servers with a minimal TLS version set below 1.2.

We’d love to hear your feedback. ⭐ us on GitHub and let us know how StackQL helps you manage your Azure resources!