Research: Use `UpgradeCode` as the unique identifier for Windows software

[noahtalerman]

Goal

User story
As a Fleet contributor,
I want to understand which Windows Fleet-maintained apps have an UpgradeCode
so that I can match these apps with software titles in Fleet's software inventory.

Key result

Auto-patch Fleet-maintained apps

Original requests

None.

Context

• Designer: @noahtalerman
• Engineer: @ksykulev

More context in the notes doc here: https://docs.google.com/document/d/1s41j8q9GzkyoogE_xG2JvOCyVQ09AVniPOSrwuIrYbU/edit?tab=t.0

Changes

Product

• [ ] Create a public Google doc with the research findings for the following items:
  1. For each of the Windows Fleet-maintained apps in the list here see if we can get the UpgradeCode from the Windows registry.
     1. What osquery query can Fleet run (if any) to get the UpgradeCode? If there's no query, what changes to osquery could we make to get the UpgradeCode? -> https://github.com/osquery/osquery/pull/8587
     2. Can Fleet use UpgradeCode to match the Fleet-maintained app to the software title that comes back in Fleet's software inventory in the following scenarios? If not, explain why.
        1. End user installed the software outside of Fleet, via the third-party vendor's website.
        2. IT admin or end user installed the software via Fleet.
  2. If we can't get the UpgradeCode for an app or the UpgradeCode won't help us match for one of the above scenarios, what's the fallback? What else could Fleet use to match the Fleet-maintained app to the software title that comes back in Fleet's software inventory?
• [x] UI changes: No changes.
• [x] CLI (fleetctl) usage changes: No changes.
• [x] YAML changes: No changes.
• [x] REST API changes: No changes.
• [x] Fleet's agent (fleetd) changes: No changes.
• [x] GitOps mode changes: No changes.
• [x] Activity changes: No changes.
• [x] Permissions changes: No changes.
• [x] Changes to paid features or tiers: No changes.
• [x] Transparency changes: No changes.
• [x] First draft of test plan added
• [x] Other reference documentation changes: No changes.
• [ ] Once shipped, requester has been notified
• [x] Once shipped, dogfooding issue has been filed

Engineering

• [x] Test plan is finalized: @noahtalerman: No test plan needed for this research story.
• [x] Contributor API changes: No changes.
• [x] Feature guide changes: No changes.
• [x] Database schema migrations: No changes.
• [x] Load testing: No changes.

ℹ️  Please read this issue carefully and understand it. Pay special attention to UI wireframes, especially "dev notes".

QA

No QA/testing needed for this research story.

[iansltx]

Additional context: based on this information and related osquery work the next step will be to determine how we want to factor UpgradeCode (and the msix equivalent) into software inventory, similar to how we use bundle identifier for macOS.

[ksykulev]

Hey team! Please add your planning poker estimate with Zenhub @iansltx @jahzielv

[mostlikelee]

research stories don't need to go to USR. Moving to ready for spec. Will be chatting with our windows consultant on this.

[noahtalerman]

Hey @mostlikelee! Just a reminder to remove the :product label when you move a story from the drafting board. Otherwise, it will keep showing up on the drafting board. We have an automation to put anything with :product on the drafting board.

[SeattleGari]

Updated with results. Summary: Unfortunately upgrade codes aren't used in a consistent enough fashion to be reliable for locating previous versions of software. It's possible that a key consisting of the publisher name and display name might work but only if system components (apps with a SystemComponent value of 1) are ignored.

https://docs.google.com/document/d/1QbeqgINNOfFHqb9GmGyutj0DARNn2uKgmtk-D4lztVU/edit?tab=t.0

[noahtalerman]

@SeattleGari thanks for taking on this research!

Can you please give [email protected] edit access to the Google doc you created and make it public? (give everyone on the internet read access)

[noahtalerman]

Hey @mostlikelee I see you moved this story and the following stories back to the drafting board:

• #27940
• #29464

I assigned them to myself to these we don't lose them.

What are next steps? Are we unblocked on shipping these apps as Windows Fleet-maintained apps?

[mostlikelee]

@noahtalerman these were assigned to a contractor who is no longer here, i'm reviewing the research today to see if there is enough information there to form anything actionable or if we need more research.

[noahtalerman]

i'm reviewing the research today to see if there is enough information there to form anything actionable or if we need more research.

@mostlikelee awesome! I just assigned this story and the following stories to you:

• https://github.com/fleetdm/fleet/issues/27940
• https://github.com/fleetdm/fleet/issues/29464

[marko-lisica]

Hey @mostlikelee, where are we at with this research? Is there a doc that the contractor made? We should link it to the issue description.

Should we move this to release board?

[mostlikelee]

research links: https://docs.google.com/document/d/1QbeqgINNOfFHqb9GmGyutj0DARNn2uKgmtk-D4lztVU/edit?usp=sharing https://docs.google.com/spreadsheets/d/1XA-d4_g6_xPLItTmkOX00bfdT3QxjCag/edit?usp=sharing&ouid=115597256383006053733&rtpof=true&sd=true

@Illbjorn for your reference

[fleet-release]

Uninstall made clear, Match UpgradeCode with care, Fleet's path is sincere.