Built off the open source project Osquery
Description: Search multiple artifacts of execution to search for evidence of an executable seen by a system. The query searches several tables; shimcache, filesystem (prefetch), background activity moderator, and userassist. These tables are not exhaustive of all execution artifacts but lays the ground work as osquery continues to add new artifact and tables.
To use: find+replace 'evil.exe' with the file (executable) to search
(search related to prior post - https://community.carbonblack.com/t5/Query-Exchange/Ideas-for-working-out-FULL-OUTER-JOIN-limitation...)
What The Data Shows: Timestamp and path related to the source artifact of the file
SQL:
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%');
Copyright © 2005-2023 Broadcom. All Rights Reserved. The term “Broadcom” refers to Broadcom Inc. and/or its subsidiaries.