Printnightmare Hunting Query (Data Lake)

Thanks for doublechecking.

I checked it again on a dll that I created. And it seems like the DLL file changes aren't stored from all locations. Therefore you're right that you cannot detect the DLL IOCs with this query..

I had some weird results with the query above. This one seems to be more reliable when querying live endpoints.

SELECT
   path,
   directory,
   filename,
   inode,
   uid,
   gid,
   mode,
   device,
   size,
   block_size,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(atime,'unixepoch')) atime,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(mtime,'unixepoch')) mtime,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(ctime,'unixepoch')) ctime,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(btime,'unixepoch')) btime,
   hard_links,
   symlink,
   type,
   attributes,
   volume_serial,
   file_id,
   product_version,
   bsd_flags
FROM file
WHERE
    mtime > $$startTime$$
    AND (path LIKE '$$filePath$$%' AND 
    (filename like '%.dll' OR filename like '%.exe'))

I just doublechecked and I didn't get any %.dll files, inside the  C:\Windows\System32\spool\drivers\x64\% folder, however if you use the general wildcard instead of the spool drivers folder I received several results.

@reg1nleifr  you please check the query in your Sophos Central if you get results for DLLs?

Yes the data lake is active in our system. When I change the query to "WHERE(path LIKE '$$SpoolDriver$$.exe')" I get results, but the query WHERE
(path LIKE '$$SpoolDriver$$.dll') is not receiving any results.

I assume this is regarding the data lake query? Remember, you only have 7 days of data in the datalake (but you can schedule the query), also you will need to manually activate sending data to the data lake. Regarding activation of data lake uploads see: https://docs.sophos.com/central/Customer/help/en-us/central/Customer/tasks/DataLakeUploads.html

KR

Hi reg1nleifr,

I tested this query to show all DLLs under the path "C:\Windows\System32\spool\drivers\x64\%".

It looks like no DLL files are indexed in this table and the attack is based on loading malicious DLLs so the query won't help to detect the attack.

SELECT
meta_hostname AS ep_name,
filename,
path,
ctime,
sha1,
sha256,
file_size,
ml_score,
ml_score_data,
pua_score,
global_rep,
global_rep_data,
local_rep,
local_rep_data,
core_file_info
FROM xdr_data
WHERE
(path LIKE '$$SpoolDriver$$.dll')

Similar to this is the non-data lake query that we've been using.

SELECT
   path,
   directory,
   filename,
   inode,
   uid,
   gid,
   mode,
   device,
   size,
   block_size,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(atime,'unixepoch')) atime,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(mtime,'unixepoch')) mtime,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(ctime,'unixepoch')) ctime,
   strftime('%Y-%m-%dT%H:%M:%SZ', datetime(btime,'unixepoch')) btime,
   hard_links,
   symlink,
   type,
   attributes,
   volume_serial,
   file_id,
   product_version,
   bsd_flags
FROM file
WHERE
    mtime > $$startTime$$
    AND ((path LIKE '$$filePath$$.exe' OR path LIKE '$$filePath$$.dll'))