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

Parents

Irvin Rosario1 11 months ago

Hi,

Is it possible to do it but reversed? I mean export this information from the Sophos Threat Intelligence console in STIX format?

We have certain clients that must comply with local regulations that require them to send the events detected by the SIEM in STIX format 3 times a day.

I ask since XDR and MDR capture third party information with their telemetry add-ins. So we have the necessary indicators. But how could we export it?
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel

Comment

Irvin Rosario1 11 months ago

Hi,

Is it possible to do it but reversed? I mean export this information from the Sophos Threat Intelligence console in STIX format?

We have certain clients that must comply with local regulations that require them to send the events detected by the SIEM in STIX format 3 times a day.

I ask since XDR and MDR capture third party information with their telemetry add-ins. So we have the necessary indicators. But how could we export it?
- Cancel
- Vote Up 0 Vote Down
- Sign in to reply
- More
- Cancel

Children

No Data