search

PHACDataHub / sci-portal

1 stars 0 forks source link

Prepare to surface Budget details into the Backstage UI #90

Closed seanpoulter-foci closed 5 months ago

seanpoulter-foci commented 6 months ago

Determine the API calls to get the % budget and current total cost for a given GCP project, or group of projects

We can build out the backend with this info.

srinises commented 5 months ago

possible route - cron job running once a day to get the budget info into the same data set in BQ that hosts the billing data and then query that from looker and show it to the end user

@srinises working on a demo using cloud scheduler to get the budget data to BQ

srinises commented 5 months ago

Updated document detailing export of budget data to BQ via cloud functions

srinises commented 5 months ago

BQ Query to get the budget_consumed [IF BUDGET NAME IS PROJECT ID]

SELECT projId, total_cost, amount, (total_cost*100/amount) as budget_consumed FROM ( SELECT projId, SUM(t0_qt_mnecho490c) AS total_cost FROM ( SELECT projId, SUM(clmn0_) AS t0_qt_mnecho490c, clmn3_ AS t0_qt_nnecho490c FROM ( SELECT projId, SAFE_CAST(DATETIME_TRUNC(clmn2_, SECOND) AS DATE) AS clmn3_, SAFE_CAST(DATETIME_TRUNC(clmn2_, SECOND) AS DATE) AS clmn4_, clmn0_, clmn1_ FROM ( SELECT projId, SAFE_CAST(clmn1_ AS DATETIME) AS clmn2_, clmn0_, clmn1_ FROM ( SELECT t0.project.id AS projId, t0.cost AS clmn0_, t0.usage_start_time AS clmn1_ FROMfirst-project-329017.billingExport.gcp_billing_export_v1_017FE0_2CFE056AC320` / Replace with desired Billing export table ID / AS t0 ) ) ) WHERE (clmn1 >= TIMESTAMP '2024-04-09'/ Replace with desired Start time yyyy-mm-dd / AND clmn1_ < TIMESTAMP_ADD(TIMESTAMP '2024-05-08' / Replace with desired End time yyyy-mm-dd /, INTERVAL 1 DAY)) GROUP BY projId, t0_qt_nnecho490c ORDER BY t0_qt_nnecho490c ASC LIMIT 2000001) GROUP BY projId) INNER JOIN first-project-329017.billingExport.budget / Replace with desired Budget table ID / as budget ON budget.name=projId; `

srinises commented 5 months ago

BQ Query to surface budget and total cost [IF BUDGET NAME IS PROJECT NAME]

SELECT projId, total_cost, amount, (total_cost100/amount) as budget_consumed FROM ( SELECT projId, SUM(t0_qt_mnecho490c) AS totalcost FROM ( SELECT projId, SUM(clmn0) AS t0_qtmnecho490c, clmn3 AS t0_qt_nnecho490c FROM ( SELECT projId, SAFE_CAST(DATETIMETRUNC(clmn2, SECOND) AS DATE) AS clmn3_, SAFE_CAST(DATETIMETRUNC(clmn2, SECOND) AS DATE) AS clmn4, clmn0, clmn1_ FROM ( SELECT projId, SAFECAST(clmn1 AS DATETIME) AS clmn2, clmn0, clmn1 FROM ( SELECT t0.project.name AS projId, t0.cost AS clmn0, t0.usage_starttime AS clmn1 FROM first-project-329017.billingExport.gcp_billing_export_v1_017FE0_2CFE05_6AC320 / Replace with desired Billing export table ID / AS t0 ) ) ) WHERE (clmn1_ >= TIMESTAMP '2024-04-09'/ Replace with desired Start time yyyy-mm-dd / AND clmn1_ < TIMESTAMP_ADD(TIMESTAMP '2024-05-08' / Replace with desired End time yyyy-mm-dd /, INTERVAL 1 DAY)) GROUP BY projId, t0_qt_nnecho490c ORDER BY t0_qt_nnecho490c ASC LIMIT 2000001) GROUP BY projId) INNER JOIN first-project-329017.billingExport.budget / Replace with desired Budget table ID */ as budget ON budget.name=projId;

srinises commented 5 months ago

Updated doc with JS implementation of cloud functions and code to issue BQ query using JS client libraries