Under Review

Hunting in the Data lake then pivoting to the device for details

So with this query you can see MITRE ATT&CK classifications for a few hundred TTPs

/**************************************************************************\
| This query was derived from examination of the CALDERA, Atomic RedTeam   |
| and other available online resources as of April 2021                    |
\**************************************************************************/
-- Common MITRE ATT&CK TTPs (Caldera Map)
-- VARIABLE  $$device_name$$                  STRING
-- VARIABLE  $$mitre_id$$                     STRING
-- VARIABLE  $$tactic name$$                  STRING
-- VARIABLE  $$technique name$$               STRING
-- VARIABLE  $$Number of hours to search$$    STRING
-- VARIABLE  $$Verbosity 0-9$$                STRING  VERBOSITY SCORE OF 10 indicates it has not been evaluated

-- Provide a map to the MITRE INFO
WITH mitre_techniques(id, subid, tactic, technique, subtechnique) AS (
    VALUES
	    -- COLLECTION
        ('T1005', '',    'collection', 'Data from Local System', ''),
        ('T1056', '002', 'collection', 'Input Capture', 'GUI Input Capture'),
        ('T1059', '003', 'collection', 'Command and Scripting Interpreter', 'Windows Command Shell'),
        ('T1059', '008', 'collection', 'Command and Scripting Interpreter', 'Network Device CLI'),
        ('T1560', '',    'collection', 'Archive Collected Data', ''),
        ('T1074', '',    'collection', 'Data Staged', ''),
        ('T1074', '001', 'collection', 'Data Staged', 'Local Data Staging'),
        ('T1074', '004', 'collection', 'Data Staged', 'Local Data Staging'),
        ('T1113', '',    'collection', 'Screen Capture', ''),
        ('T1114', '001', 'collection', 'Email Collection', 'Local Email Collection'),
        ('T1115', '',    'collection', 'Clipboard Data', ''),
        ('T1119', '',    'collection', 'Automated Collection', ''),
        ('T1123', '',    'collection', 'Audio Capture', ''),
        -- COMMAND AND CONTROL
        ('T1071', '001', 'command_and_control', 'Application Layer Protocol', 'Web Protocols'),
        ('T1071', '004', 'command_and_control', 'Application Layer Protocol', 'DNS'),
        ('T1090', '',    'command_and_control', 'Proxy', ''),
        ('T1090', '001', 'command_and_control', 'Proxy', 'Internal Proxy'),
        ('T1105', '',    'command_and_control', 'Ingress Tool Transfer', ''),
        ('T1219', '',    'command_and_control', 'Remote Access Software', ''),
        ('T1571', '',    'command_and_control', 'Non-Standard Port', ''),
        -- CREDENTIAL ACCESS
        ('T1003', '',    'credential_access', 'Credential Dumping', ''),
        ('T1003', '001', 'credential_access', 'Credential Dumping', 'LSASS Memory'),
        ('T1003', '002', 'credential_access', 'Credential Dumping', 'Credentials in Registry'),
        ('T1110', '001', 'credential_access', 'Brute Force', 'Password Guessing'),
        ('T1110', '003', 'credential_access', 'Brute Force', 'Password Spraying'),
        ('T1040', '',    'credential_access', 'Network Sniffing', ''),
        ('T1552', '001', 'credential_access', 'Unsecured Credentials', 'Credentials in Files'),
        ('T1552', '002', 'credential_access', 'Unsecured Credentials', 'Credentials in Registry'),
        ('T1552', '004', 'credential_access', 'Unsecured Credentials', 'Private Keys'),
        ('T1552', '006', 'credential_access', 'Unsecured Credentials', 'Group Policy Prefrences'),
        ('T1555', '',    'credential_access', 'Credentials from Password Stores', ''),  
        ('T1555', '003', 'credential_access', 'Credentials from Password Stores', 'Credentials from Web Browsers'),
        ('T1558', '001', 'credential_access', 'Steal or Forge Kerberos Tickets', 'Golden Ticket'),
        ('T1558', '003', 'credential_access', 'Steal or Forge Kerberos Tickets', 'Kerberoasting'),
        -- DEFENSE EVASION
        ('T1006', '',    'defense_evasion', 'Direct Volume Accesss', ''),
        ('T1027', '',    'defense_evasion', 'Obfuscated Files or Information', ''),
        ('T1027', '004', 'defense_evasion', 'Obfuscated Files or Information', 'Compile After Delivery'),
        ('T1036', '003', 'defense_evasion', 'Masquerading', 'Rename System Utilities'),
        ('T1055', '001', 'defense_evasion', 'Process Injection', 'Dynamic-Link Library Injection'),
        ('T1055', '002', 'defense_evasion', 'Process Injection', 'Portable Executable Injection'),
        ('T1055', '012', 'defense_evasion', 'Process Injection', 'Process Hollowing'),
        ('T1070', '001', 'defense_evasion', 'Indicator Removal on Host', 'Clear Windows Event Logs'),
        ('T1070', '003', 'defense_evasion', 'Indicator Removal on Host', 'Clear Command History'),
        ('T1070', '004', 'defense_evasion', 'Indicator Removal on Host', 'File Deletion'),
        ('T1070', '005', 'defense_evasion', 'Indicator Removal on Host', 'Network Share Connection Removal'),
        ('T1070', '006', 'defense_evasion', 'Indicator Removal on Host', 'Timestomp'),
        ('T1108', '',    'defense_evasion', 'Redundant Access', ''),
        ('T1112', '',    'defense_evasion', 'Modify Registry', ''),
        ('T1134', '004', 'defense_evasion', 'Access Token Manipulation', 'Parent PID Spoofing'),
        ('T1135', '',    'defense_evasion', 'Network Share Discovery', ''),
        ('T1218', '',    'defense_evasion', 'Signed Binary Proxy Executioin', ''),
        ('T1218', '001', 'defense_evasion', 'Signed Binary Proxy Executioin', 'Compiled HTML File'),
        ('T1218', '004', 'defense_evasion', 'Signed Binary Proxy Execution', 'InstallUtil'),
        ('T1218', '005', 'defense_evasion', 'Signed Binary Proxy Execution', 'mshta'),
        ('T1218', '008', 'defense_evasion', 'Signed Binary Proxy Executioin', 'odbconf'),
        ('T1218', '011', 'defense_evasion', 'Rundll32', 'Execution'),
        ('T1553', '004', 'defense_evasion', 'Subvert Trust Controls', 'Install Root Certificate'),
        ('T1562', '001', 'defense_evasion', 'Impair Defenses', 'Disable or Modify Tools'),
        ('T1562', '002', 'defense_evasion', 'Impair Defenses', 'Disable Windows Event Logging'),
        ('T1562', '004', 'defense_evasion', 'Impair Defenses', 'Disable or Modify System Firewall'),
        ('T1564', '003', 'defense_evasion', 'Hide Artifacts', 'Hidden Window'),
        ('T1564', '004', 'defense_evasion', 'Hide Artifacts', 'NTFS File Attributes'),
        ('T1574', '012', 'defense_evasion', 'Hijack Execution Flow', 'COR_PROFILER'),
        -- DISCOVERY
        ('T1007', '',    'discovery', 'System Service Discovery', ''),
        ('T1010', '',    'discovery', 'Application Window Discovery', ''),
        ('T1012', '',    'discovery', 'Query Registry', ''),
        ('T1016', '',    'discovery', 'System Network Configuration Discovery', ''),
        ('T1018', '',    'discovery', 'Remote System Discovery', ''),
        ('T1033', '',    'discovery', 'System Owner/User Discovery', ''),
        ('T1046', '',    'discovery', 'Network Service Scanning', ''),
        ('T1049', '',    'discovery', 'System Network Connections Discovery', ''),
        ('T1057', '',    'discovery', 'Process Discovery', ''),
        ('T1063', '',    'discovery', 'Security Software Discovery', ''),
        ('T1069', '',    'discovery', 'Permission Groups Discovery', ''),
        ('T1069', '001', 'discovery', 'Permission Groups Discovery', 'Local Groups'),
        ('T1069', '002', 'discovery', 'Permission Groups Discovery', 'Domain Groups'),
        ('T1082', '',    'discovery', 'System Information Discovery', ''),
        ('T1083', '',    'discovery', 'File and Directory Discovery', ''),
        ('T1087', '001', 'discovery', 'Account Discovery', 'Local Account'),
        ('T1087', '002', 'discovery', 'Account Discovery', 'Domain Account'),
        ('T1120', '',    'discovery', 'Peripheral Device Discovery', ''),
        ('T1124', '',    'discovery', 'System Time Discovery', ''),
        ('T1201', '',    'discovery', 'Password Policy Discovery', ''),
        ('T1217', '',    'discovery', 'Browser Bookmark Discovery', ''),
        ('T1482', '',    'discovery', 'Domain Trust Discovery', ''),
        ('T1497', '001', 'discovery', 'Virtualization/Sandbox Evasion', 'System Checks'),
        ('T1518', '',    'discovery', 'Software Discovery', ''),
        ('T1518', '001', 'discovery', 'Software Discovery', 'Security Software Discovery'),
        -- EXECUTION
        ('T1047', '',    'execution', 'Windows Management Instrumentation', ''),
        ('T1059', '001', 'execution', 'Command and Scripting Interpreter', 'PowerShell'),
        ('T1059', '005', 'execution', 'Command and Scripting Interpreter', 'Visual Basic'),
        ('T1059', '',    'execution', 'Command and Scripting Interpreter', ''),
        ('T1072', '',    'execution', 'Software Deployment Tools', ''),
        ('T1204', '002', 'execution', 'User Execution', 'Malicious File'),
        ('T1546', '008', 'execution', 'Event Triggered Execution', 'Accessibility Features'),
        -- EXFILTRATION
        ('T1002', '',    'exfiltration', 'Archive Collected Data', ''),
        ('T1041', '',    'exfiltration', 'Exfiltration Over Command and Control Channel', ''),
        ('T1048', '003', 'exfiltration', 'Exfiltration Over Alternate Protocol', 'Exfiltration Over Unencrypted/Obsfucated Non-C2 Protocol'),
        ('T1560', '001', 'exfiltration', 'Archive Collected Data', 'Archive via Utility'),
        ('T1020', '',    'exfiltration', 'Automated Exfiltration', ''),
        -- INITIAL ACCESS
        ('T1566', '001', 'initial_access', 'Phishing', 'Spearphishing Attachment'),
        -- IMPACT
        ('T1485', '',    'impact', 'Data Destruction',''),
        ('T1490', '',    'impact', 'Inhibit System Recovery', ''),
        ('T1491', '',    'impact', 'Defacement', ''),
        ('T1565', '001', 'impact', 'Data Manipulation','Stored Data Manipulation'),
        -- LATERAL MOVEMENT
        ('T1021', '002', 'lateral_movement', 'Remote Services', 'SMB/Windows Admin Shares'),
        ('T1021', '003', 'lateral-movement', 'Remote Services', 'Distributed Component Object'),
        ('T1021', '006', 'lateral-movement', 'Remote Services', 'Windows Remote Management'),
        -- PERSISTENCE
        ('T1031', '',    'persistence', 'Modify Existing Service', ''),
        ('T1050', '',    'persistence', 'New Service', ''),
        ('T1098', '',    'persistence', 'Account Manipulation', ''),
        ('T1136', '001', 'persistence', 'Create Account', 'Local Account'),
        ('T1136', '002', 'persistence', 'Create Account', 'Domain Account'),
        ('T1505', '002', 'persistence', 'Server Software Component', 'Transport Agent'),
        ('T1547', '004', 'persistence', 'Boot or Logon Autostart Executioin', 'Winlogon Helper DLL'),
        -- PRIVELEGE ESCALATION
        ('T1548', '002', 'privilege_escalation', 'Abuse Elevation Control Mechanism', 'Bypass User Account Control')
),

