STIX scanning with XDR

27 Dec 2021

For query assistance, please see the following Best Practices guide

The world is full of tools and products to facilitate threat hunting in your environment. In this post I explore how to take threat intelligence from a 3rd party repository and search my systems for the presence or absence of the indicators of compromise. Many threat intelligence feeds provide the information in STIX format. STIX stands for Structured Threat Information Expression. STIX provides is a standardized language to describe cyber security information that can be used to perform detections of malicious activity.

For more information on the standard see the document from MITRE. https://www.mitre.org/sites/default/files/publications/stix.pdf and the OASIS site: https://oasis-open.github.io/cti-documentation/stix/intro.html

The objective of the query is to first get a STIX file from the repo, then to extract the Indicator information and perform a search on the specified device for those indicators. The vast majority of IOCs defined in STIX files fall into just a few patterns and we are almost always looking for a direct match. STIX can be much more sophisticated in how it defines patterns but in my experience it is rare that you will encounter a STIX rule that identifies a pattern that has to repeat over time or involves process state information, also the evaluation condition on STIX rules are almost always match. With that assumption in hand I wrote a basic STIX processor that looks for matching indicators of compromise in the current running state of the system and through the sophos journals on Windows. For a more complete integration with STIX you would want to use the API's and can get started by checking out the Sophos IT Security project for an example using a MISP Server. https://github.com/sophos-cybersecurity/sophos-central-api-connector

Indicators Supported

FILE HASH

These are unique numbers derived from a file. Each of the three types of hashes below are often provided when a malware sample is detected. Why so many different types of HASH values? Well that is another story that goes into cryptography, collisions, and compute time. Just suffice it to say that any file can have a algorithm run against it to generate each of the following types of hash values and when the STIX file contains this type of indicator it is specifying that if a file with the matching hash value is present on the system then malware is likely present. Nothing prevents someone from publishing a STIX file that identifies some legitimate application with a HASH value so if you run the query and get a match, you will want to learn more about what it matched on and you can use the PIVOT feature to get more details on the particular hash value.

MD5
SHA1
SHA256

URL

URL stands for Uniform Resource Locator. A URL is nothing more than the address of a given unique resource on the Web. When this type of indicator is included in a STIX file it is identifying a malicious web location. The queries below will determine if we have historic activity from the device to the specified URL. A match indicates you want to investigate further. You may want to pivot on the SophosPID to get more information about the process that was connecting to the URL.

DOMAIN

When a domain indicator is included in the IOC patterns of a STIX file it is indicating the domain name is associated with the malicious activity.

NETWORK IP_Address

When a network IP address is included it indicates that the specific IP and protocol have been identified with malicious activity.

SAMPLE QUERIES

The example query below runs on WINDOWS devices and will query the STIX file repository at https://osint.digitalside.it/Threat-Intel/stix2/ to pull down one or more current STIX files and extract the Indicators then perform a hunt.

Each of these queries need to run on the device with Live Discover.

STIX IOC Hunt from repo

VARIABLES:

Variable	Type	Value
Hours of historic data to scan	STRING	3
Most Recent N STIX Files	STRING	1

SQL

-- STIX SCANNER FROM ON-LINE STIX indicators from the REPO at https://osint.digitalside.it/Threat-Intel/stix2/
-- VARIABLE $$Most Recent N STIX Files$$                    STRING
-- VARIABLE $$Hours of historic data to scan$$              STRING

WITH RECURSIVE 
-- We need a loop to look for the number of specified files
Counter(x) AS (VALUES (1) UNION ALL SELECT x+1 FROM Counter WHERE x < $$Most Recent N STIX Files$$+3),

