Hi there! I want to add a new policy in Fleet with the following logic: 1. Check for file presence. If not exists, return 1. 2. If the file exists, check it’s hash, and if the hash equals to the pre-defined value I’d specify in the query, then return 1. Can anybody help me compose the query? All I could come up with is:

select 1
from hash
where path = '/usr/local/update/bin/upd.sh'
  and sha256 in ('6e8ada37393aba10bbbd27074e10e27f4d1edaf2c37ec8eec3e2ee7d1054d55d');

But I’ve no clue on how to return

1

in case of file absence.

Hey there, returning 1 on an absence is quite easy actually! The following query will return 1 when the file is not found

SELECT 1 WHERE NOT EXISTS ( SELECT 1 from file where path = '/usr/local/update/bin/upd.sh');

If you want an all in one:

SELECT 1
Where EXISTS (SELECT 1
FROM hash
WHERE path = '/usr/local/update/bin/upd.sh'
  AND sha256 IN ('6e8ada37393aba10bbbd27074e10e27f4d1edaf2c37ec8eec3e2ee7d1054d55d'))
  OR NOT EXISTS (SELECT 1
FROM hash
WHERE path = '/usr/local/update/bin/upd.sh');

@Keith Swagler @Kathy Satterlee thanks, it works!