6 posts tagged with "finops"

We've released an updated StackQL Datadog provider covering the Datadog v1 and v2 REST APIs together: 18 services, 597 resources and 1658 operations, up from 16 services and 575 operations in the previous release.

What's new​

The previous provider was built from the v2 API alone. Datadog's most-used resources - monitors, dashboards, synthetics, SLOs, hosts, log indexes and pipelines - only exist in the v1 API, so this release merges the two specs into one provider. The v2 surface has also grown considerably since the last build. In summary:

• The v1 API: monitors (list, search, create, replace, validate, delete), dashboards and dashboard lists, synthetics tests (API, browser and mobile), locations, private locations and global variables, SLOs and SLO corrections, hosts, host totals and host tags, notebooks, log indexes and pipelines, the Azure, PagerDuty, Slack and webhook integrations, and usage metering.
• New v2 surfaces: cases and case projects, on-call schedules, escalation policies and paging, status pages, incident configuration and responders, feature flags, deployment gates, LLM Observability (projects, datasets, experiments, prompts, annotation queues), Fleet Automation, cloud cost budgets, commitments and tag pipelines, security findings automation, static analysis and SCA, agentless scanning, SIEM historical detections, RUM replay and product analytics, reference tables, org groups and personal access tokens, among others.
• Site from the environment: the provider addresses https://api.{site}, and site is resolved from DD_SITE when it is set (datadoghq.eu, us5.datadoghq.com, ap2.datadoghq.com, ...), the same convention as the Datadog Agent and API clients. Queries carry no site clause; a WHERE site = '...' still wins for one statement.
• Pagination and pushdown: cursor-paginated lists (audit events, container images, spans, RUM events, CI events, security signals and findings) are traversed transparently, and a SQL LIMIT is sent as the API's page-size parameter.
• snake_case surface: columns and WHERE / INSERT keys are snake_case throughout; the few camelCase wire names are aliased.
• Terraform-aligned authentication: DD_API_KEY and DD_APP_KEY, unchanged.

Service highlights​

Authentication​

Export an API key and an application key; set DD_SITE if your organization is not on datadoghq.com:

export DD_API_KEY=...
export DD_APP_KEY=...
export DD_SITE=datadoghq.eu     # optional, defaults to datadoghq.com

Monitors​

Every monitor with its state:

SELECT id, name, type, overall_state, tags
FROM datadog.monitoring.monitors;

Only alerting monitors, using the API's own filter:

SELECT id, name, overall_state
FROM datadog.monitoring.monitors
WHERE group_states = 'alert';

Monitor search, with the same syntax as the Manage Monitors page:

SELECT id, name, status, type
FROM datadog.monitoring.monitor_search_results
WHERE query = 'type:metric status:alert';

Dashboards, SLOs and synthetics​

SELECT id, title, layout_type, author_handle, modified_at
FROM datadog.dashboards.dashboards;

SELECT id, name, type, target_threshold, timeframe
FROM datadog.service_management.slos;

SELECT public_id, name, type, status, locations
FROM datadog.monitoring.synthetics_tests;

Users, roles and keys​

v2 resources return the JSON:API row shape - id, type, attributes, relationships - so attributes are one json_extract away. A user audit:

SELECT id,
       json_extract(attributes, '$.email') AS email,
       json_extract(attributes, '$.status') AS status,
       json_extract(attributes, '$.disabled') AS disabled,
       json_extract(attributes, '$.created_at') AS created_at
FROM datadog.organization.users;

API keys by age, the input to a rotation policy:

SELECT id,
       json_extract(attributes, '$.name') AS name,
       json_extract(attributes, '$.created_at') AS created_at,
       json_extract(attributes, '$.last4') AS last4
FROM datadog.organization.api_keys
ORDER BY created_at;

Infrastructure and logs​

Hosts reporting to Datadog, and the log indexes with their retention:

