If you, like me, are used to doing SQL queries that join tables on a unique ID of some sort, you may find querying multiple tables at once in FleetDM to be confusing.

Even though every query you run on FleetDM will report back a hostname, you can’t do something like

SELECT * FROM first_table ft
INNER JOIN second_table st
ON ft.hostname = st.hostname
WHERE field_name = 'some_value';

hostname will show up in the results, but it’s not a selectable field in most FleetDM tables.

I asked about this in the #fleetdm channel of the MacAdmins Slack, and the solution is fairly simple (special thanks to Adam Anklewicz for the tip): you just have a subquery in the SELECT clause for whatever you want from the other table.

Adam linked to this example, which grabs the username from the users table to include in a query on Munki installs:

SELECT
name,
installed_version,
end_time,
(SELECT username FROM users WHERE directory LIKE '/Users/%' ORDER BY uid LIMIT 1) AS primary_user
FROM
munki_installs
WHERE
installed = 'true';

I was able to then verify that you can do a similar principle to get the serial number as well, which would be something like:

SELECT field1, field2, field3,
(SELECT hardware_serial FROM system_info LIMIT 1) AS hardware_serial
FROM someothertable
WHERE field1 = value1;

The idea, I think, is that every query is being run on the same system, so joining on hostname or serial is a bit redundant, so it will always be the same host and the same serial.