Loop through SQL query using variable from another table

I have two tables file & users, I want to see the file info for each user for C:Users%USERNAME%Documents

So e.g. this would get the info from ‘example’ documents:

SELECT * 
FROM file 
WHERE path LIKE 'C:UsersexampleDocuments%%';

But the username is coming from the users

SELECT username FROM users;

returns

+--------------------+
| username           |
+--------------------+
| Administrator      |
| DefaultAccount     |
| example            |
| Guest              |
| WDAGUtilityAccount |
| SYSTEM             |
| LOCAL SERVICE      |
| NETWORK SERVICE    |
+--------------------+

Alternatively, there’s:

SELECT directory FROM users;

+---------------------------------------------+
| directory                                   |
+---------------------------------------------+
|                                             |
|                                             |
| C:Usersexample                            |
|                                             |
|                                             |
| %systemroot%system32configsystemprofile  |
| %systemroot%ServiceProfilesLocalService   |
| %systemroot%ServiceProfilesNetworkService |
+---------------------------------------------+

Which provides the first part of the path, but still can’t get to join ‘Documents’ to end of query and also run the file query.

So, how do I loop through the each of the usernames.

I’ve tried modifying but neither table can be modified

Answer

This is a great opportunity to use a JOIN query:

SELECT f.*
FROM file f JOIN users u
WHERE f.path LIKE 'C:Users' || u.username || 'Documents%%'

When you run this query, osquery will first generate the list of users, then substitute the username into the path provided to the file table.

JOIN is a really powerful way to combine the results of various tables, and it’s well worth taking some time to experiment and learn how to use this power.