SELECT host_name, up, is_muted, apps, last_reported_time
FROM datadog.infrastructure.hosts;

SELECT name, num_retention_days, daily_limit
FROM datadog.logs.indexes;

Audit log​

The audit event list is cursor-paginated and takes the time window as a query parameter:

SELECT json_extract(attributes, '$.timestamp') AS timestamp,
       json_extract(attributes, '$.attributes.evt.name') AS event,
       json_extract(attributes, '$.attributes.usr.email') AS actor
FROM datadog.organization.audit_logs
WHERE "filter[from]" = 'now-1d';

Security monitoring rules​

Which detection rules are enabled, and who last changed them:

SELECT id, name, type, is_enabled, is_default, updated_at, update_author_id
FROM datadog.security.monitoring_rules
WHERE is_default = false;

Provisioning​

Mutations use the same SQL grammar. v1 resources take their fields as columns; v2 resources take the JSON:API data document. A monitor end to end - validate the definition, create it, replace it (the v1 monitor API updates with PUT), delete it:

EXEC datadog.monitoring.monitors.validate_monitor
  @type = 'metric alert',
  @query = 'avg(last_5m):avg:system.cpu.user{env:prod} by {host} > 90',
  @name = 'High CPU on prod hosts';

INSERT INTO datadog.monitoring.monitors (name, type, query, message, tags)
SELECT 'High CPU on prod hosts',
       'metric alert',
       'avg(last_5m):avg:system.cpu.user{env:prod} by {host} > 90',
       'CPU above 90% on {{host.name}} @slack-ops',
       '["team:web", "managed-by:stackql"]';

REPLACE datadog.monitoring.monitors
SET name = 'High CPU on prod hosts', type = 'metric alert',
    query = 'avg(last_5m):avg:system.cpu.user{env:prod} by {host} > 95'
WHERE monitor_id = 12345678;

DELETE FROM datadog.monitoring.monitors
WHERE monitor_id = 12345678;

A downtime for a release window, and a role:

INSERT INTO datadog.service_management.downtimes (data)
SELECT '{"type": "downtime",
         "attributes": {"message": "release window", "scope": "env:prod",
                        "monitor_identifier": {"monitor_tags": ["team:web"]},
                        "schedule": {"start": "2026-09-01T22:00:00Z", "end": "2026-09-01T23:00:00Z"}}}';

INSERT INTO datadog.organization.roles (data)
SELECT '{"type": "roles", "attributes": {"name": "read-only-auditors"}}';

UPDATE datadog.organization.roles
SET data = '{"id": "<role-id>", "type": "roles", "attributes": {"name": "auditors"}}'
WHERE role_id = '<role-id>';

Get started​

Pull the provider from the public registry:

registry pull datadog;

Provider docs are at datadog-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released a new StackQL provider for ClickHouse Cloud:

• clickhouse - the ClickHouse Cloud control plane (api.clickhouse.cloud): organizations, services (lifecycle, scaling, settings, passwords, private endpoints), API keys, members and invitations, organization roles, backups and backup configuration, usage cost and quotas, activities, ClickPipes, the ClickStack surface (dashboards, alerts, sources, webhooks, saved searches), user-defined functions and Managed Postgres (10 services, 44 resources, 139 operations)

The provider covers the Cloud management API only. The ClickHouse server HTTP interface - SQL against a service endpoint and the system.* tables - is a separate surface with separate authentication and is reserved as a future sibling provider, clickhouse_server. What takes two Terraform providers today (the Cloud infrastructure provider and the DBops provider) will be two namespaces in one StackQL session.

Organization scope from the environment​

Every resource except organizations is scoped to an organization. The organization ID is a server variable that StackQL resolves from CLICKHOUSE_ORG_ID when it is set, so queries carry no organization clause:

SELECT name, state, provider, region
FROM clickhouse.services.services;