-- GET the HTML file of the main index page and extract the filepath information and fetch the STIX Indicators from each file
Index_File(index_of_rules) AS (SELECT result FROM curl WHERE url = 'https://osint.digitalside.it/Threat-Intel/stix2/'),
File_Names AS (SELECT 'https://osint.digitalside.it/Threat-Intel/stix2/'||SPLIT(SPLIT(SPLIT(index_of_rules,CHAR(10),x),'>',1),'<',0) File_Name FROM Index_File, Counter WHERE File_Name LIKE '%.json'),
STIX_Files(URL, J) AS (SELECT url, result J FROM File_Names JOIN curl ON url = File_Names.File_Name ),
Top_Level_Details(Objects) AS (SELECT JSON_EXTRACT(J, '$.objects') Objects FROM STIX_Files),
IOC_Objects AS (SELECT JSON_EXTRACT(Objects,'$['||key||']') IOC_Object FROM Top_level_Details, JSON_EACH(Top_Level_Details.Objects) WHERE JSON_EXTRACT(Objects,'$['||key||'].type') IN ('indicator')),

-- Extract the patterns to search for from the STIX File
Hunt_List AS (
   SELECT 
      CASE 
         WHEN Patterns.Value LIKE '[file:hashes.MD5%' THEN 'MD5' 
         WHEN Patterns.Value LIKE '[file:hashes.SHA1%' THEN 'SHA1'
         WHEN Patterns.Value LIKE '[file:hashes.SHA256%' THEN 'SHA256'
         WHEN Patterns.Value LIKE '[url:value%' THEN 'URL'
         WHEN Patterns.Value LIKE '[domain-name:value%' THEN 'DOMAIN_NAME'
         WHEN Patterns.Value LIKE '[network-traffic:dst_ref.type%' THEN 'NETWORK_TRAFFIC'
         ELSE 'UNSUPPORTED: '||Patterns.Value
      END AS IOC_Type,
      CASE 
         WHEN Patterns.Value LIKE '[file:hashes.MD5%' THEN SUBSTRING(Value,22,LENGTH(VALUE)-23)
         WHEN Patterns.Value LIKE '[file:hashes.SHA1%' THEN SUBSTRING(Value,22,LENGTH(VALUE)-23)
         WHEN Patterns.Value LIKE '[file:hashes.SHA256%' THEN SUBSTRING(Value,24,LENGTH(VALUE)-25)
         WHEN Patterns.Value LIKE '[url:value%' THEN SUBSTRING(Value,15,LENGTH(VALUE)-16)
         WHEN Patterns.Value LIKE '[domain-name:value%' THEN SUBSTRING(Value,23,LENGTH(VALUE)-24)
         WHEN Patterns.Value LIKE '[network-traffic:dst_ref.type%' THEN SUBSTRING(Value,82,LENGTH(VALUE)-83)
         ELSE 'UNSUPPORTED'
      END AS IOC_Value,
      STIX_Files.URL Source_STIX_FILE
   FROM STIX_Files, IOC_Objects, JSON_TREE(IOC_Objects.IOC_Object) AS Patterns
   WHERE Patterns.type = 'text'
      AND Patterns.fullkey LIKE '%pattern'
   
   -- Setup some TEST DATA for each of the types of IOCs we can scan for   
   UNION ALL SELECT 'SHA256', '336f0c1af96390d3d02fe6f984a26cf39d631334efa0b6a53a6be2135185c857', 'TEST DATA' -- MS YourPhone.exe
   UNION ALL SELECT 'SHA1', '6e296b384e81bfe8eee1ad58756ca4d48fd1c514', 'TEST DATA' -- MS YourPhone.exe
   UNION ALL SELECT 'MD5', 'be9532411b00a22160ca13babced530a', 'TEST DATA' -- MS YourPhone.exe
   UNION ALL SELECT 'DOMAIN_NAME', 'graph.microsoft.com', 'TEST DATA' --Legitimate MS Domain
   UNION ALL SELECT 'URL', 'http://office.com/favicon.ico', 'TEST DATA' -- Legitimate MS URL
   UNION ALL SELECT 'NETWORK_TRAFFIC', '172.253.63.188', 'TEST DATA' -- Common URL
   ),
   
