Update /macadmins endpoints to include MDM name and aggregate count

[lukeheath]

Goal

For MacOS hosts, add ability to know which host is enrolled in an undesired Mobile Device Management (MDM) solution.

Figma

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

Related

• Know how many hosts, and which hosts, are enrolled in a specific Mobile device management (MDM) solution #6433 (epic)
• Retrieve and aggregate available MDM solutions for MacOS hosts #6928 (frontend)

Tasks

1

• Update GET /api/v1/fleet/hosts/{id}/macadmins endpoint to provide MDM solution name.
• Osquery does not currently provide the MDM name, only the server url. In order to provide the name, we are going to parse the server url.
• If the “Sever URL” contrins “kandji”, the “Name” is “Kandji”
• If the “Sever URL” contains “jamf”, the “Name” is “Jamf”
• If the “Sever URL” contains “airwatch”, the “Name” is “VMware Workspace ONE”
• If the “Sever URL” contains “microsoft”, the “Name” is “Intune”
• If the “Sever URL” contains “simplemdm”, the “Name” is “SimpleMDM”
• The “Name” is null for all other cases but still grouped by server_url.
• There may be other URLs that we need mapping support for in the future.
• server_url should be unique. There may be multiple identical name rows, but the server_url will be unique.

Example response

{
  "macadmins": {
    "munki": {
      "version": "1.2.3"
    },
    "mobile_device_management": {
      "id": 1,
      "enrollment_status": "Enrolled (automated)",
      "server_url": "https://a.simplemdm.com/mdm"
      "name": "SimpleMDM"
    }
  }
}

2

• Update GET /api/v1/fleet/macadmins endpoint to include new mobile_device_management_solution array.
• Contains an object with "name" and "hosts_count" properties.
• Support filtering results by team id using the team_id query param. Example:GET /api/v1/fleet/macadmins?team_id=3

New endpoint response example:

{
  "macadmins": {
    "counts_updated_at": "2021-03-21 12:32:44",
    "munki_versions": [
      {
        "version": "5.5",
        "hosts_count": 8360
      },
      {
        "version": "5.4",
        "hosts_count": 1700
      },
      {
        "version": "5.3",
        "hosts_count": 400
      },
      {
        "version": "5.2.3",
        "hosts_count": 112
      },
      {
        "version": "5.2.2",
        "hosts_count": 50
      }
    ],
    "mobile_device_management_enrollment_status": {
      "enrolled_manual_hosts_count": 124,
      "enrolled_automated_hosts_count": 124,
      "unenrolled_hosts_count": 112
    },
    "mobile_device_management_solution": [
      {
        "id": 1,
        "name": "SimpleMDM",
        "hosts_count": 8360,
        "server_url": "https://a.simplemdm.com/mdm"
      },
      {
        "id": 2,
        "name": "Intune",
        "hosts_count": 1700,
        "server_url": "https://enrollment.manage.microsoft.com"
      },
      ...
    ]
  }
}

Example UI

We will use the new aggregated counts in this table:

3

• Update the GET /api/v1/fleet/hosts endpoint as needed so that hosts can be filtered by mdm id.
• We have a host_mdm table, but it looks like it may be time to add a mobile_device_management table.

4

• Update the GET /api/v1/fleet/hosts endpoint as needed so that hosts can be filtered by mdm enrollment status.
• The frontend will send the status as a URL param:
  /hosts/manage/?mdm_enrollment_status=manual
/hosts/manage/?mdm_enrollment_status=automatic
/hosts/manage/?mdm_enrollment_status=unenrolled

5

• Load test as necessary with platform team support.
• If unsure if necessary, ask Tomas or Lucas.

6

• Add mobile_device_management_solution object to GET /hosts response when mdm_id filter applied.

Example response:

{
  "hosts": [
    {
      "created_at": "2020-11-05T05:09:44Z",
      "updated_at": "2020-11-05T06:03:39Z",
      "id": 1,
      "detail_updated_at": "2020-11-05T05:09:45Z",
      "label_updated_at": "2020-11-05T05:14:51Z",
      "seen_time": "2020-11-05T06:03:39Z",
      "hostname": "2ceca32fe484",
      "uuid": "392547dc-0000-0000-a87a-d701ff75bc65",
      "platform": "centos",
      "osquery_version": "2.7.0",
      "os_version": "CentOS Linux 7",
      "build": "",
      "platform_like": "rhel fedora",
      "code_name": "",
      "uptime": 8305000000000,
      "memory": 2084032512,
      "cpu_type": "6",
      "cpu_subtype": "142",
      "cpu_brand": "Intel(R) Core(TM) i5-8279U CPU @ 2.40GHz",
      "cpu_physical_cores": 4,
      "cpu_logical_cores": 4,
      "hardware_vendor": "",
      "hardware_model": "",
      "hardware_version": "",
      "hardware_serial": "",
      "computer_name": "2ceca32fe484",
      "public_ip": "",
      "primary_ip": "",
      "primary_mac": "",
      "distributed_interval": 10,
      "config_tls_refresh": 10,
      "logger_tls_period": 8,
      "additional": {},
      "status": "offline",
      "display_text": "2ceca32fe484",
      "team_id": null,
      "team_name": null,
      "pack_stats": null,
      "issues": {
        "failing_policies_count": 2,
        "total_issues_count": 2
      }
    }
  ],
  "mobile_device_management_solution': {
      "id": 1,
      "name": "SimpleMDM",
      "hosts_count": 8360,
      "server_url": "https://a.simplemdm.com/mdm"
    },

