Purpose

This query is taken directly from the Sophos Rapid Response query pack located here: https://github.com/SophosRapidResponse/OSQuery/blob/main/TTPs/Execution/Program%20Execution.sql

This query will combine several tables associated with program execution, such as BAM, Shimcache, Userassist, Prefetch, Shortcuts, and Filesystem. This is a go-to query for quickly identifying evidence of a program execution.

Prerequisites

You must have XDR enabled in your environment. This is a Live Discover query for Windows.

Query Focus

/*************************** Sophos.com/RapidResponse ***************************\
| DESCRIPTION                                                                    |
| The query uses several tables associated with a program execution such as:     |
| BAM, Shimcache, Userassist, Prefetch, Shortcuts, and Filesystem tables.        |
| This is a go-to query to quickly identify evidence of a program execution      |
|                                                                                |
| VARIABLE                                                                       |
| - filename: (STRING)  - if want to get everything, please use (%)              |
|                                                                                |
| Example:                                                                       |
| - malware, malware.exe, C:\ProgramData\                                        |
|                                                                                |
| Author: The Rapid Response Team | Elida Leite                                  |
| github.com/SophosRapidResponse                                                 |
\********************************************************************************/

SELECT 
	bam.path As Path,
	'-' AS Filename,
	datetime(bam.last_execution_time,'unixepoch') AS Last_Executed,
	'-' AS Last_Modified,
	'-' AS Creation_Time,
	'-' As Count,
	bam.sid As SID,
	CAST ((SELECT user.username FROM users user WHERE bam.sid = user.uuid) AS Text) Username,
	'BAM' AS Data_Source,
	'TA0002 - Program Execution' As Query
FROM background_activities_moderator as bam
WHERE bam.path like '%$$filename$$%'

UNION

SELECT
	shim.path As Path,
	'-' AS Filename,
	'-' AS Last_Executed,
	datetime(shim.modified_time,'unixepoch') AS Last_Modified,
	'-' AS Creation_Time,
	'-' As Count,
	'-' As SID,
	'-' As Username,
	'Shimcache' AS Data_Source,
	'TA0002 - Program Execution' As Query
FROM shimcache as shim
WHERE shim.path like '%$$filename$$%'

UNION

SELECT
	ua.path as Path,
	'-' AS Filename,
	datetime(ua.last_execution_time,'unixepoch') AS Last_Executed,
	'-' AS Last_Modified,
	'-' AS Creation_Time,
	ua.count As Count,
	ua.sid As SID,
	CAST ((SELECT user.username FROM users user WHERE ua.sid = user.uuid) AS Text) Username,
	'Userassist' AS Data_Source,
	'TA0002 - Program Execution' As Query
FROM userassist as ua
WHERE ua.path like '%$$filename$$%'

UNION

SELECT
	pf.path As Path,
	pf.filename AS Filename,
	datetime(pf.last_run_time,'unixepoch') AS Last_Executed,
	'-' AS Last_Modified,
	'-' Creation_Time,
	'-' As Count,
	'-' As SID,
	'-' As Username,
	'Prefetch' AS Data_Source,
	'TA0002 - Program Execution' As Query
FROM prefetch as pf
WHERE pf.filename like '%$$filename$$%'

UNION

SELECT
	f.path As Path,
	f.filename AS Filename,
	'-' AS Last_Executed,
	datetime(f.mtime,'unixepoch') AS Last_Modified,
	datetime(f.btime,'unixepoch') AS Creation_Time,
	'-' As Count,
	'-' As SID,
	'-' As Username,
	'Filesystem' AS Data_Source,
	'TA0002 - Program Execution' As Query
FROM file as f 
WHERE ((f.directory LIKE 'C:\Users\%\%' OR f.directory IN ('C:\Windows\','C:\ProgramData\','C:\')) AND f.filename like '%$$filename$$%')
UNION
SELECT
	f.path AS Path,
	f.filename AS Filename,
	'-' As Last_Executed,
	strftime('%Y-%m-%dT%H:%M:%SZ',datetime(f.mtime,'unixepoch')) AS Last_Modified, 
	strftime('%Y-%m-%dT%H:%M:%SZ',datetime(f.btime,'unixepoch')) AS Creation_Time, 
	'-' As Count,
	'-' As SID,
	'-' As Username,
	'Recent_Files' AS Data_Source,
	'TA0002 - Program Execution' AS Query
FROM file as f
WHERE f.path LIKE 'C:\Users\%\AppData\Roaming\Microsoft\%\Recent\%$$filename$$%'
	AND filename != '.' 
ORDER BY Last_Executed DESC, Last_Modified DESC

You can see by searching with the wildcard, you return a lot of information, with over 5 pages of results.

Understanding the Code

As mentioned in the description, this query is pulling information from separate tables. In lines 17 - 29, this is the first grouping of table results from the background_activities_monitor. This data is then merged with a union from the results from lines 33 - 45. You will notice this trend continues throughout.

Try searching for a specific file name to minimize the amount of "noise" you're seeing.

Happy querying!

-jk