-- Go collect the hash information and open socket details for all running processes
Running_Process_Details AS (SELECT name, cmdline, start_time, pid||':'||start_time SophosPID, sha1, md5, sha256 FROM processes JOIN hash on hash.path = processes.path),
Running_Process_Open_Sockets AS (SELECT p.name, p.cmdline, p.start_time, p.pid||':'||p.start_time SophosPID, pos.remote_address FROM process_open_sockets pos JOIN processes p ON p.pid = pos.pid),

-- Perform the scan on running and historic data   
Scan_Results AS (
   -- Running Processes MATCHING SHA256, MD5 or SHA1 hash values 
   SELECT DISTINCT
      CASE rpd.SHA256 NOT NULL  
         WHEN 1 THEN 'DETECTED (Active)'||CHAR(10)||'processes'
         ELSE 'Indicator not active'
      END Result,
      IOC.Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      CASE 
         WHEN ioc.ioc_type = 'SHA256' THEN rpd.sha256
         WHEN ioc.ioc_type = 'SHA1' THEN rpd.sha1
         WHEN ioc.ioc_type = 'MD5' THEN rpd.sha1
         ELSE 'ERROR - Unsupported IOC_Type'
      END Evidence,
      DATETIME(rpd.start_time,'unixepoch') Date_Time,
      rpd.SophosPID,
      rpd.name,
      rpd.cmdline
   FROM Hunt_List ioc
      LEFT JOIN Running_Process_Details rpd ON ioc.IOC_VALUE IN (rpd.sha256,rpd.md5, rpd.sha1)
   WHERE ioc.ioc_type IN ('SHA256', 'MD5', 'SHA1')

   UNION ALL
   -- Running processes_open_sockets on matching NETWORK_TRAFFIC values 
   SELECT DISTINCT
      CASE rpos.remote_Address NOT NULL  
         WHEN 1 THEN 'DETECTED (Active)'||CHAR(10)||'processes_open_sockets'
         ELSE 'Indicator not active'
      END Result,
      IOC.Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      rpos.remote_address Evidence,
      DATETIME(rpos.start_time,'unixepoch') Date_Time,
      rpos.SophosPID,
      rpos.name,
      rpos.cmdline
   FROM Hunt_List ioc
      LEFT JOIN Running_Process_Open_Sockets rpos ON ioc.IOC_VALUE = (rpos.remote_address)
   WHERE ioc.ioc_type = 'NETWORK_TRAFFIC'
   
   UNION ALL
   -- HISTORIC Processes MATCHING SHA256, SHA1 hash values.  (NOTE: MD5 data is not stored in the forensic journals) 
   SELECT DISTINCT
      CASE spj.SophosPID NOT NULL  
         WHEN 1 THEN 'DETECTED (Historic)'||CHAR(10)||'sophos_process_journal'
         ELSE 'Indicator not present(Historic)'
      END Result,
      IOC.Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      CASE 
         WHEN ioc.ioc_type = 'SHA256' THEN spj.sha256
         WHEN ioc.ioc_type = 'SHA1' THEN spj.sha1
         ELSE 'ERROR - Unsupported IOC_Type'
      END Evidence,
      DATETIME(spj.time,'unixepoch') Date_Time,
      spj.sophosPID,
      spj.processName,
      spj.cmdline
   FROM Hunt_List ioc
      LEFT JOIN sophos_process_journal spj ON ioc.IOC_Value IN (spj.sha256, spj.sha1) 
         AND spj.time > strftime('%s','now','-$$Hours of historic data to scan$$ hours')
   WHERE ioc.ioc_type IN('SHA256','SHA1')

   UNION ALL
   -- HISTORIC Processes MATCHING URL, DOMAIN_NAME, NETWORK_TRAFFIC values 
   SELECT DISTINCT
      CASE spa.SophosPID NOT NULL  
         WHEN 1 THEN 'DETECTED (Historic)'||CHAR(10)||'sophos_process_activity'
         ELSE 'Indicator not present(Historic)'
      END Result,
      IOC.Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      spa.subject||' : '||spa.object Evidence,
      DATETIME(spa.time,'unixepoch') Date_Time,
      spa.sophosPID,
      spa.processName,
      spa.cmdline
   FROM Hunt_List ioc
      LEFT JOIN sophos_process_activity spa ON spa.subject IN ('Dns', 'Http', 'Ip', 'Network')
      and spa.object LIKE '%'||ioc.IOC_Value||'%' 
         AND spa.time > strftime('%s','now','-$$Hours of historic data to scan$$ hours')
   WHERE ioc.ioc_type IN('URL','DOMAIN_NAME','NETWORK_TRAFFIC')

   UNION ALL
   -- HISTORIC sophos_web_transaction_journal MATCHING URL values 
   SELECT DISTINCT
      CASE swfj.Sophos_PID NOT NULL  
         WHEN 1 THEN 'DETECTED (Historic)'||CHAR(10)||'sophos_web_transaction_journal'
         ELSE 'Indicator not present(Historic)'
      END Result,
      IOC.Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      swtj.url Evidence,
      DATETIME(swtj.time,'unixepoch') Date_Time,
      swfj.sophos_PID AS sophosPID,
      (SELECT process_name FROM sophos_process_journal AS spj WHERE spj.sophos_pid = swfj.sophos_pid) AS processName,
      (SELECT cmdline FROM sophos_process_journal AS spj WHERE spj.sophos_pid = swfj.sophos_pid) AS cmdline
   FROM Hunt_List ioc
      LEFT JOIN sophos_web_transaction_journal swtj ON swtj.url LIKE ioc.IOC_Value 
         AND swtj.time > strftime('%s','now','-$$Hours of historic data to scan$$ hours')
      LEFT JOIN sophos_web_flow_journal AS swfj ON 
         swfj.time = (CAST(SPLIT(swtj.flow_id, '-', 0) AS INT) - 11644473600)
         AND swfj.flow_id = swtj.flow_id 
   WHERE ioc.ioc_type = 'URL'
   )

