Is there a way to create a filter for machines that do not have a specific file present?

What do you mean “filter”? Is this a question about a server feature? Which feature?

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

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

SELECT 1 from file where path = '/path/to/file'

works well enough for finding the file. 🙂

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>

?

Ah, brilliant!

You can left join against time, instead of using sub selects.

You can left join against time, instead of using sub selects.

Ah, nice TIL -- do you know of an example for this?

It feels like we’re abusing subselects (or joins), but in reality this is what they do. 🙂

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        |
+----------+

doh I’ll go hide in the corner of SQL shame.

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

How would you use “left join against time” to emulate

select not exists(select 1 from file where path = '/tmp/foo');

?

Play with

with _f AS (select * from file where path = '/tmp/nope') select * from time left join _f;