Table: vsphere_vm

select
  *
from
  vsphere_vm;

select
  *
from
  vsphere_vm
where
  num_cpu > 6;

select
  *
from
  vsphere_vm
where
  name ILIKE '%test%'
  and uptime > 3600;

select
  vm.name,
  host.name,
  vm.power
from
  vc.vsphere_vm as vm
  inner join vc.vsphere_host as host on vm.host_moref = host.host_moref

with alldisks as (
  select
    jsonb_array_elements(storageconsumed) as disks,
    name,
    moref
  from
    vc.vsphere_vm
)
select
  moref,
  (array_agg(name)) [ 1 ] as Name,
  sum(disks [ 'Committed' ] :: bigint / (1024 * 1024 * 1024)) as UsageGB,
  count(disks [ 'Committed' ]) as DatastoresCount,
  string_agg(disks [ 'Datastore' ] [ 'Value' ] :: text, ', ') as Datastores
from
  alldisks
group by
  moref
order by
  UsageGB desc

with disks as (
  with devices as (
    select
      name,
      moref,
      jsonb_array_elements(devices) as device
    from
      vc.vsphere_vm
  )
  select
    moref,
    name,
    trim(
      both '"'
      from
        device [ 'DeviceInfo' ] [ 'Label' ] :: text
    ) as label,
    device [ 'CapacityInKB' ] :: bigint / (1024 * 1024) as sizeinGB,
    trim(
      both '"'
      from
        device [ 'Backing' ] [ 'FileName' ] :: text
    ) as filename,
    device [ 'Backing' ] [ 'ThinProvisioned' ] :: boolean as thinprovisioned
  from
    devices
)
select
  *
from
  disks
where
  label like '%Hard disk%'
order by
  sizeinGB