-- Provide Detection rules based on CMD_LINE evaluations EVERYTHING HAS TO BE lowercase
-- ALL RULES are for ANY Process's CMD_LINE
mitre_methods(noise_level, id, subid, process, indicator) AS (
    VALUES
      -- T1000-1099
        (0,'T1002', '',    '%', '%compress-archive%'),
        (0,'T1003', '',    '%', '%get-netcomputer%'),
        (0,'T1003', '',    '%', '%creds.dmp%'),
        (0,'T1003', '',    '%', '%invoke-mimikatz%'),
        (0,'T1003', '',    '%', '%download%powersploit%'),
        (0,'T1003', '',    '%', '%exfiltration%'),
        (0,'T1003', '',    '%', '%nppspy%'),
        (0,'T1003', '',    '%', '%hklm%sam%'),
        (6,'T1003', '',    '%', '%hklm%security%'),
        (7,'T1003', '',    '%', '%hklm%system%'),
        (0,'T1003', '001', '%', '%procdump%lsass%'),
        (0,'T1003', '001', '%', '%minidump%lsass%'),
        (0,'T1003', '001', 'rundll32.exe', '%comsvcs.dll%'),
        (0,'T1003', '001', '%', '%dumpcreds%'),
        (0,'T1003', '001', '%', '%ma lsass.exe%'),
        (0,'T1003', '002', '%', '%reg query hklm%f password%t reg_sz% '),
        (0,'T1003', '002', 'reg.exe', '%query hklm%f password%t reg_sz% '),
        (0,'T1003', '002', '%', '%webrequest%empire%dump% '),
        (0,'T1005', '',    '%', '%get-childitem c:%users -recurse -include%'),
        (0,'T1006', '',    '%', '%io.filestream%format-hex%'),
        (5,'T1007', '',    '%', '%get-service%'),
        (0,'T1010', '',    '%', '%get-process%mainwindowtitle%shell.application%'),
        (0,'T1012', '',    '%', '%get-itemproperty%hklm:%windows%currentversion%'),
        (9,'T1016', '',    'ipconfig.exe', '%'),
        (0,'T1016', '',    '%', '%nbtstat -n%'),
        (0,'T1016', '',    'nbstat.exe', '%-n%'),
        (0,'T1016', '',    '%', '%wifi.ps1 -scan%'),
        (9,'T1016', '',    '%', '%-scan%'),
        (0,'T1016', '',    '%', '%-find%'),
        (0,'T1016', '',    '%', '%foreach%port%'),
        (0,'T1016', '',    '%', '%wifi.ps1 -pref%'),
        (9,'T1016', '',    '%', '%ping %'),  -- simply way too noisy 
        (0,'T1018', '',    '%', '%powerview.ps1%get-domaincomputer%'),
        (0,'T1018', '',    '%', '%nltest %dclist%'),
        (0,'T1018', '',    'nltest.exe', '%dclist%'),
        (0,'T1018', '',    '%', '%arp -a%'),
        (6,'T1018', '',    'arp.exe', '%-a%'),
        (0,'T1018', '',    '%', '%nltest %dclist%'),
        (0,'T1018', '',    'nltest.exe', '%dclist%'),
        (0,'T1018', '',    '%', '%nslookup%mail%'),
        (0,'T1018', '',    'nslookup.exe', '%mail%'),
        (0,'T1018', '',    '%', '%nslookup #{remote.host.ip}%'),
        (0,'T1018', '',    'nslookup.exe', '%remote.host.ip%'),
        (0,'T1018', '',    '%', '%nbtstat -a #{remote.host.ip}%'),
        (0,'T1018', '',    'nbstat.exe', '%remote.host.ip%'),
        (0,'T1018', '',    '%', '%ipconfig%findstr%'),
        (0,'T1018', '',    '%', '%get-netcomputer%'),
        (0,'T1018', '',    '%', '%nltest%dsgetdc%'),
        (0,'T1018', '',    'nltest%', '%dsgetdc%'),
        (0,'T1018', '',    '%', '%get-domaincomputer%'),
        (0,'T1020', '',    '%', '%application%octet-stream%'),
        (0,'T1021', '002', '%', '%net use%/user:%n%'),
        (0,'T1021', '002', '%', '%new-psdrive%target%'),
        (0,'T1021', '002', 'net.exe', '%use%/user:%n%'),
        (0,'T1021', '003', '%', '%createinstance%executeshellcommand%'),
        (0,'T1021', '003', '%', '%gettypefromprogid%executeshellcommand%'),
        (0,'T1021', '006', '%', '%enable-psremoting%'),
        (0,'T1021', '006', '%', '%evil-winrm%admin%-p%'),
        (0,'T1021', '006', '%', '%evil-winrm%'),
        (0,'T1021', '006', '%', '%invoke-command%computername%script%'),
        (0,'T1027', '',    '%', '%base64%'),
        (0,'T1027', '',    '%', '%unicode%'),
        (0,'T1027', '',    '%', '%encode%'),
        (0,'T1027', '',    '%', '%-ec %'),
        (0,'T1027', '',    '%', '%set-itemproperty%hkcu%iex%'),
        (0,'T1027', '',    '%', '%set-itemproperty%hklm%iex%'),
        (0,'T1027', '004', '%', '%invoke-expressiion%compile%'),
        (0,'T1031', '',    '%', '%get-service -name%stop-service%copy-item -path%'),
        (0,'T1033', '',    '%', '%get-netuser -admincount%'),
        (5,'T1033', '',    '%', '%whoami%'),
        (0,'T1033', '',    '%', '%$env:username%'),
        (0,'T1033', '',    '%', '%get-netuser -spn%'),
        (0,'T1033', '',    '%', '%get-netuser%'),
        (0,'T1033', '',    '%', '%userhunter%stealth%'),
        (0,'T1036', '003', '%', '%copy%windows%wow%'),
        (0,'T1036', '003', '%', '%copy%windows%system%'),
        (0,'T1036', '003', '%', '%start-process%exe%.id%'),
        (0,'T1040', '',    '%', '%new-neteventsession -name%-capturemode%'),
        (0,'T1040', '',    '%', '%pcap%tcp%'),
        (7,'T1041', '',    '%', '%file%upload%'),
        (0,'T1046', '',    'nmap.exe', '%sv -p'),
        (0,'T1046', '',    '%', '%nmap -sv -p'),
        (0,'T1046', '',    '%', '%get-netipconfiguration%'),
        (0,'T1046', '',    '%', '%nmap%'),
        (0,'T1046', '',    'nmap.exe', '%'),
        (0,'T1046', '',    '%', '%scan%ports%'),
        (9,'T1047', '',    '%', '%wmic%'),  -- likely too noisy to be of value
        (8,'T1047', '',    '%', '%wmic%process%get%'),
        (0,'T1047', '',    '%', 'wmic %node:%user:%password:%process call create%'),
        (0,'T1047', '',    'wmic.exe', '%node%password%process call create%'),
        (0,'T1047', '',    'wmic.exe', '%process get%executablepath%name%processid%parentprocessid%'),
        (0,'T1047', '',    'wmic.exe', '%node:%user:%password%process call create% '),
        (0,'T1047', '',    '%', '%wmic %node:%user:%password%process call create% '),
        (0,'T1048', '003',    '%', '%system.net.networkinginformation.ping% '),
        (4,'T1048', '003',    '%', '%send%.%.%.%,%,%'),
        (0,'T1048', '003',    '%', '%ping.send(%)%'),
        (0,'T1048', '003',    '%', '%uri%method%post%body%'),
        (0,'T1049', '',    '%', '%netstat -ano%get-nettcpconnection%'),
        (0,'T1049', '',    'netstat.exe', '%-ano%'),
        (0,'T1049', '',    '%', '%get-nettcpconnection%'),
        (0,'T1049', '',    '%', '%invoke-%scanner%'),
        (0,'T1049', '',    '%', '%invoke-kerber%'),
        (0,'T1049', '',    '%', '%find-%share%'),
        (0,'T1050', '',    '%', '%new-service%'),
        (0,'T1050', '',    'sc.exe', '%create service%'),
        (3,'T1050', '',    '%', '%sc%create%service%'),
        (0,'T1055', '001',    '%', '%odbcconf.exe %s %a%'),
        (0,'T1055', '001',    'odbcconf.exe', '%s %a%'),
        (0,'T1055', '002',    '%', '%mavinject.exe%'),
        (0,'T1055', '002',    'mavinject.exe', '%'),
        (0,'T1055', '002', '%', '%invoke-reflectivepeinjection.ps1%'),
        (0,'T1055', '012', '%', '%get-process%hollow%'), 
        (0,'T1056', '002', '%', '%promptforcredential%'), 
        (0,'T1057', '',    '%', '%owner%%get-process%'),
        (0,'T1057', '',    '%', '%get-process%lsass%'),
        (0,'T1057', '',    '%', '%owner%user%get-process%'),
        (0,'T1057', '',    '%', '%get-process%'),
        (7,'T1057', '',    '%', '%tasklist %m%'),
        (7,'T1057', '',    'tasklist.exe', '%tasklist %m%'),
        (0,'T1057', '',    '%', '%pslist.exe%'),
        (0,'T1057', '',    'pslist.exe', '%'),
        (0,'T1057', '',    '%', '%gwmi win32_process%'),
        (0,'T1057', '',    '%', '%processname%-e%lsass%'),
        (0,'T1057', '',    '%', '%get-process >>%'),
        (0,'T1059', '',    '%', '%server=%%url=%system.net.webclient%'),
        (0,'T1059', '',    '%', '%.exe -nonewwindow -passthru -credential%'),
        (0,'T1059', '',    '%', '%-nonewwindow -passthru -credential%'),
        (0,'T1059', '',    '%', '%echo $(get-uac)%'),
        (0,'T1059', '001', '%', '%https:%bit.ly%'),
        (0,'T1059', '001', '%', '%mimikatz%'),
        (0,'T1059', '001', '%', '%copy-item%powershell%'),
        (0,'T1059', '001', '%', '%new-item%pstools%'),
        (0,'T1059', '001', '%', '%get-process -name%powershell%stop-process%'),
        (0,'T1059', '001', '%', '%wifi.ps1 -off%'),
        (0,'T1059', '001', '%', '%copy-item%powershell.exe%'), 
        (0,'T1059', '001', '%', '%iex%downloadstring%'),
        (0,'T1059', '001', '%', '%set-itemproperty -path hklm:%software%policies%microsoft%windows%powershell -name executionpolicy -value bypass%'),
        (0,'T1059', '001', '%', '%emulate-administrator-tasks.ps1%'),
        (0,'T1059', '001', '%', '%get-process%ncat%'),
        (0,'T1059', '001', 'ncat.exe', '%get-process%ncat%'),
        (0,'T1059', '001', 'cmd.exe', '%net user%'),
        (0,'T1059', '001', 'cmd.exe', '%whoami%'),
        (0,'T1059', '001', 'cmd.exe', '%netstat -ano%'),
        (0,'T1059', '001', '%', '%powershell%iex%'),
        (0,'T1059', '001', 'powershell.exe', '%iex%'),
        (0,'T1059', '001', '%', '%new.webclient%'),
        (0,'T1059', '001', '%', '%downloadstring%'),
        (0,'T1059', '001', '%', '%wscript.shell%'),
        (0,'T1059', '001', '%', '%https%invoke%'),
        (0,'T1059', '001', '%', '%out-athpowershell%'),
        (0,'T1059', '001', '%', '%encodedcommandparam%'),
        (0,'T1059', '001', '%', '%useencodedarguments%'),
        (0,'T1059', '001', '%', '%-execute%'),
        (0,'T1059', '001', '%', '%reg.exe%add%base64%'),
        (0,'T1059', '001', 'reg.exe', '%add%base64%'),
        (0,'T1059', '001', '%', '%new-object%net.webclient%'),
        (0,'T1059', '001', '%', '%add-content%stream%'),
        (0,'T1059', '001', '%', '%powershell%-version%2%command%'),
        (0,'T1059', '001', 'powershell.exe', '%-version 2%'),
        (0,'T1059', '001', '%', '%new-pssession%'),
        (0,'T1059', '001', '%', '%new-pssession%computername%'),
        (5,'T1059', '001', '%', '%powershell.exe -c%get-wmiobject -class win32_operatingsystem%'),
        (0,'T1059', '001', '%', '%get-wmiobject -class win32_operatingsystem%'),
        (0,'T1059', '001', '%', '%get-localuser%'),
        (0,'T1059', '001', '%', '%get-itemproperty%registry%'),
        (0,'T1059', '001', '%', '%get-process%ncat%'),
        (0,'T1059', '003', 'cmd.exe', '%net user%'),
        (0,'T1059', '003', '%', '%start-process%.bat%'),
        (0,'T1059', '005', '%', '%cscript%vbs%>%'),
        (9,'T1059', '005', '%', '%cscript%vbs%'),
        (9,'T1059', '005', 'cscript.exe', '%.vbs%'),
        (0,'T1059', '008', 'net.exe', '%user%'),
        (0,'T1063', '',    '%', '%wmic%namespace%antivirusproduct%'),
        (0,'T1063', '',    'wmic.exe', '%antivirusproduct%'),
        (0,'T1063', '',    '%', '%securitycenter%antivirusproduct%'),
        (0,'T1063', '',    'wmic.exe', '%securitycenter%antivirusproduct%'),
        (0,'T1069', '',    '%', '%gpresult %r%'),
        (0,'T1069', '',    '%', '%get-wmiobject -class win32_useraccount%'),
        (0,'T1069', '', '%', '%powerview.ps1%'),
        (0,'T1069', '001', 'gpresult.exe', '%r%'),
        (0,'T1069', '002', '%', '%get-aduser%'),
        (0,'T1069', '002', '%', '%find%computeradmin%'),
        (0,'T1069', '002', '%', '%invoke-enumeratelocaladmin%'),
        (0,'T1069', '002', '%', '%get-adprincipalgroupmembership%'),
        (0,'T1070', '001', '%', '%clear%log%'),
        (0,'T1070', '003', '%', '%set-psreadlineoption%savenothing%'),
        (0,'T1070', '003', '%', '%remove-item%historysavepath%'),
        (0,'T1070', '003', '%', '%clear%history%'),
        (0,'T1070', '004', '%', '%remove-item%.log%'),
        (0,'T1070', '004', '%', '%remove-item%.pf%'),
        (0,'T1070', '004', '%', '%remove-item%recurse%'),
        (0,'T1070', '004', '%', '%delete%.log%'),
        (0,'T1070', '004', '%', '%timeout%del%'),
        (0,'T1070', '005', '%', '%remove-smbshare%'),
        (0,'T1070', '005', '%', '%remove-fileshare%'),
        (0,'T1070', '006', '%', '%get-childitem%lastwritetime%'), 
        (0,'T1070', '006', '%', '%get-childitem%lastaccesstime%'), 
        (0,'T1070', '006', '%', '%get-childitem%creationtime%'), 
        (0,'T1070', '006', '%', '%import%timestomp%'), 
        (0,'T1070', '006', '%', '%timestomp%dest%'), 
        (0,'T1071', '001', '%', '%invoke-webrequest%'),
        (0,'T1071', '004', '%', '%dns%domain%jitter%'), -- perhaps over fit
        (0,'T1071', '004', '%', '%dns%beacon%'),
        (0,'T1071', '004', '%', '%dns%query%'),
        (3,'T1072', '',    '%', '%powershell%-enc%'),
        (0,'T1074', '',    '%', '%copy-item%'),
        (0,'T1074', '',    '%', '%new-item%-path%-itemtype%'),
        (0,'T1074', '001', '%', '%invoke-webrequest%discovery.bat%'), -- tight fit for atomic
        (0,'T1074', '001', '%', '%new-item%directory%'), 
        (0,'T1074', '001', '%', '%copy-item%password%'),        
        (0,'T1074', '001', '%', '%copy-item%zip%'),        
        (0,'T1074', '001', '%', '%copy-item%inbox%'),        
        (0,'T1074', '004', '%', '%compress-archive%destinationpath%'), 
        (0,'T1082', '',    '%', '%psversiontable%'),
        (0,'T1082', '',    '%', '%osversion.version%'),
        (0,'T1083', '',    '%', '%get-childitem -path #{host.system.path}%'),
        (0,'T1083', '',    '%', '%-c pwd%'), 
        (0,'T1083', '',    '%', '%ls%recurse%'), 
        (0,'T1083', '',    '%', '%get-children%recurse%'), 
        (0,'T1083', '',    '%', '%gci%recurse%'), 
        (0,'T1087', '001', '%', '%query user%'),
        (0,'T1087', '001', '%', '%net user%'),
        (0,'T1087', '001', 'net.exe', '%user%'),
        (0,'T1087', '001', '%', '%get-localuser%'),
        (0,'T1087', '001', '%', '%get-localgroupmember%'),
        (0,'T1087', '001', '%', '%cmdkey.exe%list%'),
        (0,'T1087', '001', 'cmdkey.exe', '%list%'),
        (0,'T1087', '001', '%', '%get-children%users%'),
        (0,'T1087', '001', '%', '%get-localgroup%'),
        (0,'T1087', '001', '%', '%net localgroup%'),
        (0,'T1087', '001', 'net.exe', '%localgroup%'),
        (0,'T1087', '001', '%', '%get-wmiobject%win32_useraccount%'),
        (0,'T1087', '002', '%', '%user%domain%'),
        (0,'T1087', '002', '%', '%net user #{domain.user.name} %domain%'),
        (0,'T1087', '002', 'net.exe', '%user.name%domain%'),
        (0,'T1087', '002', '%', 'net user %domain'),
        (0,'T1087', '002', 'net.exe', '%user %domain%'),
        (0,'T1087', '002', '%', '%get-localgroupmember%users%'),
        (0,'T1087', '002', '%', '%get-aduser -filter *%'),
        (0,'T1087', '002', '%', '%invoke-expression%recon%'),
        (0,'T1090', '',    'netsh.exe', '%winhttp%set%proxy%'),
        (0,'T1090', '',    'netsh.exe', '%portproxy%'),
        (0,'T1090', '001', '%', '%proxy%127.0.0.1%'),
        (0,'T1098', '', '%', '%set-localuser%'),
      -- T1100-1199
        (0,'T1105', '',    '%', '%certutil%verifyctl%move-item%destination%'), 
        (0,'T1105', '',    'certutil.exe', '%verifyctl%-f%'), 
        (0,'T1105', '',    '%', '%set-content%-path% -value%hacked%'),
        (0,'T1105', '',    '%', '%set-content%-path% -value%pay%'),
        (0,'T1105', '',    '%', '%new-pssession -computername%'),
        (0,'T1105', '',    '%', '%psexec%.exe%'),
        (0,'T1105', '',    '%', '%msiexec%powershellcore%'),
        (0,'T1105', '',    'msiexec.exe', '%powershellcore%'),
        (4,'T1105', '',    '%', '%download%powershell%'),
        (0,'T1105', '',    '%', '%download%pstools%'),
        (0,'T1105', '',    '%', '%new-object%webclient%'),
        (0,'T1105', '',    '%', '%start-process%socket%'),
        (7,'T1108', '',    '%', '%sleep%'),
        (0,'T1110', '001', '%', '%foreach%password%'),
        (0,'T1110', '003', '%', '%ldap%foreach%password%'),
        (0,'T1110', '003', '%', '%passwordspray%'),
        (0,'T1112', '',    '%', '%set-executionpolicy%bypass%'),
        (0,'T1112', '',    '%', '%key=%zonemap%'),
        (0,'T1112', '',    '%', '%new-itemproperty%http%2%'),
        (0,'T1112', '',    '%', '%new-itemproperty%internet settings%name%value%'),
        (0,'T1113', '',    '%', '%copyfromscreen%'),
        (0,'T1113', '',    'psr.exe', '%output%'),
        (4,'T1113', '',    '%', '%screenshot%'),
        (0,'T1114', '001', '%', '%get-inbox%mail%'), -- over fit
        (0,'T1115', '',    '%', '%get-clipboard%'),
        (0,'T1115', '',    '%', '%getclipboard%'),
        (0,'T1119', '',    '%', '%new-item%recurse%'),
        (5,'T1119', '',    '%', '%get-service%'),
        (0,'T1119', '',    '%', '%get-childitem%'),
        (0,'T1119', '',    '%', '%get-process%'),
        (0,'T1120', '',    '%', '%get-wmiobject%win32_pnpentity%'),
        (0,'T1123', '',    '%', '%windowsaudiodevice-powershell-cmdlet%'),  -- dependent on specific git repo tool
        (8,'T1124', '',    '%', '%get-date -uformat%'),
        (9,'T1124', '',    '%', '%get-date%'),
        (0,'T1134', '004', '%', '%start-athprocessunderspecificparent%'), 
        (0,'T1135', '',    '%', '%get-smbshare%'),
        (0,'T1135', '',    '%', '%net%view%localhost%'),
        (0,'T1135', '',    '%', '%smbshare%localhost%'),
        (0,'T1135', '',    '%', '%iex%iwr%'),
        (0,'T1136', '001', '%', '%new-localuser%'),
        (0,'T1136', '002', '%', '%samaccountname%password%'),
        (0,'T1136', '002', '%', '%automation.pscredential%'),
        (0,'T1136', '002', '%', '%passwordnotrequired%'),
      --T1200-1299
        (0,'T1201', '',    '%', '%net accounts%'),
        (0,'T1201', '',    'net.exe', '%accounts%'),
        (0,'T1204', '002', '%', '%wscript.ext%jscript%jse'),
        (0,'T1204', '002', '%', '%.jse%script.exe%'),
        (0,'T1217', '',    '%', '%user%bookmarks'),
        (0,'T1218', '',    '%', '%invoke%disable%'),
        (0,'T1218', '001', '%', '%invoke-athcompiledhelp%hhfilepath%'),
        (0,'T1218', '001', '%', '%invoke-athcompiledhelp%jscript%'),
        (0,'T1218', '001', 'hh.exe', '-chmfilepath%'),
        (0,'T1218', '004', '%', '%installutil%'), 
        (0,'T1218', '005', 'jscript.exe', '%inlineprotocolhandler%userundll32%'), 
        (0,'T1218', '005', '%', '%invoke-athhtmlapplication%jscript%'), 
        (0,'T1218', '005', '%', '%simulate%doubleclick%'),
        (0,'T1218', '005', '%', '%invoke%mshta.exe%'),
        (0,'T1218', '005', 'mshta.exe', '%'),
        (0,'T1218', '008', '%', '%csc.exe%enterpriseservices.dll%regsvcs.exe%'),
        (0,'T1218', '011', 'rundll32.exe', '%vnc%server%'),
        (0,'T1219', '',    '%ammyy%admin.exe', '%-connect%'),
        (0,'T1219', '',    '%', '%web-request%teamviewer_setup.exe%'),
        (0,'T1219', '',    '%', '%start-process%teamviewer%'),
        (0,'T1219', '',    '%', '%logmeinignition.msi%'),
        (0,'T1219', '',    '%', '%start-process%logmein%'),
        (0,'T1219', '',    '%', '%web-request%anydesk%'),
        (0,'T1219', '',    'teamviewer.exe', '%assign%'),
        (0,'T1219', '',    'vncserver.exe', '%-connect%'),
        (0,'T1219', '',    'tvnserver.exe', '%-connect%'),
        (0,'T1219', '',    'vncviewer.exe', '%-proxy%'),
      --T1300-1399
      --T1400-1499
        (0,'T1482', '',    '%', '%powerview%get-netdomain%'),
        (0,'T1482', '',    '%', '%get-netforest%'),
        (0,'T1482', '',    '%', '%get-addomain%'),
        (0,'T1482', '',    '%', '%get-adgroupmember%'),
        (0,'T1485', '',    '%', '%invoke-expression%sddelete.exe%'), 
        (0,'T1485', '',    '%', '%foreach%delete%'), 
        (0,'T1490', '',    '%', '%shadowcopy%delete%'), 
        (0,'T1491', '',    '%', '%invoke-memekatz%'), 
        (0,'T1497', '001', '%', '%procexp%get-process%'), -- assumes they build a list of things to look for 
        (0,'T1497', '001', '%', '%get-wmiobject%win32_computersystem%model'),
        (0,'T1497', '001', '%', '%security%found%'),
        (0,'T1497', '001', '%', '%security%detected%'),
        (0,'T1497', '001', '%', '%av%found%'),
        (0,'T1497', '001', '%', '%av%detected%'),
        (0,'T1497', '001', '%', '%get-process%procexp%'), -- only checks if they are looking for process explorer  most common 
      --T1500-1599
        (0,'T1505', '002', '%', '%install-transportagent%'),
        (0,'T1505', '002', '%', '%microsoft.exchange.security.interop.dll%'),
        (6,'T1518', '',    '%', '%get-itemproperty%hklm%uninstall%'),
        (0,'T1518', '',    '%', '%get-itemproperty%internet explorer%'), 
        (0,'T1518', '',    '%', '%echo%get-uac%'), 
        (0,'T1518', '',    '%', '%get-uac%'), 
        (0,'T1518', '001', '%', '%get-wmiobject%securitycenter%antivirusproduct%'), 
        (0,'T1518', '001', '%', '%get-process%virus%'), 
        (0,'T1518', '001', '%', '%wmic%securitycenter%'), 
        (0,'T1518', '001', '%', '%wmic%virus%'), 
        (0,'T1546', '008', '%', '%image file execution options%new-item%'), 
        (0,'T1547', '004', '%', '%new-item%winlogon%notify%set-itemproperty%'), 
        (0,'T1547', '004', '%', '%winlogon%userinit%'),         
        (0,'T1548', '002', '%', '%new-itemproperty%%hklm:software%enablelua%-value 0%'),
        (0,'T1548', '002', '%', '%akagi64.exe%'),
        (0,'T1548', '002', 'akagi64.exe', '%'),
        (0,'T1548', '002', '%', '%bypass-uac%'),
        (0,'T1552', '001', '%', '%findstr%password%'),
        (0,'T1552', '001', 'findstr.exe', '%password%'),
        (0,'T1552', '002', '%', '%reg%query%hkcu%password%'),
        (0,'T1552', '002', 'reg.exe', '%query%hkcu%password%'),
        (0,'T1552', '004', '%', '%.key%get-childitem%'),
        (0,'T1552', '004', '%', '%.pgp%get-childitem%'),
        (0,'T1552', '004', '%', '%.gpg%get-childitem%'),
        (0,'T1552', '004', '%', '%.ppk%get-childitem%'),
        (0,'T1552', '004', '%', '%.p12%get-childitem%'),
        (0,'T1552', '004', '%', '%.pem%get-childitem%'),
        (0,'T1552', '004', '%', '%.pfx%get-childitem%'),
        (0,'T1552', '004', '%', '%.cer%get-childitem%'),
        (0,'T1552', '004', '%', '%.p7b%get-childitem%'),
        (0,'T1552', '004', '%', '%.asc%get-childitem%'),
        (0,'T1552', '004', '%', '%.crt%get-childitem%'),
        (0,'T1552', '006', '%', '%gpppassword%'),
        (0,'T1553', '004', '%', '%import-cert%root%cer%'),
        (0,'T1553', '004', '%', '%certutil%addstore%root%cer%'),
        (0,'T1553', '004', 'certutil%', '%addstore%root%cer%'),
        (0,'T1555', '',    '%', '%iex%maldoc'),
        (0,'T1555', '',    '%', '%word%extract'),
        (0,'T1555', '003', '%', '%accesschk%accepteula'),
        (0,'T1555', '003', '%', '%chrome%login data%'),
        (0,'T1555', '003', 'accesschk.exe', '%accepteula%'),
        (0,'T1558', '001', '%', '%goldenticket%'),
        (0,'T1558', '001', '%', '%replace%domain_sid%'),
        (0,'T1558', '001', '%', '%golden.bat%'),
        (0,'T1558', '001', '%', '%ticket%attack%'),
        (0,'T1558', '003', '%', '%keberoast%'),
        (0,'T1560', '',    '%', '%dir%recurse%compress-archive%'),
        (0,'T1560', '001', '%', '%compress-archive%'),
        (0,'T1562', '001', '%', '%set%path%value%bypass%'),
        (0,'T1562', '001', '%', '%set-mppreference -disablerealtimemonitoring 1%'),
        (0,'T1562', '001', '%', '%set-mppreference -disableintrusionpreventionsystem%set-mppreference -disableioavprotection%set-mppreference -disablerealtimemonitoring%set-mppreference -disablescriptscanning%set-mppreference -enablecontrolledfolderaccess disabled%'),
        (0,'T1562', '001', '%', '%set-mppreference -disableintrusionpreventionsystem%'),
        (0,'T1562', '001', '%', '%set-mppreference -disablescriptscanning%'),
        (0,'T1562', '001', '%', '%set-mppreference -enablecontrolledfolderaccess disabled%'),
        (0,'T1562', '001', 'netsh.exe', '%firewall%add%rule%'),
        (0,'T1562', '001', '%', '%remove-item%amsi%provider%'),
        (0,'T1562', '001', '%', '%remove-service%sophos%'), 
        (0,'T1562', '001', '%', '%remove-service%mcafee%'), 
        (0,'T1562', '001', '%', '%remove-service%symantec%'), 
        (0,'T1562', '001', '%', '%sophos%uninstall%'), 
        (0,'T1562', '001', '%', '%windowssensor%uninstall%'), 
        (0,'T1562', '001', '%', '%uninstall%sophos%'), 
        (0,'T1562', '001', '%', '%uninstall%windowssensor%'), 
        (0,'T1562', '001', '%', '%uninstall%crowdstrike%'), 
        (0,'T1562', '001', '%', '%stop-service%sophos%'), 
        (0,'T1562', '001', '%', '%stop-service%mcafee%'), 
        (0,'T1562', '001', '%', '%stop-service%symantec%'), 
        (0,'T1562', '001', '%', '%disable%antic%'), 
        (0,'T1562', '001', '%', '%amsiinitfailed%setvalue%true%'), 
        (0,'T1562', '001', '%', '%add-mppreference%exclu%'), 
        (0,'T1562', '001', '%', '%office%security%disableinternetfilesinpv%'),
        (0,'T1562', '001', '%', '%office%security%disableunsafe%'),
        (0,'T1562', '001', '%', '%office%security%disableattachmentsinpv%'),
        (0,'T1562', '002', '%', '%invoke-phantom%'), 
        (0,'T1562', '002', '%', '%appcmd%dontlog:true%'), 
        (0,'T1562', '002', 'appcmd.exe', '%dontlog:true%'), 
        (0,'T1562', '004', '%', '%firewall%add%rule%allow%'), 
        (0,'T1562', '004', '%', '%firewall%add%port%allow%profile%any%'), 
        (0,'T1564', '003', '%', '%window%hidden%'), 
        (0,'T1564', '004', '%', '%set-contet%stream%value%'), 
        (3,'T1565', '001', '%', '%mv%.%.exe%'),
        (0,'T1565', '001', 'copy.exe', '%.%.exe%'),
        (0,'T1566', '001', '%', '%iex%invoke-maldoc%'), -- over fit
        (0,'T1571', '', '%', '%port%808%'),
        (0,'T1571', '', '%', '%:8080%'),
        (0,'T1571', '', '%', '%:8081%'),
        (0,'T1574', '012', '%', '%cor_profiler%')
),
-- DEBUG SECTION CONFIRM ALL Techniques have a METHOD Mapping
/****************
ValidationMap AS ( SELECT mt.id technique_id, mt.subid technique_subid, mm.id method_id, mm.subid methd_sub, COUNT(mm.id) TTP_Rules
FROM mitre_methods mm
LEFT JOIN mitre_techniques mt ON mm.id = mt.id AND mm.subid = mt.subid  
GROUP BY mt.id, mt.subid, mm.id, mm.subid ORDER by mt.id,mt.subid, mm.id DESC, mm.subid
)
--SELECT * FROM ValidationMap order by ttp_rules asc
SELECT SUM(ttp_rules) COUNT_OF_RULES FROM ValidationMap
*****************/

