Google Cloud VMware Engine, PowerCLI and BigQuery AnalyticsGoogle Cloud VMware Engine, PowerCLI and BigQuery AnalyticsStrategic Cloud Engineer, Google Cloud Professional ServicesStrategic Cloud Engineer, Google Cloud Professional ServicesStrategic Cloud Engineer, Google Cloud Professional Services

Google Cloud Billing allows Billing Account Administrators to configure the export of Google Cloud billing data to a BigQuery dataset for analysis and intercompany billback scenarios. Developers may choose to extract Google Cloud VMware Engine (GCVE) configuration and utilization data and apply internal cost and pricing data to create custom reports to support GCVE resource billback scenarios.  Using VMware PowerCLI, a collection of modules for Windows PowerShell, data is extracted and loaded into Big Query. Once the data is loaded into Big Query, analysts may choose to create billing reports and dashboards using Looker or Google Sheets. 

Exporting data from Google Cloud billing data into a BigQuery dataset is relatively straight-forward. However, exporting data from GCVE into BigQuery requires PowerShell scripting. The following blog details steps to extract data from GCVE and load it into BigQuery for reporting and analysis. 

Initial Setup VMware PowerCLI Installation

Installing and configuring VMware PowerCLI is a relatively quick process, with the primary dependency being network connectivity between the host used to develop and run the PowerShell script and the GCVE Private Cloud.

  1. [Option A] Provision a Google Compute Engine instance, for example Windows Server 2019, for use as a development server.
    [Option B] Alternatively, use a Windows laptop with Powershell 3.0 or higher installed

  2. Launch the Powershell ISE as Administrator. Install and configure the VMware PowerCLI, any required dependencies and perform a connection test.