Fantastic resource! Thank you. You're right it is not pretty but it will get the job done.
To use the search, find+replace 'evil.exe' with the executable
Results of this query answer the question during an incident of what systems have seen, and possibly executed, a particular file by name.
Since the following search is a bad idea and won't work
select * from file where path like 'c:\%%' and filename like 'evil.exe';
This is a way to identify the possible existence of a binary on host by searching artifacts of execution on a windows system (and a few folder paths tossed in because they're frequently abused). The time stamp associated with each data source has a different meaning respective of each table/artifact (along with other artifact specific nuances). Bottom line is a result indicates a system has seen a file and should be added to the scope of systems for investigation.
select f.path,ua.path as ua_path,shim.path as shim_path,bam.path as bam_path,
datetime(shim.modified_time,'unixepoch') AS shim_file_last_modified_time,
datetime(bam.last_execution_time,'unixepoch') AS bam_file_last_execution_time,
datetime(ua.last_execution_time,'unixepoch') AS ua_file_last_execution_time,
datetime(f.mtime,'unixepoch') AS filesystem_last_modified_time,
datetime(f.btime,'unixepoch') AS filesystem_created_time,
ROUND((f.size * 10e-7),4) AS size_megabytes
FROM background_activities_moderator as bam
LEFT JOIN userassist as ua
using (path)
LEFT JOIN shimcache as shim
using (path)
LEFT JOIN file as f
using (path)
WHERE (shim.path like '%evil.exe' OR bam.path like '%evil.exe' OR ua.path like '%evil.exe' OR (f.directory IN ('c:\windows\prefetch\','c:\windows\','c:\programdata\','c:\') and f.filename like 'evil.exe%'))
UNION
select f.path,ua.path as ua_path,shim.path as shim_path,bam.path as bam_path,
datetime(shim.modified_time,'unixepoch') AS shim_file_last_modified_time,
datetime(bam.last_execution_time,'unixepoch') AS bam_file_last_execution_time,
datetime(ua.last_execution_time,'unixepoch') AS ua_file_last_execution_time,
datetime(f.mtime,'unixepoch') AS filesystem_last_modified_time,
datetime(f.btime,'unixepoch') AS filesystem_created_time,
ROUND((f.size * 10e-7),4) AS size_megabytes
FROM userassist as ua
LEFT JOIN background_activities_moderator as bam
using (path)
LEFT JOIN shimcache as shim
using (path)
LEFT JOIN file as f
using (path)
WHERE (shim.path like '%evil.exe' OR bam.path like '%evil.exe' OR ua.path like '%evil.exe' OR (f.directory IN ('c:\windows\prefetch\','c:\windows\','c:\programdata\','c:\') and f.filename like 'evil.exe%'))
UNION
select f.path,ua.path as ua_path,shim.path as shim_path,bam.path as bam_path,
datetime(shim.modified_time,'unixepoch') AS shim_file_last_modified_time,
datetime(bam.last_execution_time,'unixepoch') AS bam_file_last_execution_time,
datetime(ua.last_execution_time,'unixepoch') AS ua_file_last_execution_time,
datetime(f.mtime,'unixepoch') AS filesystem_last_modified_time,
datetime(f.btime,'unixepoch') AS filesystem_created_time,
ROUND((f.size * 10e-7),4) AS size_megabytes
FROM shimcache as shim
LEFT JOIN background_activities_moderator as bam
using (path)
LEFT JOIN userassist as ua
using (path)
LEFT JOIN file as f
using (path)
WHERE (shim.path like '%evil.exe' OR bam.path like '%evil.exe' OR ua.path like '%evil.exe' OR (f.directory IN ('c:\windows\prefetch\','c:\windows\','c:\programdata\','c:\') and f.filename like 'evil.exe%'))
UNION
select f.path,ua.path as ua_path,shim.path as shim_path,bam.path as bam_path,
datetime(shim.modified_time,'unixepoch') AS shim_file_last_modified_time,
datetime(bam.last_execution_time,'unixepoch') AS bam_file_last_execution_time,
datetime(ua.last_execution_time,'unixepoch') AS ua_file_last_execution_time,
datetime(f.mtime,'unixepoch') AS filesystem_last_modified_time,
datetime(f.btime,'unixepoch') AS filesystem_created_time,
ROUND((f.size * 10e-7),4) AS size_megabytes
FROM file as f
LEFT JOIN background_activities_moderator as bam
using (path)
LEFT JOIN userassist as ua
using (path)
LEFT JOIN shimcache as shim
using (path)
WHERE (f.directory IN ('c:\windows\prefetch\','c:\windows\','c:\programdata\','c:\') and f.filename like 'evil.exe%');