Data API Example Queries

Please note that this API is in Private Preview and is not available to the general public at this time. More information about release phases may be found here

Data API Query 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.

/*
    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 & Commited Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)      AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Commited Use Discounts are applied */,
       SUM(cst.net_fiscal_amount) AS net_fiscal /* Public Prcicing AFTER CSP Discounts & Commited 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

/*
    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 & Commited Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)      AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Commited Use Discounts are applied */,
       SUM(cst.net_fiscal_amount) AS net_fiscal /* Public Prcicing AFTER CSP Discounts & Commited 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

/*
    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 & Commited Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)      AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Commited Use Discounts are applied */,
       SUM(cst.net_fiscal_amount) AS net_fiscal /* Public Prcicing AFTER CSP Discounts & Commited 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


/*
    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 & Commited Use Discounts (RI/SPs) */,
       SUM(cst.gross_fiscal)                              AS gross_fiscal /* Public Pricing BEFORE CSP Discounts, AFTER Commited Use Discounts are applied */,
       SUM(cst.net_fiscal_amount)                         AS net_fiscal /* Public Prcicing AFTER CSP Discounts & Commited 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

/* 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

/* Return Cost per AWS EC2 Instance over the last 30 days, where the resource is stopped */
SELECT cloud_account_id, tags, resource_id, state, tags, 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, tags