mitre_methods_ID AS ( SELECT ROW_NUMBER() OVER( ORDER BY id, subid, process, indicator) Rule_Id, id, noise_level, subid, process, indicator FROM mitre_methods ),

-- Identify which rules to run based on admin variable selections
ttp(mitre_id, mitre_subid, tactic, technique, subtechnique, process, hunt_rule, mitre_link, Rule_ID, noise_level) AS (
    SELECT
        mitre_techniques.id AS mitre_id,
        mitre_techniques.subid AS mitre_subid,
        mitre_techniques.tactic AS tactic,
        mitre_techniques.technique AS technique,
        mitre_techniques.subtechnique AS subtechnique,
        mitre_methods_ID.process AS process,
        mitre_methods_ID.indicator AS hunt_rule,
        CASE mitre_techniques.subid
            WHEN '' THEN 'https://attack.mitre.org/techniques/' || mitre_techniques.id
            ELSE 'https://attack.mitre.org/techniques/' || mitre_techniques.id || '/' || mitre_techniques.subid
        END AS mitre_link,
        mitre_methods_ID.Rule_ID Rule_ID,
        noise_level
    FROM mitre_methods_ID 
    LEFT JOIN mitre_techniques ON
        mitre_methods_ID.id = mitre_techniques.id
        AND mitre_methods_ID.subid = mitre_techniques.subid
        AND mitre_methods_ID.id || '.' || mitre_methods_ID.subid LIKE UPPER('%$$mitre_id$$%')
    WHERE
        mitre_methods_ID.noise_level <= $$Verbosity 0-9$$
        AND mitre_techniques.tactic LIKE LOWER('%$$tactic name$$%')
        AND mitre_techniques.technique LIKE LOWER('%$$technique name$$%')
),

