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\' 

Notes:

• 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%')or (filename like '%.hta'))
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.

Regards,
Jak

Note: I have added ".hta" as an extension as Black Claw ransomware uses it for the note.  We could have changed the existing "%.htm%" to "%.ht%" but I think it's clearer and less prone to error to use a new hta clause.