tools/gcve2bq/get-gcve-util.ps1

#  Copyright 2023 Google Inc. All Rights Reserved.
#
#  Licensed under the Apache License, Version 2.0 (the "License");
#  you may not use this file except in compliance with the License.
#  You may obtain a copy of the License at
#
#      http://www.apache.org/licenses/LICENSE-2.0
#
#  Unless required by applicable law or agreed to in writing, software
#  distributed under the License is distributed on an "AS IS" BASIS,
#  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
#  See the License for the specific language governing permissions and
#  limitations under the License.

function get-DatastoreUtil {
    <#
        .SYNOPSIS
             Run Get-Datastore and write contents to file.
        .EXAMPLE
            get-DatastoreUtil -server "aVcenterServer"
            get-DatastoreUtil -server "10.0.0.1"
    #>
    param (
        [Parameter(Mandatory=$true, Position=0)][string]$server
    )

    $uuid = "datastores.json"

    if (Test-Path $uuid) {
        Remove-Item $uuid
    }
    $run_date = [datetime]::Now.ToUniversalTime().ToString("s")
    Write-Host  $run_date
    $run_id = New-Guid

    $datastores = (Get-Datastore | Select-Object -Property Name, CapacityGB, FreeSpaceGB)
    foreach ($datastore in $datastores){

        #hashTable allows for flexibility with extracted objects
        $ht = @{} 

        $datastore | Add-Member -Type NoteProperty -Name "run_id" -Value $run_id.ToString()
        $datastore | Add-Member -Type NoteProperty -Name "run_date" -Value $run_date
        $datastore | Add-Member -Type NoteProperty -Name "vCenter" -Value $server
        $datastore | Add-Member -Type NoteProperty -Name "region" -Value $server.split('.')[2] 
        
        $ht.add("run_id",$datastore.run_id); 
        $ht.add("run_date",$datastore.run_date);
        $ht.add("vCenter",$datastore.vCenter); 
        $ht.add("region",$datastore.region);
        $ht.add("Name",$datastore.Name);
        $ht.add("CapacityGB",$datastore.CapacityGB);
        $ht.add("FreeSpaceGB",$datastore.FreeSpaceGB);

        $jsonline = $ht | ConvertTo-Json -Compress  
        Add-Content $uuid $jsonline
    }

    Write-Host  $uuid
}

function get-HostUtil {
    <#
        .SYNOPSIS
            Run Get-VMHost and write contents to file.

        .EXAMPLE
            get-HostUtil -server "aVcenterServer"
            get-HostUtil -server "10.0.0.1"
    #>
    param (
        [Parameter(Mandatory=$true, Position=0)][string]$server
    )

    $uuid = "esxi.json"
    Write-Host "Writing to $uuid"

    if (Test-Path $uuid) {
        Remove-Item $uuid
    }

    $run_date = [datetime]::Now.ToUniversalTime().ToString("s")
    Write-Host  $run_date
    $run_id = New-Guid

    $esxiHosts = (Get-VMHost | Select-Object -Property Name,`
    CpuUsageMhz,CpuTotalMhz,`
    MemoryUsageGB, MemoryTotalGB, PowerState, NumCpu)
    foreach ($esxi in $esxiHosts){

        #hashTable allows for flexibility with extracted objects
        $ht = @{}  

        #Grab the host to access extended properties
        $an_esxi_host = Get-VMHost -Name $esxi.Name 
        #Parent Name of the Host is the Cluster Name
        $Cluster = $an_esxi_host.Parent.Name  

        $NumCpuCores = $an_esxi_host.ExtensionData.Summary.Hardware.NumCpuCores 
        $NumCpuThreads = $an_esxi_host.ExtensionData.Summary.Hardware.NumCpuThreads 

        $esxi | Add-Member -Type NoteProperty -Name "run_id" -Value $run_id.ToString()
        $esxi | Add-Member -Type NoteProperty -Name "run_date" -Value $run_date
        $esxi | Add-Member -Type NoteProperty -Name "vCenter" -Value $server
        $esxi | Add-Member -Type NoteProperty -Name "region" -Value $server.split('.')[2]          
        
        #add our extended properties to our main object      
        $esxi | Add-Member -Type NoteProperty -Name "NumCpuCores" -Value $NumCpuCores
        $esxi | Add-Member -Type NoteProperty -Name "NumCpuThreads" -Value $NumCpuThreads
        $esxi | Add-Member -Type NoteProperty -Name "Cluster" -Value $Cluster
  
        $ht.add("run_id",$esxi.run_id); 
        $ht.add("run_date",$esxi.run_date);
        $ht.add("vCenter",$esxi.vCenter); 
        $ht.add("region",$esxi.region);

        #Name, CpuUsageMhz, CpuTotalMhz, MemoryUsageGB, MemoryTotalGB, PowerState)
        $ht.add("Name",$esxi.Name);
        $ht.add("CpuUsageMhz",$esxi.CpuUsageMhz);
        $ht.add("CpuTotalMhz",$esxi.CpuTotalMhz);
        $ht.add("MemoryUsageGB",$esxi.MemoryUsageGB);
        $ht.add("MemoryTotalGB",$esxi.MemoryTotalGB);
        $ht.add("PowerState",$esxi.PowerState);

        #CPU info 
        $ht.add("NumCpu",$esxi.NumCpu);
        $ht.add("NumCpuCores",$esxi.NumCpuCores);
        $ht.add("NumCpuThreads",$esxi.NumCpuThreads);
        
        #Cluster Name
        if ( $esxi.Cluster -eq $null ){
            $esxi.Cluster = ""
        }
        $ht.add("Cluster",$esxi.Cluster.ToString() ); 

        $jsonline = $ht | ConvertTo-Json -Compress
        Add-Content $uuid $jsonline
    }

    Write-Host  $uuid
}