-- Perform the evaluations
detections AS (
    SELECT
        xdr_data.meta_hostname AS ep_name,
        xdr_data.meta_endpoint_type AS device_type,
        DATE_FORMAT(FROM_UNIXTIME(xdr_data.time), '%Y-%m-%dT%H:%i:%SZ') AS date_time,
        ttp.mitre_id,
        ttp.mitre_subid,
        ttp.tactic,
        ttp.technique,
        ttp.subtechnique,
        ttp.process process_filter,
        ttp.hunt_rule,
        ttp.mitre_link,
        xdr_data.username AS username,
        xdr_data.name AS process_name,
        xdr_data.cmdline AS cmd_line,
        xdr_data.sophos_PID AS sophos_pid,
        xdr_data.path AS path_name,
        xdr_data.parent_name,
        ttp.Rule_ID,
        ttp.noise_level
    FROM xdr_data
    JOIN ttp ON LOWER(xdr_data.name) LIKE ttp.process AND LOWER(xdr_data.cmdline) LIKE ttp.hunt_rule
    WHERE
        xdr_data.query_name = 'running_processes_windows_sophos'
        AND LOWER(xdr_data.meta_hostname) LIKE LOWER('%$$device_name$$%')
        AND CAST(xdr_data.time AS INT) > CAST(TO_UNIXTIME(NOW()) AS INT) - $$Number of hours to search$$ * 3600
)
--, full_results AS (  -- ENABLE THIS LINE WHEN SETTING UP NOISE LEVEL REDUCTION RULES

-- GROUP results so we can show counted number of events
SELECT
    ARRAY_JOIN(ARRAY_AGG(DISTINCT ep_name), CHR(10)) AS ep_list,
    tactic,
    technique,
    COUNT(ep_name) AS instances,
    process_name,
    cmd_line,
    MIN_BY(sophos_pid,replace(sophos_pid, rtrim(sophos_pid, replace(sophos_pid, ':', '')), '') ) Earliest_SophosPID,  -- NOTE THIS IS EXPENSIVE
    ARRAY_JOIN(ARRAY_AGG(DISTINCT sophos_pid), CHR(10)) AS FULL_sophos_pid_list,
    ARRAY_JOIN(ARRAY_AGG(DISTINCT username), CHR(10)) AS user_list,
    subtechnique,
    mitre_id,
    mitre_subid,
    mitre_link,
    process_filter,
    hunt_rule,
    COUNT(DISTINCT ep_name) AS ep_count,
    MIN(date_time) AS first_seen,
    MAX(date_time) AS last_seen,
    ARRAY_JOIN(ARRAY_AGG(DISTINCT path_name), CHR(10)) AS path_name_list,
    ARRAY_JOIN(ARRAY_AGG(DISTINCT parent_name), CHR(10)) AS parent_name_list,
    noise_level
--    , Rule_ID -- REQUIRED FOR NOISE LEVEL REDUCTION
FROM detections
GROUP BY
    mitre_id,
    mitre_subid,
    tactic,
    technique,
    process_name,
    cmd_line,
    process_filter,
    hunt_rule,
    subtechnique,
    mitre_link,
    noise_level
--    , Rule_ID -- REQUIRED FOR NOISE LEVEL REDUCTION
ORDER BY instances DESC, tactic, technique, mitre_id, mitre_subid, process_name

-- USE BELOW FOR CALCULATING NOISE LEVEL REDUCTIOIN VALUES
/*******************
)
SELECT SUM(instances) Total_Detections, mitre_id, mitre_subid, process_filter, hunt_rule, Rule_ID, tactic, technique, subtechnique, noise_level 
FROM full_results
GROUP BY Rule_ID, tactic, technique, subtechnique, mitre_id, mitre_subid, process_filter, hunt_rule, noise_level
ORDER BY Total_Detections DESC
********************/

If you see something interesting just PIVOT on the SophosPID to the Process Tree with MITRE Enrichments.  It will run a more detailed query on the endpoint

-- Process Tree with MITRE enrichment for CMD LINE
-- VARIABLE $$sophosPID$$      SophosPID

-- Generate Process Tree
WITH RECURSIVE
get_ancestors(sophosPID, level, startTime, cmdLine, procName,    parentSophosPID, sha256, sid) AS (
    SELECT sophosPID, 0, processStartTime, cmdLine, processName, parentSophosPID, sha256, sid
    FROM sophos_process_journal
    WHERE sophosPID = '$$sophosPID$$' AND eventtype = 0

    UNION ALL

    SELECT p.sophosPID, get_ancestors.level - 1, p.processStartTime, p.cmdLine, p.processName, p.parentSophosPID, p.sha256, p.sid
    FROM sophos_process_journal p
    JOIN get_ancestors ON p.sophosPID = get_ancestors.parentSophosPID 
    ORDER BY 2 ASC
),
ancestor_tree AS (
    SELECT DISTINCT 
        startTime,
        printf('%.' || ABS(level) || 'c', '<') || ' ' || procName processBranch,
        sophosPID,
        cmdLine,
        sha256,
        sid
    FROM get_ancestors
    WHERE level < 0
    ORDER BY level ASC
),
get_children(sophosPID, level, startTime, cmdLine, procName, targetsophosPID, sha256, sid) AS (
    SELECT p.sophosPID, 0, p.processStartTime, p.cmdLine, p.processName,spa.targetsophosPID, p.sha256, p.sid
    FROM sophos_process_journal p
    LEFT JOIN sophos_process_activity spa ON spa.sophosPID = p.sophosPID AND subject = 'Process' 
    WHERE p.sophosPID = '$$sophosPID$$'

    UNION ALL

    SELECT p.sophosPID, get_children.level + 1, p.processStartTime, p.cmdLine, p.processName, spa.targetsophosPID, p.sha256, p.sid
    FROM sophos_process_journal p
    JOIN get_children ON p.sophosPID = get_children.targetsophosPID LEFT JOIN sophos_process_activity spa ON spa.sophosPID = p.sophosPID AND subject = 'Process'
    ORDER BY 2 DESC
),
child_tree AS (
    SELECT
        DISTINCT startTime,
        CASE
            WHEN level = 0 THEN procName
            ELSE printf('%.' || level || 'c', '>') || ' ' || procName
        END as processBranch,
        sophosPID,
        cmdLine,
        sha256,
        sid
    FROM get_children
),
Full_Tree AS (
    SELECT * FROM ancestor_tree
    UNION ALL
    SELECT * FROM child_tree
),

-- Get map from GIT LOCATION
mitre_map_file(Line, str) AS (
  SELECT '', (SELECT result from curl where url = 'https://raw.githubusercontent.com/karlrackerman/Query_Library/main/MITRE_ID_MAP.CSV') ||char(10)
  UNION ALL
  SELECT substr(str, 0, instr(str, char(10) )), substr(str, instr(str, char(10) )+1) FROM mitre_map_file WHERE str!=''
 ),
-- Create Table for Mitre_MAP
mitre_map (Tid, tactic, id, technique, subid, subtechnique) AS (
   SELECT SPLIT(Line,',',0) Tid, SPLIT(Line,',',1) tactic, SPLIT(Line,',',2) id, SPLIT(Line,',',3) technique, SPLIT(Line,',',4) subid, SPLIT(Line,',',5) subtechnique
   FROM mitre_map_file WHERE Line != '' 
),
-- Get detection rules from GIT LOCATION
mitre_rule_file(Line, str) AS (
  SELECT '', (SELECT result from curl where url = 'https://raw.githubusercontent.com/karlrackerman/Query_Library/main/TTP_Rules.CSV') ||char(10)
  UNION ALL
  SELECT substr(str, 0, instr(str, char(10) )), substr(str, instr(str, char(10) )+1) FROM mitre_rule_file WHERE str!=''
),
-- Create Table for Detection_Rules
mitre_detection_rules (method, noise_level, id,subid,process,indicator) AS (
   SELECT SPLIT(Line,',',0) method, SPLIT(Line,',',1) noise_level, SPLIT(Line,',',2) id, SPLIT(Line,',',3) subid, SPLIT(Line,',',4) process, SPLIT(Line,',',5) indicator
   FROM mitre_rule_file WHERE Line != ''
),

-- Map the methods to the description
Rule_Map AS ( SELECT mm.id||'.'||mm.subid||' -- '||mm.tactic||':: '||mm.technique||'- '||mm.subtechnique||CHAR(10)||'. Method: '||mdr.method||CHAR(10)||'. Process LIKE: '||mdr.process||CHAR(10)||'. Indicator LIKE: '||mdr.indicator info, mdr.process, mdr.indicator
FROM mitre_map mm
JOIN mitre_detection_rules mdr ON CAST(mm.id AS TEXT) = CAST(mdr.id AS TEXT) AND CAST(mm.subid AS TEXT) = CAST(mdr.subid AS TEXT)  
)

-- USED TO DEBUG
   -- SELECT * FROM mitre_map
   -- SELECT CAST(method AS TEXT) Method, CAST(noise_level AS TEXT) Noise_Level, CAST(id AS TEXT) ID, CAST(subid AS TEXT) SubID, CAST(process AS TEXT) Process, CAST(indicator AS TEXT) Indicator FROM mitre_detection_rules
   -- SELECT * FROM Rule_Map ORDER BY info
   -- SELECT * FROM mitre_map UNION ALL SELECT * FROM mitre_detection_rules

-- Display the Tree and enrich the CMDLINE
SELECT 
   DateTime(starttime,'unixepoch') Date_Time, 
   processBranch, 
   cmdline, 
   -- NOTE GROUP_CONCAT incase a cmdline has multiple TTP hits
   CAST((SELECT '('||COUNT(info)||') '||CHAR(10)||GROUP_CONCAT(info, CHAR(10)) FROM Rule_Map WHERE processBranch LIKE '%'||process AND Cmdline LIKE indicator) AS TEXT) Mitre_Info,  
   (SELECT username FROM users WHERE uuid = sid) username, 
   sophosPID, 
   (SELECT pathname FROM sophos_process_journal WHERE sophosPID = Full_Tree.sophosPID AND eventtype = 0) pathname,
   CAST(sha256 AS TEXT) sha256, 
   sid,
   ROW_NUMBER() OVER() Row_number
FROM Full_Tree

And to go deep into the facts in the historic activity records, run the Extended Process tree to see image loads, threads, filesystem, registry and network activity for each member of the tree.

-- Extended Process Tree for a SophosPID

-- VARIABLE $$SophosPID$$ sophosPID

-- NOTE THE PROCESS OR ANCESTORS MAY STILL BE RUNNING SO HANDLE ENDTIME CORRECTLY by changing endtime for running processes to now + 10 min
WITH RECURSIVE 
-- GET A LIST OF ALL ANCESTORS OF A SOPHOS PID
Ancestors(SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end) AS (
   -- Define the SEED Row as the information on the SophosPID provided
   SELECT sophosPID, 0, ParentSophosPID, processname, pathname, cmdline, sha256, sid, time,
     CASE WHEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0) > 0 
        THEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0)
        ELSE strftime('%s','now','+10 minutes') END
    FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) = 0
   
   UNION ALL
   
   -- Recursvly identify all decendents
   SELECT spj.SophosPID, Level - 1, spj.ParentSophosPID, spj.processname, spj.pathname, spj.cmdline, spj.sha256, spj.sid, spj.time, 
      CASE WHEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0) > 0
        THEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0)
        ELSE strftime('%s','now','+10 minutes') END
   FROM Ancestors JOIN Sophos_Process_Journal spj ON spj.SophosPID = Ancestors.parent AND CAST(spj.endtime AS INT) = 0 
   -- Perform a Depth First Search ASC would perform a Breadth First Search
   ORDER BY 2 DESC
   ),
