The VMware Carbon Black Tech Zone is live! Checkout this great resource: Mastering Carbon Black Audit & Remediation.

Search windows artifacts of execution for evidence of a file

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%');

 

1 Comment
jnelson
Carbon Black Employee
Status changed to: Approved