-- LIST RESULTS with DETECTIONS Shown First
SELECT 
   Result,
   Source_STIX_FILE,
   IOC_Type,
   IOC_Value,
   Date_Time,
   SophosPID,
   CAST(name AS TEXT) Process_Name,
   CAST(cmdline AS TEXT) cmdline,
   Evidence
FROM Scan_Results 
WHERE Source_STIX_FILE NOT LIKE 'TEST DATA' -- DISABLE this line to confirm test data is detected
ORDER By Result, IOC_type

Sample result

STIX Scan from specified STIX File

VARIABLES

Variable	Type	Value
Hours of historic data to scan	STRING	24
STIX File URL	URL

SQL

/*************************************************************************\
| The admin will supply the URL for an online STIX file                   |
\*************************************************************************/
-- STIX SCANNER FROM ON-LINE STIX RULES GIT REPO https://osint.digitalside.it/Threat-Intel/stix2/
-- VARIABLE $$STIX File URL$$     URL
WITH  
-- Get the relevant STIX File
STIX_File(URL, J) AS (SELECT '$$STIX File URL$$' URL, result J FROM curl WHERE url = '$$STIX File URL$$'),
Top_Level_Details(Objects) AS (SELECT JSON_EXTRACT(J, '$.objects') Objects FROM STIX_File),
IOC_Objects AS (SELECT JSON_EXTRACT(Objects,'$['||key||']') IOC_Object FROM Top_level_Details, JSON_EACH(Top_Level_Details.Objects) WHERE JSON_EXTRACT(Objects,'$['||key||'].type') IN ('indicator')),

