Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.

Purpose

The Great Karl_Ackerman put this query together to provide IOC hunting capabilities in the data lake. At this time, we do not have every hash in the lake, but rather information for PE files that were executed. It will not contain a record of every file accessed. Refer to Live Discover queries for this information.

Prerequisites

You must have XDR enabled in your environment. This is a Data Lake query.

This can be used for Windows, Linux, and Mac.

Query Focus

 
WITH IOC_LIST(IOC_Type, Indicator, note) AS (
VALUES
('domain','avsvmcloud.com','malware/callhome'),
('domain','digitalcollege.org','malware/callhome'),
('domain','freescanonline.com','malware/repository'),
('domain','microsoft.com','Test'),
('domain','windowsupdate.com','Test'),
('ip','13.59.205.66','C2 malware/repository'),
('ip','52.168.117.170','Test'),
('sha256','643ec58e82e0272c97c2a59f6020970d881af19c0ad5029db9c958c13b6558c7','Test'),
('sha256','d0d626deb3f9484e649294a8dfa814c5568f846d5aa02d4cdad5d041a29d5600','Troj/SunBurst-A(Installer|CORE-2019.4.5220.20574-SolarWinds-Core-v2019.4.5220-Hotfix5.msp')
)
SELECT
  xdr.meta_hostname AS ep_name,
  xdr.timestamps,
  xdr.name,
  xdr.cmdline,
  xdr.pids, 
  xdr.sophos_pids,
  xdr.username,
  xdr.sha256,
  xdr.domain,
  xdr.clean_urls, 
  xdr.source_ip, 
  xdr.destination_ip, 
  xdr.destination_port, 
  xdr.protocol
FROM 
  IOC_LIST ioc
LEFT JOIN xdr_data xdr ON xdr.domain LIKE ('%'||ioc.Indicator||'%')
WHERE query_name = 'sophos_urls_windows'
AND ioc.IOC_Type = 'domain'
UNION ALL
SELECT
  xdr.meta_hostname AS ep_name,
  xdr.timestamps,
  xdr.name,
  xdr.cmdline,
  xdr.pids, 
  xdr.sophos_pids,
  xdr.username,
  xdr.sha256,
  xdr.domain,
  xdr.clean_urls, 
  xdr.source_ip, 
  xdr.destination_ip, 
  xdr.destination_port, 
  xdr.protocol
FROM
  IOC_LIST ioc
LEFT JOIN xdr_data xdr ON xdr.destination_ip LIKE ('%'||ioc.Indicator||'%')
WHERE
  query_name = 'sophos_ips_windows'
AND ioc.IOC_Type = 'ip'
UNION ALL
SELECT
  xdr.meta_hostname AS ep_name,
  xdr.timestamps,
  xdr.name,
  xdr.cmdline,
  xdr.pids, 
  xdr.sophos_pids,
  xdr.username,
  xdr.sha256,
  xdr.domain,
  xdr.clean_urls, 
  xdr.source_ip, 
  xdr.destination_ip, 
  xdr.destination_port, 
  xdr.protocol
FROM 
  IOC_LIST ioc
LEFT JOIN xdr_data xdr ON sha256 = ioc.Indicator
WHERE query_name IN ( 'running_processes_windows_sophos', 'running_processes_linux_events', 'running_processes_osx_events')
AND ioc.IOC_Type = 'sha256'

Understanding the Code

We are going to focus our time in understanding this code better. 

The query uses a virtual table "IOC_LIST" to give a list of results with "IOC_Type", "Indicator", and "note" as values. You can see it below:

WITH IOC_LIST(IOC_Type, Indicator, note) AS (
VALUES
('domain','avsvmcloud.com','malware/callhome'),
('domain','digitalcollege.org','malware/callhome'),
('domain','freescanonline.com','malware/repository'),
('domain','microsoft.com','Test'),
('domain','windowsupdate.com','Test'),
('ip','13.59.205.66','C2 malware/repository'),
('ip','52.168.117.170','Test'),
('sha256','643ec58e82e0272c97c2a59f6020970d881af19c0ad5029db9c958c13b6558c7','Test'),
('sha256','d0d626deb3f9484e649294a8dfa814c5568f846d5aa02d4cdad5d041a29d5600','Troj/SunBurst-A(Installer|CORE-2019.4.5220.20574-SolarWinds-Core-v2019.4.5220-Hotfix5.msp')
)

You can use other types and information in future queries. You can place "SHA265" instead of MD5. You can replace your own values. You can increase the total values to use. You are ultimately defining the list. The great thing about this IOC Hunts in the Data Lake are performance. Since no production endpoints are involved, no devices are tasked with complex querying.

Table Names

You will see within the code there are several tables abbreviated. Let's examine lines 29 through 32.

IOC_LIST ioc
LEFT JOIN xdr_data xdr ON xdr.domain LIKE ('%'||ioc.Indicator||'%')
WHERE query_name = 'sophos_urls_windows'
AND ioc.IOC_Type = 'domain'

When the table "IOC_List" is called, the snippet can now refer to the table as "ioc." This is particularly useful to shorten calling the specific values of tables last. As example, now you can see it is repeated in line 30, "xdr_data xdr." In lines 14 - 27, the shortened "xdr" to used to simplifying writing out the requests for tableName.columnName.

Now consider in line 31 where we see "query_name = 'sophos_urls_windows' " - this is actually calling an existing canned query that our XDR schema is building in the data lake. You can find the Data Lake Schema here and click on "sophos_urls_windows."

Finally, in line 32, we can match the values out of our earlier created IOC_LIST where "ioc.IOC_Type" "

This order of operations is repeated again in lines 50 - 54 and 72 - 75. This should make everyone very quick and dangerous at hunting their Data Lakes!

---

Happy querying!

-jk

Added Disclaimer
[edited by: GlennSen at 3:48 PM (GMT -7) on 5 Apr 2023]