Disclaimer: This information is provided as-is for the benefit of the Community. Please contact Sophos Professional Services if you require assistance with your specific environment.

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

Added Disclaimer
[edited by: GlennSen at 3:51 PM (GMT -7) on 5 Apr 2023]