Approved

Ryuk and Trick Bot IOC Detection

With the current attacks on hospital infrastructure ongoing and the advisories being sent prior to the election I wanted to fix one of the queries I posted earlier.
A number of partners/customers may be bringing up questions on the CISA advisory.  With EDR they can check for IOCs in their environment, ALSO NOTE these are things we will block on if they have protections enabled, but often folks just want to be sure, so they can run a query that will take the SOPHOS Labs produced IOC list and hunt for it across windows devices.
The operators of Ryuk ransomware are at it again. After a long period of quiet, we identified a new spam campaign linked to the Ryuk actors—part of a new wave of attacks.
news.sophos.com

The query takes a CSV file published by SOPHOS LABS and parses out the IOC's then performs a hunt. Note you have to setup the variables and use the RAW csv file.
To test it you can drop an executable in the C:\PerfLogs directory and run it.  Putting executables in that directory will be a deteced IOC.
TEST IT
If you drop any executable in the C:\PerfLogs\ directory and run the query it should show it as a MATCH for one of the IOC's  If that is not happening you will want to ensure that the CURL command to the git repository is working.
IF NOT WORKING
Determine if the CURL is returning a result or not
That should return a single row with a 200 for response and a large data blob in the 'results' column.  If it is not then the Device may still be able to reach the internet but something is preventing osquery service from doing the same.  It may be a problem at the ISP, GIT or your own firewall rules, potentially identifying the content of the CSV as MAL or some other rule.

VARIABLES:

VARIABLE 

TYPE VALUE
Number of Hours of activity to search STRING 24
RAW IOC List location from a URL STRING https://raw.githubusercontent.com/sophoslabs/IoCs/master/Ransomware-Ryuk.csv
Start Search From
DATE 10/29/2020 12:00:00
SQL 
/*****************************************************************************************\
| First we have to get the file from GIT then cut it into lines                           |
| We then convert each line into its component parts as a table                           |
| Each line has an identified IOC Type, Indicator and Notes so we will use some string    |
| functions to seperate each element into our IOC_List                                    |
\*****************************************************************************************/
WITH IOC_LIST (IOC_Type, Indicator, note) AS (
 WITH IOC_FILE(Line, str) AS (
  SELECT 'ip,127.0.0.1,TEST DATA', (SELECT result from curl where url = '$$RAW IOC List location from a URL$$') ||char(10)
  UNION ALL
  SELECT substr(str, 0, instr(str, char(10) )), substr(str, instr(str, char(10) )+1) FROM IOC_FILE WHERE str!=''
 )
SELECT
 replace(Line, ltrim(Line, replace(Line, ',', '')), '') 'Indicator Type', /* IOC type */
 replace(replace(substr(Line, instr(Line, ',')+1), ltrim(substr(Line, instr(Line, ',')+1), replace(substr(Line, instr(Line, ',')+1), ',', '')), ''),'*','%')  Indicator,       /* Actual IOC Data */ /* Convert wildcard * to % */
 replace(Line, rtrim(Line, replace(Line, ',', '')), '') 'Note' /* Note */
FROM IOC_FILE WHERE Line != '' AND Line != 'Indicator type,Data,Note' AND Line NOT LIKE 'Description%' AND Line NOT LIKE '%TEST DATA%' AND Line NOT LIKE '%indicator_type%'
)

--SELECT IOC_Type, CAST(LOWER('%'||Indicator||'%') AS TEXT), note FROM IOC_LIST -- Uncomment this line out to check if we are importing the IOC data correctly 

/************************************************************************\
| OK that should give us a table of IOCs to go hunt for                  |
| Enable the line below to just dump the table to confirm all is working |
| SELECT * from IOC_LIST;                                                |    
\************************************************************************/

/**********************************************************************\
| The admin may want to search a large amount of data in the tables so |
| split time into 20 min chunks given the number hours specified       |
\**********************************************************************/

, for(x) AS (
   VALUES ( (CAST ($$Start Search From$$ AS INT) ) )
   UNION ALL
   SELECT x+1200 FROM for WHERE x < (CAST ($$Start Search From$$ AS INT) + CAST( ($$Number of Hours of activity to search$$ * 3600) AS INT))
)

/****************************************************************************\
| Check for matching domain or URL info seen in the specified lookback period|
\****************************************************************************/

