Description: Given a file path check for the existence and evidence of execution of a file
What The Data Shows: Results will return rows for any match of the file path on the file system or if the file was deleted but executed at some point. The idea is to take a file found on one system and look for the evidence of it's existence across all sensors.
datetime(shim.modified_time,'unixepoch') AS shim_file_last_modified_time,
datetime(bam.last_execution_time,'unixepoch') AS bam_file_last_execution_time,
datetime(f.mtime,'unixepoch') AS file_last_modified_time,
datetime(f.btime,'unixepoch') AS filetable_created_time,
datetime(ua.last_execution_time,'unixepoch') AS ua_file_last_execution_time,
ROUND((f.size * 10e-7),4) AS size_megabytes
from file as f
OUTER JOIN background_activities_moderator as bam
OUTER JOIN hash as h
OUTER JOIN userassist as ua
OUTER JOIN shimcache as shim
where (path like 'c:\Windows\temp\scvhost.exe';
Osquery does not support FuLL OUTER JOINs. So is there another way to check multiple artifacts of execution and file system in one search that can return results that are in one table but not all?