MEM Query Retrieve List of Hosts

MySQL Enterprise Manager Query

I needed a report of all of the database instances and their notes fields that are stored in MySQL Enterprise Manager (MEM).

The schema for MEM has an EAV design so after a few trials I came up with this query.  I used this in a Python script to drive culling data from all of the hosts (multi-threaded), which I'll share later.



    select
        h.latest_string_value as host, n.latest_string_value as notes
    from
        mem.inventory_instance_attributes h
        inner join mem.inventory_instance_attributes n on
                   h.instance_id = n.instance_id
    where
        h.attribute_id in (SELECT
                attribute_id
            FROM
                mem.inventory_attributes
            where
                attribute_name = 'visible.displayname')

        and
        n.attribute_id in (SELECT
                attribute_id
            FROM
                mem.inventory_attributes
            where
                attribute_name = 'notes')
       order by host ;