List IP addresses allocated in MAAS.

maas_allocated_ip_addresses.sql

SELECT
    sip.ip,
    CASE
        WHEN sip.alloc_type = 0 THEN 'AUTO'
        WHEN sip.alloc_type = 1 THEN 'STICKY'
        WHEN sip.alloc_type = 4 THEN 'USER_RESERVED'
        WHEN sip.alloc_type = 5 THEN 'DHCP'
        WHEN sip.alloc_type = 6 THEN 'DISCOVERED'
        ELSE CAST(sip.alloc_type as CHAR)
    END "alloc_type",
    subnet.cidr,
    node.hostname,
    iface.id AS "ifid",
    iface.name AS "ifname",
    iface.type AS "iftype",
    iface.mac_address,
    bmc.id,
    bmc.power_type
    FROM maasserver_staticipaddress sip
        LEFT OUTER JOIN maasserver_subnet subnet
            ON subnet.id = sip.subnet_id
        LEFT OUTER JOIN maasserver_interface_ip_addresses ifip 
            ON sip.id = ifip.staticipaddress_id
        LEFT OUTER JOIN maasserver_interface iface
            ON iface.id = ifip.interface_id
        LEFT OUTER JOIN maasserver_node node
            ON iface.node_id = node.id            
        LEFT OUTER JOIN maasserver_bmc bmc
            ON bmc.ip_address_id = sip.id
    ORDER BY sip.ip;