Live Discover Query - Ransom note discovery?

  REVIEWED by Sophos 

I'm not sure if this would work, or even how much merit there is in trying but here goes anyway.

Ransomware, to the best of my limited knowledge, tends to add some sort of instruction file to an obvious location such as the user's desktop, presumably as the last step in the process for a given computer.  After a quick search online for these instruction files, it seems that around 90% of these notes have the extension '.txt', '.htm', or '.html'. 

Does this only apply now to the home user scenario where the campaign has no adversary managing the larger scale infection directly?

That said, we could cover the above, using just the 'core' 'file' table with:
directory like 'C:\Users\%\Desktop\' 


  • I don't think we even need a % on the end of the path as creating notes in sub-directories seems pointless.
  • I also appreciate that users's Desktop locations can sometimes be redirected to a UNC share, so something to consider.

The 3 extensions can be covered with these 2:
(filename like '%.txt') or (filename like '%.htm%')

As this exercise is probably most about minimizing false-positives, we can probably make the assumption that we most care about files created in the last 2 weeks as a starting point:
(ctime > STRFTIME('%s','NOW','-14 days')) 

Then there is the size attribute, for a meaningful message I would expect a ransom note to be larger than 500 bytes but less than 3000 bytes. Maybe someone has some actual data, given a repository of notes?
(size >500 and size < 3000)

From the example 80 filenames I found, then we have the tricky part of limiting the results by the most common wording of the filename. This has to be balanced against files that might legitimately exist on a user's desktop. Not that much should be on a user desktop but then we all know how that gets out of control pretty quickly!

I found "decrypt", "recover", "restore" to be common strings and not too commonplace, especially given all the constraints above.  Underscores also seem to be favored, hence "_files" and I included "readme" which might be worth reconsidering if it throws up too many FPs.

All added together we have the following:

select filename, directory, datetime(ctime,'unixepoch') Timestamp
from file
where directory like 'C:\Users\%\Desktop\'
and ( ctime > STRFTIME('%s','NOW','-14 days'))
and (size >500 and size < 3000)
and ((filename like '%.txt') or (filename like '%.htm%'))
and ((filename like '%decrypt%')
  or (filename like '%recover%')
  or (filename like '%readme%')
  or (filename like '%_files%')
  or (filename like '%restore%'));

I'd be curious to know how people might get on with this or how it could be refined.


  • good to see folks writing and using live discover thanks. 

  • hi,

    i'm testing this query but i have 2 quetions:


    • First and most important, just copy and paste and it doesn't work for me. i have created documents in my desktop ( decrypt, hello.txt, recover, but i can't get any information. the only change i have made is the size of the files, because my files are smaller.

    (size >1 and size < 3000)

    • second one, if we create a variable with the new EDR features, how can i include it in the query? something like this?

    and ((filename like '%' + $$variable$$ + '%')


  • In reply to oscar_lopez:

    Try this one:

       (select replace(filename, rtrim(filename, replace(filename, '.', '')), '')) extension,
       datetime(ctime,'unixepoch') Create_Timestamp,
       datetime(mtime,'unixepoch') Modify_Timestamp,
       datetime(atime,'unixepoch') Access_Timestamp
    FROM file
       directory like 'C:\Users\%\Desktop\' AND
       ( ctime > STRFTIME('%s','NOW','-14 days') OR mtime > STRFTIME('%s', 'NOW', '-14 days') ) AND
       size > 1 AND
       size < 3000000 AND
       ($$Extension_CHECK$$ ) AND
       ($$Filename_CHECK$$ );


    This uses two variables, one to drop in the SQL for extension checking and one for the Filename checking.

    $$Extension_CHECK$$ = extension LIKE 'txt' OR extension LIKE 'htm%'
    $$Filename_CHECK$$ = (filename like '%decrypt%') OR (filename like '%recover%') OR (filename like '%readme%') OR (filename like '%_files%') OR (filename like '%restore%')

    NOTE the variables can be a few K of SQL code.

    Also the size is in bytes, so I increased it a fair bit.

  • In reply to oscar_lopez:

    If you simply try running the following against one representative client:

    select filename, directory, datetime(ctime,'unixepoch') Timestamp  from file  where directory like 'C:\Users\%\Desktop\'

    does that list the files on each users desktop? 

    Are you redirecting the users desktop say through group policy to a filer, so a UNC path?  The service will run as local system on the client so it may not be able to "see" the location as the user can.



  • In reply to jak:


    my windows machine is a virtual windows 10, it belongs to a domain but there is no policies or GPO at all, 

    • If i run your query it works. 
    • If i modify your query like this, it works fine:


    select filename, directory, datetime(ctime,'unixepoch') Timestamp  from file  where directory like 'C:\Users\%\Desktop\' and filename like '%readme%'


    • if i try to use a variable then it doesn't work, the value of $$file$$ is readme as the previous sample.

    select filename, directory, datetime(ctime,'unixepoch') Timestamp  from file  where directory like 'C:\Users\%\Desktop\' and filename like $$file$$


    After doing some testing and re reading your post and the karl's post several times i have realized where was my mistake the variable must contain the full instruction, not only the value, doing this it works fine:

    first i have created the variable $$file$$ 

    in the field value i have written '%readme%' 

    when i run the query

    select filename, directory, datetime(ctime,'unixepoch') Timestamp  from file  where directory like 'C:\Users\%\Desktop\' and filename like $$file$$


    it works, so i assume that the variable must or can contain not only the value i'm looking for, it can contain part of the code as well

  • In reply to oscar_lopez:

    That is correct, variables are simple string replacement operations. Wherever the $$variable$$ is referenced we will replace it with the text you have set for the variable data.  This can be SQL instructions and is limited to 5K of data.  

    You can use that 5K to drop in an entire other select statement or a large amount of data.  I used the variable for the Bulk SHA256 query to hold the list of hashes, but it could have easily also had a curl statement to pull that data from the github directly.

    You can replace the variable data and instead of using a list of hashes have that data delivered by the statement below.

    (SELECT result from curl WHERE url = '' )


    Thanks for exploring