Ideas for working out FULL OUTER JOIN limitation

[jstreet16]

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.

SQL: 

select f.path,ua.path,shim.path,bam.path,h.sha256,

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

    using (path)

OUTER JOIN hash as h

    using (path)

OUTER JOIN userassist as ua

    using (path)

OUTER JOIN shimcache as shim

    using (path)

where (path like 'c:\Windows\temp\scvhost.exe';

Issue:

.

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?

[jnelson]

@jstreet16  Here is what you are looking for, but this method will make your query pretty complex visually:

https://www.sqlitetutorial.net/sqlite-full-outer-join/ 

[jstreet16]

Fantastic resource! Thank you. You're right @jnelson 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%');

[jnelson]

@jstreet16 amazing work! I love the method you used to avoid searching the entire filesystem. Would you mind posting the working query as a new query with an appropriate title so that people could more easily find it?