Hi all, I think I've asked this before, but I wanted to revisit to see if anything has changed: I'm trying to tie users to a MAC address using the interface_details table. Unfortunately I don't see anything I can use to join users and interfaces together, aside from the Host column which isn't available for use. Is there a way to access that Host column that I'm not aware of?

Since all queries are run on the individual host, you can use a join without using the hostname to associate rows. If you'd like to fill me in a little more on what your end goal is and what shape the data needs to be in, I can probably help you get a query put together!

Hi Kathy! Sure, here's what's going on: So I have a user who would like to see the user associated with a given MAC address in FleetDM. From what I've found so far, it looks like SELECT mac FROM interface_details will get me there. But I'm not seeing a matching column that I can use to join it together. In the end, I think we would just want the hostname, username(s) and mac address.

While you can do the JOIN without an ON condition (since queries are run on each individual host, you always know that the 'users' and 'mac addresses' are coming from the same host), this is actually one that I'd probably actually approach using subqueries:

SELECT 
  (SELECT GROUP_CONCAT (username) FROM users WHERE directory like "/Users%") as "users", 
  (SELECT GROUP_CONCAT (DISTINCT mac) FROM interface_details) as "mac_addresses"

This is great, thank you! I'll do some digging into subqueries, this looks pretty useful.

Awesome! There are a lot of creative things you can do, depending on how you're going to use the data. Like if I wanted a list of mac addresses for any host that a specific user had logged in to, I might try:

SELECT DISTINCT mac 
FROM interface_details
WHERE EXISTS (
  SELECT 1 FROM users where username='ksatter'
)

Now I'm going through some of my other queries and adding in this contextual data. Super cool!

I sometimes have to remind myself that the whole database is just a skin to streamline syntax for os-level commands. When you get comfy with that, you can start doing some really interesting things.