This is similar to how a software object is returned with the payload when a software id filter is applied. Because there are no dedicated endpoints to retrieve MDM details by mdm_id, we need the object included in this response payload in order to populate the filter data in the UI.

[RachelElysia]

@lukeheath typo
enrolled_automatic_hosts_count
should be
enrolled_automated_hosts_count

[lukeheath]

@RachelElysia Thanks! Fixed. It was also wrong in the API docs (where I copied it from), so I've put in a PR to fix that as, well.

[mna]

@lukeheath @noahtalerman I'll document that the new mdm_id and mdm_enrollment_status query string filters are also available in the GET /api/_version_/fleet/hosts/count endpoint, which supports the same arguments as the list hosts endpoint, except that it just returns the count.

Another thing I wanted to point out is that typically, when adding information to hosts we're careful with the migrations time as there may be a lot of hosts, so what I did is that the MDM name and aggregate stats are not computed at migration time for existing hosts, it will be gradually done as hosts send back the MDM osquery results. I think in most cases this means that within an hour (the osquery.detail_update_interval parameter) all hosts would've sent back fresh data for that query and the stats would be up-to-date, but in the meantime they would not immediately show up-to-date statistics. Let me know if that is not an acceptable tradeoff.

[noahtalerman]

we're careful with the migrations time as there may be a lot of hosts, so what I did is that the MDM name and aggregate stats are not computed at migration time for existing hosts, it will be gradually done as hosts send back the MDM osquery results

@mna makes sense to me!

in most cases this means that within an hour (the osquery.detail_update_interval parameter) all hosts would've sent back fresh data for that query and the stats would be up-to-date, but in the meantime they would not immediately show up-to-date statistics.

Can you please make sure that this explanation is included in the /changes file for this feature? This way, we'll remember to explain the behavior to users in the release notes.

[mna]

Can you please make sure that this explanation is included in the /changes file for this feature? This way, we'll remember to explain the behavior to users in the release notes.

Sure thing, will do. Thanks!

[mna]

I ran a load test for the backend data ingestion part (making sure the ingestion could properly handle the new mobile_device_management_solutions table and mdm_id in host_mdm, etc.), with 60K hosts all look good, no obvious impact on usage. Here are the screenshots of the metrics (the update host_mdm statement does show up as last in "Top SQL" but its impact is still very small).

[lukeheath]

@mna During implementation on the frontend, @RachelElysia pointed out that we have one additional requirement on the backend to fully implement this issue. I've added the new spec as item 6 in the description above.

On Monday, would you please prioritize adding this spec, and ask that the review process be expedited on the backend? We need to get that in so Rachel can finish the frontend and get that merged, which we're hoping to do by EOD Monday so that this feature can be included in the next release. Sorry for the last minute fire drill! Fortunately I think this will be a pretty quick and easy change. Please let @RachelElysia know the status of this as it is completed on Monday.

[mna]

@lukeheath @chiiph Allright so I implemented the last (6th) bullet, one thing to keep in mind is that for now I haven't implemented any cleanup logic of the new mobile_device_management_solutions table (we probably expect a low number of rows, as it will only create one per different mdm server_url value, I'm guessing a few dozen at most per organization but I may be wrong?).

AFAICS, there is also no cleanup logic for the general aggregated_stats table, which is used for the aggregated stats of this ticket but also for other MDM and Munki stats. This should also grow very slowly (only due to deleted teams, I believe).

Let me know if y'all want me to create issues for those (one per table), it may not be worth it but wanted to double-check especially for the mdm solutions one.

[lukeheath]

@mna Thanks for checking! I agree, I don't foresee too many rows in the mobile_device_management_solutions table, but I'll defer to @chiiph on any DB performance questions.

[chiiph]

Deferring the cleanup makes sense to me, yeah.