PowerShell, REST и Excel

Обнаружили проблему: командлет Get-OrgVdcNetwork почему-то не выводит часть сетей orgVDC. Опытным путем выяснили, что ему не нравятся Shared-сети, то есть сети доступные из нескольких orgVDC.

Для решения проблемы решили воспользоваться REST API от vCloud Director.

А чтобы было не скучно, решили вывод сетей делать сразу в файл Excel, чтобы уменьшить количество ручной работы.

Оригинал PoSH-скрипта я положил на гитхаб, тут его приведу для наглядности (хотя с отступами при копипасте беда).

Скрипту понадобится список vCloud Director’ов в файле vcd_list.txt.

#Script makes an Excel file, which contains oVDC's networks. It takes VCD list from vcd_list.txt file.
function Get-Netmask {
  param ($prefixLength)
  $bitString = ('1' * $prefixLength).PadRight(32, '0')
  $ipString=[String]::Empty

# make 1 string combining a string for each byte and convert to int
  for($i=0;$i -lt 32;$i+=8){
    $byteString=$bitString.Substring($i,8)
    $ipString+="$([Convert]::ToInt32($byteString, 2))."
  }
  $ipString.TrimEnd('.')
}

$ExcelApplication = new-object -comobject excel.application 
$ExcelApplication.Visible = $false
$WorkBook = $ExcelApplication.Workbooks.Add()
$sheets = 1

$apiVersion = '35.0'
$cred = Get-Credential
$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($cred.Password))
if($password -like "*:*"){Write-Host("Password is containing ':'. Exiting now...");break}
$userPass = $cred.Username
if(!($userPass -like "*@*")){$userPass += "@system"}
$userPass += ":$password"
$bytes= [System.Text.Encoding]::UTF8.GetBytes($userpass)
$encodedlogin=[Convert]::ToBase64String($bytes)

$content = get-content vcd_list.txt | sort -Descending

foreach ($cont in $content) {
  $base_uri = $cont
  $sheets += 1
  $xws1 = $WorkBook.Worksheets.add()
  $xws1.Name = $base_uri.split('/')[2]
  $headers = @{}
  $authheader = "Basic " + $encodedlogin
  $headers.Add("Authorization",$authheader)

  $version_uri = $base_uri + "api/versions"
  try {$get_version = Invoke-RestMethod -Uri $version_uri -Headers $headers -Method GET -ErrorAction Stop}
  catch {$err_mes = $_;$get_version = $false}

  $check_version = $get_version.SupportedVersions.VersionInfo | Where-Object {$_.Version -eq $apiVersion} 

  if ($check_version){
    $headers.Add("Accept","application/*;version=$apiVersion")
    $session_uri = $base_uri + "api/sessions"
    try{$get_session = Invoke-WebRequest -Uri $session_uri -Headers $headers -Method POST -ErrorAction Stop}
    catch{$err_mes = $_;$get_version = $false}
    if ($get_session.StatusCode -eq '200'){
      $base_uri
      Write-Host "Success" -ForegroundColor White
      $bearer_token = 'Bearer '+ $get_session.Headers.Item('X-VMWARE-VCLOUD-ACCESS-TOKEN')
      $headers.Remove("Authorization")
      $headers.Add("Authorization",$bearer_token)
      $net_uri = $base_uri + 'cloudapi/1.0.0/orgVdcNetworks'
      $Data = Invoke-RestMethod -Uri $net_uri -Headers $headers -Method get -ContentType 'application/*+json'
      $total = $Data.resultTotal
      for ($num = 1 ; $num -le [int][Math]::Ceiling($total / 16) ; $num++){
        $tmp_net_uri = $net_uri + '?page=' + [string]$num + '&pageSize=16'
        $Data = Invoke-RestMethod -Uri $tmp_net_uri -Headers $headers -Method get -ContentType 'application/*+json'
        if($num -eq 1){$orgnets = $Data.values}
        else{$orgnets += $Data.values}
        }
      $orgVDCs = @()
      for ($num = 0 ; $num -lt $total ; $num++){
        $row = "" | select Tenant, vDC, OrgVdcNetwork, Gateway, Netmask
        $row.Tenant = $orgnets[$num].orgref.name
        $row.vDC = $orgnets[$num].orgvdc.name
        $row.OrgVdcNetwork = $orgnets[$num].name
        $tmp_net_uri = $net_uri + '/' + $orgnets[$num].id
        $Data = Invoke-RestMethod -Uri $tmp_net_uri -Headers $headers -Method get -ContentType 'application/*+json'
        $row.Gateway = $Data.subnets.values.gateway
        $row.Netmask = Get-Netmask -prefixLength $Data.subnets.values.prefixlength
        $orgVDCs += $row
        }
      $orgVDCs_tmp = $orgVDCs | sort Tenant
      $xws1.Cells.Item(1,1) = 'Tenant'
      $xws1.Cells.Item(1,2) = 'vDC'
      $xws1.Cells.Item(1,3) = 'OrgVdcNetwork'
      $xws1.Cells.Item(1,4) = 'Gateway'
      $xws1.Cells.Item(1,5) = 'Netmask'
      for ($num = 0 ; $num -lt $total ; $num++){
        $xws1.Cells.Item($num+2,1) = $orgVDCs_tmp[$num].Tenant
        $xws1.Cells.Item($num+2,2) = $orgVDCs_tmp[$num].vDC
        $xws1.Cells.Item($num+2,3) = $orgVDCs_tmp[$num].OrgVdcNetwork
        $xws1.Cells.Item($num+2,4) = $orgVDCs_tmp[$num].Gateway
        $xws1.Cells.Item($num+2,5) = $orgVDCs_tmp[$num].Netmask
        }    
      $usedRange = $xws1.UsedRange
      $usedRange.EntireColumn.AutoFit() | Out-Null
      }
    else{Write-Host "Failed" -ForegroundColor Red;Write-Host $err_mes.Exception -ForegroundColor Red}
  }
  else{Write-Host "Failed" -ForegroundColor Red;Write-Host $err_mes.Exception -ForegroundColor Red}
}
$ext=".xlsx"
$path=(Get-Location).Path + "\orgvdcnet$ext"
$ExcelApplication.DisplayAlerts = $false;
$workbook.Worksheets.Item($sheets).Delete()
$WorkBook.Saveas($path)
$WorkBook.Close()
$ExcelApplication.Quit()

