Browse Live Response and Discover Queries by Category
Under Review

Query for MD5 hashes

Hello,

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
FROM
JSON_EACH('["' || REPLACE(REPLACE('$$sha_list$$', ' ', ''), ',', '","') || '"]')
)

SELECT DISTINCT
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
WHERE
split.sha != ''
AND process_journal.sophosPID != ''

Abdullah Lababidi
  • in reply to RaviSoni

    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.

  • in reply to RaviSoni

    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!=''
   )
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.

Unfiltered HTML