Title
#general
t

Terje Kvernes

11/18/2022, 10:44 PM
Is there a way to create a filter for machines that do not have a specific file present?
s

seph

11/19/2022, 3:38 AM
What do you mean “filter”? Is this a question about a server feature? Which feature?
t

Terje Kvernes

11/20/2022, 4:40 PM
Filter/query. I want to create a query that returns machines that are missing a specific file. I can find machines with the file easily enough, but the opposite stumps me. 🙂
s

sharvil

11/21/2022, 7:44 AM
I can find machines with the file easily enough, but the opposite stumps me
Can you share this query? I think the inverse of this is possible with some sql magic..
t

Terje Kvernes

11/21/2022, 12:03 PM
SELECT 1 from file where path = '/path/to/file'
works well enough for finding the file. 🙂
s

sharvil

11/21/2022, 12:32 PM
something like this:
osquery> select exists(select 1 from file where path = '/tmp/test');
+-----------------------------------------------------+
| exists(select 1 from file where path = '/tmp/test') |
+-----------------------------------------------------+
| 1                                                   |
+-----------------------------------------------------+
osquery> select exists(select 1 from file where path = '/tmp/foo');
+----------------------------------------------------+
| exists(select 1 from file where path = '/tmp/foo') |
+----------------------------------------------------+
| 0                                                  |
+----------------------------------------------------+
osquery>
?
12:33 PM
with
case/when
,
exists/not exists
and subqueries, you can get as fancy as required
t

Terje Kvernes

11/21/2022, 1:00 PM
Ah, brilliant!
1:00 PM
I mean, horrific, but brilliant. 🙂
s

seph

11/21/2022, 1:04 PM
You can left join against time, instead of using sub selects.
s

sharvil

11/21/2022, 1:45 PM
You can left join against time, instead of using sub selects.
Ah, nice TIL -- do you know of an example for this?
1:46 PM
I mean, horrific, but brilliant. 🙂
wait, why @Terje Kvernes.? genuinely curious (I use this pattern every now and then, and wondering if it could be improved)
t

Terje Kvernes

11/21/2022, 3:08 PM
It feels like we’re abusing subselects (or joins), but in reality this is what they do. 🙂
s

sharvil

11/21/2022, 3:20 PM
Ah, okay…in this instance you can also do a
count(*)
I guess if one wants to avoid joins/subqueries:
osquery> select count(*) from file where path = '/tmp/exists';
+----------+
| count(*) |
+----------+
| 1        |
+----------+
osquery> select count(*) from file where path = '/tmp/not_exists';
+----------+
| count(*) |
+----------+
| 0        |
+----------+
t

Terje Kvernes

11/21/2022, 3:21 PM
doh I’ll go hide in the corner of SQL shame.
3:21 PM
Thanks!
s

seph

11/21/2022, 3:22 PM
abusing subselects (or joins)
It’s worth thinking through what that means, and what’s good or bad. There are lots of ways to do things, and some fit circumstances better than others. I, personally, tend to avoid subselects, because it can cause additional “table scans” which translate to API hits. Mostly NBD, but some tables are slow. Joins don’t have that problem, but are usually a bit harder to write. Whether any of this matters depends a lot on the details
3:23 PM
Despite looking like a database, osquery isn’t one. So there’s a lot of weirdness around query planning and execution.
t

Terje Kvernes

11/21/2022, 3:24 PM
How would you use “left join against time” to emulate
select not exists(select 1 from file where path = '/tmp/foo');
?
3:25 PM
I clearly need to retake some SQL courses, the last one I took was in 2003. I feel old. 🙂
s

seph

11/21/2022, 3:28 PM
Play with
with _f AS (select * from file where path = '/tmp/nope') select * from time left join _f;
3:28 PM
cross join might be easier. Depends on the humans reading it