100

101

102

103

104

105

#Script makes an Excel file, which contains oVDC's networks. It takes VCD list from vcd_list.txt file.

function Get-Netmask {

param ($prefixLength)

$bitString = ('1' * $prefixLength).PadRight(32, '0')

$ipString=[String]::Empty

# make 1 string combining a string for each byte and convert to int

for($i=0;$i -lt 32;$i+=8){

$byteString=$bitString.Substring($i,8)

$ipString+="$([Convert]::ToInt32($byteString, 2))."

}

$ipString.TrimEnd('.')

}

$ExcelApplication = new-object -comobject excel.application

$ExcelApplication.Visible = $false

$WorkBook = $ExcelApplication.Workbooks.Add()

$sheets = 1

$apiVersion = '35.0'

$cred = Get-Credential

$password = [System.Runtime.InteropServices.Marshal]::PtrToStringAuto([System.Runtime.InteropServices.Marshal]::SecureStringToBSTR($cred.Password))

if($password -like "*:*"){Write-Host("Password is containing ':'. Exiting now...");break}

$userPass = $cred.Username

if(!($userPass -like "*@*")){$userPass += "@system"}

$userPass += ":$password"

$bytes= [System.Text.Encoding]::UTF8.GetBytes($userpass)

$encodedlogin=[Convert]::ToBase64String($bytes)

$content = get-content vcd_list.txt | sort -Descending

