Question


How can I get a custom report which is not available in the user interface?


Answer


Please refer to the Access the MySQL Database in OnApp page for additional details about working with the OnApp database.

Use the SELECT statements to get information:

  • 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;
    CODE
  • Template usage (how many existing virtual servers 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;
    CODE
  • List of users who own current virtual servers 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;
    CODE
  • Get a user's datastore 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;
    CODE
  • Virtual servers 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;
    CODE
  • Virtual servers 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;
    CODE

Additional Information


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