https://github.com/osquery/osquery logo
#sql
Title
# sql
t

toka

06/26/2018, 3:07 PM
Hey, I'd like to scan my fleet to see if
some_filename
is missing on any host, for whatever reason.
file
table gives me a nice result for machines that have my
some_filename
in place, but how to invert such query?
so in other words im interested in hostnames that are not listed in the results when I do
SELECT path FROM file WHERE path="some_file"
a

allister

06/26/2018, 4:09 PM
maybe something like
osquery> select type from file where path = '/etc/hosts' and type != 'regular';
?
t

toka

06/26/2018, 4:31 PM
I think when file is not existent, each host is excluded from results anyway
z

zwass

06/26/2018, 4:46 PM
SELECT 1 AS missing WHERE (SELECT path FROM file WHERE path="some_file") IS NULL;
2
t

toka

06/26/2018, 5:00 PM
I was messing with making JOINS to
system_info
, @zwass this is excellent, thanks!
z

zwass

06/26/2018, 5:01 PM
You're welcome! Always love a good #sql problem 🙂
2 Views