Under Review

Query for MD5 hashes


I would like suggestions regarding how to put together a query to find MD5 hashes.

There is a built-in query called Processes matching SHA-256 hashes in the last 30 days (below), but I would like to search for MD5 hashes not SHA-256, since that were the only values that was provided to me as IOCs. Thank you.

WITH split(sha) AS (
SELECT value
JSON_EACH('["' || REPLACE(REPLACE('$$sha_list$$', ' ', ''), ',', '","') || '"]')

CAST(split.sha AS TEXT) AS sha256,
process_journal.pathname AS path,
process_journal.sophosPID AS sophos_pid
FROM split
LEFT JOIN sophos_process_journal AS process_journal ON
LOWER(split.sha) = process_journal.sha256
AND process_journal.time > STRFTIME('%s', 'now', '-30 days')
AND process_journal.eventType = 0
split.sha != ''
AND process_journal.sophosPID != ''

  • 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.

  • This is excellent thank you RaviSoni. I tried the query several times but it keeps timing out, any suggestions? I am setting Days to Search to 30 and using a list of 12 MD5 hashes. Thanks.

  • 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!=''
       REPLACE(DATETIME(MIN(spj.time),'unixepoch'),' ','T') First_Seen,
       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,
    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.