steampipe plugin install theapsgroup/vsphere

Table: vsphere_vm

A VM is a virtual machine.

The vsphere_vm table can be used to query virtual machines.

Examples

List virtual machines

select
*
from
vsphere_vm;

Select all vms with more than 6 cores assigned

select
*
from
vsphere_vm
where
num_cpu > 6;

Select all vms with a name containing test and an uptime of more than 1 hour

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

Select all VMs showing their powerstate and on which host they are running

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

Total Actual storage consumption per VM Order by size in Gigabytes and on how many/which datastores

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

Show all virtual disks in order of size that are used by all the VMs with size and type information

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

Schema for vsphere_vm

NameTypeOperatorsDescription
_ctxjsonbSteampipe context in JSON form, e.g. connection_name.
cpu_usagebigintVM cpu usage in mhz
devicesjsonbVirtual Machine hardware devices
guest_full_nametextGuest operating system name configured on the virtual machine.
guest_memory_usagebigintCurrent memory usage in mb
hardwaretextVersion of the virtual hardware
host_memory_usagebigintConsumed memory on the host by this vm
host_moreftextThe host that is responsible for running a virtual machine.
idtextThe guest operating system identifier (short name)
ip_addresstextPrimary IP address assigned to the guest operating system, if known
memorybigintMemory size of the virtual machine in MB
moreftextManaged object reference of the virtual machine
nametextThe name of the virtual machine
num_cpubigintNumber of virtual processors in the virtual machine
powertextThe powerstate of this vm
statustextThe overall guest status
storage_consumedjsonbConsumed Storage Usage
uptimebigintThe guest uptime in seconds
uuidtextThe UUID of the virtual machine