Support "No teams" filter on Hosts and Controls pages

noahtalerman commented 1 year ago

Goal

I want to be able to filter by "No teams" on the Hosts and Controls pages and see the correct filtered list of hosts and counts.

Context

Currently, the Hosts UI doesn't support a "No teams" filter
The Controls UI does have a "No teams" filter but the host counts aren't being filtered correctly

Tasks

Backend

The following endpoints should accept a team_id=0 parameter that returns only hosts assigned to no team.

[ ] GET /os_versions
[x] GET /hosts
[x] GET /count
[x] GET /labels/{label_id}/hosts

Same for those endpoints, to return correct statistics:

[x] GET /hosts/summary/mdm
[x] GET /macadmins

Frontend

[ ] On the Controls > macOS updates page, the macOS versions table should include the query param team_id=0 when no team is selected.
[ ] Add a "No team" option to the teams dropdown on the Manage Hosts page. When selected, apply the team_id=0 query parameter to all host / statistics requests (/hosts, /count, /labels/{label_id}/hosts, /hosts/summary/mdm, /macadmins)

lukeheath commented 1 year ago

@gillespi314 @jacobshandling @ghernandez345 I added specs. Is anything missing? You can edit the specs directly if you like.

@mna I'm adding you to this ticket to support Sarah's efforts next week, as this will be a release blocker and may have some complications.

mna commented 1 year ago

@lukeheath sounds good, @gillespi314 let me know if I can be of any help. One thing that will cause issues is with the aggregated_stats table where we pre-compute a number of statistics at regular intervals, in this code we've used team id 0 to mean "all hosts" in many cases (e.g. munki issues counts, mdm solutions counts, os_versions, etc.). I'm not sure if those stats are reported by those affected endpoints, but it seems likely.

mna commented 1 year ago

@lukeheath @gillespi314 I did some investigation to see if we use the same team_id=0 to mean "all teams" elsewhere (other than the aggregated_stats table).

7 tables have a team_id column:

mysql> select table_name from information_schema.columns where table_schema = 'fleet' and column_name = 'team_id';
+----------------------------------+
| table_name                       |
+----------------------------------+
| enroll_secrets                   |
| hosts                            |
| invite_teams                     |
| mdm_apple_configuration_profiles |
| policies                         |
| software_host_counts             |
| user_teams                       |
+----------------------------------+
7 rows in set (0.01 sec)

Of those, enroll_secrets, hosts, invite_teams, policies and user_teams are fine - they even have a foreign key constraint on teams (except for hosts, but that is just for performance reason, the team_id in hosts still refers to a true team and doesn't use 0 as a special meaning).

That leaves mdm_apple_configuration_profiles and software_host_counts. In both of those tables, the team_id is non-nullable. For the apple profiles, it's fine as long as we don't have a need to apply profiles to "all hosts", because as noted in the migration that created the table:

team_id is zero for configuration profiles that are not associated with any team

So at least for now, this table is ok. If we want to support profiles for "all hosts" at some point, this will need to be addressed, but as long as profiles are either for hosts in a team or hosts in no team, this is ok.

software_host_counts, however, appears to be problematic, similarly to aggregated_stats, it uses team_id=0 to indicate the global counts across all teams. Assuming this gets returned by the endpoints affected by this ticket, we'll need to address this table too.

EDIT: I believe this is only an issue if GET /software and/or GET /software/count are affected by this change (i.e. if those endpoints will be called when a filter of "no team" can be applied). I think that's the case, but if @lukeheath or @gillespi314 you can confirm?

EDIT2: just to make sure, I also listed the tables that have an id column name that is not an auto-increment (since this was the case for aggregated_stats, its id is general and does not always correspond to a team id):

mysql> select table_name from information_schema.columns where table_schema = 'fleet' and column_name = 'id' and extra != 'auto_increment';
+-----------------------------+
| table_name                  |
+-----------------------------+
| aggregated_stats            |
| app_config_json             |
| nano_cert_auth_associations |
| nano_command_results        |
| nano_devices                |
| nano_enrollment_queue       |
| nano_enrollments            |
| nano_users                  |
| nano_view_queue             |
+-----------------------------+
9 rows in set (0.01 sec)

I think those are all fine (well, except aggregated_stats that was already identified of course), I can't see any issue with the rest of those.

gillespi314 commented 1 year ago

I don't think we'll urgently need to support "no team" for GET /software and GET /software/count in the short term because I don't think those endpoints are being called inside the same UI views. If my memory serves, the software endpoints are being called in dedicated tabs.

mna commented 1 year ago

@gillespi314 Thanks, sounds good, yeah I double-checked and the only places where the software counts are displayed are on the dashboard and the manage software pages, and this ticket only mentions supporting "No teams" on Hosts and Controls tabs.

Dashboard Software Counts ("No Team" not supported)

Dashboard-Software-Count

Manage Software Counts ("No Team" not supported)

Manage-Software-Count

Manage Hosts, Filter By Software ID ("No Team" will be supported, but software statistics are not displayed)

ManageHosts-Filter-By-SoftwareID

So based on this, @noahtalerman and @lukeheath , we won't be making changes for software_host_counts for this release, as it's not necessary for this ticket. Will be good to keep this in mind in the future when/if we extend the places where we support the "No Team" filter though.

noahtalerman commented 1 year ago

we won't be making changes for software_host_counts for this release, as it's not necessary for this ticket

@mna @gillespi314 got it! Makes sense to me.

fleet-release commented 1 year ago

No teams filter shines, Hosts, Controls harmonize, Cloud city's paths align.

fleetdm / fleet