Dashboard: GCP Cloud Billing Report: Cost by Label

This dashboard answers the following questions:

What is the total cost for resources with specific labels?
How many distinct label values are being used?
How has the cost trended monthly by label value?
Which label values incurred the highest costs?
What are the detailed costs by label value across projects?

This dashboard contains 2 cards, 3 inputs and 1 table.

Usage

Install the mod:

mkdir dashboards
cd dashboards
powerpipe mod init
powerpipe mod install github.com/turbot/tailpipe-mod-gcp-cloud-billing-insights

Start the Powerpipe server:

powerpipe server

Open http://localhost:9033 in your browser and select GCP Cloud Billing Report: Cost by Label dashboard.

You could also snapshot and share results via Turbot Pipes:

powerpipe login
powerpipe dashboard run gcp_cloud_billing_insights.dashboard.cloud_billing_report_cost_by_label_dashboard --share

Queries

This dashboard uses the the following queries:

with filtered as (
  select 
    project_id, 
    project_name, 
    location.region as region,
    cost, 
    currency,
    project_labels, 
    labels, 
    system_labels
  from 
    gcp_billing_report
  where 
    ('all' in ($1) or project_id in $1)
),
picked as (
  select 
    project_id, 
    project_name, 
    region, 
    currency, 
    cost,
    json_extract_string(f.project_labels, '$[' || i || '].value') as label_value
  from 
    filtered f, 
    generate_series(0, 200) as gs(i)
  where $2 = 'project_labels'
    and f.project_labels is not null
    and i < json_array_length(f.project_labels)
    and json_extract_string(f.project_labels, '$[' || i || '].key') = $3

  union all

  select 
    project_id, 
    project_name, 
    region, 
    currency,
    cost,
    json_extract_string(f.labels, '$[' || i || '].value')
  from 
    filtered f, 
    generate_series(0, 200) as gs(i)
  where $2 = 'labels'
    and f.labels is not null
    and i < json_array_length(f.labels)
    and json_extract_string(f.labels, '$[' || i || '].key') = $3

  union all

  select 
    project_id, 
    project_name, 
    region, 
    currency, 
    cost,
    json_extract_string(f.system_labels, '$[' || i || '].value')
  from 
    filtered f, 
    generate_series(0, 200) as gs(i)
  where $2 = 'system_labels'
    and f.system_labels is not null
    and i < json_array_length(f.system_labels)
    and json_extract_string(f.system_labels, '$[' || i || '].key') = $3
)
select
  label_value                as "Label Value",
  project_id                 as "Project ID",
  project_name               as "Project Name",
  coalesce(region, 'global') as "Region",
  round(sum(cost), 2)        as "Total Cost",
  max(currency)              as "Currency"
from 
  picked
where 
  label_value is not null and label_value <> ''
group by 
  label_value, 
  project_id, 
  project_name, 
  region
order by 
  sum(cost) desc;

{
  "$1": "project_ids",
  "$2": "label_type",
  "$3": "label_key"
}

Dashboard: GCP Cloud Billing Report: Cost by Label

Usage

Queries

Tags