Add Microsoft Account (Office 365 and Azure AD Account) information to the "Used by" field

[dherder]

Goal

User story
As an administrator of Fleet at an organization that uses Microsoft products,
I want to pull in O365 and Azure AD user information, similar to Google Chrome profiles,
so that I can identify which user is associated to a piece of hardware.

Changes

This issue's estimation includes completing:

• [ ] UI changes: TODO
• [ ] CLI usage changes: TODO
• [ ] REST API changes: TODO
• [ ] Permissions changes: TODO
• [ ] Database schema migrations: TODO
• [ ] Outdated documentation changes: TODO
• [ ] Scope transparency changes? TODO
• [ ] Breaking changes requiring major version bump? TODO
• [ ] Changes to paid features or tiers? TODO
• [ ] QA complete?
• [ ] ...

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

Context

• Requestor(s): @dherder

QA

Risk assessment

• [ ] Requires load testing TODO

Risk level: Low / High TODO

Risk description: TODO

Automated:

• Fleet: Cover / Will not cover
• QAWolf: Cover / Will not cover

Manual testing steps

1. Step 1
2. Step 2
3. Step 3

Testing notes

Confirmation

1. [ ] Engineer (@____): Added comment to user story confirming succesful completion of QA.
2. [ ] QA (@____): Added comment to user story confirming succesful completion of QA.

Requirements

Include the results of the following query in the same "Used by" field currently populated by the google_chrome_profiles query.

Potential Solutions

Office365 User:

SELECT user,regex_match(key,'[^\\]+$', 0) as "Microsoft Account" FROM registry JOIN logon_sessions WHERE key LIKE 'HKEY_USERS\%\Software\Microsoft\IdentityCRL\StoredIdentities\%' AND logon_sessions.logon_sid=registry.name GROUP BY "Microsoft Account";

Azure AD User:

select enrolled_user from mdm_bridge

Design

https://www.figma.com/file/WYkQ6zhSqmSwCVnGHS2XzT/%239783-Add-Microsoft-account-information-to-the-%22Used-by%22-field?type=design&node-id=2-130&mode=design

How to test

https://support.microsoft.com/en-us/account-billing/join-your-work-device-to-your-work-or-school-network-ef4d6adb-5095-4e51-829e-5457430f3973

[lucasmrod]

@dherder Thanks for the query!, really helpful.

Do you have any advice on how to test/verify this? (Maybe some test microsoft accounts for us to test with?)

[lucasmrod]

/cc @marcosd4h

[zhumo]

@zayhanlon @edwardsb what happens if there are both chrome and MS accounts?

[zayhanlon]

@edwardsb Did you ever sync with Marcos on this? Perhaps we kick this back to Lucas

@zhumo We have an FR in the pipe #9601 to auto select a primary, I think the way it should work is to show all used by email addresses in the tooltip still. But Fleet chooses a primary to display. Thoughts?

[zhumo]

WDYT of separating it... "Chrome users" vs. "MS Office 365 users"?

[edwardsb]

@zayhanlon yeah 2 phase decommission took longer than expected. I am fine with letting Lucas/Marcos take a look if they have extra cycles.

[zayhanlon]

@zhumo I'm open to this. I can create a frontend ticket for the UI work and assign to Jacob

[zhumo]

Should be design reviewed if so.

[zhumo]

@rachaelshaw @zayhanlon I added the part about the WIndows MDM user from PFR to this issue in both the description and the design. Could you take this through the design process and re-estimate. I proposed calling this mo@example.com (MDM User), but not strongly attached to it.

[zayhanlon]

@rachaelshaw This is ready for a total re-design. You can consider what's in Figma if you'd like, but feel free to propose new changes.

[zhumo]

@dherder taking this off the prioritized board because it's not likely to be designed and shipped within 6 weeks. Please bring it back to FF for consideration if it continues to be desired.

[zhumo]

Hi @dherder, unfortunately, we were not able to get to this work in our 6-week timeframe. Please bring this back to Feature Fest if it's still desired. Thanks!

[noahtalerman]

We moved the requirements from this story to "Lookup hosts based on IdP email or Microsoft account" here: #13034

Closing this story

[fleet-release]

Microsoft's cloud calls, Azure, Office info gleam, User's hardware dream.

[noahtalerman]

Re-opening this story. It looks like the story that was meant to replace this one (#13034) was closed.

Potential osquery queries for Microsoft account

Office365 User:

SELECT user,regex_match(key,'[^\\]+$', 0) as "Microsoft Account" FROM registry JOIN logon_sessions WHERE key LIKE 'HKEY_USERS\%\Software\Microsoft\IdentityCRL\StoredIdentities\%' AND logon_sessions.logon_sid=registry.name GROUP BY "Microsoft Account";

Azure AD User:

SELECT enrolled_user FROM mdm_bridge

[ksatter]

@noahtalerman @sharon-fdm Does this need to go back through the intake process?

[sharon-fdm]

@noahtalerman I'm not sure where we landed with this. Will leave it to your decision.

[noahtalerman]

@ksatter yes! If you think we should consider prioritizing, please add it through feature fest.

[zayhanlon]

@dherder we discussed this one in slack, can you please bring this back if it becomes workflow blocking in the short term?