SELECT
 CAST( datetime(spa.time,'unixepoch') AS TEXT) DATE_TIME,
 'MATCH FOUND' Detection,
 ioc.IOC_Type,
 ioc.Indicator,
 ioc.note,
 spa.subject,
 spa.SophosPID,
 CAST ( (select replace(spa.pathname, rtrim(spa.pathname, replace(spa.pathname, '\', '')), '')) AS TEXT) process_name,
 spa.action,
 spa.object,
 spa.url
FROM for
 LEFT JOIN IOC_LIST ioc ON LOWER(ioc.IOC_Type) IN('domain', 'url')
 LEFT JOIN sophos_process_activity spa ON spa.subject IN ('Http','Url','Network') AND spa.time >= for.x and spa.time <= for.x+1200  
WHERE spa.url LIKE ioc.indicator

UNION ALL

/****************************************************************************\
| Check for matching IP info seen in the specified lookback period           |
\****************************************************************************/

SELECT
 CAST( datetime(spa.time,'unixepoch') AS TEXT) DATE_TIME,
 'MATCH FOUND' Detection,
 ioc.IOC_Type,
 ioc.Indicator,
 ioc.note,
 spa.subject,
 spa.SophosPID,
 CAST ( (select replace(spa.pathname, rtrim(spa.pathname, replace(spa.pathname, '\', '')), '')) AS TEXT) process_name,
 spa.action,
 spa.object,
 spa.url
FROM for
 LEFT JOIN IOC_LIST ioc ON LOWER(ioc.IOC_Type) IN('ip')
 LEFT JOIN sophos_process_activity spa ON spa.subject IN ('Http','Ip','Network') AND spa.time >= for.x and spa.time <= for.x+1200  
WHERE spa.source LIKE ioc.Indicator OR spa.destination LIKE ioc.Indicator

UNION ALL

/***********************************************************************************\
| Check for matching port info seen in the specified lookback period|
\***********************************************************************************/

SELECT
 CAST( datetime(spa.time,'unixepoch') AS TEXT) DATE_TIME,
 'MATCH FOUND' Detection,
 ioc.IOC_Type,
 ioc.Indicator,
 ioc.note,
 spa.subject,
 spa.SophosPID,
 CAST ( (select replace(spa.pathname, rtrim(spa.pathname, replace(spa.pathname, '\', '')), '')) AS TEXT) process_name,
 spa.action,
 spa.object,
 spa.destinationPort
FROM for
 LEFT JOIN IOC_LIST ioc ON LOWER(ioc.IOC_Type) IN('port')
 LEFT JOIN sophos_process_activity spa ON spa.subject IN ('Http','Ip','Network') AND spa.time >= for.x and spa.time <= for.x+1200  
WHERE spa.destinationPort LIKE ioc.Indicator

UNION ALL

/***********************************************************************************\
| Check for matching sha256 info seen in the specified lookback period|
\***********************************************************************************/

SELECT
 CAST( datetime(spj.time,'unixepoch') AS TEXT) DATE_TIME,
 'MATCH FOUND' Detection,
 ioc.IOC_Type,
 ioc.Indicator,
 ioc.note,
 'sophos_process_journal',
 spj.SophosPID,
 CAST ( (select replace(spj.pathname, rtrim(spj.pathname, replace(spj.pathname, '\', '')), '')) AS TEXT) process_name,
 spj.eventtype,
 'process execution',
 spj.sha256
FROM for
 LEFT JOIN IOC_LIST ioc ON LOWER(ioc.IOC_Type) IN('sha256')
 LEFT JOIN sophos_process_journal spj ON spj.time >= for.x and spj.time <= for.x+1200  

WHERE LOWER(spj.sha256) LIKE LOWER(ioc.Indicator)

UNION ALL

/***********************************************************************************\
| Check for matching process activity info seen in the specified lookback period|
\***********************************************************************************/

SELECT
 CAST( datetime(spa.time,'unixepoch') AS TEXT) DATE_TIME,
 'MATCH FOUND' Detection,
 ioc.IOC_Type,
 ioc.Indicator,
 ioc.note,
 spa.subject,
 spa.SophosPID,
 CAST ( (select replace(spa.pathname, rtrim(spa.pathname, replace(spa.pathname, '\', '')), '')) AS TEXT) process_name,
 spa.action,
 spa.object,
 spa.pathname
FROM for
 LEFT JOIN IOC_LIST ioc ON LOWER(ioc.IOC_Type) IN('pathname', 'file_path', 'file_path_name', 'filename')
 LEFT JOIN sophos_process_activity spa ON spa.subject IN ('Image','Process') AND spa.time >= for.x and spa.time <= for.x+1200  
WHERE LOWER(spa.pathname) LIKE LOWER(ioc.Indicator) OR LOWER(spa.object) LIKE LOWER(ioc.Indicator)

UNION ALL

/***********************************************************************************\
| Check for matching file/directory on the CURRENT SATE of the device               |
\***********************************************************************************/

SELECT DISTINCT
 CAST( datetime(file.btime,'unixepoch') AS TEXT) DATE_TIME,
 'MATCH FOUND' Detection,
 ioc.IOC_Type,
 ioc.Indicator,
 ioc.note,
 'File_system',
 '' ,
 file.filename,
 'on disk',
 file.path,
 ''
FROM IOC_LIST ioc 
 LEFT JOIN file ON LOWER(ioc.IOC_Type) IN('pathname', 'file_path', 'file_path_name', 'filename') AND file.path LIKE ioc.indicator
WHERE DATE_TIME <> ''
  • Thanks so much for this awesome query, Karl.  The reason I was having trouble getting results was due to TLS inspection on my XG Firewall.  Once I excluded the site from TLS decryption, the query succeeded and I was able to get results.

    There are 2 ways to add the necessary exclusion:

    1. Use the SSL/TLS connections widget on the XG Firewall Control Center
      1. Click the widget
      2. Click the "Fix errors" link in the upper right
      3. Search for "raw.githubusercontent.com" and select it
      4. Click the "Exclude from decryption" button in the bottom right
      5. Click "Apply" to confirm
    2. Create the entry manually
      1. From the XG Firewall, click PROTECT -> Web
      2. Select the URL groups tab
      3. Click the pencil icon to the right of the  "Local TLS exclusion list" group to edit it
      4. Enter "raw.githubusercontent.com" into the "Search / Add" field and click the plus sign
      5. Click Save