-- Extract the patterns to search for from the STIX File
Hunt_List AS (
   SELECT 
      CASE 
         WHEN Patterns.Value LIKE '[file:hashes.MD5%' THEN 'MD5' 
         WHEN Patterns.Value LIKE '[file:hashes.SHA1%' THEN 'SHA1'
         WHEN Patterns.Value LIKE '[file:hashes.SHA256%' THEN 'SHA256'
         WHEN Patterns.Value LIKE '[url:value%' THEN 'URL'
         WHEN Patterns.Value LIKE '[domain-name:value%' THEN 'DOMAIN_NAME'
         WHEN Patterns.Value LIKE '[network-traffic:dst_ref.type%' THEN 'NETWORK_TRAFFIC'
         ELSE 'UNSUPPORTED: '||Patterns.Value
      END AS IOC_Type,
      CASE 
         WHEN Patterns.Value LIKE '[file:hashes.MD5%' THEN SUBSTRING(Value,22,LENGTH(VALUE)-23)
         WHEN Patterns.Value LIKE '[file:hashes.SHA1%' THEN SUBSTRING(Value,22,LENGTH(VALUE)-23)
         WHEN Patterns.Value LIKE '[file:hashes.SHA256%' THEN SUBSTRING(Value,24,LENGTH(VALUE)-25)
         WHEN Patterns.Value LIKE '[url:value%' THEN SUBSTRING(Value,15,LENGTH(VALUE)-16)
         WHEN Patterns.Value LIKE '[domain-name:value%' THEN SUBSTRING(Value,23,LENGTH(VALUE)-24)
         WHEN Patterns.Value LIKE '[network-traffic:dst_ref.type%' THEN SUBSTRING(Value,82,LENGTH(VALUE)-83)
         ELSE 'UNSUPPORTED'
      END AS IOC_Value,
      STIX_File.URL Source_STIX_FILE
   FROM STIX_File, IOC_Objects, JSON_TREE(IOC_Objects.IOC_Object) AS Patterns
   WHERE Patterns.type = 'text'
      AND Patterns.fullkey LIKE '%pattern'
   
   -- Setup some TEST DATA for each of the types of IOCs we can scan for   
   UNION ALL SELECT 'SHA256', '336f0c1af96390d3d02fe6f984a26cf39d631334efa0b6a53a6be2135185c857', 'TEST DATA' -- MS YourPhone.exe
   UNION ALL SELECT 'SHA1', '6e296b384e81bfe8eee1ad58756ca4d48fd1c514', 'TEST DATA' -- MS YourPhone.exe
   UNION ALL SELECT 'MD5', 'be9532411b00a22160ca13babced530a', 'TEST DATA' -- MS YourPhone.exe
   UNION ALL SELECT 'DOMAIN_NAME', 'graph.microsoft.com', 'TEST DATA' --Legitimate MS Domain
   UNION ALL SELECT 'URL', 'http://office.com/favicon.ico', 'TEST DATA' -- Legitimate MS URL
   UNION ALL SELECT 'NETWORK_TRAFFIC', '172.253.63.188', 'TEST DATA' -- Common URL
   ),
   
-- Go collect the hash information and open socket details for all running processes
Running_Process_Details AS (SELECT name, cmdline, start_time, pid||':'||start_time SophosPID, sha1, md5, sha256 FROM processes JOIN hash on hash.path = processes.path),
Running_Process_Open_Sockets AS (SELECT p.name, p.cmdline, p.start_time, p.pid||':'||p.start_time SophosPID, pos.remote_address FROM process_open_sockets pos JOIN processes p ON p.pid = pos.pid),

