feat(azure): Cost management view queries, query views by subscription scope by disq · Pull Request #5898 · cloudquery/cloudquery

disq · 2022-12-21T15:17:21Z

NOTICE Let's keep this unmerged for now it as might need tweaking to make the blog post viable

azure_cost_management_views were coming up empty. Querying with the scope subscription we can get user's previously saved views (in Cost Analysis) and some data.

Unfortunately the main resource only returns metadata about pivots and aggregations, to get the actual table data/metrics we still need to run the query. Not sure if this is the right way to do it in terms of performance.

Example "views" row:

subscription_id | 12345678-1234-1234-1234-123456789012
e_tag           | "fedcda987654321"
properties      | {"kpis": [{"id": "COST_NAVIGATOR.BUDGET_OPTIONS.NONE", "type": "Budget", "enabled": true}, {"type": "Forecast", "enabled": true}], "chart": "Table", "query": {"type": "ActualCost", "dataSet": {"filter": {"and": [{"dimensions": {"name": "PublisherType", "values": ["azure"], "operator": "In"}}, {"dimensions": {"name": "PublisherType", "values": ["azure"], "operator": "In"}}, {"dimensions": {"name": "PublisherType", "values": ["azure"], "operator": "In"}}]}, "sorting": [{"name": "UsageDate", "direction": "Ascending"}], "grouping": [{"name": "ResourceGroupName", "type": "Dimension"}], "aggregation": {"totalCost": {"name": "Cost", "function": "Sum"}, "totalCostUSD": {"name": "CostUSD", "function": "Sum"}}, "granularity": "Daily"}, "timeframe": "None", "includeMonetaryCommitment": false}, "scope": "subscriptions/12345678-1234-1234-1234-123456789012", "metric": "ActualCost", "pivots": [], "currency": "USD", "createdOn": "2022-12-21T14:06:33.1518576Z", "dateRange": "CurrentBillingPeriod", "modifiedOn": "2022-12-21T14:17:14.5524763Z", "accumulated": "False", "displayName": "test2"}
id              | /subscriptions/12345678-1234-1234-1234-123456789012/providers/Microsoft.CostManagement/views/test2
name            | test2
type            | Microsoft.CostManagement/Views

Example "view_queries" row:

properties      | {"rows": [[0.000203782, 0.000203782, 20221206, "er-function-app-resource-group-example-resources", "USD"], [0.374281415, 0.374281415, 20221206, "er-web-app-resource-group-example-resources", "USD"], [0.197516129032258, 0.197516129032258, 20221214, "abc-test-sg", "USD"], [0.526709677419355, 0.526709677419355, 20221215, "abc-test-sg", "USD"], [0.504763440860215, 0.504763440860215, 20221216, "abc-test-sg", "USD"], [0.526709677419355, 0.526709677419355, 20221217, "abc-test-sg", "USD"], [0.131677419354839, 0.131677419354839, 20221218, "abc-test-sg", "USD"]], "columns": [{"name": "Cost", "type": "Number"}, {"name": "CostUSD", "type": "Number"}, {"name": "UsageDate", "type": "Number"}, {"name": "ResourceGroupName", "type": "String"}, {"name": "Currency", "type": "String"}]}
e_tag           |
id              | subscriptions/12345678-1234-1234-1234-123456789012/providers/Microsoft.CostManagement/query/12345678-1234-5678-90ab-123456789012
location        |
name            | 12345678-1234-5678-90ab-123456789012
sku             |
tags            | {}
type            | Microsoft.CostManagement/query

There's a ResourceGuid option which doesn't match anything we have, so we use the "Resource" field which will give us a full id of the resource (/subscriptions/<uuid>/resourcegroups/<groupname>/providers/<provider>/<type>/<name> format) so we'll also need to include that id ("full_id" I'm calling it) in azure_resources.

The query to join all of them to see cost vs. res is:

WITH cost_by_guid AS (
SELECT (r->>(cid.pos::int - 1))::text AS res_id, (r->>(ccost.pos::int - 1))::numeric AS cost_usd
FROM azure_costmanagement_view_queries q
JOIN JSONB_ARRAY_ELEMENTS(q.properties->'rows') AS r ON TRUE
JOIN JSONB_ARRAY_ELEMENTS(q.properties->'columns') WITH ORDINALITY cid(col, pos) ON cid.col->>'name'='ResourceId'
JOIN JSONB_ARRAY_ELEMENTS(q.properties->'columns') WITH ORDINALITY ccost(col, pos) ON ccost.col->>'name'='CostUSD'
JOIN azure_costmanagement_views v ON v._cq_id=q._cq_parent_id
WHERE v.name='test3' -- <-- this is the name of your saved view with "resource" field
)
SELECT
	c.*,
	r.name,
	r.kind,
	r.location
FROM cost_by_guid c JOIN azure_resources r ON lower(r.full_id)=lower(c.res_id);

github-actions · 2022-12-21T15:17:50Z

This PR has the following changes to source plugin(s) tables:

Table azure_costmanagement_view_queries was added

plugins/source/azure/resources/services/costmanagement/view_queries.go

disq · 2022-12-21T15:19:20Z

plugins/source/azure/resources/services/costmanagement/view_queries_fetch.go

