Processing Threat Intel on blacklisted IP addresses

 REVIEWED by Sophos 

With Live Discover we have the ability to pull information from an external location.   If that location is a list of IOC's that is periodically updated you can write a query that will on-demand go collect the current list of IOC's and perform a hunt.

For this query I pull a list of suspect IP-Addresses from the https://feodotracker.abuse.ch/ black list of IP-addresses that should be on your black list.  but if you have another source of blacklisted IP's that you want to process you can simply replace the variable that identifies the source.

We use three variables

• RAW IOC List location from a URL >          https://feodotracker.abuse.ch/downloads/ipblocklist_recommended.txt 
• Number of Hours of activity to search >    24
• Start Search From >                                06/15/2020 11:51:17 

A few notes on Threat Intel. 

• Sophos Labs will likely have already updated anything as simple as a convictable IOC from 3rd party threat intel, and the endpoints will be blocking if they encounter any of them.
• Sophos subscribes to dozens of threat intel sources and publishes our own threat intel to the industry.

Retrospective hunting for trail head identification:

• This is more interesting...  If an IOC was just published it is very likely that hours or days earlier the adversary was active and that what is now worthy of taking a block action was being allowed.  Adversaries my have been active in the environment prior to sophos or other vendors knowing that they should block the activity. 
• Given that CIXA+EDR has forensic journals going back about 90 days we now have the ability to search through those logs to see if we recorded activity that matches a new indicator of compromise
• Searching recorded data for the presence of NEW IOCs provides an excellent hunting ground.  The query will identify things that likely should have been blocked but were not, so as an admin you will want to check out what happened.

WITH IOC_LIST (IOC_Source, IOC_Type, Indicator) AS (
   WITH IOC_FILE(Line, str) AS (
      SELECT '', (SELECT result from curl where url = '$$RAW IOC List location from a URL$$') ||char(10)
      UNION ALL
      SELECT substr(str, 0, instr(str, char(10) )), substr(str, instr(str, char(10) )+1) FROM IOC_FILE WHERE str!=''
   )
SELECT
   '$$RAW IOC List location from a URL$$', /* IOC File Name */
   'IP_Address', /* IOC type */
   Line /* Actual IOC Data */
FROM IOC_FILE WHERE Line != ''
)

SELECT
   ioc.indicator,
   CASE ( source LIKE CAST(ioc.indicator AS TEXT) OR destination LIKE CAST(ioc.indicator AS TEXT) )
      WHEN NOT '' THEN 'IOC Detected'
      ELSE 'Not Present'
   END IOC_Detection_Status,
   datetime(spa.time,'unixepoch') Date_Time,
   spa.sophosPid,
   replace(spp.pathname, rtrim(spp.pathname, replace(spp.pathname, '\', '')), '') process_name,
   spa.subject,
   spa.action,
   spa.object,
   spa.url,
   spa.source,
   spa.sourcePort,
   spa.destination,
   spa.destinationPort,
   spa.originalDestination,
   spa.originalDestinationPort,
   spa.protocol,
   spp.pathname,
   spp.mlscore,
   spp.puascore,
   spp.globalrep,
   spp.localrep,
   spp.sha256
FROM IOC_LIST ioc
   LEFT JOIN Sophos_process_activity spa ON spa.subject IN ('Http','Network','Ip')
   /* COMMENT OUT the line below to ENABLE VERBOSE MODE */
      AND ( spa.source LIKE CAST(ioc.indicator AS TEXT) OR spa.destination LIKE CAST(ioc.indicator AS TEXT) )
   LEFT JOIN sophos_process_properties spp ON spp.sophosPID = spa.SophosPID
WHERE
   instr(ioc.Indicator, '#') = 0 AND
   spa.time > $$Start Search From$$ AND
   spa.time < $$Start Search From$$ + $$Number of Hours of activity to search$$ * 3600 AND
   spa.sourceport NOT IN (137,138,139) AND spa.destinationPort NOT IN (137,138,139)
ORDER by spa.time;