-- Perform the scan on running and historic data   
Scan_Results AS (
   -- Running Processes MATCHING SHA256, MD5 or SHA1 hash values 
   SELECT DISTINCT
      CAST(CASE rpd.SHA256 NOT NULL  
         WHEN 1 THEN 'DETECTED (Active)'||CHAR(10)||'processes'
         ELSE 'Indicator not active'
      END AS TEXT) Result,
      CAST(IOC.Source_STIX_FILE AS TEXT) Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      CASE 
         WHEN ioc.ioc_type = 'SHA256' THEN rpd.sha256
         WHEN ioc.ioc_type = 'SHA1' THEN rpd.sha1
         WHEN ioc.ioc_type = 'MD5' THEN rpd.sha1
         ELSE 'ERROR - Unsupported IOC_Type'
      END Evidence,
      DATETIME(rpd.start_time,'unixepoch') Date_Time,
      rpd.SophosPID,
      rpd.name,
      rpd.cmdline
   FROM Hunt_List ioc
      LEFT JOIN Running_Process_Details rpd ON ioc.IOC_VALUE IN (rpd.sha256,rpd.md5, rpd.sha1)
   WHERE ioc.ioc_type IN ('SHA256', 'MD5', 'SHA1')

   UNION ALL
   -- Running processes_open_sockets on matching NETWORK_TRAFFIC values 
   SELECT DISTINCT
      CAST(CASE rpos.remote_Address NOT NULL  
         WHEN 1 THEN 'DETECTED (Active)'||CHAR(10)||'processes_open_sockets'
         ELSE 'Indicator not active'
      END AS TEXT)Result,
      CAST(IOC.Source_STIX_FILE AS TEXT) Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      rpos.remote_address Evidence,
      DATETIME(rpos.start_time,'unixepoch') Date_Time,
      rpos.SophosPID,
      rpos.name,
      rpos.cmdline
   FROM Hunt_List ioc
      LEFT JOIN Running_Process_Open_Sockets rpos ON ioc.IOC_VALUE = (rpos.remote_address)
   WHERE ioc.ioc_type = 'NETWORK_TRAFFIC'
   
   UNION ALL
   -- HISTORIC Processes MATCHING SHA256, SHA1 hash values.  (NOTE: MD5 data is not stored in the forensic journals) 
   SELECT DISTINCT
      CAST( CASE spj.SophosPID NOT NULL  
         WHEN 1 THEN 'DETECTED (Historic)'||CHAR(10)||'sophos_process_journal'
         ELSE 'Indicator not present(Historic)'
      END AS TEXT) Result,
      CAST(IOC.Source_STIX_FILE AS TEXT) Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      CASE 
         WHEN ioc.ioc_type = 'SHA256' THEN spj.sha256
         WHEN ioc.ioc_type = 'SHA1' THEN spj.sha1
         ELSE 'ERROR - Unsupported IOC_Type'
      END Evidence,
      DATETIME(spj.time,'unixepoch') Date_Time,
      spj.sophosPID,
      spj.processName,
      spj.cmdline
   FROM Hunt_List ioc
      LEFT JOIN sophos_process_journal spj ON ioc.IOC_Value IN (spj.sha256, spj.sha1) 
         AND spj.time > strftime('%s','now','-$$Hours of historic data to scan$$ hours')
   WHERE ioc.ioc_type IN('SHA256','SHA1')

   UNION ALL
   -- HISTORIC Processes MATCHING URL, DOMAIN_NAME, NETWORK_TRAFFIC values 
   SELECT DISTINCT
      CAST( CASE spa.SophosPID NOT NULL  
         WHEN 1 THEN 'DETECTED (Historic)'||CHAR(10)||'sophos_process_activity'
         ELSE 'Indicator not present(Historic)'
      END AS TEXT) Result,
      CAST(IOC.Source_STIX_FILE AS TEXT) Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      spa.subject||' : '||spa.object Evidence,
      DATETIME(spa.time,'unixepoch') Date_Time,
      spa.sophosPID,
      spa.processName,
      spa.cmdline
   FROM Hunt_List ioc
      LEFT JOIN sophos_process_activity spa ON spa.subject IN ('Dns', 'Http', 'Ip', 'Network')
      and spa.object LIKE '%'||ioc.IOC_Value||'%' 
         AND spa.time > strftime('%s','now','-$$Hours of historic data to scan$$ hours')
   WHERE ioc.ioc_type IN('URL','DOMAIN_NAME','NETWORK_TRAFFIC')

   UNION ALL
   -- HISTORIC sophos_web_transaction_journal MATCHING URL values 
   SELECT DISTINCT
      CAST( CASE swfj.Sophos_PID NOT NULL  
         WHEN 1 THEN 'DETECTED (Historic)'||CHAR(10)||'sophos_web_transaction_journal'
         ELSE 'Indicator not present(Historic)'
      END AS TEXT) Result,
      CAST(IOC.Source_STIX_FILE AS TEXT) Source_STIX_FILE,
      IOC.IOC_type,
      IOC.IOC_Value,
      swtj.url Evidence,
      DATETIME(swtj.time,'unixepoch') Date_Time,
      swfj.sophos_PID AS sophosPID,
      (SELECT process_name FROM sophos_process_journal AS spj WHERE spj.sophos_pid = swfj.sophos_pid) AS processName,
      (SELECT cmdline FROM sophos_process_journal AS spj WHERE spj.sophos_pid = swfj.sophos_pid) AS cmdline
   FROM Hunt_List ioc
      LEFT JOIN sophos_web_transaction_journal swtj ON swtj.url LIKE ioc.IOC_Value 
         AND swtj.time > strftime('%s','now','-$$Hours of historic data to scan$$ hours')
      LEFT JOIN sophos_web_flow_journal AS swfj ON 
         swfj.time = (CAST(SPLIT(swtj.flow_id, '-', 0) AS INT) - 11644473600)
         AND swfj.flow_id = swtj.flow_id 
   WHERE ioc.ioc_type = 'URL'
   )