A WHERE organization_id = '...' still takes precedence when you need to address another organization in the same session. Columns and WHERE/INSERT keys are snake_case (created_at, ip_access_list, service_id); the provider maps them to the API's camelCase on the wire.

Estate inventory​

State, footprint and scaling configuration for every service in one result set:

SELECT name, state, provider, region,
       num_replicas,
       min_replica_memory_gb, max_replica_memory_gb,
       json_extract(current_scaling, '$.effectiveAutoscalingMode') AS scaling_mode,
       idle_scaling, idle_timeout_minutes, clickhouse_version
FROM clickhouse.services.services
ORDER BY provider, region, name;

Organization quotas report usage against limits, which is the quickest answer to "how much room is left":

SELECT quota_code, name, value AS quota_limit, usage
FROM clickhouse.organizations.quotas;

Usage cost by day and entity​

The usage cost endpoint returns one row per entity per day in ClickHouse Credits, with the compute, storage, backup and data-transfer components in a metrics object:

SELECT date, entity_type, entity_name, total_chc,
       json_extract(metrics, '$.computeCHC') AS compute_chc,
       json_extract(metrics, '$.storageCHC') AS storage_chc,
       json_extract(metrics, '$.backupCHC')  AS backup_chc
FROM clickhouse.organizations.usage_costs
WHERE from_date = '2026-08-01' AND to_date = '2026-08-31'
ORDER BY date, entity_name;

Joined to the services list, the same data answers which stopped or idle services still carried cost in the window:

SELECT s.name, s.state, SUM(c.total_chc) AS chc_in_window
FROM clickhouse.services.services s
JOIN clickhouse.organizations.usage_costs c
  ON c.service_id = s.id
WHERE c.from_date = '2026-08-01' AND c.to_date = '2026-08-31'
  AND s.state IN ('stopped', 'idle')
GROUP BY s.name, s.state
ORDER BY chc_in_window DESC;

Scaffolding​

Provisioning is the usual SQL verbs. A service is an INSERT; the state command (start, stop, awake) is an EXEC; access-list changes are an UPDATE whose PATCH body takes add/remove arrays, passed through as written:

INSERT INTO clickhouse.services.services
  (name, provider, region, min_replica_memory_gb, max_replica_memory_gb, num_replicas, idle_scaling, idle_timeout_minutes)
SELECT 'analytics-dev', 'aws', 'us-east-1', 8, 8, 1, true, 15;

UPDATE clickhouse.services.services
SET ip_access_list = '{"add": [{"source": "203.0.113.0/24", "description": "office"}],
                       "remove": [{"source": "0.0.0.0/0", "description": "Anywhere"}]}'
WHERE service_id = '<service-uuid>';

EXEC clickhouse.services.services.update_state
  @serviceId = '<service-uuid>',
  @command = 'stop';

Organizations that use Custom Roles assign API key roles by ID, so the role lookup and the key creation are one statement, and the generated secret comes back with RETURNING:

INSERT INTO clickhouse.keys.keys (name, assigned_role_ids, state)
SELECT 'finops-reader', '["' || id || '"]', 'enabled'
FROM clickhouse.roles.roles
WHERE name = 'Organization API Reader'
RETURNING json_extract(result, '$.key.id')    AS id,
          json_extract(result, '$.keyId')     AS key_id,
          json_extract(result, '$.keySecret') AS key_secret;

Backup configuration is queryable across the estate, and settable per service:

SELECT s.name, b.backup_period_in_hours, b.backup_retention_period_in_hours, b.backup_start_time
FROM clickhouse.services.services s
JOIN clickhouse.backups.backup_configurations b
  ON b.service_id = s.id;

Observability as code​

ClickStack dashboards, alerts, sources, webhooks and saved searches are resources with full CRUD, so a dashboard definition lives in version control and is applied with an INSERT:

