REVIEWED by Sophos
When hunting for indicators of compromise it is not uncommon to find a list of things you should be checking. In the example below I will show how to use variables to select some csv data that is under 5KB in size and then convert the data into a table so we can use it for an automated check.
In this example we will look at a list of SHA256 data published by Sophos on our Git repository related to one of the articles we publish for threat research.
NOTE - If the indicator is deterministic, ie something that if present indicates malware or an adversary, Sophos will already be blocking it with the intercept x protection features.
Going to https://github.com/sophoslabs/IoCs I can find a variety of files with threat indicators, most of these are fairly old and again anything in these lists is already going to be blocked if you have the protection policies enabled. But say you find some long list of SHA256 from some other threat feed location and you want to check to see if your systems have been exposed or not. It may be that the threat Intel is very fresh and you do not know if Sophos already blocks it or it may be that the data is non-deterministic and only indicative of the threat but not sufficient to convict on by itself. We see lots of IOC's that are non-deterministic these are often things like use of powershell, or psexec or behaviors like setting an auto-run registry key. In this example we are just going to be addressing a list of SHA256 data, but you can easily convert this query to take a list of other things, like say system tools, command lines, urls, ip-addresses and anything else as long as each element has a separator we can convert it into a table that we can then use with the rest of the query.
With the query below we can simply copy that info into the variable field and hit run. It will do the work of converting the list to a table we can use in the query and then search the sophos_file_hash_journal for any matches.
Just convert the JOIN to a LEFT JOIN if you want to confirm it is parsing the input correctly.
/************************************************************************\
| Convert the CSV or space seperated list of SHA256 values into a TABLE |
| with each row being its own SHA256 value that we want to look for. |
\************************************************************************/
WITH SHA256_LIST(SHA256, str) AS (
SELECT '', '$$List of SHA256 data$$' || $$delimiter - CHAR(32) space or CHAR(44) comma$$
UNION ALL
SELECT substr(str, 0, instr(str, $$delimiter - CHAR(32) space or CHAR(44) comma$$ )),
substr(str, instr(str, $$delimiter - CHAR(32) space or CHAR(44) comma$$ )+1)
FROM SHA256_LIST
WHERE str!=''
)
SELECT
'AN IOC was detected',
CAST(sl.SHA256 AS TEXT) Target_SHA256,
datetime(sfhj.time,'unixepoch') Date,
sfhj.fileID,
sfhj.fileSize,
sfhj.sha256
FROM SHA256_LIST sl
JOIN sophos_file_hash_journal sfhj ON sfhj.sha256 = sl.SHA256
AND sfhj.time > strftime('%s','now','-1 days')
AND sl.SHA256 != '';
---------------------------------------------------------------------------------------
The RAW table of data I used.
So we have found our list of SHA256's
| 98024a9008c88899991f0a75ae5222a0aa607c070299304bdc3b340e4bb72b0e |
| 864c5468754656efb5d5cf80b1330fc80457cf5bd56b95eca367822b86fbe7ec |
| e2172dff8cd76b892c26d10e236cc2f0fe438f935befd338ea1af5c8555e8462 |
| a26087bb88d654cd702f945e43d7feebd98cfc50531d2cdc0afa2b0437d25eea |
| 47e30119daaf163d28ee9fb3a7cdfd8f193d09e7a6ac559337e1f9d5da4b9b20 |
| 6d7c1e93dcf8094538ae84747075c9a7cca5c45f0433feb1ff0efac94a048297 |
| e3d8de0b07f1587a079e60bf4d9607f57aadc6414d518d66c1699fcf305c82f9 |
| 0fdb07ce063f7daef196b38da25ef0da2c8219b631a745d5d258905fe33dec13 |
| ed28cb4a0861297628275db21a791d972cffbd495e51d0f82289ecaebb6c0b42 |
| 996ea85f12a17e8267dcc32eae9ad20cff44115182e707153006162711fbe3c9 |
| 65855e39e325238153e5cf4aa393834c70bf6b819a7d3a0152d28a5970642db2 |
| 83c5e7c7dcae7b9561f703e0127c24387b9a6289649136916c64613cc6f52484 |
| 9984b03be3a35419e0b626df77963804ce14d7c9e38876d5630cf27700a8723e |
| f4285bf2810261fc400d124c64ba7f68ca5dac4ae217be155499decb113cb420 |
| 65e3cf1c6f8e2415404618f31d9769e4f4970943bfaf2146839e68a78f671f8b |
| 57778777dd6d79eef55b16d01cb17a4ac903ffc2d67e740e3db29a7316f47e84 |
| 13c0fd18c602dd6aa71d78072ad6617a1871cf24b366a12c8c3f2f278f301f5c |
| e9efca0f08ba2dbecfe4a024362a0f5542e410ea30cc9ab66fcd3368072c8fb1 |
| ea946afa87dfbf7c3a8c0ab623733f3ca0f9aec52efdc3e0f065691c6b104e75 |
| 9d6baea99c261754745145c2f1cee857ae7e7ca783a82150b90bbba518597073 |
| 3659576a1a60322081d9286849abe56d0e7eb394816e5547da6c3ccaf87981ee |
| 5b155f40a24d127dee2fbbbf468a4035d2c3a4233af5a8f27c184da8e391077b |
| 8fce957e88d61a502691591362e10635186d24d942a624a08f76a0ecb2752c50 |
| 690c50ba25d962f9a984c5e62418677890612bb947259cf83e042e0c1770c103 |
| e7bcf561e04178764289188bdf6e5d46a67b86fa6facbec42413478e0a2f1725 |
| a23d3caed5e69dc9ef72e69885500fd1dd4f6b69af426d35efcf64cf94a4bb7a |
| c63b6ce9df080da582972192ece021786ebcc5f6537219bd75d2a4ba20459760 |
| dc134589a2494283eb9e81f3ac6b8215bfdfe422a04e62480729b65cef3e4164 |
| 833ec79d84c8bf7501493f1bc40376203e01aee90c8e30748636f4cab36812aa |
| bdd9dbc6d72ecc5ea0a063a1fc99e414a4cff177ec8726da0011134d8589c7d2 |
| 1761d1969358c4a650aa6e20520854cc62cc5672470f0404c37b16c08bcfdaf5 |
| 0c8a167489a9e271a4af5529aeeb0ab28a28ac983a446b6cc185972052362d81 |
| c9f7ba64ba9bdf2473c4c87fc62e25408d11556567944bf100f4ddb0b6c9bd29 |
| 6e9060d56e669658b059f25a05f37f4d266658fece36afdb564536607fd9570b |
| 0b03bf1c7b596a862978999eebfa0703e6de48912c9a57e2fed3ae5cd747bea7 |
| 0b03bf1c7b596a862978999eebfa0703e6de48912c9a57e2fed3ae5cd747bea7 |
| 0676816e9e450dea861a65a0b29f44179e1999f09a24e488ec6756528a5e6b65 |
| 91d07adbf35edb6bb96e7b210f17b9b868ed858802727d6f69c1e5a2d37a9c53 |
| 941af29a59f8d5960af161b9116bbc7d574a9af6f69a47cf0d3daeb31cba6eb1 |
| 75b9aaeb94ca47c9f52a4bd68f0c05c50b939a25bd6501a7bf403fc956df4e5a |
| 42f07bec4edcba04adac1d944f5ec131628565da831fccbfcd42292ea520a620 |
| 3e4aa4d4ad12c656f8093179b1fdd276bbda6e2538f176cc13b74cbdc528ef4a |
| 193697be39290126d24363482627ff49ad7ff76ad12bbac43f53c0a3a614db5d |
| 8343a00a027c09a2bf823a8cb6b71ef7fa4993d9d014ee2646fca912ad260988 |
| 075f86e2db93138f3f3291bc8f362e5f54dfdeeb98b63026697b266fbebddb00 |
| 31f4cb9126bbbc904c4f73e0fd3dc5d6b577aa46045b6f2dc4f036a8788236c2 |
| 10600cbe4b2eab29dc475708e5695ba2f28f8b30e1752d2530f3878bc73fb779 |
| d0c7b512610a1a206dbf4b4d8c352a26a26978abe8b5d0d3255f0b02196482a1 |
| a11be7c4f47ee1901679cf84d060155380f04e9a84548c776d91d6e4a1794e46 |
| 3c5742a1d0fa1464732611c4b187f25090ec2741521318ed51eaec64f2b2bcb7 |
| 6bec396b35f057948bc8b468621955a80b3b14130f3147b02435713ae8067656 |
| 6044a92189ff1d1f874f983e27ef656d78a0c0ae497bbcde4e5d823612fbc0b4 |
| 242713ef2f372f0d39ca8f01bd09c9f99bcfe850e156621c023dd9e0bfb9bd95 |
| 04b5b58db1c91d6cc2db5cd9c38474df21b36f63792ce9e3db6b86af346a5bd3 |
| e644b88e3ab8e153ad0fef9c511c1844f1652becd860ac90c3091e1b1113e4aa |
| 5f8a62d4fb413d73c2de84ec8c2084e647c0ff16b80af89a2d88bace34fd1eee |
| 4fda935614c7b8a59a3f49faf18c3f8b39139c7bc63cd1d63ef5aee727d80ade |
| 837225ea60a613948fb55cae93d5df18a19f388fa7ce06af576ea3441d66e1eb |
| 4cdff0b579f0101c6ee13c549c7665a3901c5af4c05e859e40236bcf43bb0ef4 |
| d3c257a86c875c1435b6e7180ab55e74b005cf0528cd0ba09ce0f5f826ffb8e7 |
| 8294a4be10adfb8192eaaab48aa801a8050b5a500723f3493a44e72bf5a11d8e |
| 11887b412b71c04c06385a007dc37bde559072e5556a9ae36692340a225454de |
| 335160bee7e253c4ffa69e5164c4a36fe5fb4be2c246958dfcc509d8202db5cf |
| 6ee87b3057e5d5db1cda606fa47faf7625161e1a9f9fb1c84db7cba46a2f7412 |
| 38d93c4bf757ba8c75acd0e2cef72cc396b45980918388d491d8a3ec52d7a0ce |
| 1bc21ac96495a44fdaa6b09f20f44390b1b721b1316d175893f6ad7ef6b4e201 |
| b8cb92b1377888883d30c15fa2a206d2bd70b7ee8d64a878df6581985658c408 |
| 7a00ba75b9cc0542947b0bd65f46aa2b6c5bee25eb28023e7db2e09bfe8cd5e7 |
| 39fe5e4f8f62d9891c736e584a108b9a62294f61386899ec32147fc054399cca |
| bc39998bad128866015b7f0a2e160afba3629b3ef83fe84e664e9e117beeef75 |
| 0daf6b838ddb12f1c20f15091d61cbbf5a73112ba1a797a0684fd7d19caf3b0b |
| 0c6137e6ac1bd87c806a05333a8e09b8df52d5a9fbe2b4a606fa9da80c45cc17 |
| aad588dd12577aba808566cab9ce0a8a005fd6d78216c535e618f6a64b59b03f |
| b59dbc74b5a2895b67649da34be1825de2d59ba675ff8c7a4f60b73684bc2027 |
| 0277191b0848d59e76db3ba3a87000baca0056356d85945ad19b14b37bf2c437 |
| dcf1014e4f80bf8ab81a1f27c6d379c300429a0e776452688bdade53fb00e44e |
| efe5c20a3b71e738a8956ecda5af8375ef1e53dbee25ff97cb02a4c82e6d1f63 |