-- LIST RESULTS with DETECTIONS Shown First
SELECT 
   CAST(Result AS TEXT) Result,
   CAST(Source_STIX_FILE AS TEXT) Source_STIX_FILE,
   IOC_Type,
   IOC_Value,
   Date_Time,
   SophosPID,
   CAST(name AS TEXT) Process_Name,
   CAST(cmdline AS TEXT) cmdline,
   Evidence
FROM Scan_Results 
WHERE Source_STIX_FILE NOT LIKE 'TEST DATA' -- DISABLE this line to confirm test data is detected
ORDER By Result, IOC_type

Sample result

STIX IOC Pattern Extractor from file

VARIABLES

Variable	Type	Value
STIX File URL	URL

SQL

/*************************************************************************\
| The admin will supply the URL for an online STIX file                   |
\*************************************************************************/
-- STIX SCANNER FROM ON-LINE STIX RULES GIT REPO https://osint.digitalside.it/Threat-Intel/stix2/
-- VARIABLE $$STIX File URL$$     URL
WITH  
-- Get the relevant STIX File
STIX_File(URL, J) AS (SELECT '$$STIX File URL$$' URL, result J FROM curl WHERE url = '$$STIX File URL$$'),
Top_Level_Details(Objects) AS (SELECT JSON_EXTRACT(J, '$.objects') Objects FROM STIX_File),
IOC_Objects AS (SELECT JSON_EXTRACT(Objects,'$['||key||']') IOC_Object FROM Top_level_Details, JSON_EACH(Top_Level_Details.Objects) WHERE JSON_EXTRACT(Objects,'$['||key||'].type') IN ('indicator')),

