Title
#fleet
j

Jason

09/06/2022, 6:27 PM
Does osquery support
NOT EXISTS
? I'm trying to run a query for a policy that will return true if a process is not running... I;m running into issues.
Michal Nicpon

Michal Nicpon

09/06/2022, 6:30 PM
It should. Can you paste the query you are trying to use?
j

Jason

09/06/2022, 6:30 PM
SELECT * FROM processes WHERE name NOT EXISTS(SELECT name from processes WHERE name = "Google Chrome");
6:31 PM
I get a syntax error
Error: near "EXISTS": syntax error
- I'm new to this operator so maybe I'm doing this wrong
s

sharvil

09/06/2022, 6:33 PM
I think one can do
1-EXISTS
trick
6:33 PM
something like
SELECT (1-EXISTS(SELECT name from processes WHERE name = "Google Chrome"))
Michal Nicpon

Michal Nicpon

09/06/2022, 6:34 PM
the problematic part is
name NOT EXISTS
, it should be
SELECT * FROM processes WHERE NOT EXISTS (SELECT name from processes WHERE name = "Google Chrome");
6:37 PM
But, I’m not sure exactly what you are trying to do here. Are you trying to find processes that aren’t chrome? You could do
SELECT * from processes WHERE name != "Google Chrome";
j

Jason

09/06/2022, 6:39 PM
I'm trying to detect if chrome is NOT running
Kathy Satterlee

Kathy Satterlee

09/06/2022, 6:41 PM
I think Michal's first example is perfect for that. For simplicity's sake, I'd change the select a little:
SELECT 1 WHERE NOT EXISTS (SELECT 1 from processes WHERE name = "Google Chrome");
That way you get your
true
without selecting data you don't need.
6:44 PM
Here's an example of a policy checking for something not being present: https://fleetdm.com/queries/no-1-password-emergency-kit-stored-on-desktop-or-in-downloads-mac-os
j

Jason

09/06/2022, 6:45 PM
oh yeah, my example query was just showing more info for debugging purposes.
6:45 PM
how is
SELECT 1 FROM processes WHERE NOT EXISTS (SELECT name from processes WHERE name = "Google Chrome") LIMIT 1;
from your perspective ?
Kathy Satterlee

Kathy Satterlee

09/06/2022, 6:45 PM
I cleaned up the example a little more 🙂
j

Jason

09/06/2022, 6:46 PM
perfect - thanks!!
Kathy Satterlee

Kathy Satterlee

09/06/2022, 6:53 PM
Since the whole "Double positive to prove a negative" thing is a little confusing.... IF the policy is "Google Chrome is not running" and you want a pass when it is not or a fail if it is, that's the right query.
j

Jason

09/06/2022, 6:57 PM
yes, just for full clarity I'm trying to create a policy that passes if your version of Chrome is up to date OR google chrome isn't running (because you likely don't use it)
Kathy Satterlee

Kathy Satterlee

09/06/2022, 6:58 PM
Thanks! Can never be too careful with twisty logic.
7:01 PM
j

Jason

09/06/2022, 7:02 PM
I actually did look at that one and cribbed a little from it.
7:03 PM
Our issue is that we deliver all laptops with Google Chrome (and Firefox) - so users have a choice.. but I don't want your policy to fail if your chrome is out of date but you aren't actively using Chrome
Kathy Satterlee

Kathy Satterlee

09/06/2022, 7:12 PM
Totally makes sense. I think you're querying the right thing here, assuming that you've got an integration or webhook set up for your policies so that you aren't just relying on whether they were running Chrome when the most recent policy check happened. Though if people are using Chrome it's usually always running, so that may just be a made up concern 😃
j

Jason

09/06/2022, 7:13 PM
you are following my thoughts exactly. The query isn't perfect but I'm most likely to catch "active" Chrome users with out of date browsers
7:13 PM
do you know of any way to dynamically grab the latest version in these queries?
7:14 PM
I was thinking of maybe using the API for Fleet and the API for endoflife.date and modifying the policy queries that way
7:14 PM
but maybe there is an easier way
Kathy Satterlee

Kathy Satterlee

09/06/2022, 7:16 PM
I feel like I ran across someone before that was using a cron job to periodically query endoflife and update the policy with either the API or
fleetctl
7:16 PM
Let me see what I can dig up....
j

Jason

09/06/2022, 7:16 PM
thx!
7:17 PM
I will likely use Tines.io for this