Title
#general
e

Eoin Miller

02/18/2020, 7:21 PM
Seeing some weird behavior from the
scheduled_tasks
table when doing JOIN's against other tables such as
file
and hash:
osquery> SELECT count(*) FROM scheduled_tasks;
| 162      |
When joining, we get an empty set response:
osquery> SELECT count(*) FROM scheduled_tasks JOIN file USING (path);
| 0        |
osquery> SELECT count(*) FROM scheduled_tasks JOIN hash USING (path);
| 0        |
I'll capture it in a GitHub issue, just wondering if this is know or others have encountered similiar?
zwass

zwass

02/18/2020, 7:27 PM
The problem is
scheduled_tasks
path does not seem to be a filesystem path
7:27 PM
When you do a JOIN any rows that have a null value in the joined table get dropped. So you could still get 162 results if you did a LEFT JOIN (but you wouldn't get the file table info).
7:30 PM
I'm trying to understand what the path actually represents... The docs sure make it look like it would be a filesystem path.
7:39 PM
Okay so I'm not sure exactly why this is, but the path actually seems to be relative.
e

Eoin Miller

02/18/2020, 7:40 PM
Hrmmm, yea it doesn't look like path means a filesystem path like it means with other tables. This looks like something relative to the scheduled task:
osquery> SELECT path FROM scheduled_tasks WHERE path LIKE "%testing%";
+----------+
| path     |
+----------+
| \testing |
+----------+
A bit confusing, but also maybe more importantly, if it can't hash/file with a JOIN, should that create an empty set?
zwass

zwass

02/18/2020, 7:40 PM
Yes that should create an empty set.
7:41 PM
Okay I found it's relative to DRIVE_NAME:\Windows\Tasks
e

Eoin Miller

02/18/2020, 7:41 PM
It would seem that doing a join should hopefully never cause you to lose data you would be collecting otherwise and all that.
zwass

zwass

02/18/2020, 7:42 PM
This is for MySQL, but answer applies: https://stackoverflow.com/a/9770404/491710
e

Eoin Miller

02/18/2020, 7:48 PM
Ah, okay. So maybe it is just the description or what is being collected for
path
that is misleading or incorrect? The location of the task is what is being set to
path
but the docs say:
Path to the executable to be run If
path
were the path to the executable for this task, then it should be reporting back the value of "C:\Windows\System32\cmd.exe"
zwass

zwass

02/18/2020, 7:50 PM
path
is the (relative) path to the task definition. A query like
select * from scheduled_tasks s JOIN file f ON (f.path = '\Windows\System32\Tasks' || s.path);
may be what you want.
7:51 PM
Now it sounds like what you want is the path to the
action
column, but that is more tricky.
7:51 PM
Because for
action
may not be a simple path. Here's a value from running on my VM:
%windir%\system32\ProvTool.exe /turn 5 /source LogonIdleTask
7:53 PM
Some actions are blank, etc.
7:53 PM
I even see a weird one like this:
$(Arg1) %windir%\system32\gatherNetworkInfo.vbs
7:55 PM
What you could probably do is use a regex to extract the relevant portion and then join the path. It would likely be somewhat brittle, so you'd want to use
LEFT JOIN
to ensure you aren't dropping results.
s

seph

02/18/2020, 9:02 PM
It would seem that doing a join should hopefully never cause you to lose data you would be collecting otherwise and all that.
That doesn’t seem write. JOIN is a sql pragma. It does what join does. You might want a LEFT JOIN, or a JOIN, but fundamentally permuting data changes it.
zwass

zwass

02/19/2020, 1:06 AM
@seph @Eoin Miller regarding that comment, the linked SO post explains well what the semantics of the JOIN are. It's clear (to me) that a LEFT JOIN is to be used when we want to ensure data from the table on the left of the JOIN is not dropped when there is no corresponding data on the right.
e

Eoin Miller

02/19/2020, 1:09 AM
This is very true, the shame is my own on the JOIN vs LEFT JOIN🤦
s

seph

02/19/2020, 1:09 AM
No shame. There’s a lot to learn.
1:10 AM
TBH I google it anytime I start digging into the weds. There are some great illustrations.
1:11 AM
I like the venn diagrams in

https://www.lexo.ch/blog/wp-content/uploads/2012/06/Visual_SQL_JOINS_orig.jpg