Update `GET /hosts`, `GET /labels/{id}/hosts` and `GET /hosts/count` to include new `macos_settings` filters

[lukeheath]

Tasks

• [ ] Update GET /hosts, GET /labels/hosts and GET /hosts/count to include new MDM profile status filters

• Possible filter values: latest, pending, failing

• If the macos_settings query param is provided with no team id param, it will return "No team" hosts.

• If the macos_settings query param is provided with a team id, it will return the filter applied to the requested team.

• [ ] Ensure the filter column is properly indexed, and that will likely require a load test (which may also require some changes to osquery-perf to ensure we generate/apply mdm profiles for some hosts).

Figma

https://www.figma.com/file/hdALBDsrti77QuDNSzLdkx/%F0%9F%9A%A7-Fleet-EE-(dev-ready%2C-scratchpad)?node-id=10517%3A316027

[lukeheath]

Hey team! Please add your planning poker estimate with Zenhub @gillespi314 @mna @roperzh

[mna]

We should ensure the filter column is properly indexed, and that will likely require a load test (which may also require some changes to osquery-perf to ensure we generate/apply mdm profiles for some hosts).

[lukeheath]

@mna Thanks for the note. I've added it to the requirements so we can include the additional work in our estimation.

[gillespi314]

For the index, I believe we can accomplish what we want to do here by indexing the uuid column. This would allow us to readily join against host_mdm_apple_profiles to accomplish the filtering. Below are some simplified SQL statements to illustrate the concept.

# aggregate mdm status for a host is "failing" if there is a failure installing/removing one or more profiles on the host
SELECT
    h.*
FROM
    hosts h
    JOIN host_mdm_apple_profiles hmap ON h.uuid = hmap.host_uuid
WHERE
    hmap.status = 'failed'

#aggregate mdm status for a host is "pending" if there is one or more profiles pending (but none failed) on the host 
SELECT
    h.*
FROM
    hosts h
    JOIN host_mdm_apple_profiles hmap ON h.uuid = hmap.host_uuid
WHERE
    hmap.status = 'pending'
    AND hmap.host_uuid NOT in(
        SELECT
            host_uuid FROM host_mdm_apple_profiles
        WHERE
            status = 'failed')

# aggregate mdm status for a host is "latest" if one or more profiles is applied and there are no profiles pending or failed on the host. 
SELECT
    h.*
FROM
    hosts h
    JOIN host_mdm_apple_profiles hmap ON h.uuid = hmap.host_uuid
WHERE
    hmap.status = 'applied'
    AND hmap.host_uuid NOT in(
        SELECT
            host_uuid FROM host_mdm_apple_profiles
        WHERE
            status = 'failed'
            OR status = 'pending')

[mna]

@gillespi314 I agree, the index on hosts.uuid is quickly becoming a requirement. We should just make sure that it doesn't introduce any issues at scale (@roperzh had a PR to add the index a while ago but noticed some weird results in load testing, that would require investigating this a bit more).

[gillespi314]

@noahtalerman @lukeheath, what is the expected interplay with respect to the new mdm_profiles_status filter and the teams filter? I think we are missing some specifications around this. Currently, there isn't any concept of "no team" for the lists hosts endpoint/manage hosts page that corresponds to how teams work with profiles.

[lukeheath]

@gillespi314 Thanks for calling this out. Updated the specs. Please let me know if there are any issues with implementing that.

[fleet-release]

A better view of hosts, MacOS settings revealed. Tools for teams to weave, Efficiency to be healed.