Query for MD5 hashes

For Live Discovery:-

If you have a list of hashes to search for, You can use the below query.

-- HASH Hunt (Accepts MD5 and SHA256)
-- VARIABLE $$Days to Search$$             STRING  -- The number of days to search back from NOW
-- VARIABLE $$List of SHA256 OR MD5 data$$ SHA-256 -- Comma separated list of MD5 and SHA256 data
-- Create the list of SHA and MD5 hash data
WITH Indicator_List(IOC, str) AS (
   SELECT '', '$$List of SHA256 OR MD5 data$$'||CHAR(44)
   UNION ALL
   SELECT substr(str, 0, instr(str, CHAR(44))), substr(str, instr(str, CHAR(44))+1) FROM Indicator_List WHERE str!=''
   )
SELECT
   REPLACE(DATETIME(MIN(spj.time),'unixepoch'),' ','T') First_Seen,
   sfj.pathname,
   CASE WHEN hash.md5 >'' THEN hash.md5 ELSE 'File no longer on disk' END MD5,
   CASE WHEN hash.sha256 >'' THEN hash.sha256 ELSE '--' END SHA256,
   spj.process_name,
   spj.sophos_pid
FROM sophos_file_journal sfj
JOIN sophos_process_journal spj ON sfj.SophosPID = spj.SophosPID
JOIN hash ON hash.path = sfj.pathname
JOIN Indicator_LIST ON hash.md5 LIKE IOC OR hash.sha256 LIKE IOC
WHERE sfj.subject IN ('FileBinaryChanges','FileDataChanges','FileOtherChanges')
   AND sfj.event_type IN (0,1,3)
   AND sfj.time > strftime('%s','now','-$$Days to Search$$ days')
   AND hash.sha256 > ''
GROUP BY sfj.pathname, hash.sha256, hash.md5, spj.process_name, spj.sophos_pid
ORDER BY md5, First_Seen

It will take a list of comma-separated MD5 and SHA256 entries then check the FIle creation logs for matches.

any chance we can get md5 und sha1 on datalake as well. I'm often getting IOCs in that form and can't really handle them using scheduled querys atm.

any chance we can get md5 und sha1 on datalake as well. I'm often getting IOCs in that form and can't really handle them using scheduled querys atm.