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

I have posted sample data, along with the queries that gets the data in the original post. — asparamancer, Jul 04 '19 at 14:53
So the related columns for the 2 tables are path from table file and directory from table users? — forpas, Jul 04 '19 at 14:55
Yep, I'd like to append Documents to each directory from users and then run the SELECT * FROM file WHERE path LIKE for the ''joined directory" — asparamancer, Jul 04 '19 at 14:57

score 1 · Accepted Answer · answered Jul 06 '19 at 19:25

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.

score 0 · Answer 2 · answered Dec 27 '19 at 19:07

Zach's answer is great, but there are times that a user's directory can be named differently than their respective username.

Thankfully, we also have the directory column in the users table which returns a user's home directory. Using this column will prevent directory/username mismatches from causing issues in your query output:

SELECT f.*
FROM file f JOIN users u
WHERE f.path LIKE u.directory || '\Documents\%%';

Loop through SQL query using variable from another table

2 Answers2