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