Utilizing a post from Karl_Ackerman and the precanned queries in Sophos Central, here is a query that can pull down a remote csv table, and join it to the sophos_ip_journal.
It takes one variable:
URL -- String -- $$URL$$
In this use case, I took a list of Emotet IOC ips distributed by Talos Intelligence, and placed them in a single column of IP addresses on a Github repo.
When ran, the query grabbed those values, joined them to the sophos_ip_journal, and (in theory) allowed me to see which machines could have been talking to those IPs.
WITH
Remote_CSV_file(Line, str) AS (
SELECT '', (SELECT result from curl where url = '$$URL$$') ||char(10)
UNION ALL
SELECT substr(str, 0, instr(str, char(10) )), substr(str, instr(str, char(10) )+1) FROM Remote_CSV_file WHERE str!=''
),
-- Create Table for Remote_CSV_file
Remote_Loaded_Table (ioc_ip) AS (
SELECT SPLIT(Line,',',0) Col1
FROM Remote_CSV_file WHERE Line != ''
)
SELECT remote_loaded_table.ioc_ip, sophos_ip_journal.destination, sophos_ip_journal.source
FROM remote_loaded_table
Left JOIN sophos_ip_journal on remote_loaded_table.ioc_ip = sophos_ip_journal.destination
