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?

The problem is

scheduled_tasks

path does not seem to be a filesystem path

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?

Yes that should create an empty set.

It would seem that doing a join should hopefully never cause you to lose data you would be collecting otherwise and all that.

This is for MySQL, but answer applies: https://stackoverflow.com/a/9770404/491710

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"

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.

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.

@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.

This is very true, the shame is my own on the JOIN vs LEFT JOINfacepalm

No shame. There’s a lot to learn.