Obtain custom human-device mapping in query results

[ddribeiro]

Problem

As a Fleet admin, I want to enrich query results by including a custom human-device mapping value, if set, so that log results include the user associated to a host.

What have you tried?

I am able to add the following to the end of my query to roughly get the user as determined by the account signed into Google Chrome profiles.

 WITH 
chrome_profiles AS (
  SELECT email
  FROM google_chrome_profiles
  WHERE NOT ephemeral
  AND email <> ""
  AND email LIKE "%@fleetdm.com"
)

Fleet uses the google_chrome_profile table to populate the human-device mapping field. If the device is enrolled using ADE with SSO authentication, Fleet will also provide the email address of the user that authenticated during enrollment. Fleet also supports custom mappings added via the API.

The solution of using the google_chrome_profile table in a query works for some cases, but it is not definitive.

Since Fleet has other ways to determine human-device mapping, it would be nice to be able to query that attribute directly.

Potential solutions

In general, being able to query device attributes stored in the Fleet server would solve for this problem. In addition to human-device mapping, this could also be useful for querying device location as determined via the GeoIP location database. It would also be helpful for mobile devices were osquery does not run and all device attributes are stored server side.

What is the expected workflow as a result of your proposal?

As a result of this proposal, I'd be able to write queries that could return human-device mappings stored in Fleet that are not obtained from the `google_chrome_profiles` table

[JoStableford]

Related to a Slack conversation

[noahtalerman]

I want to enrich query results by including a custom human-device mapping value, if set, so that log results include the user associated to a host.

Hey @ddribeiro thanks for tracking this!

Do you know what customer-reedtimmer would do once they have these logs? What workflows would having these logs enable?

Do you know