Loop through SQL query using variable from another table

Question

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:\Users\example\Documents\%%';

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:\Users\example                            |
|                                             |
|                                             |
| %systemroot%\system32\config\systemprofile  |
| %systemroot%\ServiceProfiles\LocalService   |
| %systemroot%\ServiceProfiles\NetworkService |
+---------------------------------------------+

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 1

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.