INSERT INTO clickhouse.clickstack.dashboards (service_id, name, tiles, tags)
SELECT '<service-uuid>', 'Service Overview',
       '[{"name": "Error rate", "x": 0, "y": 0, "w": 6, "h": 3,
          "config": {"displayType": "line",
                     "select": [{"aggFn": "count", "where": "SeverityText = ''ERROR''"}]}}]',
       '["production"]';

and audited with a SELECT:

SELECT d.name, json_array_length(d.tiles) AS tiles, d.tags
FROM clickhouse.clickstack.dashboards d
WHERE d.service_id = '<service-uuid>';

Alerts and webhooks follow the same pattern (clickhouse.clickstack.alerts, clickhouse.clickstack.webhooks), and the organization activity log is a table for audit questions:

SELECT created_at, type, actor_type, actor_details
FROM clickhouse.organizations.activities
ORDER BY created_at DESC;

The data platform estate in one query​

Cross-provider joins are ordinary SQL, so ClickHouse Cloud services sit alongside Snowflake warehouses and Databricks clusters in one inventory:

SELECT 'clickhouse' AS platform, name, state, region
FROM clickhouse.services.services
UNION ALL
SELECT 'snowflake', name, state, NULL
FROM snowflake.warehouses.warehouses
UNION ALL
SELECT 'databricks', cluster_name, state, NULL
FROM databricks_workspace.compute.clusters
WHERE deployment_name = '<workspace>';

Authentication​

Create an API key pair in the ClickHouse Cloud console (Settings -> API Keys) with the role the queries need - Organization API Reader / Service API Reader for inventory and cost queries, Service API Admin for provisioning - and export three variables:

export CLICKHOUSE_CLOUD_API_KEY=...      # Key ID
export CLICKHOUSE_CLOUD_API_SECRET=...   # Key Secret
export CLICKHOUSE_ORG_ID=...             # organization ID

The API allows a fixed window of requests per key (documented as 10 per 10 seconds); a 429 is the signal to pace wide scans.

Get started​

Pull the provider from the public registry:

registry pull clickhouse

Provider docs are at clickhouse-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released an update to the StackQL providers for the OpenAI platform:

• openai - the platform API surface available to standard API keys: models, files, fine-tuning, batches, vector stores, assistants, evals, conversations, uploads, containers, and skills (11 services, 26 resources, 97 operations)
• openai_admin [new] - the organization and administration API surface: usage and cost reporting, projects, organization users and invites, groups and roles, admin API keys, audit logs, and certificates (10 services, 29 resources, 81 operations)

Both providers expose a SQL-first surface: authentication is handled automatically, push down support using the LIMIT clause and built in pagination handling.

The openai provider is a ground-up rebuild of the previous provider, generated from the vendor's published OpenAPI specification. Some resources have been renamed and the organization/admin surface has moved to openai_admin - the previous provider version remains available in the registry for pinning, and the full disposition is documented at openai-provider.stackql.io.

Async jobs as SQL​

Fine-tuning jobs, batches, vector store file batches and uploads follow the same pattern: INSERT creates the job, SELECT polls it, EXEC cancels it. For example:

SELECT id, status, model, fine_tuned_model, trained_tokens
FROM openai.fine_tuning.jobs;

SELECT id, status, endpoint, request_counts
FROM openai.batches.batches
LIMIT 10;

Vector stores are a full CRUD surface with file membership:

SELECT id, name, status, usage_bytes, file_counts
FROM openai.vector_stores.vector_stores;

Inference endpoints (chat/completions, responses, embeddings, images, audio) are deliberately out of scope - the provider covers the control plane; use the vendor SDKs for invocation.

The admin provider - your OpenAI org as data​

The openai_admin provider presents the organization management surface as SQL.

Usage and cost are bucketed time series: one row per time bucket, with the per-group breakdown in the results JSON column, fanned out with JSON_EACH. Token usage by project and model over a 30-day window:

