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
Schema for vsphere_vm
Name | Type | Operators | 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 |