Technical training on XDR Data lake with Queries used

10 Mar 2021

For query assistance, please see the following Best Practices guide

Watch the video from the technical demo where we cover how to use Live Discover datalake queries.

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