OptiQL

OptiQL is a SQL workspace for your cloud data. Ask any question of your cost, resources, tags, waste findings, recommendations and budgets, across Azure and AWS in one query. See the answer as a table or a chart, and save the queries worth keeping, for yourself or your team.

You’ll find it in the portal navigation as OptiQL.

The workspace

The page has three parts:

The editor, with SQL syntax highlighting and autocompletion that knows the tables and columns. Press Ctrl + Enter (Cmd + Enter on a Mac) to run.
The side panel, with three tabs:
- Schema: every table and view with its columns and what they mean. Filter by name, and click a name to insert it into the editor.
- Examples: ready-made queries for common questions, such as top services, month-over-month spend per account, untagged resources, waste by category, tax and credits by month, and AI spend. Click one to load it.
- Library: your saved queries and the ones shared with you.
The result: row count and run time, the data as a table or a chart, and buttons to copy it (ready to paste into a spreadsheet) or download it as CSV.

Writing a query

OptiQL uses SQLite’s SQL dialect. A first query, spend per month and cloud:

SELECT month, cloud_provider, ROUND(total_cost, 2) AS cost
FROM v_monthly_spend
ORDER BY month DESC, cloud_provider;

Top services over the last 30 days:

SELECT service_name, ROUND(SUM(cost), 2) AS cost
FROM service_daily_costs
WHERE date >= date('now', '-30 day')
GROUP BY service_name
ORDER BY cost DESC
LIMIT 10;

Credits and refunds per month (from billing exports and the Cost & Usage Report):

SELECT month, cloud_provider,
       ROUND(credits, 2) AS credits, ROUND(refunds, 2) AS refunds
FROM v_credits_by_month
ORDER BY month DESC;

The tables and views are the same ones the SQL query widget uses in Custom Views. The Schema tab lists them all, with a description of every column.

What the data covers

Your whole organisation. Queries see every active subscription and account, across both clouds. The portal’s global filter doesn’t apply here, so narrow a query with a WHERE clause, for example on subscription_id or cloud_provider.
All history CloudOptify holds for those accounts.
Amounts in USD. Every cost column is converted to USD when the data is loaded, so totals across accounts, clouds and billing currencies are always valid. The currency columns always contain 'USD'.

Charts

Pick how to show a result from the bar above it:

View	Best for
Table	Every row, exactly as returned.
Bar	Comparing a number across categories, such as cost per service.
Line	A number over time, such as daily or monthly spend.
Share	How a total splits between categories, ranked.
Value	A single headline number.

Charts need at least one numeric column. When you save a query, its chart is saved with it, so everyone who opens it sees the same view.

Click Save, give the query a name and an optional description, and choose who can see it:

Visibility	Who can open and run it
Only me	Just you. This is the default, because a query in progress isn’t a publication.
Specific people	Colleagues you pick by name.
Everyone	Anyone in your organisation who has OptiQL.

People a query is shared with can open and run it exactly as it was saved. They can’t change it, and a shared query always runs the SQL its author wrote.
A saved query can be changed or deleted by its author. Owners, Admins and Contributors can also change or delete any saved query they can open.
Members with the Reader role can run queries and open shared ones, but can’t save or share.
The library shows who wrote each query shared with you, and how often it’s been run.

Limits

Limit	Value
Rows shown per result	500. A larger result is marked truncated. Add a `LIMIT` or aggregate further.
Run time per query	10 seconds.
Statements	One per run, read-only: it must start with `SELECT` or `WITH`. Anything that writes is refused with an explanation.

Security

Your data only. Each run builds a fresh, in-memory copy of your organisation’s data and runs the query against it. The copy has no connection to CloudOptify’s database and is discarded when the query finishes, so a query can’t reach another organisation’s data.
Read-only. Statements that write are refused, and the engine itself is locked to read-only as a second safeguard.
Nothing outside the sandbox. Commands that could reach files or change the database engine (ATTACH, PRAGMA, extension loading) are blocked.

OptiQL and Custom Views

Both use the same data and the same safeguards. Use OptiQL to explore, answer a question and keep a library of queries. Use a Custom View with a SQL query widget to pin a result to a dashboard you check regularly.