How can I search for a HASH list with live discover?

Question

Hello. 
 I&acute;m trying to create a query that allows me to check if a HASH from a list (with a comma separated) is located on some device. 
 The problem I have when consulting the hash table is that it does not show me any value if I do not define a directory first, but I need to search for those hashes in any partition of the device and both in linux and windows. 
 For example: 
 define Variable $$sha_list$$ = hash1,hash2,hash3 ( these hashes belong to files located in c:\windows	emp) 
 WITH split(sha) AS ( SELECT value FROM JSON_EACH('["' || REPLACE(REPLACE('$$sha_list$$', ' ', ''), ',', '","') || '"]') ) 
 SELECT * from hash INNER JOIN split ON hash.sha256 =split.sha WHERE hash.directory like 'C:\%%' 
 this query fails, but if I change the value of hash.directory for the next, the query works fine 
 WHERE hash.directory like 'C:\W%\T%' 
 
 How can I search a list of hash on a devices?

Sophos User930 · Accepted Answer

The hash table is more like a function, it doesn't hold a database of checksums of all files and I don't think you can tell it to recurse from a directory. 
 osquery | Schema 
 If you have a hash variable called sha_1_hash_to_find which has the value aa105b0320b14ef0e7b89cbeb738b9f3feae43b7 
 If it was in C:\windows	emp\ it will find the file if the hash exists with: 
 select path, directory, sha1 from hash where directory = "C:\windows	emp\" and sha1 = '$$sha_1_hash_to_find$$' 
 Without knowing the directory, I don't believe you can check all files. Maybe if you have a list of common directories to search it would work, e.g. 
 select path, directory, sha1 from hash where directory in( "C:\windows	emp\", "C:	emp\") and sha1 = '$$sha_1_hash_to_find$$' 
 But you still need to hint where to look. Maybe some recursive query could do it but it would be expensive I would think to check all files on disk

How can I search for a HASH list with live discover?

Top Replies