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

Let's revisit a query written by Kyle Seike - post can be found here

In this query, Kyle writes a great query to hunt down IOCs related to the CISA Alert AA21-1481.

As an XDR operator, this query illustrates how anyone can now repurpose this for any IOC hunt moving forward.

Prerequisites

You must have XDR enabled in your environment. This is entirely a Live Discover query.

This is intended for Windows only.

Query Focus

WITH IOC_HUNT_BY_TYPE AS (

WITH IOC_LIST (ThreatActor, IOC_TYPE, Indicator) AS (
VALUES
('Cobalt','MD5','cbc1dc536cd6f4fb9648e229e5d23361'),
('Cobalt','MD5','ebe2f8df39b4a94fb408580a728d351f'),
('Cobalt','MD5','29e2ef8ef5c6ff95e98bff095e63dc05'),
('Cobalt','MD5','dcfd60883c73c3d92fceb6ac910d5b80'),
('Cobalt','MD5','b40b30329489d342b2aa5ef8309ad388'),
('Cobalt','MD5','7edf943ed251fa480c5ca5abb2446c75'),
('Cobalt','MD5','1c3b8ae594cb4ce24c2680b47cebf808'),
('Cobalt','IP','83.171.237.173'),
('Cobalt','IP','192.99.221.77'),
('Cobalt','IP','208.75.122.11'),
('Cobalt','Domain','usaid.theyardservice.com'),
('Cobalt','Domain','dataplane.theyardservice.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','cdn.theyardservice.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','static.theyardservice.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','static.theyardservice.com'),
('Cobalt','Domain','worldhomeoutlet.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','worldhomeoutlet.com'),
('Cobalt','Domain','cdn.theyardservice.com'),
('Cobalt','Filename','ICA-declass.iso'),
('Cobalt','Filename','Reports.lnk'),
('Cobalt','Filename','ICA-declass.pdf'),
('Cobalt','Filename','DOCUMENT.DLL')

)
/* Search the MD5 data for matches */
SELECT DISTINCT 
CASE h.path NOT NULL 
WHEN 1 THEN 'Detected IOC >' || h.path 
ELSE 'IOC NOT FOUND' 
END Result,
ThreatActor, IOC_TYPE, Indicator
FROM IOC_LIST ioc JOIN processes p ON 1
LEFT JOIN hash h on h.path = p.path AND h.md5 = ioc.Indicator
WHERE ioc.ioc_type = 'MD5'

UNION ALL 

/* Search the Process and Filename matches */
SELECT DISTINCT
CASE p.path NOT NULL
WHEN 1 THEN 'DETECTED IOC >' || p.path
ELSE "IOC NOT FOUND"
END Result,
ThreatActor, IOC_TYPE, Indicator
FROM IOC_LIST ioc LEFT JOIN processes p on p.path LIKE "'%'" || ioc.indicator || "'%'"
WHERE ioc.ioc_type = 'Filename'

UNION ALL

/* Search the IP Matches */
SELECT DISTINCT
CASE sij.SophosPID NOT NULL
WHEN 1 THEN 'DETECTED IOC >' || sij.SophosPID
ELSE 'IOC NOT FOUND'
END Reuslt,
ThreatActor, IOC_TYPE, Indicator
FROM IOC_LIST ioc LEFT JOIN sophos_ip_journal sij ON sij.destination = ioc.indicator AND sij.time > strftime('%s','now','-7 days')
WHERE ioc.ioc_type = 'IP'

UNION ALL

/* Search the Domain Matches */
SELECT DISTINCT
CASE sij.SophosPID NOT NULL
WHEN 1 THEN 'DETECTED IOC >' || sij.SophosPID
ELSE 'IOC NOT FOUND'
END Reuslt,
ThreatActor, IOC_TYPE, Indicator
FROM IOC_LIST ioc LEFT JOIN sophos_ip_journal sij ON sij.destination = ioc.indicator AND sij.time > strftime('%s','now','-7 days')
WHERE ioc.ioc_type = 'Domain'

)
SELECT * FROM IOC_HUNT_BY_TYPE;

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 Threat Actor, IOC Type, and Indicator value. You can see it below:

WITH IOC_LIST (ThreatActor, IOC_TYPE, Indicator) AS (
VALUES
('Cobalt','MD5','cbc1dc536cd6f4fb9648e229e5d23361'),
('Cobalt','MD5','ebe2f8df39b4a94fb408580a728d351f'),
('Cobalt','MD5','29e2ef8ef5c6ff95e98bff095e63dc05'),
('Cobalt','MD5','dcfd60883c73c3d92fceb6ac910d5b80'),
('Cobalt','MD5','b40b30329489d342b2aa5ef8309ad388'),
('Cobalt','MD5','7edf943ed251fa480c5ca5abb2446c75'),
('Cobalt','MD5','1c3b8ae594cb4ce24c2680b47cebf808'),
('Cobalt','IP','83.171.237.173'),
('Cobalt','IP','192.99.221.77'),
('Cobalt','IP','208.75.122.11'),
('Cobalt','Domain','usaid.theyardservice.com'),
('Cobalt','Domain','dataplane.theyardservice.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','cdn.theyardservice.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','static.theyardservice.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','static.theyardservice.com'),
('Cobalt','Domain','worldhomeoutlet.com/jquery-3.3.1.min.woff2'),
('Cobalt','Domain','worldhomeoutlet.com'),
('Cobalt','Domain','cdn.theyardservice.com'),
('Cobalt','Filename','ICA-declass.iso'),
('Cobalt','Filename','Reports.lnk'),
('Cobalt','Filename','ICA-declass.pdf'),
('Cobalt','Filename','DOCUMENT.DLL')

)

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 other area you may want to increase or decrease is the amount of time you're searching. The longer you look back, the longer it will take to return results. And the same to be said for the opposite.

SELECT DISTINCT
CASE sij.SophosPID NOT NULL
WHEN 1 THEN 'DETECTED IOC >' || sij.SophosPID
ELSE 'IOC NOT FOUND'
END Reuslt,
ThreatActor, IOC_TYPE, Indicator
FROM IOC_LIST ioc LEFT JOIN sophos_ip_journal sij ON sij.destination = ioc.indicator AND sij.time > strftime('%s','now','-7 days')
WHERE ioc.ioc_type = 'IP'

In line 7 from above, you'll see strftime('%s','now','-7 days')

By changing "7" to another number, you can modify how much you are searching. Keep in mind that this is used twice in the original query syntax (line 61 and 73) if you decide to tweak it at all.

---

Happy querying!

-jk

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