Browse Live Discover and Response 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
Parents

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

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

Comment Children
No Data
Unfiltered HTML