Data API Example Queries

note

Data API is in Open Preview and is not General Availability (GA) at this time. More information about release phases may be found here.

Data API Query Endpoint URL

https://data.api.vegacloud.io/docs#/operations/data-query

Query Examples

Please Note:

You must remove all spaces from the example queries before POSTING it to the API otherwise it will fail validation.

Example 1

Current Year Spend By Account, Including Public Cost, Cost Before Discounts, & Cost After Discounts. Excludes Taxes & Credits

SELECT cst.closing_month,
       cst.linked_account_id,
       cst.linked_account_name,
       SUM(cst.ondemand_cost)     as ondemand_cost /* Public Pricing BEFORE CSP Discounts & Committed Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)      AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Committed Use Discounts are applied */,
       SUM(cst.net_fiscal_amount) AS net_fiscal /* Public Pricing AFTER CSP Discounts & Committed Use Discounts (RI/SPs) are applied (Includes Amortization & Support Fees) */
FROM Cost cst
WHERE cst.closing_month >= '2024-01-01'
  AND cst.cost_category NOT IN ('Tax', 'Credit')
GROUP BY cst.closing_month, cst.linked_account_id, cst.linked_account_name

Example 2

Current Year Spend By Product Details for a given Account, Including Public Cost, Cost Before Discounts, & Cost After Discounts. Excludes Taxes & Credits

SELECT cst.closing_month,
       cst.product_group,
       cst.product_category,
       cst.product_cost_detail_category,
       SUM(cst.ondemand_cost)     as ondemand_cost /* Public Pricing BEFORE CSP Discounts & Committed Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)      AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Committed Use Discounts are applied */,
       SUM(cst.net_fiscal_amount) AS net_fiscal /* Public Pricing AFTER CSP Discounts & Committed Use Discounts (RI/SPs) are applied (Includes Amortization & Support Fees) */
FROM Cost cst
WHERE cst.closing_month >= '2024-01-01'
  AND cst.cost_category NOT IN ('Tax', 'Credit', 'Refund')
  AND cst.linked_account_id = '614630089683'
GROUP BY cst.closing_month, cst.product_group, cst.product_category, cst.product_cost_detail_category

Example 3

Current Year RDS Spend for a given Account by Cost Category (OnDemand, RI Covered), Including Public Cost, Cost Before Discounts, & Cost After Discounts. Excludes Taxes & Credits

SELECT cst.closing_month,
       cst.usage_day,
       cst.cost_category,
       cst.cloud_region,
       cst.operating_system,
       SUM(cst.ondemand_cost)     as ondemand_cost /* Public Pricing BEFORE CSP Discounts & Committed Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)      AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Committed Use Discounts are applied */,
       SUM(cst.net_fiscal_amount) AS net_fiscal /* Public Pricing AFTER CSP Discounts & Committed Use Discounts (RI/SPs) are applied (Includes Amortization & Support Fees) */
FROM Cost cst
WHERE cst.closing_month >= '2024-01-01'
  AND cst.cost_category NOT IN ('Tax', 'Credit', 'Refund')
  AND cst.linked_account_id = '614630089683'
  AND cst.product_cost_detail_category = 'RDS Usage'
GROUP BY cst.closing_month, cst.usage_day, cst.cost_category, cst.cloud_region, cst.operating_system

Example 4

Current Year RDS Aurora MSQL Spend for a given Account by Cost Category (OnDemand, RI Covered) with Average Cost Per Hour, Including Public Cost, Cost Before Discounts, & Cost After Discounts. Excludes Taxes & Credits

SELECT cst.closing_month,
       cst.usage_day,
       cst.cost_category,
       cst.cloud_region,
       cst.operating_system,
       SUM(cst.usage_amount)                              AS usage_amount,
       SUM(cst.ondemand_cost)                             as ondemand_cost /* Public Pricing BEFORE CSP Discounts & Committed Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)                              AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Committed Use Discounts are applied */,
       SUM(cst.net_fiscal_amount)                         AS net_fiscal /* Public Pricing AFTER CSP Discounts & Committed Use Discounts (RI/SPs) are applied (Includes Amortization & Support Fees) */,
       SUM(cst.net_fiscal_amount) / SUM(cst.usage_amount) AS average_cost
FROM Cost cst
WHERE cst.closing_month >= '2024-01-01'
  AND cst.cost_category NOT IN ('Tax', 'Credit', 'Refund')
  AND cst.linked_account_id = '614630089683'
  AND cst.product_cost_detail_category = 'RDS Usage'
  AND cst.operating_system = 'Aurora MySQL'
GROUP BY cst.closing_month, cst.usage_day, cst.cost_category, cst.cloud_region, cst.operating_system

Example 5

Return all Anomalies from the last 7 days, where the net fiscal increase was greater than $500

SELECT *
FROM Anomaly anomaly
WHERE anomaly.base_anomaly_flag = 'Net Fiscal Spike'
  AND anomaly.net_fiscal_difference > 500
  AND anomaly.usage_day > current_date - 7
ORDER BY anomaly.net_fiscal_difference

Example 6

Return Cost per AWS EC2 Instance over the last 30 days, where the resource is stopped

SELECT cloud_account_id, tags, resource_id, state, SUM(cost_rolling_thirty) AS monthly_spend
FROM DiscoveredResource
WHERE provider_str = 'AWS'
  AND resource_type = 'Virtual Machine'
  AND state <> 'running'
GROUP BY cloud_account_id, tags, resource_id, state