SELECT
  json_extract(r.value, '$.project_id')    AS project_id,
  json_extract(r.value, '$.model')         AS model,
  strftime('%Y-%m-%d', u.start_time, 'unixepoch') AS usage_date,
  json_extract(r.value, '$.input_tokens')  AS input_tokens,
  json_extract(r.value, '$.output_tokens') AS output_tokens
FROM openai_admin.usage.completions u, json_each(u.results) r
WHERE u.start_time = 1781481600
  AND u.bucket_width = '1d'
  AND u.limit = 31
  AND u.group_by = 'project_id'
ORDER BY usage_date, project_id;

Daily spend in USD by project:

SELECT
  strftime('%Y-%m-%d', c.start_time, 'unixepoch') AS cost_date,
  json_extract(r.value, '$.project_id')   AS project_id,
  json_extract(r.value, '$.amount.value') AS amount_usd
FROM openai_admin.costs.costs c, json_each(c.results) r
WHERE c.start_time = 1781481600
  AND c.limit = 180
  AND c.group_by = 'project_id'
ORDER BY cost_date, amount_usd DESC;

Usage is broken out per capability - completions, embeddings, moderations, images, audio, vector stores and code interpreter sessions - and can be grouped by project_id, api_key_id or model.

Governance and auditing​

Projects and their child resources (users, service accounts, API keys, rate limits) are queryable and writable:

SELECT p.name AS project, p.status, sa.name AS service_account, sa.role
FROM openai_admin.projects.projects p
JOIN openai_admin.projects.service_accounts sa ON sa.project_id = p.id
WHERE p.status = 'active';

Admin key hygiene and audit logs work the same way:

SELECT name, created_at, last_used_at, owner
FROM openai_admin.admin_api_keys.admin_api_keys
ORDER BY created_at;

SELECT id, type, effective_at, actor, project
FROM openai_admin.audit_logs.audit_logs
WHERE "effective_at[gt]" = 1750000000
  AND "event_types[]" = 'project.created';

Because it's all SQL, you can join usage to projects, materialize daily cost snapshots into a database, or point a BI tool at StackQL's Postgres wire protocol server and build an org-wide OpenAI spend dashboard without writing a line of integration code.

Authentication​

The two providers use different key types, which are disjoint by design:

# openai - standard API key
export OPENAI_API_KEY=sk-...

# openai_admin - org-scoped admin key (created by organization owners)
export OPENAI_ADMIN_KEY=sk-admin-...

Admin keys are available to organization accounts only and can be provisioned by organization owners in the platform console. A standard key cannot call the admin endpoints and vice versa.

Get started​

Pull the providers from the public registry:

registry pull openai
registry pull openai_admin

Provider docs are at openai-provider.stackql.io and openai-admin-provider.stackql.io. Let us know what you build. Star us on GitHub.

We've released a major update to the StackQL Azure provider family:

• azure - core Microsoft Azure services: 268 services, 3,473 resources and 13,559 operations (up from 202 services, a 33% increase in service coverage)
• azure_extras - domain-specific and specialized Microsoft services (44 services)
• azure_isv - Azure Native ISV and partner services: Databricks, Datadog, Confluent, Elastic, MongoDB Atlas, Oracle Database@Azure and more (27 services)
• azure_stack - the Azure Stack / Azure Local family (4 services)

Service, resource and method names are consistently snake_cased, service titles carry the official Azure product names, and related services have been consolidated - SHOW SERVICES IN azure now reads like the Azure portal, not like an SDK package index.

Control plane and data plane in one provider​

Most Azure tooling stops at ARM. This provider exposes Azure data plane APIs alongside the ARM control plane as first-class services, so the same SQL surface that manages a resource can work with what's inside it.

Enumerate Key Vaults in a subscription (control plane), then list the secrets in one of them (data plane):

SELECT name, location
FROM azure.key_vault.vaults
WHERE subscription_id = '<subscription_id>';

SELECT id, content_type, attributes
FROM azure.key_vault_secrets.secrets
WHERE vault_name = 'my-vault';

