MYSQL: How to decode the ip_number from the ip_addresses table?

Question

How do I decode the IPv4 addresses in the ip_addresses table to something readable?

Environment

All OnApp versions

Answer

https://onapp.zendesk.com/entries/22073502-mysql-how-to-access-the-mysql-database-in-onapp

The IPv4 addresses are stored as the decimal representation of the full 32 byte hexadecimal representation.

To decode the addresses back into human-readable form, use the following function where you would access the ip_number:

INET_NTOA(ip_number) as 'IPv4'

As an example query, this returns a table of the VMs with the IPv4 addresses, along with their identifiers:

 OnApp 5.0-5.3:

select v.label,v.identifier,inet_ntoa(address) as 'IPv4' from ip_addresses i join ip_address_joins j on i.id=j.ip_address_id join network_interfaces n on j.network_interface_id=n.id join virtual_machines v on n.virtual_machine_id=v.id where ipv4=1 order by v.label;

select v.label,v.identifier,inet_ntoa(address) as 'IPv4' from networking_ip_addresses i join networking_ip_address_joins j on i.id=j.ip_address_id join networking_network_interfaces n on j.network_interface_id=n.id join virtual_machines v on n.virtual_machine_id=v.id where ipv4=1 order by v.label;