Video: https://vimeo.com/519661823
Queries used:
Queries used during SophSkills Demo
DATA LAKE - List all EP and FW tables in the data lake
This query will need to run against the data lake. As we add more sensors to the data lake we will be extending this query with additional sections to list the tables for the additional sensor. For example Email is coming to the data lake in just a few weeks so we will be adding that. Note the query does not yet detect if the sensor is present so for account without FW Data in the lake you should comment out that section or the query will report an error that it could not find the xgfw_data table.
-- LIST ALL EP and XG TABLES
WITH Full_Data_Set AS (
(SELECT DISTINCT 'XG_Firewall' Source, log_component Table_Name, COUNT(dist_key) Entries
FROM xgfw_data
GROUP BY log_component
ORDER By log_component ASC)
UNION ALL
(SELECT DISTINCT 'Endpoint' Source, query_name Table_Name, COUNT(endpoint_id) Entries
FROM xdr_data
GROUP BY query_name
ORDER BY query_name ASC)
)
SELECT UPPER(Source) Sensor_Type, LOWER(Table_Name) Table_Name, Entries FROM Full_Data_Set ORDER BY Sensor_Type, Table_Name, Entries
ENDPOINT - Data Lake Hydration Queries
This query runs as an Endpoint Query is supported on Windows MAC and Linux, you only need to run it on a single device for each OS type as the data lake hydration queries are common across all instances of a supported OS
-- Data Lake Hydration query SELECT name, query, interval FROM osquery_schedule
DATA LAKE - Windows programs Inventory Search (Datalake)
This query takes variables that will need to be defined when you save the query.
|
Descriptive Name |
Variable type |
SQL variable name |
Value |
|
Application Name |
String |
$$Application Name$$ |
% |
|
Application Version |
String |
$$Application Version$$ |
% |
|
Host Name |
String |
$$Host Name$$ |
% |
|
Publisher Name |
String |
$$Publisher Name$$ |
% |
WITH Counted_Apps AS (
WITH App_List AS (
SELECT DISTINCT meta_hostname, name, version, publisher, install_date
FROM xdr_data
WHERE query_name = 'windows_programs' AND name > '' AND
LOWER(name) LIKE LOWER('%$$Application Name$$%') AND LOWER(version) LIKE LOWER('%$$Application Version$$%') AND
LOWER(publisher) LIKE LOWER ('%$$Publisher Name$$%') AND LOWER(meta_hostname) LIKE LOWER ('%$$Host Name$$%')
)
-- WHEN Name, version and Publisher are wildcards group by publisher and put the name, version and devices info into a list
SELECT
publisher, COUNT(meta_hostname) Instances,
array_join(array_agg(DISTINCT name), ','||CHR(10)) App_name_List,
array_join(array_agg(DISTINCT version), ','||CHR(10)) version_LIST,
array_join(array_agg(DISTINCT meta_hostname), ','||CHR(10)) DeviceName_LIST, MIN(install_date) Earliest_Install, MAX(install_date) Last_Install
FROM App_List WHERE
'$$Application Name$$' = '%' AND '$$Application Version$$' = '%' AND '$$Publisher Name$$' = '%'
GROUP BY publisher
UNION ALL
-- In all other instances breach out everything on their own line, only grouping the device info
SELECT
publisher, COUNT(meta_hostname) Instances,
name,
version,
array_join(array_agg(DISTINCT meta_hostname), ','||CHR(10)) DeviceName_LIST, MIN(install_date) Earliest_Install, MAX(install_date) Last_Install
FROM App_List WHERE
('$$Application Name$$' <> '%' OR '$$Application Version$$' <> '%' OR '$$Publisher Name$$' <> '%')
GROUP BY publisher, name, version
)
SELECT DISTINCT * FROM Counted_Apps ORDER BY publisher ASC
DATA LAKE – MITRE ATT&CK - EXFILTRATION
This query scans all data in the data lake looking for IOC’s that match a list of 67 detection rules. It does take variables
|
Descriptive Name |
Variable type |
SQL variable name |
Value |
|
Any Field String Match |
String |
$$Any Field String Match$$ |
% |
|
Device Name |
String |
$$Device Name$$ |
% |
|
MITRE ATT&CK ID |
String |
$$MITRE ATT&CK ID$$ |
% |
|
Tactic |
String |
$$Tactic$$ |
% |
|
Technique |
String |
$$Technique$$ |
% |
-- Hunt for EXFILTRATION IOC
-- VARIABLE $$Any Field String Match$$ STRING
-- VARIABLE $$Device Name$$ STRING
-- VARIABLE $$MITRE ATT&CK ID$$ STRING
-- VARIABLE $$Tactic$$ STRING
-- VARIABLE $$Technique$$ STRING
-- Calculate detection counts for similar IOC's across multiple devices or multiple instances of the same IOC on a device for the same process-name and cmdline
WITH Detection_Counts AS (
WITH Detections AS (
-- Detect MITRE ATT&CK Impact Tactic using Process and cmdline info only
WITH Mitre_map (ID,Tactic,Technique,SubTechnique,Mitre_Link,Method,Condition,SubCondition,Refrence) AS (
-- EXFILTRATION DETECTION SET
SELECT 'T1020.001','Exfiltration','Automated Exfiltration','Traffic Duplication','https://attack.mitre.org/techniques/T1020/001','Process_Cmd','powershell.exe','%Set-VMNetworkAdapter%PortMirroring%',''
UNION ALL SELECT 'T1020.001','Exfiltration','Automated Exfiltration','Traffic Duplication','https://attack.mitre.org/techniques/T1020/001','Process_Cmd','powershell.exe','%Add-NetEventPacketCaptureProvider%',''
UNION ALL SELECT 'T1030','Exfiltration','Data Transfer Size Limits','','https://attack.mitre.org/techniques/T1030','Process_Cmd','powershell.exe','%GetRequestStream%Write%length%',''
UNION ALL SELECT 'T1030','Exfiltration','Data Transfer Size Limits','','https://attack.mitre.org/techniques/T1030','Process_Cmd','powershell.exe','%upload%chunksize%',''
UNION ALL SELECT 'T1048.003','Exfiltration','Exfiltration Over Alternative Protocol','Exfiltration Over Unencrypted/Obfuscated Non-C2 Protocol','https://attack.mitre.org/techniques/T1048/003','Process_Cmd','telnet.exe','%localhost 80%','https://www.codingame.com/playgrounds/13710/conversation-over-http'
UNION ALL SELECT 'T1048.003','Exfiltration','Exfiltration Over Alternative Protocol','Exfiltration Over Unencrypted/Obfuscated Non-C2 Protocol','https://attack.mitre.org/techniques/T1048/003','Process_Cmd','telnet.exe','% 80%','https://www.codingame.com/playgrounds/13710/conversation-over-http'
UNION ALL SELECT 'T1048.003','Exfiltration','Exfiltration Over Alternative Protocol','Exfiltration Over Unencrypted/Obfuscated Non-C2 Protocol','https://attack.mitre.org/techniques/T1048/003','Process_Cmd','telnet.exe','% 25%','https://www.pentestpartners.com/security-blog/data-exfiltration-techniques/'
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','DISM.exe','%telnetclient%','Installing telnetclient'
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','DISM.exe','%telnetserver%','Installing telnetserver'
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','pkmgr.exe','%telnetclient%','Installing telnetclient'
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','pkmgr.exe','%telnetserver%','Installing telnetserver'
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','scp.exe','%host%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','pscp.exe','%host%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','ftp.exe','%.%.%.%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','powershell.exe','%Copy-Item%path%destination%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','powershell.exe','%Copy-Item%tosession%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','powershell.exe','%Start-BitsTransfer%source%destination%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','powershell.exe','%Start-BitsTransfer%source%destination%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','powershell.exe','%write-host%',''
UNION ALL SELECT 'T1041','Exfiltration','Exfiltration Over C2 Channel','','https://attack.mitre.org/techniques/T1041','Process_Cmd','curl.exe','%-T%','Curl upload'
UNION ALL SELECT 'T1011.001','Exfiltration','Exfiltration Over Other Network Medium','Exfiltration Over Bluetooth','https://attack.mitre.org/techniques/T1011/001','Process_Cmd','btobex.exe','%','https://superuser.com/questions/1211965/send-a-file-via-bluetooth-from-the-command-prompt'
UNION ALL SELECT 'T1011.001','Exfiltration','Exfiltration Over Other Network Medium','Exfiltration Over Bluetooth','https://attack.mitre.org/techniques/T1011/001','Process_Cmd','fsquirt.exe','%','https://docs.microsoft.com/en-us/windows-hardware/drivers/bluetooth/bluetooth-user-interface'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%Get-DeviceInfo%','https://superuser.com/questions/369959/how-do-i-access-mtp-devices-on-the-command-line-in-windows'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%Get-PNPDevice%','https://docs.microsoft.com/en-us/powershell/module/pnpdevice/get-pnpdevice?view=win10-ps'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%Get-PNPDevice%usb%','https://docs.microsoft.com/en-us/powershell/module/pnpdevice/get-pnpdevice?view=win10-ps'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%GWmi Win32_LogicalDisk%USB%','https://social.technet.microsoft.com/Forums/scriptcenter/en-US/ad6182d8-f52b-4985-96bc-02601484bebb/powershell-script-to-move-files-from-removable-drive-and-eject?forum=ITCG'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%GWmi Win32_LogicalDisk%camera%','https://social.technet.microsoft.com/Forums/scriptcenter/en-US/ad6182d8-f52b-4985-96bc-02601484bebb/powershell-script-to-move-files-from-removable-drive-and-eject?forum=ITCG'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%GWmi Win32_LogicalDisk%phone%','https://social.technet.microsoft.com/Forums/scriptcenter/en-US/ad6182d8-f52b-4985-96bc-02601484bebb/powershell-script-to-move-files-from-removable-drive-and-eject?forum=ITCG'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%GWmi Win32_LogicalDisk%ssd%','https://social.technet.microsoft.com/Forums/scriptcenter/en-US/ad6182d8-f52b-4985-96bc-02601484bebb/powershell-script-to-move-files-from-removable-drive-and-eject?forum=ITCG'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%GWmi Win32_USBControllerDevice%','https://docs.microsoft.com/en-us/windows/win32/cimwin32prov/win32-usbcontrollerdevice'
UNION ALL SELECT 'T1052.001','Exfiltration','Exfiltration Over Physical Medium','Exfiltration over USB','https://attack.mitre.org/techniques/T1052/001','Process_Cmd','powershell.exe','%GWmi Win32_USBController%','https://docs.microsoft.com/en-us/windows/win32/cimwin32prov/win32-usbcontrollerdevice'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','powershell.exe','%iex%http%chocolatey%','https://chocolatey.org/install'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','cmd.exe','%iex%http%chocolatey%','https://chocolatey.org/install'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','powershell.exe','%install%git%','https://chocolatey.org/packages/git'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','git.exe','%iex%http%chocolatey%','https://chocolatey.org/install'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','git.exe','%push%','https://docs.github.com/en/free-pro-team@latest/github/importing-your-projects-to-github/adding-an-existing-project-to-github-using-the-command-line'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','git.exe','%add%','https://docs.github.com/en/free-pro-team@latest/github/importing-your-projects-to-github/adding-an-existing-project-to-github-using-the-command-line'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','git.exe','%commit%','https://docs.github.com/en/free-pro-team@latest/github/importing-your-projects-to-github/adding-an-existing-project-to-github-using-the-command-line'
UNION ALL SELECT 'T1567.001','Exfiltration','Exfiltration Over Web Service','Exfiltration to Code Repository','https://attack.mitre.org/techniques/T1567/001','Process_Cmd','choco.exe','%git%install%','https://chocolatey.org/packages/git.install'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','choco.exe','%gsutil%install%','https://cloud.google.com/storage/docs/gsutil/commands/cp'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','%','%gsutil%cp%','https://cloud.google.com/storage/docs/gsutil/commands/cp'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','%','%dropbox%uploader%','http://raspberrypimaker.com/transferring-to-dropbox-command-line/'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','cmd.exe','%dropbox%copy%','https://codereview.stackexchange.com/questions/111483/copying-files-into-dropbox-folder-from-command-line'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','idevsutil.exe','%upload%','https://www.idrivesync.com/evs/dev-guide-parameters.htm'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','sugersyncinstall.exe','%','https://www.sugarsync.com/'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','sugersync.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','FreeFileSync.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','ifolder%.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','ipfs.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','rsync.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','bitsadmin.exe','%upload%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1567.002','Exfiltration','Exfiltration Over Web Service','Exfiltration to Cloud Storage','https://attack.mitre.org/techniques/T1567/002','Process_Cmd','rclone.exe','%copy%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1029','Exfiltration','Scheduled Transfer','NA','https://attack.mitre.org/techniques/T1029','ProcessCmd','schtasks.exe','%copy%',''
UNION ALL SELECT 'T1029','Exfiltration','Scheduled Transfer','NA','https://attack.mitre.org/techniques/T1029','ProcessCmd','schtasks.exe','%upload%',''
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','choco.exe','%gsutil%install%','https://cloud.google.com/storage/docs/gsutil/commands/cp'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','%','%gsutil%cp%','https://cloud.google.com/storage/docs/gsutil/commands/cp'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','%','%dropbox%uploader%','http://raspberrypimaker.com/transferring-to-dropbox-command-line/'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','cmd.exe','%dropbox%copy%','https://codereview.stackexchange.com/questions/111483/copying-files-into-dropbox-folder-from-command-line'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','idevsutil.exe','%upload%','https://www.idrivesync.com/evs/dev-guide-parameters.htm'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','sugersyncinstall.exe','%','https://www.sugarsync.com/'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','sugersync.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','FreeFileSync.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','ifolder%.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','ipfs.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','rsync.exe','%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','bitsadmin.exe','%upload%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
UNION ALL SELECT 'T1537','Exfiltration','Transfer Data to Cloud Account','','https://attack.mitre.org/techniques/T1537','Process_Cmd','rclone.exe','%copy%','https://en.wikipedia.org/wiki/Comparison_of_file_synchronization_software'
)
--SELECT * FROM Mitre_Map
-- DETECTION BASED ON PROCESS_CMD
SELECT
xdr_data.meta_hostname Device_Name,
xdr_data.meta_os_type Device_Type,
xdr_data.unix_time Date_Time,
map.ID Mitre_ID,
map.Tactic Tactic,
map.Technique Technique,
map.SubTechnique SubTechnique,
map.condition || ' + ' || map.subcondition Hunt_Rule,
map.Mitre_Link,
map.Refrence,
xdr_data.username User_Name,
xdr_data.name Process_Name,
xdr_data.cmdline Cmd_Line,
xdr_data.sophos_PID SophosPID,
xdr_data.path Path_Name
FROM xdr_data
JOIN Mitre_map map ON
LOWER(xdr_data.name) LIKE LOWER(map.condition) AND
LOWER(xdr_data.cmdline) LIKE LOWER(map.SubCondition)
WHERE
LOWER(xdr_data.meta_hostname) LIKE LOWER('%$$Device Name$$%') AND
LOWER(map.Tactic) LIKE LOWER('%$$Tactic$$%') AND
LOWER(map.Technique) LIKE LOWER ('%$$Technique$$%') AND
LOWER(map.ID) LIKE LOWER ('%$$MITRE ATT&CK ID$$%') AND
map.Method = 'Process_Cmd'
)
SELECT
Mitre_ID,
Tactic,
Technique,
COUNT (Device_Name) Instances,
Process_Name,
Cmd_Line,
array_join(array_agg(DISTINCT User_Name), CHR(10)) User_List,
Hunt_Rule,
array_join(array_agg(DISTINCT Device_Name), CHR(10)) Device_LIST,
array_join(array_agg(DISTINCT Device_Type), CHR(10)) Device_Type,
MIN(Date_Time) First_Seen,
MAX(Date_Time) LAST_Seen,
SubTechnique,
array_join(array_agg(DISTINCT SophosPID), CHR(10)) SophosPID_LIST,
array_join(array_agg(DISTINCT Path_Name), CHR(10)) Path_Name_LIST,
MITRE_Link,
Refrence
FROM Detections
GROUP BY Mitre_ID, Tactic, Technique, Process_Name, Cmd_Line, Hunt_Rule, SubTechnique, User_Name, MITRE_Link, Refrence
)
SELECT * FROM Detection_Counts
WHERE LOWER(CAST (Mitre_ID AS VARCHAR) ||
CAST(Tactic AS VARCHAR) ||
CAST(Technique AS VARCHAR) ||
CAST(Instances AS VARCHAR) ||
CAST(Process_Name AS VARCHAR) ||
CAST(Cmd_Line AS VARCHAR) ||
CAST(User_List AS VARCHAR) ||
CAST(Hunt_Rule AS VARCHAR) ||
CAST(Device_List AS VARCHAR) ||
CAST(Device_Type AS VARCHAR) ||
CAST(First_Seen AS VARCHAR) ||
CAST(LAST_Seen AS VARCHAR) ||
CAST(SubTechnique AS VARCHAR) ||
CAST(SophosPID_LIST AS VARCHAR) ||
CAST(Path_Name_LIST AS VARCHAR) ||
CAST(MITRE_Link AS VARCHAR) ||
CAST(Refrence AS VARCHAR)
)
LIKE '%$$Any Field String Match$$%'
ORDER BY Instances DESC
ENDPOINT – Process Tree for a Sophos PID
This query takes the SophosPID and provides the process tree. It is a bit different than the Sophos Canned query for the same, in that it adds some different fields in the result.
|
Descriptive Name |
Variable type |
SQL variable name |
Value |
|
SPID |
String |
$$SPID$$ |
% |
SELECT *
FROM (
WITH RECURSIVE Trace(SophosPID, LEVEL, parent, TIME, cmdline, sha256, pathname, endtime) AS (
VALUES (
'$$SPID$$',
'▬▬▬▬▬▬▬▬▬▬▬',
(SELECT ParentSophosPID FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
(SELECT TIME FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
'▬▬▬▬▬▬▬▬▬▬▬', /* not required as this row will be excluded */
'▬▬▬▬▬▬▬▬▬▬▬', /* not required as this row will be excluded */
'▬ ANCESTORS ABOVE ▬', /* Create a seperator to indicate where the target SPID is in the tree */
'▬▬▬▬▬▬▬▬▬▬▬'
)
UNION ALL
SELECT spj.SophosPID,
Trace.LEVEL - 1,
spj.parentSophosPID,
spj.TIME,
spj.cmdline,
spj.sha256,
spj.pathname,
spj.endtime
FROM sophos_process_journal spj
JOIN Trace ON spj.SophosPID = trace.parent AND
spj.TIME > replace(trace.parent, rtrim(trace.parent, replace(Trace.parent, ':', '')), '') / 10000000 - 11644473600 -10 AND
spj.TIME < replace(trace.parent, rtrim(trace.parent, replace(Trace.parent, ':', '')), '') / 10000000 - 11644473600 +3600 AND
CAST (spj.endtime AS TEXT) = '0'
)
SELECT DATETIME (TIME,'unixepoch') Date_Time,
CASE Trace.SophosPID
WHEN '$$SPID$$' THEN pathname
ELSE substr('►►►►►►►►►►►►►►►►►►►►►►►►►', 1, (LEVEL - (SELECT LEVEL FROM trace ORDER BY LEVEL ASC LIMIT 1)) * 1) || (SELECT replace(pathname, rtrim(pathname, replace(pathname, '\', '')), ''))
END Process_Tree,
Trace.SophosPID SophosPID,
cmdline Cmdline,
sha256,
LEVEL,
endtime
FROM Trace
ORDER BY LEVEL
)
UNION ALL
SELECT DATETIME (TIME,'unixepoch'),
pathname,
SophosPID,
cmdline,
sha256,
0,
endtime
FROM sophos_process_journal
WHERE SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600
UNION ALL
SELECT *
FROM (
WITH RECURSIVE Trace(SophosPID, LEVEL, parent, TIME, cmdline, sha256, pathname, endtime) AS (
VALUES (
'$$SPID$$',
'▬▬▬▬▬▬▬▬▬▬▬',
(SELECT ParentSophosPID FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
(SELECT TIME FROM sophos_process_journal WHERE sophos_process_journal.SophosPID = '$$SPID$$' AND TIME = replace('$$SPID$$', rtrim('$$SPID$$', replace('$$SPID$$', ':', '')), '') / 10000000 - 11644473600),
'▬▬▬▬▬▬▬▬▬▬▬', /* not required as this row will be excluded */
'▬▬▬▬▬▬▬▬▬▬▬', /* not required as this row will be excluded */
'▬ CHILDREN BELOW ▬',/* Create a seperator to indicate where the target SPID is in the tree */
'▬▬▬▬▬▬▬▬▬▬▬'
)
UNION ALL
SELECT spj.SophosPID,
Trace.LEVEL + 1,
spj.parentsophospid,
spj.TIME,
spj.cmdline,
spj.sha256,
spj.pathname,
spj.endtime
FROM sophos_process_journal spj
JOIN Trace ON spj.parentSophosPID = trace.SophosPID AND
spj.TIME > replace(trace.SophosPID, rtrim(trace.SophosPID, replace(Trace.SophosPID, ':', '')), '') / 10000000 - 11644473600 -10 AND
spj.TIME < replace(trace.SophosPID, rtrim(trace.SophosPID, replace(Trace.SophosPID, ':', '')), '') / 10000000 - 11644473600 +3600 AND
CAST (spj.endtime AS TEXT) = '0'
ORDER BY 2 DESC
)
SELECT DATETIME (TIME,'unixepoch') Date_Time,
CASE Trace.SophosPID
WHEN '$$SPID$$' THEN substr('▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬▬v', 1, LEVEL * 1) || (SELECT replace(pathname, rtrim(pathname, replace(pathname, '\', '')), ''))
ELSE substr('►►►►►►►►►►►►►►►►►►►►►►►►►►►►►►', 1, LEVEL * 1) || (SELECT replace(pathname, rtrim(pathname, replace(pathname, '\', '')), ''))
END Process_Tree,
Trace.SophosPID SophosPID,
cmdline Cmdline,
sha256,
LEVEL,
endtime
FROM Trace
);
DATA LAKE – MITRE ATT&CK CALDERA
This query will check for 141 different indicators of compromise as demonstrated by the Caldera test tool. You can download that tool from GIT https://github.com/mitre/caldera
|
Descriptive Name |
Variable type |
SQL variable name |
Value |
|
Any Field String Match |
String |
$$Any Field String Match$$ |
% |
|
Device Name |
String |
$$Device Name$$ |
% |
|
MITRE ATT&CK ID |
String |
$$MITRE ATT&CK ID$$ |
% |
|
Tactic |
String |
$$Tactic$$ |
% |
|
Technique |
String |
$$Technique$$ |
% |
-- Hunt for CALDERA IOCS
-- VARIABLE $$Any Field String Match$$ STRING
-- VARIABLE $$Device Name$$ STRING
-- VARIABLE $$MITRE ATT&CK ID$$ STRING
-- VARIABLE $$Tactic$$ STRING
-- VARIABLE $$Technique$$ STRING
-- Calculate detection counts for similar IOC's across multiple devices or multiple instances of the same IOC on a device for the same process-name and cmdline
WITH Detection_Counts AS (
WITH Detections AS (
-- Detect MITRE ATT&CK Impact Tactic using Process and cmdline info only
WITH Mitre_map (ID,Tactic,Technique,SubTechnique,Mitre_Link,Method,Condition,SubCondition,Refrence) AS (
-- CALDERA DETECTION SET
SELECT 'T1002','exfiltration','Compress staged directory','Data Compressed','https://attack.mitre.org/techniques/T1002','Process_Cmd','%powershell%','%Compress-Archive%','Compress a directory on the file system'
UNION ALL SELECT 'T1003','Process Memory-access','Leverage Procdump','Memory Dump','https://attack.mitre.org/techniques/T1003','Process_Cmd','%powershell%','%procdump%','Dump a processes memory'
UNION ALL SELECT 'T1003','credential-access','GetComputers (Alice','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%powershell%','%Get-NetComputer%','Get a list of all computers in a domain'
UNION ALL SELECT 'T1003','credential-access','Leverage Procdump for lsass memory','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%','%ma lsass.exe%','Dump lsass for later use with mimikatz'
UNION ALL SELECT 'T1003','credential-access','MiniDumpWriteDump (Spooky','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%','%\creds.dmp','Custom GO credential dumper using minidumpwritedump'
UNION ALL SELECT 'T1003','credential-access','Powerkatz (Staged','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%','%DumpCreds%','Use Invoke-Mimikatz'
UNION ALL SELECT 'T1003','credential-access','Credentials in Registry','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%','%reg query HKLM%/f password% /t REG_SZ% ','Search for possible credentials stored in the HKLM Hive'
UNION ALL SELECT 'T1003','credential-access','Credentials in Registry','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%reg.exe','%query HKLM%/f password% /t REG_SZ% ','Search for possible credentials stored in the HKLM Hive'
UNION ALL SELECT 'T1003','credential-access','Run PowerKatz','Credential Dumping','https://attack.mitre.org/techniques/T1003','Process_Cmd','%powershell%','%Invoke-Mimikatz%','Use powerkatz to execute mimikatz and attempt to grab plaintext and/or hashed passwords'
UNION ALL SELECT 'T1005','collection','Find sensitive files','Data from Local System','https://attack.mitre.org/techniques/T1005','Process_Cmd','%powershell%','%Get-ChildItem C:\\Users -Recurse -Include%','Locate files deemed sensitive'
UNION ALL SELECT 'T1007','discovery','Discover system services','System Service Discovery','https://attack.mitre.org/techniques/T1007','Process_Cmd','%powershell%','%Get-Service%','Identify system services'
UNION ALL SELECT 'T1010','discovery','Application Window Discovery','Application Window Discovery','https://attack.mitre.org/techniques/T1010','Process_Cmd','%powershell%','%Get-Process%MainWindowTitle%Shell.Application%','Extracts the names of all open non-explorer windows and the locations of all explorer windows.'
UNION ALL SELECT 'T1012','discovery','Query Registry','Query Registry','https://attack.mitre.org/techniques/T1012','Process_Cmd','%powershell%','%Get-ItemProperty%HKLM:\\SOFTWARE\\Microsoft\\Windows\\CurrentVersion%','Query Registry using PowerShell Get-ItemProperty'
UNION ALL SELECT 'T1016','discovery','Find Domain','System Network Configuration Discovery','https://attack.mitre.org/techniques/T1016','Process_Cmd','%','%nbtstat -n%','Find Domain information'
UNION ALL SELECT 'T1016','discovery','Find Domain','System Network Configuration Discovery','https://attack.mitre.org/techniques/T1016','Process_Cmd','%nbstat.exe','%-n%','Find Domain information'
UNION ALL SELECT 'T1016','discovery','Scan WIFI networks','System Network Configuration Discovery','https://attack.mitre.org/techniques/T1016','Process_Cmd','%powershell%','%wifi.ps1 -Scan%','View all potential WIFI networks on host'
UNION ALL SELECT 'T1016','discovery','Preferred WIFI','System Network Configuration Discovery','https://attack.mitre.org/techniques/T1016','Process_Cmd','%powershell%','%wifi.ps1 -Pref%','See the most used WIFI networks of a machine'
UNION ALL SELECT 'T1016','discovery','Ping network','System Network Configuration Discovery','https://attack.mitre.org/techniques/T1016','Process_Cmd','%','%ping %','Ping the network in order to build the ARP cache'
UNION ALL SELECT 'T1018','discovery','Discover local hosts','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%powershell%','%powerview.ps1%Get-DomainComputer%','Use PowerView to query the Active Directory server for a list of computers in the Domain'
UNION ALL SELECT 'T1018','discovery','Discover domain controller','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%','%nltest /dclist%','Identify the remote domain controllers'
UNION ALL SELECT 'T1018','discovery','Discover domain controller','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%nltest.exe','%/dclist%','Identify the remote domain controllers'
UNION ALL SELECT 'T1018','discovery','Collect ARP details','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%','%arp -a%','Locate all active IP and FQDNs on the network'
UNION ALL SELECT 'T1018','discovery','Collect ARP details','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%arp.exe','%-a%','Locate all active IP and FQDNs on the network'
UNION ALL SELECT 'T1018','discovery','Find domain controller','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%','%nltest /dclist%','Identify remote domain controller'
UNION ALL SELECT 'T1018','discovery','Find domain controller','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%nltest.exe','%/dclist%','Identify remote domain controller'
UNION ALL SELECT 'T1018','discovery','Discover Mail Server','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%','%nslookup%mail%','Identify the organizations mail server'
UNION ALL SELECT 'T1018','discovery','Discover Mail Server','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%nslookup.exe','%mail%','Identify the organizations mail server'
UNION ALL SELECT 'T1018','discovery','Reverse nslookup IP','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%','%nslookup #{remote.host.ip}%','Find hostname of remote IP in domain'
UNION ALL SELECT 'T1018','discovery','Reverse nslookup IP','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%nslookup.exe','%remote.host.ip%','Find hostname of remote IP in domain'
UNION ALL SELECT 'T1018','discovery','Find Hostname','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%','%nbtstat -A #{remote.host.ip}%','Find hostname of remote host'
UNION ALL SELECT 'T1018','discovery','Find Hostname','Remote System Discovery','https://attack.mitre.org/techniques/T1018','Process_Cmd','%nbstat.exe','%remote.host.ip%','Find hostname of remote host'
UNION ALL SELECT 'T1031','persistence','Replace a service binary with alternate binary','Modify Existing Service','https://attack.mitre.org/techniques/T1031','Process_Cmd','%powershell%','%Get-Service -Name%Stop-Service%Copy-Item -Path%','A service was detected'
UNION ALL SELECT 'T1033','discovery','GetAdminMembers','System Owner/User Discovery','https://attack.mitre.org/techniques/T1033','Process_Cmd','%powershell%','%Get-NetUser -AdminCount%','Get Administrator users for a computer'
UNION ALL SELECT 'T1033','discovery','Identify active user','System Owner/User Discovery','https://attack.mitre.org/techniques/T1033','Process_Cmd','%','%whoami%','Find user running agent'
UNION ALL SELECT 'T1033','discovery','Identify active user','System Owner/User Discovery','https://attack.mitre.org/techniques/T1033','Process_Cmd','%','%$env:username%','Find user running agent'
UNION ALL SELECT 'T1033','discovery','GetServiceAccounts','System Owner/User Discovery','https://attack.mitre.org/techniques/T1033','Process_Cmd','%powershell%','%Get-NetUser -SPN%','Get Service Accounts for a domain'
UNION ALL SELECT 'T1040','credential-access','Sniff network traffic','Network Sniffing','https://attack.mitre.org/techniques/T1040','Process_Cmd','%powershell%','%New-NetEventSession -Name%-CaptureMode%','Perform a packet capture'
UNION ALL SELECT 'T1041','exfiltration','Exfil staged directory','Exfiltration Over Command and Control Channel','https://attack.mitre.org/techniques/T1041','Process_Cmd','%powershell%','%/file/upload%','Exfil the staged directory'
UNION ALL SELECT 'T1046','discovery','Fingerprint network services','Network Service Scanning','https://attack.mitre.org/techniques/T1046','Process_Cmd','%nmap.exe','%sV -p','Uses nmap to fingerprint services that were network accessible'
UNION ALL SELECT 'T1046','discovery','Fingerprint network services','Network Service Scanning','https://attack.mitre.org/techniques/T1046','Process_Cmd','%','%nmap -sV -p','Uses nmap to fingerprint services that were network accessible'
UNION ALL SELECT 'T1046','discovery','Network Service Scanning','Network Service Scanning','https://attack.mitre.org/techniques/T1046','Process_Cmd','%','%Get-NetIPConfiguration%','Scans the local network for common open ports'
UNION ALL SELECT 'T1047','discovery','Network Service Scanning','Network Service Scanning','https://attack.mitre.org/techniques/T1047','Process_Cmd','%powershell%','%wmic', 'process get executablepath WMIC'
UNION ALL SELECT 'T1047','execution','Remote Execute','Windows Management Instrumentation','https://attack.mitre.org/techniques/T1047','Process_Cmd','%powershell%','wmic /node:%/user:%/password:%process call create%','Remotely execute over WMI'
UNION ALL SELECT 'T1047','execution','Remote Execute','Windows Management Instrumentation','https://attack.mitre.org/techniques/T1047','Process_Cmd','%wmic.exe','%/node%password%process call create%','Remotely executes over WMI'
UNION ALL SELECT 'T1047','collection','WMIC Process Enumeration','WMIC','https://attack.mitre.org/techniques/T1047','Process_Cmd','%wmic.exe','%process get%executablepath%name%processid%parentprocessid%','Capture process id executable path pid and parent pid before writing to disk'
UNION ALL SELECT 'T1047','lateral-movement','Start remote process (WMI)','Windows Management Instrumentation','https://attack.mitre.org/techniques/T1047','Process_Cmd','%wmic.exe','%/node:%/user:%/password%process call create% ','Remotely executes a process over WMI'
UNION ALL SELECT 'T1047','lateral-movement','Start remote process (WMI)','Windows Management Instrumentation','https://attack.mitre.org/techniques/T1047','Process_Cmd','%powershell%','%wmic /node:%/user:%/password%process call create% ','Remotely executes a process over WMI'
UNION ALL SELECT 'T1049','discovery','System Network Connections Discovery','System Network Connections Discovery','https://attack.mitre.org/techniques/T1049','Process_Cmd','%','%netstat -ano%Get-NetTCPConnection%','Enumerates network connections'
UNION ALL SELECT 'T1049','discovery','System Network Connections Discovery','System Network Connections Discovery','https://attack.mitre.org/techniques/T1049','Process_Cmd','%netstat.exe','%-ano%','Enumerates network connections'
UNION ALL SELECT 'T1049','discovery','Find System Network Connections','System Network Connections Discovery','https://attack.mitre.org/techniques/T1049','Process_Cmd','%','%Get-NetTCPConnection%','Find System Network Connections'
UNION ALL SELECT 'T1050','Persistence','Privilege Escalation','New Service','https://attack.mitre.org/techniques/T1050','Process_Cmd','%powershell%','%New-Service%','powershell new service'
UNION ALL SELECT 'T1050','Persistence','Privilege Escalation','New Service','https://attack.mitre.org/techniques/T1050','Process_Cmd','%sc.exe','%create SERVICE%','sc.exe new service'
UNION ALL SELECT 'T1050','Persistence','Privilege Escalation','New Service','https://attack.mitre.org/techniques/T1050','Process_Cmd','%','%sc%create%SERVICE%','cmdline creation of new service'
UNION ALL SELECT 'T1055','defense-evasion','Inject into process','Process Injection','https://attack.mitre.org/techniques/T1055','Process_Cmd','%powershell%','%Invoke-ReflectivePEInjection.ps1%','Injects sandcat DLL into an available process'
UNION ALL SELECT 'T1055','defense-evasion','Signed Binary Execution - odbcconf','Process Injection','https://attack.mitre.org/techniques/T1055','Process_Cmd','%','%odbcconf.exe /S /A%','Leverage odbcconf for DLL injection'
UNION ALL SELECT 'T1055','defense-evasion','Signed Binary Execution - odbcconf','Process Injection','https://attack.mitre.org/techniques/T1055','Process_Cmd','%odbcconf.exe','%/S /A%','Leverage odbcconf for DLL injection'
UNION ALL SELECT 'T1055','defense-evasion','Signed Binary Execution - Mavinject','Process Injection','https://attack.mitre.org/techniques/T1055','Process_Cmd','%powershell%','%nmavinject.exe%','Leverage Mavinject (signed binary) for DLL injection'
UNION ALL SELECT 'T1055','defense-evasion','Signed Binary Execution - Mavinject','Process Injection','https://attack.mitre.org/techniques/T1055','Process_Cmd','%nmavinject.exe','%','Leverage Mavinject (signed binary) for DLL injection'
UNION ALL SELECT 'T1057','discovery','Discover injectable process','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%','%owner%%get-process%','Discovers processes that the current user has the ability to access and selects an injectable one'
UNION ALL SELECT 'T1057','discovery','Find LSASS','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%','%get-process%lsass%','Get process info for LSASS'
UNION ALL SELECT 'T1057','discovery','Find user processes','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%','%owner%user%get-process%','Get process info for processes running as a user'
UNION ALL SELECT 'T1057','discovery','System processes','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%','%Get-Process%','Identify system processes'
UNION ALL SELECT 'T1057','collection','tasklist Process Enumeration','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%','%tasklist /m%','Capture running processes and their loaded DLLs'
UNION ALL SELECT 'T1057','collection','tasklist Process Enumeration','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%tasklist.exe','%tasklist /m%','Capture running processes and their loaded DLLs'
UNION ALL SELECT 'T1057','collection','SysInternals PSTool Process Discovery','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%','%pslist.exe%','Process discovery via SysInternals pstool'
UNION ALL SELECT 'T1057','collection','SysInternals PSTool Process Discovery','Process Discovery','https://attack.mitre.org/techniques/T1057','Process_Cmd','%pslist.exe','%','Process discovery via SysInternals pstool'
UNION ALL SELECT 'T1059','execution','Stop PowerShell processes','Command-Line Interface','https://attack.mitre.org/techniques/T1059','Process_Cmd','%powershell%','%Get-Process -Name%powershell%Stop-Process%','Kill all PowerShell processes'
UNION ALL SELECT 'T1059','execution','Deploy downloaded software','Command-Line Interface','https://attack.mitre.org/techniques/T1059','Process_Cmd','%','%server=%%url=%System.Net.WebClient%','Start a new downloaded software'
UNION ALL SELECT 'T1059','execution','Disrupt WIFI','Command-Line Interface','https://attack.mitre.org/techniques/T1059','Process_Cmd','%powershell%','%wifi.ps1 -Off%','Turn a computers WIFI off'
UNION ALL SELECT 'T1063','discovery','Discover antivirus programs','Security Software Discovery','https://attack.mitre.org/techniques/T1063','Process_Cmd','%','%wmic%NAMESPACE%AntiVirusProduct%','Identify AV'
UNION ALL SELECT 'T1063','discovery','Discover antivirus programs','Security Software Discovery','https://attack.mitre.org/techniques/T1063','Process_Cmd','%wmic.exe','%AntiVirusProduct%','Identify AV'
UNION ALL SELECT 'T1063','discovery','Identify Firewalls','Security Software Discovery','https://attack.mitre.org/techniques/T1063','Process_Cmd','%','%SecurityCenter%AntiVirusProduct%','Identify Firewalls'
UNION ALL SELECT 'T1063','discovery','Identify Firewalls','Security Software Discovery','https://attack.mitre.org/techniques/T1063','Process_Cmd','%wmic.exe','%SecurityCenter%AntiVirusProduct%','Identify Firewalls'
UNION ALL SELECT 'T1064','execution','Impersonate user','Scripting','https://attack.mitre.org/techniques/T1064','Process_Cmd','%','%.exe -NoNewWindow -PassThru -Credential%','Run an application as a different user'
UNION ALL SELECT 'T1064','execution','Impersonate user','Scripting','https://attack.mitre.org/techniques/T1064','Process_Cmd','%','%-NoNewWindow -PassThru -Credential%','Run an application as a different user'
UNION ALL SELECT 'T1064','defense-evasion','Bypass ExecutionPolicy','Scripting','https://attack.mitre.org/techniques/T1064','Process_Cmd','%powershell%','%Set-ItemProperty -Path HKLM:\\Software\\Policies\\Microsoft\\Windows\\PowerShell -Name ExecutionPolicy -Value ByPass%','Ensure the ExecutionPolicy is turned to Bypass'
UNION ALL SELECT 'T1069','discovery','Permission Groups Discovery','Permission Groups Discovery','https://attack.mitre.org/techniques/T1069','Process_Cmd','%','%gpresult /R%','Summary of permission and security groups'
UNION ALL SELECT 'T1069','discovery','Permission Groups Discovery','Permission Groups Discovery','https://attack.mitre.org/techniques/T1069','Process_Cmd','%gpresult.exe','%/R%','Summary of permission and security groups'
UNION ALL SELECT 'T1069','discovery','Identify local users','Permission Groups Discovery','https://attack.mitre.org/techniques/T1069','Process_Cmd','%','%Get-WmiObject -Class Win32_UserAccount%','Identify all local users'
UNION ALL SELECT 'T1070','defense-evasion','Clear Logs','Indicator Removal on Host','https://attack.mitre.org/techniques/T1070','Process_Cmd','%powershell%','%Clear-Eventlog%','Clear logs'
UNION ALL SELECT 'T1072','execution','Encoded powershell','Scripting','https://attack.mitre.org/techniques/T1072','Process_Cmd','%','%powershell%-enc%','powershell with encoding'
UNION ALL SELECT 'T1074','collection','Stage sensitive files','Data Staged','https://attack.mitre.org/techniques/T1074','Process_Cmd','%powershell%','%Copy-Item%','copy files to staging directory'
UNION ALL SELECT 'T1074','collection','Create staging directory','Data Staged','https://attack.mitre.org/techniques/T1074','Process_Cmd','%powershell%','%New-Item%-Path%-ItemType%','create a directory for exfil staging'
UNION ALL SELECT 'T1077','lateral-movement','Net use','Windows Admin Shares','https://attack.mitre.org/techniques/T1077','Process_Cmd','%','%net use%/user:\n%','Mounts a network file share on a target computer'
UNION ALL SELECT 'T1077','lateral-movement','Net use','Windows Admin Shares','https://attack.mitre.org/techniques/T1077','Process_Cmd','%net.exe','%use%/user:\n%','Mounts a network file share on a target computer'
UNION ALL SELECT 'T1082','discovery','PowerShell version','System Information Discovery','https://attack.mitre.org/techniques/T1082','Process_Cmd','%','%PSVersionTable%','Discover the PowerShell version'
UNION ALL SELECT 'T1082','discovery','Find OS Version','System Information Discovery','https://attack.mitre.org/techniques/T1082','Process_Cmd','%','%OSVersion.Version%','Find OS Version'
UNION ALL SELECT 'T1083','discovery','File and Directory Discovery','File and Directory Discovery','https://attack.mitre.org/techniques/T1083','Process_Cmd','%powershell%','%Get-ChildItem -Path #{host.system.path}%','Find or discover files on the file system'
UNION ALL SELECT 'T1085','Defense Evasion','Execution','Rundll32','https://attack.mitre.org/techniques/T1085','Process_Cmd','%rundll32.exe','%vnc%server%','rundll32 to launch vncserver'
UNION ALL SELECT 'T1086','execution','Emulate Administrator Tasks','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%Emulate-Administrator-Tasks.ps1%','Emulate administrator tasks on a system in a separate process'
UNION ALL SELECT 'T1086','collection','PowerShell Process Enumeration','PowerShell Collection','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%get-process%ncat%','Capture running processes via PowerShell'
UNION ALL SELECT 'T1086','collection','ncat Enumeration','ncat Collection','https://attack.mitre.org/techniques/T1086','Process_Cmd','%ncat.exe','%get-process%ncat%','Capture running processes via ncat'
UNION ALL SELECT 'T1086','collection','cmd.exe information gathering','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%','%cmd.exe%net user%','User enumeration'
UNION ALL SELECT 'T1086','collection','cmd.exe information gathering','cmd','https://attack.mitre.org/techniques/T1086','Process_Cmd','%cmd.exe','%net user%','User enumeration'
UNION ALL SELECT 'T1086','collection','net.exe information gathering','net','https://attack.mitre.org/techniques/T1086','Process_Cmd','%net.exe','%user%','User enumeration'
UNION ALL SELECT 'T1086','collection','PowerShell information gathering','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%powershell.exe -c%Get-WmiObject -class win32_operatingsystem%','System Information Gathering Script'
UNION ALL SELECT 'T1086','collection','PowerShell information gathering','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%Get-WmiObject -class win32_operatingsystem%','System Information Gathering Script'
UNION ALL SELECT 'T1086','collection','UAC Status','PowerShell Collection','https://attack.mitre.org/techniques/T1086','Process_Cmd','%','%echo $(get-uac)%','Determine whether or not UAC is enabled'
UNION ALL SELECT 'T1086','execution','PowerShell bitly Link Download','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%https://bit.ly%','Download'
UNION ALL SELECT 'T1086','execution','PowerShell Invoke MimiKats','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%mimikatz%','Download'
UNION ALL SELECT 'T1086','defense-evasion','Move Powershell & triage','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%Copy-Item%PowerShell%','Copy powershell to non-standard location and perform triage commands'
UNION ALL SELECT 'T1086','execution','Install PSTools','PowerShell','https://attack.mitre.org/techniques/T1086','Process_Cmd','%powershell%','%New-Item%PSTools%','Download and install PSTools by unzipping the file'
UNION ALL SELECT 'T1087','discovery','Account Discovery (targeted','Account Discovery','https://attack.mitre.org/techniques/T1087','Process_Cmd','%','%net user #{domain.user.name} /domain%','The net utility is executed via cmd to enumerate detailed information about a specific user account.'
UNION ALL SELECT 'T1087','discovery','Account Discovery (targeted','Account Discovery','https://attack.mitre.org/techniques/T1087','Process_Cmd','%net.exe','%user.name%/domain%','The net utility is executed via cmd to enumerate detailed information about a specific user account.'
UNION ALL SELECT 'T1087','discovery','Account Discovery (all','Account Discovery','https://attack.mitre.org/techniques/T1087','Process_Cmd','%','net user /domain','The net utility is executed via cmd to enumerate domain user accounts.'
UNION ALL SELECT 'T1087','discovery','Account Discovery (all','Account Discovery','https://attack.mitre.org/techniques/T1087','Process_Cmd','%net.exe','%user /domain%','The net utility is executed via cmd to enumerate domain user accounts.'
UNION ALL SELECT 'T1088','privilege-escalation','UAC bypass registry','Bypass User Account Control','https://attack.mitre.org/techniques/T1088','Process_Cmd','%powershell%','%New-ItemProperty%%HKLM:Software%EnableLUA%-Value 0%','Set a registry key to allow UAC bypass'
UNION ALL SELECT 'T1088','privilege-escalation','wow64log DLL Hijack','Bypass User Account Control','https://attack.mitre.org/techniques/T1088','Process_Cmd','%','%Akagi64.exe%','using Akagi.exe'
UNION ALL SELECT 'T1088','privilege-escalation','wow64log DLL Hijack','Bypass User Account Control','https://attack.mitre.org/techniques/T1088','Process_Cmd','%akagi64.exe','%','using Akagi.exe'
UNION ALL SELECT 'T1088','privilege-escalation','Bypass UAC Medium','Bypass User Account Control','https://attack.mitre.org/techniques/T1088','Process_Cmd','%','%Bypass-UAC%','Bypass user account controls - medium'
UNION ALL SELECT 'T1089','defense-evasion','Disable Windows Defender Real-Time Protection','Disabling Security Tools','https://attack.mitre.org/techniques/T1089','Process_Cmd','%powershell%','%Set-MPPreference -DisableRealtimeMonitoring 1%','Disable Windows Defender Real-Time Protection'
UNION ALL SELECT 'T1089','defense-evasion','Disable Windows Defender IOAVProtection','Disabling Security Tools','https://attack.mitre.org/techniques/T1089','Process_Cmd','%powershell%','%Set-MpPreference -DisableIntrusionPreventionSystem%Set-MpPreference -DisableIOAVProtection%Set-MpPreference -DisableRealtimeMonitoring%Set-MpPreference -DisableScriptScanning%Set-MpPreference -EnableControlledFolderAccess Disabled%','Disable Windows Defender All'
UNION ALL SELECT 'T1089','defense-evasion','Disable Windows Defender IntrusionPrevention ','Disabling Security Tools','https://attack.mitre.org/techniques/T1089','Process_Cmd','%powershell%','%Set-MpPreference -DisableIntrusionPreventionSystem%','Disable Windows Defender All'
UNION ALL SELECT 'T1089','defense-evasion','Disable Windows Defender Script Scanning','Disabling Security Tools','https://attack.mitre.org/techniques/T1089','Process_Cmd','%powershell%','%Set-MpPreference -DisableScriptScanning%','Disable Windows Defender All'
UNION ALL SELECT 'T1089','defense-evasion','Disable Windows Defender ControledFolderAccess','Disabling Security Tools','https://attack.mitre.org/techniques/T1089','Process_Cmd','%powershell%','%Set-MpPreference -EnableControlledFolderAccess Disabled%','Disable Windows Defender All'
UNION ALL SELECT 'T1089','defense-evasion','netsh can be used to setup fw rules','Disabling Security Tools','https://attack.mitre.org/techniques/T1089','Process_Cmd','%netsh.exe','%firewall%add%rule%','netsh to add firewall rules'
UNION ALL SELECT 'T1090','command-and-control','netsh can be used to setup a proxy tunnel','connection proxy','https://attack.mitre.org/techniques/T1090','Process_Cmd','%netsh.exe','%winhttp%set%proxy%','setup connection proxy'
UNION ALL SELECT 'T1105','command-and-control','Leave note','Remote File Copy','https://attack.mitre.org/techniques/T1105','Process_Cmd','%powershell%','%Set-Content%-Path% -Value%hacked%','Create a text file for the user to find'
UNION ALL SELECT 'T1105','command-and-control','Leave note','Remote File Copy','https://attack.mitre.org/techniques/T1105','Process_Cmd','%powershell%','%Set-Content%-Path% -Value%pay%','Create a text file for the user to find'
UNION ALL SELECT 'T1105','lateral-movement','remote PSession','Remote File Copy','https://attack.mitre.org/techniques/T1105','Process_Cmd','%powershell%','%New-PSSession -ComputerName%','Launch Remote PSession'
UNION ALL SELECT 'T1105','lateral-movement','PSExec','Remote File Copy','https://attack.mitre.org/techniques/T1105','Process_Cmd','%powershell%','%PsExec%.exe%','using PsExec'
UNION ALL SELECT 'T1107','defense-evasion','Avoid logs','File Deletion','https://attack.mitre.org/techniques/T1107','Process_Cmd','%powershell%','%Clear-History;Clear%','Stop terminal from logging history'
UNION ALL SELECT 'T1108','defense-evasion','sleep','Redundant Access','https://attack.mitre.org/techniques/T1108','Process_Cmd','%powershell%','%sleep%','Pause all operations to avoid making noise'
UNION ALL SELECT 'T1113','collection','Screen Capture','Screen Capture','https://attack.mitre.org/techniques/T1113','Process_Cmd','%powershell%','%CopyFromScreen%','capture the contents of the screen'
UNION ALL SELECT 'T1115','collection','Copy Clipboard','Clipboard Data','https://attack.mitre.org/techniques/T1115','Process_Cmd','%powershell%','%Get-Clipboard%','copy the contents for the clipboard and print them'
UNION ALL SELECT 'T1124','discovery','Get System Time','System Time Discovery','https://attack.mitre.org/techniques/T1124','Process_Cmd','%powershell%','%Get-Date -UFormat%','get current system time (ISO 8601)'
UNION ALL SELECT 'T1135','discovery','View admin shares','Network Share Discovery','https://attack.mitre.org/techniques/T1135','Process_Cmd','%powershell%','%Get-SmbShare%','Network Share Discovery'
UNION ALL SELECT 'T1145','credential-access','Find private keys','Private Keys','https://attack.mitre.org/techniques/T1145','Process_Cmd','%powershell%','%.key%','Find private keys on the file system'
UNION ALL SELECT 'T1201','discovery','Password Policy','Password Policy Discovery','https://attack.mitre.org/techniques/T1201','Process_Cmd','%','%net accounts%','Password Policy Discovery'
UNION ALL SELECT 'T1201','discovery','Password Policy','Password Policy Discovery','https://attack.mitre.org/techniques/T1201','Process_Cmd','%net.exe','%accounts%','Password Policy Discovery'
UNION ALL SELECT 'T1214','credential-access','Credentials in Registry','Credentials in Registry','https://attack.mitre.org/techniques/T1214','Process_Cmd','%powershell%','%reg%query%HKCU%password%','Search for possible credentials stored in Registry'
UNION ALL SELECT 'T1214','credential-access','Credentials in Registry','Credentials in Registry','https://attack.mitre.org/techniques/T1214','Process_Cmd','%reg.exe','%query%HKCU%password%','Search for possible credentials stored in Registry'
UNION ALL SELECT 'T1219','Command and Control','AmmyAdmin Command and Control','Remote Access Tools','https://attack.mitre.org/techniques/T1219','Process_Cmd','%Ammyy%Admin.exe','%-connect%','AmmyAdmin can be used for Command and Control'
UNION ALL SELECT 'T1219','Command and Control','TeamViewer C2','Remote Access Tools','https://attack.mitre.org/techniques/T1219','Process_Cmd','%TeamViewer.exe','%assign%','TeamViewer can be used for Command and Control'
UNION ALL SELECT 'T1219','Command and Control','vncserver C2','Remote Access Tools','https://attack.mitre.org/techniques/T1219','Process_Cmd','%vncserver.exe','%-connect%','VNCServer can be used for Command and Control'
UNION ALL SELECT 'T1219','Command and Control','tvnserver C2','Remote Access Tools','https://attack.mitre.org/techniques/T1219','Process_Cmd','%tvnserver.exe','%-connect%','TVNServer can be used for Command and Control'
UNION ALL SELECT 'T1219','Command and Control','vncviewer C2','Remote Access Tools','https://attack.mitre.org/techniques/T1219','Process_Cmd','%vncviewer.exe','%-proxy%','vnc viewer can be used for Command and Control'
UNION ALL SELECT 'T1482','discovery','GetDomain','Domain Trust Discovery','https://attack.mitre.org/techniques/T1482','Process_Cmd','%powershell%','%powerview%Get-NetDomain%','Determine the Windows Domain of a computer'
UNION ALL SELECT 'T1492','impact','move a file to an executable','Stored Data Manipulation','https://attack.mitre.org/techniques/T1492','Process_Cmd','%powershell%','%mv%.%.exe%','move or rename an executable'
UNION ALL SELECT 'T1492','impact','move a file to an executable','Stored Data Manipulation','https://attack.mitre.org/techniques/T1492','Process_Cmd','%copy.exe','%.%.exe%','move or rename an executable'
UNION ALL SELECT 'T1497','discovery','Virtual or Real','Virtualization Sandbox Evasion','https://attack.mitre.org/techniques/T1497','Process_Cmd','%powershell%','%get-wmiobject win32_computersystem | fl model','Determine if the system is virtualized or physical'
UNION ALL SELECT 'T1518','discovery','Internet Explorer Version','Software Discovery','https://attack.mitre.org/techniques/T1518','Process_Cmd','%powershell%','%Get-ItemProperty%Internet Explorer%','Determine the version of Internet Explorer running'
)
--SELECT * FROM Mitre_Map
-- DETECTION BASED ON PROCESS_CMD
SELECT
xdr_data.meta_hostname Device_Name,
xdr_data.meta_os_type Device_Type,
xdr_data.unix_time Date_Time,
map.ID Mitre_ID,
map.Tactic Tactic,
map.Technique Technique,
map.SubTechnique SubTechnique,
map.condition || ' + ' || map.subcondition Hunt_Rule,
map.Mitre_Link,
map.Refrence,
xdr_data.username User_Name,
xdr_data.name Process_Name,
xdr_data.cmdline Cmd_Line,
xdr_data.sophos_PID SophosPID,
xdr_data.path Path_Name
FROM xdr_data
JOIN Mitre_map map ON
LOWER(xdr_data.name) LIKE LOWER(map.condition) AND
LOWER(xdr_data.cmdline) LIKE LOWER(map.SubCondition)
WHERE
LOWER(xdr_data.meta_hostname) LIKE LOWER('%$$Device Name$$%') AND
LOWER(map.Tactic) LIKE LOWER('%$$Tactic$$%') AND
LOWER(map.Technique) LIKE LOWER ('%$$Technique$$%') AND
LOWER(map.ID) LIKE LOWER ('%$$MITRE ATT&CK ID$$%') AND
map.Method = 'Process_Cmd'
)
SELECT
Mitre_ID,
Tactic,
Technique,
COUNT (Device_Name) Instances,
Process_Name,
Cmd_Line,
array_join(array_agg(DISTINCT User_Name), CHR(10)) User_List,
Hunt_Rule,
array_join(array_agg(DISTINCT Device_Name), CHR(10)) Device_LIST,
array_join(array_agg(DISTINCT Device_Type), CHR(10)) Device_Type,
MIN(Date_Time) First_Seen,
MAX(Date_Time) LAST_Seen,
SubTechnique,
array_join(array_agg(DISTINCT SophosPID), CHR(10)) SophosPID_LIST,
array_join(array_agg(DISTINCT Path_Name), CHR(10)) Path_Name_LIST,
MITRE_Link,
Refrence
FROM Detections
GROUP BY Mitre_ID, Tactic, Technique, Process_Name, Cmd_Line, Hunt_Rule, SubTechnique, User_Name, MITRE_Link, Refrence
)
SELECT * FROM Detection_Counts
WHERE LOWER(CAST (Mitre_ID AS VARCHAR) ||
CAST(Tactic AS VARCHAR) ||
CAST(Technique AS VARCHAR) ||
CAST(Instances AS VARCHAR) ||
CAST(Process_Name AS VARCHAR) ||
CAST(Cmd_Line AS VARCHAR) ||
CAST(User_List AS VARCHAR) ||
CAST(Hunt_Rule AS VARCHAR) ||
CAST(Device_List AS VARCHAR) ||
CAST(Device_Type AS VARCHAR) ||
CAST(First_Seen AS VARCHAR) ||
CAST(LAST_Seen AS VARCHAR) ||
CAST(SubTechnique AS VARCHAR) ||
CAST(SophosPID_LIST AS VARCHAR) ||
CAST(Path_Name_LIST AS VARCHAR) ||
CAST(MITRE_Link AS VARCHAR) ||
CAST(Refrence AS VARCHAR)
)
LIKE '%$$Any Field String Match$$%'
ORDER BY Instances DESC
DATA LAKE – Generic Search on Process Details
This looks across all process records in the data lake for a variety of things. It will auto group information based on Parent, Child, cmdline and user. (it does take into account the SHA256 of processes so even if the same process name is used it will not group if the SHA256 was different). We provide a number of variables to perform filtering.
|
Descriptive Name |
Variable type |
SQL variable name |
Value |
|
Command Line |
String |
$$Command Line$$ |
% |
|
Device |
String |
$$Device$$ |
% |
|
Ignore when > N Duplicates |
String |
$$Ignore when > N Duplicates$$ |
10000 |
|
Parent Process Name |
String |
$$Parent Process Name$$ |
% |
|
Process Name |
String |
$$Process Name$$ |
% |
|
User Name |
String |
$$User Name$$ |
% |
-- Generic Search
-- VARIABLE: $$Device$$ STRING
-- VARIABLE: $$Command Line$$ STRING
-- VARIABLE: $$Process Name$$ STRING
-- VARIABLE: $$Parent Process Name$$ STRING
-- VARIABLE: $$User Name$$ STRING
-- VARIABLE: $$Ignore when > N Duplicates$$ STRING
WITH Count_limt AS (
With DeDuplicate AS (
SELECT
xdr_data.meta_hostname epName,
xdr_data.meta_os_type Device_Type,
replace(replace(CAST(from_unixtime(xdr_data.time) AS VARCHAR),'.000','Z'),' ','T') Date_Time, -- add the T to help excel understand this is a date and time
CASE CAST(xdr_data.username > '' AS INT)
WHEN 1 THEN xdr_data.username --|| ' : ' || xdr_data.meta_username
ELSE xdr_data.meta_username
END User_Name,
xdr_data.parent_name Parent_Process_Name,
xdr_data.name Process_Name,
xdr_data.cmdline Cmd_Line,
xdr_data.sophos_pid SophosPID,
xdr_data.parent_sophos_pid Parent_SophosPID,
xdr_data.sha256 SHA256,
xdr_data.path Path_Name,
-- Additional Data
xdr_data.meta_ip_address Device_IP_ADDR,
xdr_data.meta_public_ip Public_IP,
xdr_data.meta_mac_address Device_MAC_Address,
xdr_data.meta_os_name Device_OS,
xdr_data.meta_os_platform Device_OS_Platform,
xdr_data.uid UID,
xdr_data.file_size File_Size,
xdr_data.ml_score ML_Score,
xdr_data.pua_score PUA_Score,
xdr_data.global_rep Global_Rep,
xdr_data.local_rep Local_Rep,
xdr_data.parent_path Parent_Path
FROM
xdr_data
WHERE
query_name = 'running_processes_windows_sophos' AND
-- SEARCH AND FILTER CRITERIA
UPPER(xdr_data.meta_hostname) LIKE UPPER('%$$Device$$%') AND
( UPPER(xdr_data.meta_username) LIKE UPPER('%$$User Name$$%') OR UPPER(xdr_data.username) LIKE UPPER('%$$User Name$$%') )AND
UPPER(xdr_data.parent_name) LIKE UPPER('%$$Parent Process Name$$%') AND
UPPER(xdr_data.name) LIKE UPPER('%$$Process Name$$%') AND
UPPER(xdr_data.cmdline) LIKE UPPER('%$$Command Line$$%')
)
SELECT
array_join(array_agg(DISTINCT epName||' : '||Device_OS), CHR(10)) Device_List,
COUNT(DISTINCT epName) Devices_With_Observable,
MIN(Date_Time) FirstSeen,
count(epName) Counter,
User_Name,
LOWER(Parent_Process_Name) Parent_Process_Name,
LOWER(Process_Name) Process_Name,
Cmd_Line,
array_join(array_agg(DISTINCT SophosPID), CHR(10)) SophosPID_LIST,
array_join(array_agg(DISTINCT Parent_SophosPID), CHR(10)) Parent_SophosPID_LIST,
SHA256,
Path_Name,
UID,
File_Size,
MIN(ML_Score) ML_Score,
MIN(PUA_Score) PUA_Score,
MIN(Global_Rep) Gloal_Rep,
MIN(Local_Rep) Local_Rep,
Parent_Path,
MAX(Date_Time) LastSeen
FROM DeDuplicate
GROUP BY
User_Name,
Parent_Process_Name,
Process_Name,
Cmd_Line,
SHA256,
Path_Name,
UID,
File_Size,
Parent_Path
)
SELECT *
FROM Count_limt
WHERE Counter <= $$Ignore when > N Duplicates$$
ORDER BY Devices_With_Observable DESC,Counter DESC
ENDPOINT – Decode Encoded powershells
This query will look for either ALL powershells or just encoded powershells by setting the first variable then for any encoded powershells it will perform a base64 decode of the encoded data.
|
Descriptive Name |
Variable type |
SQL variable name |
Value |
|
ALL powershell Commands (1) or just encoded powershell(0) |
String |
$$ALL powershell Commands (1) or just encoded powershell(0)$$ |
0 |
|
From N days ago |
String |
$$From N days ago$$ |
20 |
|
To N days ago |
String |
$$To N days ago$$ |
0 |
-- Powershell decoder
-- VARIABLE $$ALL powershell Commands (1) or just encoded powershell(0)$$ STRING
-- VARIABLE $$From N days ago$$ STRING
-- VARIABLE $$To N days ago$$ STRING
-- Go find processes that have command lines with encodedcommand data
WITH Encoded_Data AS (
SELECT
datetime(time,'unixepoch') DATE_TIME,
-- replace(pathname, rtrim(pathname, replace(pathname, '\', '')), '') ProcessName,
cmdline,
sophospid,
replace(substr(cmdline, instr(cmdline, ' -e')+2, length(cmdline)),rtrim(substr(cmdline, instr(cmdline, ' -e')+2, length(cmdline)),replace(substr(cmdline, instr(cmdline, ' -e')+2, length(cmdline)),' ','')),'') Encoded_Data,
sid,
parentSophosPID,
u.username
FROM sophos_process_journal spj LEFT JOIN users u ON spj.sid = u.uuid
WHERE pathname LIKE '%powershell%' AND eventtype = 0 AND time > strftime('%s','now','-$$From N days ago$$ days') and time < strftime('%s','now','-$$To N days ago$$ days')
AND ( cmdline LIKE '% -e %' OR cmdline LIKE '% -en %' OR cmdline LIKE '% -enc %' OR cmdline LIKE '% -enco %' OR cmdline LIKE '% -encod %' OR cmdline LIKE '% -encode %' OR cmdline LIKE '% -encoded %'
OR cmdline LIKE '% -encodedc %' OR cmdline LIKE '% -encodedco %' OR cmdline LIKE '% -encodedcom %' OR cmdline LIKE '% -encodedcomm %' OR cmdline LIKE '% -encodedcomma %'
OR cmdline LIKE '% -encocodedcomman %' OR cmdline LIKE '% -encodedcommand %' OR cmdline LIKE '% -ec %')
)
-- With a table of process information that includes encoded data in the command lines, we will convert the base64 encoding to text
-- WARNING this is ugly as we have to handle embedded NULLS and to 'see' them we have to convert to HEX and back to characters
SELECT
CAST(ed.DATE_TIME AS TEXT) Date_Time,
-- CAST(ed.ProcessName AS TEXT) ProcessName,
CAST(ed.cmdline AS TEXT) CmdLine,
CAST(ed.sophospid AS TEXT) SophosPID,
--------------- CONVERT ENCODED BASE 64 DATA FROM A POWERSHELL TO DECODED DATA
CAST ( (
WITH RECURSIVE test(c,cur) AS (
select '', (replace(hex(from_base64(ed.Encoded_Data) ),'00','') )
UNION ALL
select c || char((case substr(cur,1,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,1,1) end)*16 +
(case substr(cur,2,1) when 'A' then 10 when 'B' then 11 when 'C' then 12 when 'D' then 13 when 'E' then 14 when 'F' then 15 else substr(cur,2,1) end)), substr(cur,3)
FROM test where length(cur)>0
)
SELECT c Decoded_value FROM test WHERE length (cur) = 0
) AS TEXT) Decoded_base64_section,
--------------- END CONVERSION
CAST(ed.sid AS TEXT) SID,
CAST(ed.parentSophosPID AS TEXT) ParentSophosPID,
CAST(ed.username AS TEXT) Username
FROM Encoded_Data ed
WHERE length(Decoded_base64_section) > 0
UNION ALL
SELECT
datetime(time,'unixepoch') DATE_TIME,
-- replace(pathname, rtrim(pathname, replace(pathname, '\', '')), '') ProcessName,
cmdline,
sophospid,
'' Encoded_Data,
sid,
parentSophosPID,
u.username
FROM sophos_process_journal spj LEFT JOIN users u ON spj.sid = u.uuid
WHERE $$ALL powershell Commands (1) or just encoded powershell(0)$$ = 1 AND
pathname LIKE '%powershell%' AND eventtype = 0 AND time > strftime('%s','now','-$$From N days ago$$ days') and time < strftime('%s','now','-$$To N days ago$$ days')
AND NOT ( cmdline LIKE '% -e %' OR cmdline LIKE '% -en %' OR cmdline LIKE '% -enc %' OR cmdline LIKE '% -enco %' OR cmdline LIKE '% -encod %' OR cmdline LIKE '% -encode %' OR cmdline LIKE '% -encoded %'
OR cmdline LIKE '% -encodedc %' OR cmdline LIKE '% -encodedco %' OR cmdline LIKE '% -encodedcom %' OR cmdline LIKE '% -encodedcomm %' OR cmdline LIKE '% -encodedcomma %'
OR cmdline LIKE '% -encocodedcomman %' OR cmdline LIKE '% -encodedcommand %')