The same pattern extends across the platform - Storage blobs, queues and file shares, Cosmos DB tables, App Configuration key-values, Event Grid publishing, Container Registry repositories, Azure Monitor log queries and ingestion, Azure Maps, Azure AI Search documents, Service Fabric cluster operations, Batch jobs, and the Synapse and Purview workspace APIs are all present as data plane services next to their management planes.

The AI surface​

The biggest expansion in this release is AI - 20 services covering Azure AI Foundry and the Azure AI services portfolio:

• Azure AI Foundry: ai_projects, ai_agents, ai_inference, ai_evaluation
• Language: ai_language (conversational language understanding, question answering and their authoring surfaces in a single service), ai_text_analytics, ai_translation_text, ai_translation_document
• Vision: ai_vision_image_analysis, ai_vision_face
• Documents: ai_document_intelligence, ai_form_recognizer
• Speech: ai_transcription, ai_voice_live
• Safety and content: ai_content_safety, ai_content_understanding
• Plus ai_anomaly_detector, ai_personalizer, ai_discovery and the cognitive_services management plane

Inventory every Azure AI services account in a subscription, with kind and provisioning state:

SELECT name, kind, location, provisioning_state
FROM azure.cognitive_services.accounts
WHERE subscription_id = '<subscription_id>';

ARM's nested properties envelope is flattened at query time, so attributes like provisioning_state are ordinary columns - no JSON extraction needed for the common case.

More new and expanded coverage​

• Communication - the full Azure Communication Services surface: email, SMS, chat, calling automation, phone numbers, rooms, job router, advanced messaging and identity (9 services)
• Databases - Azure NetApp Files, Azure Cache for Redis, Azure Managed Redis, Azure DocumentDB (MongoDB compatibility), Azure HorizonDB, MySQL and PostgreSQL flexible servers, Azure Data Explorer (Kusto)
• Compute and containers - Compute Fleet, Compute Schedule, AKS (container_service), Kubernetes Fleet Manager, deployment safeguards, Container Registry tasks and data plane, Azure Red Hat OpenShift, Azure VMware Solution
• Observability - Azure Monitor log query, metrics query and logs ingestion data planes, Azure Monitor workspaces (managed Prometheus), health models
• Governance - Microsoft Purview catalog, data map, scanning, sharing and workflow APIs; a consolidated resource service spanning deployments, policy, locks, template specs and subscriptions
• Maps - geocoding, routing, rendering, geolocation, timezone and weather (6 services)
• Hybrid - Azure Arc-enabled servers, Kubernetes, VMware vSphere and System Center VMM, Arc gateway, Azure Local

Authentication​

The provider uses Azure's standard credential chain - an az login session works as-is, or set service principal credentials:

export AZURE_TENANT_ID=<tenant_id>
export AZURE_CLIENT_ID=<client_id>
export AZURE_CLIENT_SECRET=<client_secret>

Get started​

Pull the providers from the public registry:

registry pull azure
registry pull azure_extras
registry pull azure_isv
registry pull azure_stack

Then explore - it's just SQL:

SELECT name, location, provisioning_state, vm_id
FROM azure.compute.virtual_machines
WHERE subscription_id = '<subscription_id>';

Provider docs are at azure-provider.stackql.io. Let us know what you build. Star us on GitHub.

A cloud audit tells you what is wrong. The work starts when you have to fix it. Most tooling stops at the findings list and hands a spreadsheet to an engineer, and the findings sit there until someone has a quiet afternoon.

This post walks through the other half: a remediation loop that turns each finding into a reviewable pull request, verifies live state before it changes anything, and applies the fix on merge. It runs entirely in GitHub Actions, authenticates with OIDC, and uses StackQL to talk to cloud control planes. The repo is public at stackql-labs/stackql-ai-remediation, and the example throughout is FinOps waste (unattached disks, idle IPs, zero-VM projects), though the shape is the same for posture and security checks.