Google Cloud VMware Engine, PowerCLI and BigQuery Analytics

  #Install VMware.PowerCLI module 
Install-Module -Name VMware.PowerCLI 
#Test a connection to the vCenter Server of your Private Cloud
Test-NetConnection $server -port 443

  Import-Module VMware.PowerCLI -Verbose:$false
Set-PowerCLIConfiguration -confirm:$false

  bq --location=us mk -d --default_table_expiration 3600  --description "GCVE HOST DATA"  gcve_host_data

  #example list of vCenters.
 
$vcenterlist = @(
        'vcsa-01.us-west3.gve.goog'
        'vcsa-02.us-west3.gve.goog'
        'vcsa-03.us-west3.gve.goog'
    ) 

 #Loop through the vCenter list, extract data and load into BQ
 foreach ($v in $vcenterlist)
{
    #...1. Extract data & perform operations as required. Examples follow.
    #...2. Insert data into BigQuery.  Examples follow.
}

  #create a file name
 	$fileName = "export-$($vcenter)-$($region)-hostname.json"

  #Connect to a Server and Extract all VM's including the following properties: CPU, RAM, Power State, Provisioned Space, Custom Fields
Connect-VIServer -Server $server -User $user -Password $password 

#Optionally, connect using credentials stored in GCP Secret Manager (best practice)
         	$vcenter_u = (gcloud secrets versions access latest --secret="vcenter_u")
       	$vcenter_p = (gcloud secrets versions access latest --secret="vcenter_p") 
$pwd = ConvertTo-SecureString $vcenter_p -AsPlainText -Force
$cred = New-Object System.Management.Automation.PSCredential($vcenter_u,$pwd) 
Connect-VIServer -Server $server -Credential $cred 

#Extract data for all VM's including the specified fields
$vmData = (Get-VM | Select-Object -Property Name, CustomFields, NumCPU, MemoryGB, ProvisionedSpaceGB, PowerState, VMHost)


#example: esxi host info
#$esxiHosts = (Get-VMHost | Select-Object -Property Name,CpuUsageMhz,CpuTotalMhz, MemoryUsageGB, MemoryTotalGB, PowerState, NumCpu)

#example: datastore info
#$datastores = (Get-Datastore | Select-Object -Property Name, CapacityGB, FreeSpaceGB) 
 
#Create additional metadata fields as required. 
$run_date = [datetime]::Now.ToUniversalTime().ToString("s")
$run_id = New-Guid 

#Loop through the VMs and append metadata

    foreach ($vm in $vmData)
    {
        #Add metadata
        $vm | Add-Member -Type NoteProperty -Name "run_id" -Value $run_id.ToString()  -Force 

        $vm | Add-Member -Type NoteProperty -Name "run_date" -Value $run_date  -Force 
        
#Parse Custom Fields (if applicable), assuming that they are comma separated. This is useful when creating custom tags to identify a group of servers associated with an application or service. See the paragraph below on using custom tags for more information. 
        $custom = $vm | Select -ExpandProperty "CustomFields"
        foreach ($co in $custom)
        {
            if ($co.Key -eq "custom-tag") {
                $vm | Add-Member -Type NoteProperty -Name "custom-tag" -Value $co.Value.split(",")
            }
        }
       #Convert to Json
        $jsonline = $vm | ConvertTo-Json -Compress
       
       #Write content to the file  
       Add-Content $fileName $jsonline
    }

  #Powershell command to load file into BigQuery 
bq load --location us --noreplace --autodetect --source_format NEWLINE_DELIMITED_JSON gcve_host_data.gcve-billing-us $fileName

  #Disconnect 
Disconnect-VIServer -Server $server -Force -confirm:$false

  requests==2.25.1
google-cloud-secret-manager==2.4.0
google-cloud-storage==1.38.0
flask==2.0.1
gunicorn==20.1.0

  FROM ubuntu:18.04
RUN apt-get update
RUN apt-get install -y wget unzip apt-transport-https software-properties-common vim python3 python3-pip
RUN wget -q https://packages.microsoft.com/config/ubuntu/16.04/packages-microsoft-prod.deb
RUN dpkg -i packages-microsoft-prod.deb
RUN apt-get update
RUN apt-get install -y powershell
 
RUN wget https://vdc-download.vmware.com/vmwb-repository/dcr-public/8bf09c32-43c0-46d4-8816-92515c3b8228/15761272-b339-4da4-888a-4b8633f9a964/VMware-PowerCLI-11.3.0-13990089.zip -P /opt/microsoft/powershell/7/Modules
RUN unzip -d /opt/microsoft/powershell/7/Modules/ /opt/microsoft/powershell/7/Modules/VMware-PowerCLI-11.3.0-13990089.zip; exit 0
COPY requirements.txt ./
RUN pip3 install -r requirements.txt
# Copy local code to the container image.
ENV APP_HOME /app
WORKDIR $APP_HOME
COPY . ./
# Run the web service on container startup.
# Use gunicorn webserver with one worker process and 8 threads.
# For environments with multiple CPU cores, increase the number of workers
# to be equal to the cores available.
# Timeout is set to 0 to disable the timeouts of the workers to allow Cloud Run to handle instance scaling.
 
CMD exec gunicorn --bind :8080 --workers 1 --threads 8 
--timeout 0 main:app

  import base64
import datetime
import os
import json
import requests
import subprocess
from copy import deepcopy
from uuid import uuid4
from flask import Flask, request
from google.cloud import secretmanager, storage
 
 #grab vCenter auth info from Secrets Manager 
 secrets = secretmanager.SecretManagerServiceClient()
 vc_ip = secrets.access_secret_version(request={"name": os.getenv('VPC_IP')}).payload.data.decode("utf-8")
 vc_user = secrets.access_secret_version(request={"name": os.getenv('VPC_USER')}).payload.data.decode("utf-8")
 vc_pw = secrets.access_secret_version(request={"name": os.getenv('VPC_PWD')}).payload.data.decode("utf-8")
 
 command = "/usr/bin/pwsh"
 psfile = "/app/powercli.ps1"
 data = subprocess.run([command, psfile, '-server', vc_ip, '-user', vc_user, '-password', vc_pw])