topic Re: Custom Tag Usage Reporting in Get Started Discussions

Custom Tag Usage Reporting

pksilver — Wed, 05 Feb 2025 16:17:04 GMT

Hi Team,

Does anyone have a good SQL query that I can use for showing usage costs against custom tags for example on clusters. The Account Console usage report is good but I can only seem to query one custom tag at a time and ideally I want a dashboard that will show them all with a timeframe selection of week. month and day. Thanks in advance

Re: Custom Tag Usage Reporting

Alberto_Umana — Wed, 05 Feb 2025 16:56:06 GMT

Hi @pksilver,

Here's an example query you can use:

Please also see: https://docs.databricks.com/en/admin/account-settings/usage.html

SELECT
usage_date,
cluster_id,
custom_tag,
SUM(cost) AS total_cost
FROM
system.billing.usage
WHERE
usage_date BETWEEN DATE_SUB(CURRENT_DATE, INTERVAL 7 DAY) AND CURRENT_DATE -- For weekly timeframe
AND custom_tag IS NOT NULL
GROUP BY
usage_date,
cluster_id,
custom_tag
ORDER BY
usage_date,
cluster_id,
custom_tag;

Re: Custom Tag Usage Reporting

pksilver — Thu, 06 Feb 2025 10:33:38 GMT

Thanks, Im getting errors when i run this in SQL Editor, should i be running this against the system.billing catalog/schema?

Re: Custom Tag Usage Reporting

pksilver — Thu, 06 Feb 2025 10:35:44 GMT

SELECT

usage_date,

cluster_id,

custom_tag,

SUM(cost) AS total_cost

FROM

system.billing.usage

WHERE

usage_date BETWEEN date_sub(CURRENT_DATE, 7) AND CURRENT_DATE -- For weekly timeframe

AND custom_tag[] IS NOT NULL

GROUP BY

usage_date,

cluster_id,

custom_tag

ORDER BY

usage_date,

cluster_id,

[PARSE_SYNTAX_ERROR] Syntax error at or near ']'. SQLSTATE: 42601 line 10, pos 15

Re: Custom Tag Usage Reporting

Alberto_Umana — Thu, 06 Feb 2025 16:09:56 GMT

Hi @pksilver,

Please use this one, which is updated:

%sql

SELECT

usage_date,

usage_metadata.cluster_id,

custom_tags,

SUM(usage_quantity) AS total_cost

FROM

system.billing.usage

WHERE

usage_date BETWEEN date_sub(CURRENT_DATE, 7) AND CURRENT_DATE -- For weekly timeframe

AND custom_tags IS NOT NULL

GROUP BY

usage_date,

usage_metadata.cluster_id,

custom_tags

ORDER BY

usage_date,

usage_metadata.cluster_id

Re: Custom Tag Usage Reporting

pksilver — Thu, 06 Feb 2025 16:30:37 GMT

Thanks, and I run this against system.billing schema?

Re: Custom Tag Usage Reporting

Alberto_Umana — Thu, 06 Feb 2025 16:32:20 GMT

That is correct 🙂

Re: Custom Tag Usage Reporting

pksilver — Thu, 06 Feb 2025 16:46:09 GMT

ok this is what i get now;

[INTERNAL_ERROR] Couldn't find custom_tags#1085 in [usage_date#1084,_groupingexpression#1106,mapsort(custom_tags#1085)#1130,sum(usage_quantity#1087)#1100] SQLSTATE: XX000

Re: Custom Tag Usage Reporting

Alberto_Umana — Thu, 06 Feb 2025 17:15:28 GMT

Can you run select * from system.billing schema limit 20; And validate the custom_tags field.

This query should work since it's a system table that has same column, I just tested it 🙂

Re: Custom Tag Usage Reporting

Alberto_Umana — Thu, 06 Feb 2025 17:16:21 GMT

For reference:

Re: Custom Tag Usage Reporting

pksilver — Thu, 06 Feb 2025 17:47:38 GMT

Ok I was running this from SQL Editor not as python notebook so maybe that was the issue, its run ok now, im hoping i can now turn this into a visualization and put it on a dash?