-- Add Row Numbers to the Ancestor List so we order the tree corretly
-- EXCLUDE the line for the specified SophosPID so that when we show the tree we do not have a duplicate row
Orderd_Ancestors AS (SELECT SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end, -1 * ROW_Number() OVER () Row FROM Ancestors WHERE SophosPID NOT IN ('$$SophosPID$$') ),

-- GET A LIST OF ALL DECENDENTS OF A SOPHOS PID
-- NOTE THE PROCESS OR CHILDREN MAY STILL BE RUNNING SO HANDLE ENDTIME CORRECTLY by changing endtime for running processes to now + 10 min
Children(SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end) AS (
   -- Define the SEED Row as the information on the SophosPID provided
   SELECT sophosPID, 0, ParentSophosPID, processname, pathname, cmdline, sha256, sid, time,
     CASE WHEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0) > 0 
        THEN (SELECT endtime FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) > 0)
        ELSE strftime('%s','now','+10 minutes') END
    FROM sophos_process_Journal WHERE sophos_process_journal.SophosPID = '$$SophosPID$$' AND CAST(endtime AS INT) = 0
   
   UNION ALL
   
   -- Recursvly identify all decendents
   SELECT spj.SophosPID, Level +1, spj.ParentSophosPID, spj.processname, spj.pathname, spj.cmdline, spj.sha256, spj.sid, spj.time, 
      CASE WHEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0) > 0
        THEN (SELECT spj2.endtime FROM sophos_process_Journal spj2 WHERE spj2.SophosPID = spj.SophosPID AND CAST(endtime AS INT) > 0)
        ELSE strftime('%s','now','+10 minutes') END
   FROM Children JOIN Sophos_Process_Journal spj ON spj.ParentSophosPID = Children.SophosPID AND CAST(spj.endtime AS INT) = 0 AND spj.time > Children.start -5 and spj.time < Children.end +3600
   -- Perform a Depth First Search ASC would perform a Breadth First Search
   ORDER BY 2 DESC
   ),
   
   -- Add Row Numbers to the Decendent List so we order the tree corretly
   Orderd_Descendants AS (SELECT SophosPID, Level, parent, processname, pathname, cmdline, sha256, sid, start, end, ROW_Number() OVER () Row FROM Children),

   -- Now collect the activity for all descendents and the selected sophosPID using a UNION to list the decendent then the file activity it had
   File_Activity AS (

     -- FOR ANCESTORS WE WILL ONLY SHOW THE PROCESS TREE INFO (No activity will be collected)
     SELECT 
      REPLACE(DATETIME(A.start,'unixepoch'), ' ','T') Date_Time,
      CASE A.SophosPID 
		   WHEN '$$SophosPID$$' THEN A.ProcessName 
		   ELSE substr('< < < < < < < < < < < < < < < < < < < < ', 1, A.Level * -2)  || A.processName 
	   END Process_Tree,
	   '-----------' Subject,
      '-----------' Action,
      '-----------' Object,
      CAST(A.cmdline AS TEXT) Cmd_Line,
      A.SophosPID SophosPID,
      A.pathname Process_Pathname,
      A.sha256 Process_SHA256,
      A.SID Process_SID,
      A.Level Level,
      A.Row Row,
      0 Sub_Row,
      a.start time
   FROM Orderd_Ancestors A 

   UNION ALL
   
   -- SHOW THE PROCESS TREE INFO FOR DESCENDENTS
   SELECT 
      REPLACE(DATETIME(D.start,'unixepoch'), ' ','T') Date_Time,
      CASE D.SophosPID 
		   WHEN '$$SophosPID$$' THEN D.ProcessName 
		   ELSE substr('> > > > > > > > > > > > > > > > > > > > > > ', 1, D.Level * 2) || D.processName 
	   END Process_Tree,
	   '-----------' Subject,
      '-----------' Action,
      '-----------' Object,
      CAST(D.cmdline AS TEXT) Cmd_Line,
      D.SophosPID SophosPID,
      D.pathname Process_Pathname,
      D.sha256 Process_SHA256,
      D.SID Process_SID,
      D.Level Level,
      D.Row Row,
      0 Sub_Row,
      D.start time
   FROM Orderd_Descendants D 

   UNION ALL

   -- ADD THE PROCESS ACTIVITY FOR EACH DESCENDENT
   SELECT 
      REPLACE(DATETIME(MIN(spa.time),'unixepoch'),' ','T') Date_Time,
      CASE D.SophosPID 
		   WHEN '$$SophosPID$$' THEN '( '||D.processname||' ) ACTIVITY' 
		   ELSE substr('~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~ ~', 1, D.Level * 2) || '( '||D.processname||' ) ACTIVITY' 
	   END Process_Tree,
      spa.subject Subject,
      CAST(GROUP_CONCAT(DISTINCT spa.action)||' ('||COUNT(spa.action)||')' AS TEXT) Action,
      spa.object,
      CAST(D.cmdline AS TEXT) Cmd_Line,
      D.SophosPID SophosPID,
      D.pathname Process_Pathname,
      D.sha256 Process_SHA256,
      D.SID Process_SID,
      '' Level,
      D.Row Row,
      1 Sub_Row,
      spa.time time
   -- NOTE The details for each process does not include 'FileDataReads', 'FileOtherReads', 'FileBinaryReads', 'Image', 'Thread' 
   FROM Orderd_Descendants D LEFT JOIN Sophos_Process_Activity spa ON -- spa.subject IN ('Thread','DirectoryChanges','Dns','FileBinaryChanges','FileDataChanges','FileOtherChanges','Http','Ip','Network','Url','Registry','Process')
--      AND 
      spa.SophosPID = D.SophosPID 
      AND spa.time > D.start-1 
      AND spa.time < D.end+1
   WHERE spa.subject > ''
   GROUP BY spa.subject, spa.action, spa.object, spa.SophosPID, D.processname, D.SophosPID, D.pathname, D.sha256, D.SID
   )
   
-- Now that we have all activity for each descendent, we need to provide the pretty list showing the Process Tree and File activity for each process in the tree
SELECT Date_Time, Process_Tree, Subject, Action, Object, Cmd_Line, SophosPID, Process_Pathname, Process_SHA256, Process_SID, row, sub_row, time,ROW_NUMBER() OVER( ORDER BY Row, Sub_Row, Time) SORT_ORDER_FOR_EXCEL 
FROM File_Activity 
ORDER By SORT_ORDER_FOR_EXCEL