Add operating systems table and update related endpoints

[lukeheath]

Problem

I'm an engineer managing thousands of hosts and I'm overwhelmed with tracking my security goals for each of these hosts.

This makes is hard to achieve my security goals because it's difficult to find which hosts are in an undesired state and, when I do, it's difficult to ask these hosts, and their users, why they're in an undesired state.

Goal

Add ability to know which Windows hosts are running Windows versions that are no longer supported (undesired). Also, add ability to know why hosts are running unsupported Windows versions.

Figma

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

Related

• Epic: Know how many hosts, and which hosts, are running unsupported Windows versions #6428
• Blocking: Add Windows OS version information to dashboard and hosts page #6499

Tasks

1

• Add new operating_systems table.
• column name contains OS name (i.e. "Windows 10 Enterprise", "Mac OS X")
• column version contains alphanumeric OS version (i.e. "21H2", "12.4")
• For Windows, the data is retrieved from the following osquery query: select data from registry where path = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\DisplayVersion'
• For MacOS and Linux, we should already be querying this data to populate the host.os_version column.

2

• Add new host_operating_system pivot table.
• Add DB migration to populate new pivot table with all hosts and their related operating system.
• This migration will also populate the operating_systems table.
  Note: Migrations will only include the creation of the new tables. Other data will be populated the next time a host reports detail query results (by default, detail queries are reported once per hour). As a result, if a host is offline hosts at the time of the migration, it not be reflected in the new tables until its next checkin.

3

• Add new os_version query params to the GET /hosts endpoint.
• It will receive an id for the related OS version record in the new host_operating_systemoperating_systems table.

4

• Update GET /os_versions endpoint to return version data from the new host_operating_systemoperating_systems table.
• Return the current host.os_version value in the name property. This is so we don't introduce a breaking change.
• Add new version property for the alphanumeric version.
• Add new name_only property. This is a temporary property we will use until we introduce breaking changes in v5.
• Ensure operating system filter can be applied on API response using URL params. URL includes “operating_system_id” when an operating system filter is applied: /hosts/manage?operating_system_id=2

[gillespi314]

Ensure operating system filter can be applied on API response using URL params. URL includes “/operating_system_id” when an operating system filter is applied: /hosts/manage/operating_system_id=2

@lukeheath did you mean /hosts/manage?operating_system_id=2?

We use query params for similar filters (for example, software_id)

[lukeheath]

@gillespi314

did you mean /hosts/manage?operating_system_id=2?

Yes, thank you! Fixed.

[lukeheath]

@gillespi314 Now that we're in a new release just wanted to check in on the status of this and get a ballpark ETA. Hoping to get the frontend portion of this into the current release. Thanks!

[gillespi314]

@lukeheath I think we'll be able to make this release. Hoping to get the backend portion landed this week. As far as frontend blockers, I think the API specs are not going to change and should be pretty straightforward to mock if folks are ready to get started on the frontend in parallel (and of course I'm available to answer any questions that come up there).

[lukeheath]

@ghernandez345 This is the backend ticket for #6499 assigned to you. Please sync up with Sarah as necessary to complete this work. We have a mock API client that you can use to develop the frontend against before the API is ready if needed. Thanks!