+		return err
+	}
+	var qd armcostmanagement.QueryDefinition
+	if err := json.Unmarshal(b, &qd); err != nil {


Types are not compatible (ReportConfigDefinition vs. QueryDefinition) so instead of doing a deep conversion we'll use their marshal/unmarshal methods instead. If the fields were the same type and had equal names, the structs would be compatible with each other (lesser known Go feature) but the field names are different... Luckily their json tags match.

erezrokah

Code looks good, I'm assuming this is the data we want for cost analysis

…n scope

🤖 I have created a release *beep* *boop* --- ## [3.1.0](plugins-source-azure-v3.0.0...plugins-source-azure-v3.1.0) (2023-01-03) ### Features * Add azure security pricings resource ([#6023](#6023)) ([9648f26](9648f26)) * **azure:** Add storage_blob_services ([#6245](#6245)) ([0620ccd](0620ccd)) * **azure:** Cost management view queries, query views by subscription scope ([#5898](#5898)) ([ace315f](ace315f)) ### Bug Fixes * **deps:** Update module github.com/Azure/azure-sdk-for-go/sdk/internal to v1.1.2 ([#6205](#6205)) ([154fa6f](154fa6f)) * **deps:** Update module github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/redhatopenshift/armredhatopenshift to v1.1.0 ([#6183](#6183)) ([3395c09](3395c09)) * **deps:** Update module github.com/Azure/azure-sdk-for-go/sdk/resourcemanager/storage/armstorage to v1.2.0 ([#6184](#6184)) ([adc96f3](adc96f3)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.14.0 ([#6025](#6025)) ([35b2cfc](35b2cfc)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.15.0 ([#6071](#6071)) ([684b525](684b525)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.15.1 ([#6079](#6079)) ([650659c](650659c)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.16.0 ([#6098](#6098)) ([7bacdf3](7bacdf3)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.16.1 ([#6214](#6214)) ([53b2415](53b2415)) * **deps:** Update module github.com/cloudquery/plugin-sdk to v1.17.0 ([#6256](#6256)) ([b19f6cd](b19f6cd)) --- This PR was generated with [Release Please](https://github.com/googleapis/release-please). See [documentation](https://github.com/googleapis/release-please#release-please).

Missing examples. https://cloudquery-dimzb4rp1-cloudquery.vercel.app/blog/azure-cost-optimization-with-cloudquery Should be merged/released after #5898 is merged and released.

disq requested review from erezrokah and yevgenypats as code owners December 21, 2022 15:17

cq-bot added azure labels Dec 21, 2022

disq commented Dec 21, 2022

View reviewed changes

plugins/source/azure/resources/services/costmanagement/view_queries.go Outdated Show resolved Hide resolved

vercel bot deployed to Preview December 21, 2022 15:18 View deployment

disq commented Dec 21, 2022

View reviewed changes

vercel bot deployed to Preview December 22, 2022 09:03 View deployment

erezrokah approved these changes Dec 22, 2022

View reviewed changes

disq added 3 commits December 22, 2022 09:27

feat(azure): Cost management view queries, query views by subscriptio…

8573b36

…n scope

test err check fix to make linter happy

758e8a6

include the e_tag fix

c909c92

disq force-pushed the feat/azure-cost-mgmt branch from acd0c20 to c909c92 Compare December 22, 2022 09:28

Edit resources view to add "full_id", an unprocessed "id" field

f16332f

disq added the no automerge label Dec 23, 2022

disq mentioned this pull request Dec 23, 2022

feat(website): Azure cost-post #5940

Merged

erezrokah approved these changes Dec 26, 2022

View reviewed changes

Merge branch 'main' into feat/azure-cost-mgmt

ad02254

vercel bot deployed to Preview December 28, 2022 10:02 View deployment

disq merged commit ace315f into cloudquery:main Dec 28, 2022

disq deleted the feat/azure-cost-mgmt branch December 28, 2022 10:23

cq-bot mentioned this pull request Dec 28, 2022

chore(main): Release plugins-source-azure v3.1.0 #6056

Merged

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

feat(azure): Cost management view queries, query views by subscription scope#5898

feat(azure): Cost management view queries, query views by subscription scope#5898
disq merged 5 commits intocloudquery:mainfrom
disq:feat/azure-cost-mgmt

disq commented Dec 21, 2022 •

edited

Loading

Uh oh!

github-actions bot commented Dec 21, 2022

Uh oh!

Uh oh!

disq Dec 21, 2022 •

edited

Loading

Uh oh!

erezrokah left a comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

Conversation

disq commented Dec 21, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Uh oh!

github-actions bot commented Dec 21, 2022

This PR has the following changes to source plugin(s) tables:

Uh oh!

Uh oh!

disq Dec 21, 2022 • edited Loading Uh oh! There was an error while loading. Please reload this page.

Uh oh!

Choose a reason for hiding this comment

Uh oh!

erezrokah left a comment

Choose a reason for hiding this comment

Uh oh!

Reviewers

Assignees

Labels

Projects

Milestone

Development

Uh oh!

3 participants

disq commented Dec 21, 2022 •

edited

Loading

disq Dec 21, 2022 •

edited

Loading