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_vmwhere num_cpu > 6;
Select all vms with a name containing test and an uptime of more than 1 hour
select *from vsphere_vmwhere 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.powerfrom 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 Datastoresfrom alldisksgroup by moreforder 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 diskswhere label like '%Hard disk%'order by sizeinGB
.inspect vsphere_vm
VM's running in vsphere
Name | Type | Description |
---|---|---|
_ctx | jsonb | Steampipe context in JSON form, e.g. connection_name. |
cpu_usage | bigint | VM cpu usage in mhz |
devices | jsonb | Virtual Machine hardware devices |
guest_full_name | text | Guest operating system name configured on the virtual machine. |
guest_memory_usage | bigint | Current memory usage in mb |
hardware | text | Version of the virtual hardware |
host_memory_usage | bigint | Consumed memory on the host by this vm |
host_moref | text | The host that is responsible for running a virtual machine. |
id | text | The guest operating system identifier (short name) |
ip_address | text | Primary IP address assigned to the guest operating system, if known |
memory | bigint | Memory size of the virtual machine in MB |
moref | text | Managed object reference of the virtual machine |
name | text | The name of the virtual machine |
num_cpu | bigint | Number of virtual processors in the virtual machine |
power | text | The powerstate of this vm |
status | text | The overall guest status |
storage_consumed | jsonb | Consumed Storage Usage |
uptime | bigint | The guest uptime in seconds |
uuid | text | The UUID of the virtual machine |