-- Extract the patterns to search for from the STIX File
Hunt_List AS (
   SELECT 
      CASE 
         WHEN Patterns.Value LIKE '[file:hashes.MD5%' THEN 'MD5' 
         WHEN Patterns.Value LIKE '[file:hashes.SHA1%' THEN 'SHA1'
         WHEN Patterns.Value LIKE '[file:hashes.SHA256%' THEN 'SHA256'
         WHEN Patterns.Value LIKE '[url:value%' THEN 'URL'
         WHEN Patterns.Value LIKE '[domain-name:value%' THEN 'DOMAIN_NAME'
         WHEN Patterns.Value LIKE '[network-traffic:dst_ref.type%' THEN 'NETWORK_TRAFFIC'
         ELSE 'UNSUPPORTED: '||Patterns.Value
      END AS IOC_Type,
      CASE 
         WHEN Patterns.Value LIKE '[file:hashes.MD5%' THEN SUBSTRING(Value,22,LENGTH(VALUE)-23)
         WHEN Patterns.Value LIKE '[file:hashes.SHA1%' THEN SUBSTRING(Value,22,LENGTH(VALUE)-23)
         WHEN Patterns.Value LIKE '[file:hashes.SHA256%' THEN SUBSTRING(Value,24,LENGTH(VALUE)-25)
         WHEN Patterns.Value LIKE '[url:value%' THEN SUBSTRING(Value,15,LENGTH(VALUE)-16)
         WHEN Patterns.Value LIKE '[domain-name:value%' THEN SUBSTRING(Value,23,LENGTH(VALUE)-24)
         WHEN Patterns.Value LIKE '[network-traffic:dst_ref.type%' THEN SUBSTRING(Value,82,LENGTH(VALUE)-83)
         ELSE 'UNSUPPORTED'
      END AS IOC_Value,
      STIX_File.URL Soruce_STIX_FILE
   FROM STIX_File, IOC_Objects, JSON_TREE(IOC_Objects.IOC_Object) AS Patterns
   WHERE Patterns.type = 'text'
      AND Patterns.fullkey LIKE '%pattern'
   
   -- TEST DATA   
   UNION ALL SELECT 'SHA256', 'dde81d5926a57001b3d98ff83d1f4b9b08ecfd2eb0f46569bc8927385e65909d', 'TEST SHA256'
   )
SELECT * FROM Hunt_List

Sample result

Using API's for an even better integration with STIX and TAXII feeds

For folks looking to do this in bulk across hundreds of devices and with a more production version of STIX processing please review the Sophos IT Security project that leverages a MISP server for IOC collection.

https://github.com/sophos-cybersecurity/sophos-central-api-connector

Features

All features can be run against single or multiple tenants

Gather tenant system inventory
- Output to stdout, json, Splunk
Gather alerts
- Alert polling
- Output to stdout, json, Splunk
Local Sites
- Clean up Global exclusions
  - Compare exclusions to SophosLabs Intelix
- Generate report
IOC Hunting - Utilising Live Discover or XDR DataLake
- MISP Attribute hunting (eventId, tags)
- RAW JSON input
- Saved search

Karl_Ackerman over 1 year ago in reply to Jason Bristow

The Managed Threat Response team and the XDR product have the same initial set of protection features, detection rules, machine learning models and behavior analytics. As we develop and test additional capabilities for classification of malicious intent these are oten first available to customers with MTR so that we can evaluate the effectiveness of the rule and help assign a Risk score to the detection method prior to us promoting the capability to all of XDR. In MTR Advanced you also get 'Leadless Hunting'. Leadless hunting allocates time for the SOC team to explore the environment for anomaly and potentially un-detected threat activity using queries, models and other tools to detect activity that warrants a deeper look.

With regards to the STIX query above. The vast majority of information shared with STIX/TAXII is simple Hash, IP, Url and Domain information that when observed is a strong or confirming indicator of malicious activity. Sophos Labs monitors dozens of threat intelligence feeds for the protection capabilities of the product and will curate that threat intelligence on your behalf. If you check your policy configuration you will notice a default ON capability called Live Protection. When enabled this feature allows the endpoint to ask for the current threat information for Hash, URL, Domain and IP Data.

The STIX support above is for administrators who want to be more proactive in their threat hunting. It also has an advantage over Live Protect in that it will tell you if an indicator defined in the STIX record is NOT FOUND in the environment.

For those with more interest in directly leveraging the SophosLabs threat database you can use the SoophosLabs Intelix API to perform Cloud Threat Lookups.

Data Sheet for Intelix

https://www.sophos.com/en-us/medialibrary/pdfs/factsheets/sophoslabs-intelix-ds.pdf

API Guide

https://api.labs.sophos.com/doc/index.html.
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel
Jason Bristow over 1 year ago

Does the MTR team use this to hunt for threats on our devices or do we need to ask them to perform a hunt?
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel