Terje Kvernes
11/18/2022, 10:44 PMseph
11/19/2022, 3:38 AMTerje Kvernes
11/20/2022, 4:40 PMsharvil
11/21/2022, 7:44 AMI can find machines with the file easily enough, but the opposite stumps meCan you share this query? I think the inverse of this is possible with some sql magic..
Terje Kvernes
11/21/2022, 12:03 PMSELECT 1 from file where path = '/path/to/file'
works well enough for finding the file. 🙂sharvil
11/21/2022, 12:32 PMosquery> 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>
?case/when
, exists/not exists
and subqueries, you can get as fancy as requiredTerje Kvernes
11/21/2022, 1:00 PMseph
11/21/2022, 1:04 PMsharvil
11/21/2022, 1:45 PMYou can left join against time, instead of using sub selects.Ah, nice TIL -- do you know of an example for this?
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)
Terje Kvernes
11/21/2022, 3:08 PMsharvil
11/21/2022, 3:20 PMcount(*)
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 |
+----------+
Terje Kvernes
11/21/2022, 3:21 PMseph
11/21/2022, 3:22 PMabusing 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
Terje Kvernes
11/21/2022, 3:24 PMselect not exists(select 1 from file where path = '/tmp/foo');
?seph
11/21/2022, 3:28 PMwith _f AS (select * from file where path = '/tmp/nope') select * from time left join _f;