Google Data Studio Billing Report Demo for GCP multiple projects

Question

Basically I am trying to setup Google Cloud Billing Report Demo for multiple projects. Example mentioned in this link

In it there are 3 steps to configure datasource for data studio

1. Create the Billing Export Data Source
2. Create the Spending Trends Data Source
3. Create the BigQuery Audit Data Source

Now 1st point is quite clear.

For 2nd point the query example which is provided in demo is based on a single project. In my case I wanted to have spending datasource from multiple projects. Does doing UNION of query based on each project works in this case?

For 3rd point, I need Bigquery Audit log from all my projects. I thought setting the external single dataset sink as shown below for bigquery in all my project should be able to do the needful.

bigquery.googleapis.com/projects/myorg-project/datasets/myorg_cloud_costs

But I see that in my dataset tables are creating with a suffix _(1) as shown below

cloudaudit_googleapis_com_activity_ (1)
cloudaudit_googleapis_com_data_access_ (1)

and these tables doesn't contain any data despite running bigquery queries in all projects multiple times.In fact it shows below error on previewing.

Unable to find table: myorg-project:cloud_costs.cloudaudit_googleapis_com_activity_20190113 

I think auto generated name with suffix _ (1) is causing some issue and because of that data is also not getting populated.

I believe there should be a very simple solution for it, but it just I am not able to think in correct way. Can somebody please provide some information on how to solve 2nd and 3rd requirement for multiple projects in gcp datastudio billing report demo?

Answer 1

For 2nd point the query example which is provided in demo is based on a single project. In my case I wanted to have spending datasource from multiple projects. Does doing UNION of query based on each project works in this case?

That project is the project you specify for the bulling audit logs in BigQuery. The logs are attached to the billing account, which can contain multiple projects underneath it. All projects in the billing account will be captured in the logs - more specifically, the column project.id.

For 3rd point, I need Bigquery Audit log from all my projects. I thought setting the external single dataset sink as shown below for bigquery in all my project should be able to do the needful.

You use the includeChildren property. See here. If you don't have an organisation or use folders, then you will need to create a sink per project and point it at the dataset in BigQuery where you want all the logs to go. You can script this up using the gcloud tool. It's easy.

I think auto generated name with suffix _ (1) is causing some issue and because of that data is also not getting populated.

The suffix normal. Also, it can take a few hours for your logs/sinks to start flowing.