function get-util {
    <#
        .SYNOPSIS
             Run Get-VM and write contents to file.
        .EXAMPLE
            get-util -server "aVcenterServer"
            get-util -server "10.0.0.1"
    #>
    param (
        [Parameter(Mandatory=$true, Position=0)][string]$server
    )

    #$vCenterHostname = $server.split('.')[0]
    $uuid = "vm.json"

    if (Test-Path $uuid) {
        Remove-Item $uuid
    }
    $run_date = [datetime]::Now.ToUniversalTime().ToString("s")
    Write-Host $run_date
    $run_id = New-Guid
    
    $vms = (Get-VM | Select-Object -Property Name, CustomFields, NumCpu, MemoryGB,`
    ProvisionedSpaceGB, PowerState,`
    VMHost)
    
    # Add-Content $uuid $vms
    foreach ($i in $vms)
    {

        #hashTable allows for flexibility with extracted objects
        $ht = @{}  

        #Get the Cluster Parent Name
        $_vmHost = Get-VMHost -Name $i.VMHost
        $Cluster = $_vmHost.Parent.Name  

        $i | Add-Member -Type NoteProperty -Name "run_id" -Value $run_id.ToString()
        $i | Add-Member -Type NoteProperty -Name "run_date" -Value $run_date
        $i | Add-Member -Type NoteProperty -Name "vCenter" -Value $server
        $i | Add-Member -Type NoteProperty -Name "region" -Value $server.split('.')[2]
        $i | Add-Member -Type NoteProperty -Name "Cluster" -Value $Cluster


        $custom = $i | Select -ExpandProperty "CustomFields"
        foreach ($co in $custom)
        {
            if ($co.Key -eq "application") {
                $i | Add-Member -Type NoteProperty -Name "applications" -Value $co.Value.split(",")
            }
        }

        $ht.add("run_id",$i.run_id); 
        $ht.add("run_date",$i.run_date);
        $ht.add("vCenter",$i.vCenter); 
        $ht.add("region",$i.region);

        $ht.add("Name",$i.Name);
        $ht.add("CustomFields",$i.CustomFields);
        $ht.add("NumCpu",$i.NumCpu);
        $ht.add("MemoryGB",$i.MemoryGB);
        $ht.add("ProvisionedSpaceGB",$i.ProvisionedSpaceGB);
        $ht.add("PowerState",$i.PowerState);

        #null check before converting to string 
        if ( $i.VMHost -eq $null ){
            $i.VMHost = ""
        }
        if ( $i.Cluster -eq $null ){
                $i.Cluster = ""
        } 

        $ht.add("VMHost",$i.VMHost.ToString() );
        $ht.add("Cluster",$i.Cluster.ToString() );

        $jsonline = $ht | ConvertTo-Json -Compress
        Add-Content $uuid $jsonline
    }

    Write-Host  $uuid
}

function main {
	    <#
        .SYNOPSIS
             1. Fetch environment variables for vCenter and BigQuery connection
             2. Extract data and write to flat file
             3. Load Data into BQ table
        .EXAMPLE
            main
        #>

    # Fetch environment variables from Cloud Run configuration
    $dataset_region=[System.Environment]::GetEnvironmentVariable('dataset_region')
    $datastore_table=[System.Environment]::GetEnvironmentVariable('datastore_table') # format: project:dataset.table
    $esxi_table=[System.Environment]::GetEnvironmentVariable('esxi_table') # format: project:dataset.table
    $vm_table=[System.Environment]::GetEnvironmentVariable('vm_table') # format: project:dataset.table
    $vCenter_username=[System.Environment]::GetEnvironmentVariable('vCenter_username')
    $vCenter_server=[System.Environment]::GetEnvironmentVariable('vCenter_server')
    $vCenter_password=[System.Environment]::GetEnvironmentVariable('vCenter_password')
    $vCenterHostname = $vCenter_server.split('.')[0]

    # Setup PowerCLI
    Import-Module VMware.PowerCLI -Verbose:$false
    $_tmppwd = ConvertTo-SecureString $vCenter_password -AsPlainText -Force
    $_cred = New-Object System.Management.Automation.PSCredential ($vCenter_username , $_tmppwd)
    Connect-VIServer -Server $vCenter_server -Credential $_cred

    # Fetch utilization data and load to BigQuery
    get-DatastoreUtil -server $vCenter_server
    bq load --location $dataset_region --noreplace --autodetect --source_format NEWLINE_DELIMITED_JSON $datastore_table "datastores.json"
    get-HostUtil -server $vCenter_server
    bq load --location $dataset_region --noreplace --autodetect --source_format NEWLINE_DELIMITED_JSON $esxi_table "esxi.json"
    get-util -server $vCenter_server
    bq load --location $dataset_region --noreplace --autodetect --source_format NEWLINE_DELIMITED_JSON $vm_table "vm.json"

    Disconnect-VIServer -Server $vCenter_server -Force -confirm:$false

}

main