foreach ($cont in $content) {

$base_uri = $cont

$sheets += 1

$xws1 = $WorkBook.Worksheets.add()

$xws1.Name = $base_uri.split('/')[2]

$headers = @{}

$authheader = "Basic " + $encodedlogin

$headers.Add("Authorization",$authheader)

$version_uri = $base_uri + "api/versions"

try {$get_version = Invoke-RestMethod -Uri $version_uri -Headers $headers -Method GET -ErrorAction Stop}

catch {$err_mes = $_;$get_version = $false}

$check_version = $get_version.SupportedVersions.VersionInfo | Where-Object {$_.Version -eq $apiVersion}

if ($check_version){

$headers.Add("Accept","application/*;version=$apiVersion")

$session_uri = $base_uri + "api/sessions"

try{$get_session = Invoke-WebRequest -Uri $session_uri -Headers $headers -Method POST -ErrorAction Stop}

catch{$err_mes = $_;$get_version = $false}

if ($get_session.StatusCode -eq '200'){

$base_uri

Write-Host "Success" -ForegroundColor White

$bearer_token = 'Bearer '+ $get_session.Headers.Item('X-VMWARE-VCLOUD-ACCESS-TOKEN')

$headers.Remove("Authorization")

$headers.Add("Authorization",$bearer_token)

$net_uri = $base_uri + 'cloudapi/1.0.0/orgVdcNetworks'

$Data = Invoke-RestMethod -Uri $net_uri -Headers $headers -Method get -ContentType 'application/*+json'

$total = $Data.resultTotal

for ($num = 1 ; $num -le [int][Math]::Ceiling($total / 16) ; $num++){

$tmp_net_uri = $net_uri + '?page=' + [string]$num + '&pageSize=16'

$Data = Invoke-RestMethod -Uri $tmp_net_uri -Headers $headers -Method get -ContentType 'application/*+json'

if($num -eq 1){$orgnets = $Data.values}

else{$orgnets += $Data.values}

}

$orgVDCs = @()

for ($num = 0 ; $num -lt $total ; $num++){

$row = "" | select Tenant, vDC, OrgVdcNetwork, Gateway, Netmask

$row.Tenant = $orgnets[$num].orgref.name

$row.vDC = $orgnets[$num].orgvdc.name

$row.OrgVdcNetwork = $orgnets[$num].name

$tmp_net_uri = $net_uri + '/' + $orgnets[$num].id

$Data = Invoke-RestMethod -Uri $tmp_net_uri -Headers $headers -Method get -ContentType 'application/*+json'

$row.Gateway = $Data.subnets.values.gateway

$row.Netmask = Get-Netmask -prefixLength $Data.subnets.values.prefixlength

$orgVDCs += $row

}

$orgVDCs_tmp = $orgVDCs | sort Tenant

$xws1.Cells.Item(1,1) = 'Tenant'

$xws1.Cells.Item(1,2) = 'vDC'

$xws1.Cells.Item(1,3) = 'OrgVdcNetwork'

$xws1.Cells.Item(1,4) = 'Gateway'

$xws1.Cells.Item(1,5) = 'Netmask'

for ($num = 0 ; $num -lt $total ; $num++){

$xws1.Cells.Item($num+2,1) = $orgVDCs_tmp[$num].Tenant

$xws1.Cells.Item($num+2,2) = $orgVDCs_tmp[$num].vDC

$xws1.Cells.Item($num+2,3) = $orgVDCs_tmp[$num].OrgVdcNetwork

$xws1.Cells.Item($num+2,4) = $orgVDCs_tmp[$num].Gateway

$xws1.Cells.Item($num+2,5) = $orgVDCs_tmp[$num].Netmask

}

$usedRange = $xws1.UsedRange

$usedRange.EntireColumn.AutoFit() | Out-Null

}

else{Write-Host "Failed" -ForegroundColor Red;Write-Host $err_mes.Exception -ForegroundColor Red}

}

else{Write-Host "Failed" -ForegroundColor Red;Write-Host $err_mes.Exception -ForegroundColor Red}

}

$ext=".xlsx"

$path=(Get-Location).Path + "\orgvdcnet$ext"

$ExcelApplication.DisplayAlerts = $false;

$workbook.Worksheets.Item($sheets).Delete()

$WorkBook.Saveas($path)

$WorkBook.Close()

$ExcelApplication.Quit()

скрипт запрашивает учетные данные для подключения к vCD;
скрипт считывает список vCloud Director’ов из текстового файла;
создает Excel-файл, а на нем одноименный лист по каждому vCD;
заполняет список сетей по vCD на листе;
сохраняет результат в файле orgvdcnet.xlsx

Что стоило бы доделать – проверку на наличие установленного Excel (например, тут – $ExcelApplication = new-object -comobject excel.application). Если Excel нет, то делать вывод в CSV-файлы по старинке…

6 thoughts on “PowerShell, REST и Excel”

Alexander says:

April 14, 2022 at 3:41 pm

Имхо (имхо!), универсальнее все же делать для вывода массив из [ordered][PSCustomObject] который потом легко конвертнуть в CSV/JSON и уже после отправить в файл например. =) Работать же с CSV в Excel легко и просто. А вот запускать Ваш скрипт на машине без Excel уже не получится.

1. A.Vakhitov says:
  
  April 14, 2022 at 9:13 pm
  
  Александр, согласен насчет универсальности.
  Но мне поработать с Excel было интереснее, чем в очередной раз сделать
  $orgVDCs_tmp | Export-Csv -NoTypeInformation -Path “$cont.csv”
  
  Ну и “лучшее – враг хорошего”:
  в обозримом будущем запускать этот скрипт на машине без Excel я не планирую.
  А сидеть и отлаживать запуск скрипта в разных окружениях – я ж не разработчик, которому за это еще и платят 😉
  
  1. Alexander says:
    
    April 19, 2022 at 11:02 pm
    
    “поработать с Excel из powershell” – пахнуло 2010-2012 годами =)
    
Сергей says:

April 20, 2022 at 5:01 pm

Помнится через ADODB (кажется нужен был установленный MDAC) можно было писать в xls файл как в базу данных инсёртами. Новые листы тоже можно было SQL командами создавать. При этом установленный Excel был не нужен.

1. Сергей says:
  
  April 20, 2022 at 5:03 pm
  
  что важно – работало во много раз быстрее чем через объект excel.application.
  
  1. Alexander says:
    
    April 23, 2022 at 5:58 pm
    
    Если что – odbc и сейчас никуда не делся из Windows 10/11. И драйвер для Excel есть (правда 32-битный только). Только оно работает только под Windows. По этому я за то что бы писать на powercli/powershell core =)

6 thoughts on “PowerShell, REST и Excel”

Leave a Reply Cancel reply