Query for missing default shares

This query for create a virtual table from a URL file with defined CSVs. For this, we're going to look for missing default shares in Windows.

As Microsoft indicates here, it can lead to various problems in the environment and in recent reports, it is a sign of compromise from the Conti ransomware group using the log4j vulnerability.

Define $$URL$$ as a URL TYPE and the address is: https://raw.githubusercontent.com/cyber-yinzer/sophos-xdr-queries/main/url-sources/defaultShare.csv

-- Load URL for Default Share
WITH
defaultShare(Line, str) AS (
SELECT '', (SELECT result AS name 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 defaultShare WHERE str!=''),
-- Create Table for DefaultShareTable
defaultShareTable AS (
SELECT SPLIT(Line,',',0) Description, SPLIT(Line,',',1) Name, SPLIT(Line,',',2) Path
FROM defaultShare WHERE Line != ''
)

-- Compare Column Values
SELECT 
CASE WHEN dst.name = sr.name THEN 'FOUND' ELSE 'NOT FOUND' END "Default Windows Shares", 
dst.Name,
dst.Description,
dst.Path
FROM defaultShareTable dst
LEFT OUTER JOIN shared_resources sr ON 
dst.name LIKE sr.name
ORDER BY "Default Windows Shares" DESC