Example MySQL Select Queries

Question

How can I get a custom report which isn't available in the user interface?

Environment

OnApp 5.x

Answer

You can log in to the MySQL database and use SELECT statements to get information. 

Here are some useful examples:

Basic Usage Data:

SELECT vm.id, vm.identifier, vm.hostname, vm.hypervisor_id, vm.memory, vm.cpus, vm.cpu_shares, sum(disk_size) FROM virtual_machines vm LEFT JOIN disks d ON vm.id=d.virtual_machine_id WHERE vm.deleted_at IS null GROUP BY vm.identifier;

Template Usage (How many existing VMs are using each template):

SELECT T.id AS "Template ID",T.label,T.file_name,count(*) AS "VM Count" FROM templates T JOIN virtual_machines VMS ON VMS.template_id=T.id WHERE VMS.deleted_at IS NULL GROUP BY file_name ORDER BY `VM Count` desc;

List of Users who own current VMs and how many:

SELECT DISTINCT u.id, u.login, u.first_name, u.last_name, u.email, u.created_at, u.last_sign_in_at, u.status,count(vm.user_id) FROM virtual_machines vm LEFT JOIN users u ON vm.user_id=u.id WHERE vm.deleted_at IS null GROUP BY vm.user_id ORDER BY u.id;

Get a User's Data Store Usage By Their User ID:

SELECT d.data_store_id,sum(disk_size) FROM virtual_machines vm JOIN disks d ON vm.id=d.virtual_machine_id WHERE vm.user_id=1 GROUP BY d.data_store_id;

VMs and their IPs (5.0 - 5.3):

SELECT vm.identifier,inet_ntoa(ip.address) FROM virtual_machines vm LEFT JOIN network_interfaces nic ON nic.virtual_machine_id=vm.id LEFT JOIN ip_address_joins ipj ON ipj.network_interface_id=nic.id LEFT JOIN ip_addresses ip ON ipj.ip_address_id=ip.id WHERE deleted_at IS null ORDER BY vm.identifier;

VMs and their IPs (5.4 and after):

SELECT vm.identifier,inet_ntoa(ip.address) FROM virtual_machines vm LEFT JOIN networking_network_interfaces nic ON nic.virtual_machine_id=vm.id LEFT JOIN networking_ip_address_joins ipj ON ipj.network_interface_id=nic.id LEFT JOIN networking_ip_addresses ip ON ipj.ip_address_id=ip.id WHERE deleted_at IS null ORDER BY vm.identifier;

Additional Info

You can replace the ; at the end MySQL queries with \G to get vertically formatted output.  This can make larger tables more readable when displayed in console.