[QueryCorner][September2022] Data Lake - IOC Hunting

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  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]
Parents
  • Hi GlennSen,

    Thanks for this Query and will make use of this for hunting Iocs. We sometimes get just the file names in the list of IOCs and their locations are not known.

    So what do you use in the query construct as explained above? 

    query_name = 'sophos_urls_windows' "

    Thanks in advance.

    Deepak

  • Hi  ,

    In Line 31, we are referenced our scheduled query collector into the Data Lake. You can see the full collection of data fields we are searching from here: Data Lake Scheduled Queries

    Can you share examples of what IoCs are provided to you? I can suggest a means to better improve the search parameters with better understand of how you're using the intel.

    Thanks,


    jk

  • Thanks for getting back to me regarding my Query.

    We get Ioc's like this.

    IOC Type 
    soc.ps1 Script
    e13afb4c3485e8627989939796b53e894d52b1d7 SHA1
    f2afd46cfef3883fc858ca7b7730d4d6ee56a7aedbdb1b1f7bda7dba054f489e SHA256
    procdump.exe exe
    Sendspace.com website
    94.156.189.36 IP
    217.145.84.64 IP

    I was referring to  file name for example "soc.ps1"  a Script 

    As  i do not know the location of where that file could exist and my query does not work and as its very intensive searching.

    SELECT
    path,
    directory,
    filename,
    device,
    size
    FROM file
    WHERE
    directory LIKE 'C:\%%' and filename LIKE 'soc.ps1'

    Cheers

    Deepak

  • My question is how do i run the above query on Data lake so systems are not impacted.

    Thanks

  • Note : When i use the query above (source:  Data Lake) i get error as shown below but runs with Endpoint selected.

    Invalid sql: WITH IOC_LIST(IOC_Type, Indicator, note) AS

    Please advice.
    I
  • Hi,

    Just making a simple query to list the IOC's does not work in Data Lake

    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
    *
    FROM IOC_LIST;

    Error

    Invalid sql: WITH IOC_LIST(IOC_Type, Indicator, note) AS (\nVALUES\n('domain','avsvmcloud.com','malware/callhome'),\n('domain','digitalcollege.org','malware/callhome'),\n('domain','freescanonline.com','malware/repository'),\n('domain','microsoft.com','Test'),\n('domain','windowsupdate.com','Test'),\n('ip','13.59.205.66','C2 

    Thanks

Reply
  • Hi,

    Just making a simple query to list the IOC's does not work in Data Lake

    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
    *
    FROM IOC_LIST;

    Error

    Invalid sql: WITH IOC_LIST(IOC_Type, Indicator, note) AS (\nVALUES\n('domain','avsvmcloud.com','malware/callhome'),\n('domain','digitalcollege.org','malware/callhome'),\n('domain','freescanonline.com','malware/repository'),\n('domain','microsoft.com','Test'),\n('domain','windowsupdate.com','Test'),\n('ip','13.59.205.66','C2 

    Thanks

Children