For query assistance, please see the following Best Practices guide
(NEW) Video on Schemas for EDR and Data Lake (15 Min)
With the addition of the data lake a significant amount of new information is available. In this document we will discuss each of the core database schemas.
For those that simply want the schema data some links are provided below
Endpoint
- OSQuery (Windows, Mac, Linux) - OSQuery Schema for Windows, Mac and Linix
- Windows Schema EXCEL - https://community.sophos.com/intercept-x-endpoint/edr-data-lake-eap/m/files/9514/download
- Mac Schema EXCEL - https://community.sophos.com/intercept-x-endpoint/edr-data-lake-eap/m/files/9515/download
- LINUX Schema EXCEL - https://community.sophos.com/intercept-x-endpoint/edr-data-lake-eap/m/files/9516/download
- Sophos Extension (Windows)- Sophos Extension Schema for Windows
Data Lake
- Endpoint - Data Lake Schema for Endpoints
- Firewall - Coming Soon
Endpoint-OSQuery
We are currently using OSQuery version 4.5.1, in total between Windows Mac and Linux this gives you access to over 200 tables of device information. For a full discussion on the OSQuery extension I recommend starting with the OSQUERY.IO web site.
'osquery is an operating system instrumentation framework for Windows, OS X (macOS), Linux, and FreeBSD. The tools make low-level operating system analytics and monitoring both performant and intuitive.
osquery exposes an operating system as a high-performance relational database. This allows you to write SQL queries to explore operating system data. With osquery, SQL tables represent abstract concepts such as running processes, loaded kernel modules, open network connections, browser plugins, hardware events or file hashes.'
OSQuery acts as the foundation technology for issuing queries to endpoints and collecting the information back to Sophos Central (A Fleet Manager in OSQuery language). Sophos actively participates in the open source project and contributes back to the community both new capabilities (Support for Windows 7) and bug fixes.
There are a number of online locations where folks exchange queries that work with OSQuery directly and almost all of those should work fine from Sophos Central.
Each of the supported operating system has common tables that are shared with other operating systems and a number of tables that are unique to the operating system. For example all operating systems have an OSQuery table for processes, but only windows has a table for registry keys as these are unique to windows.
From central you can get a fair bit of information on exactly what version of OSQuery running on the device and information about the extensions we added as well as information on how we fill the sophos data lake.
-- Get the OSQuery information from the device
SELECT * FROM osquery_info
epName
|
pid
|
uuid
|
instance_id
|
version
|
config_hash
|
config_valid
|
extensions
|
build_platform
|
build_distro
|
start_time
|
watcher
|
platform_mask
|
10428
|
4C4C4544-0046-3310-804A-B8C04F504E32
|
29b56dc5-7666-4878-b28b-6da1dcf02b82
|
4.5.1
|
2997cf682f53a5df5b6605a1edb7dfaa8e68727e
|
1
|
active
|
windows
|
10
|
1613748982
|
10508
|
2
|
For those already familiar with OSquery you will notice a column you might not have expected. The epName Column is automatically added by sophos to identify the device that returned the result. We do this to allow administrator to set a highlevel filter on what devices they want to send the query to. This allows administrators to target the query to a single device or group of devices and is a common filering method with fleet managers where the admin may not want to issue a complex queries to all devices or may be issuing a query that only works on Linux and does not want to send it to devices that sill simply return a table not found error.
With this query you can also see the version information and an indication that extensions are 'Active'. This leads to some other work we have done to extend osquery.
Endpoint-Sophos Extension (Windows)
With OSQuery we have excellent information on the current running state of a device and access to some of the event and system logs. For forensics you really need to be able to go back in time and see what was happening on the device days or weeks in the past. To enable this sophos records all process activity and puts the information into a series of journals that are made available with the extension. Among other things we are recording all threads for the process and information on them like was it a remote loaded threat (Injection) as well as all changes to the registry, access to the file system and network activity. This high fidelity data allows a forensics team to see exactly how an adversary or malware took advantage of the system. You can observer the file reads made by a process over 90 days ago, observing it read a document with one thread and copy it to a zip file with another before sending the entire payload to some external C2 server.
You can also see some information on the Extension direct from a query.
SELECT DISTINCT
osquery_registry.name "table",
osquery_extensions.name "extension"
FROM osquery_extensions
JOIN osquery_registry ON osquery_extensions.uuid = osquery_registry.owner_uuid
JOIN pragma_table_info(osquery_registry.name) pti
WHERE osquery_registry.registry = 'table' AND osquery_extensions.name != 'core'
This list the tables in OSQuery that are not 'core
table
|
extension
|
sophos_directory_journal
|
SophosExtension
|
sophos_dns_journal
|
SophosExtension
|
sophos_endpoint_info
|
SophosExtension
|
sophos_events_details
|
SophosExtension
|
sophos_events_summary
|
SophosExtension
|
sophos_file_hash_journal
|
SophosExtension
|
sophos_file_journal
|
SophosExtension
|
sophos_file_properties
|
SophosExtension
|
sophos_file_scan_results_journal
|
SophosExtension
|
sophos_hmpa_mitigations_journal
|
SophosExtension
|
sophos_http_journal
|
SophosExtension
|
sophos_image_journal
|
SophosExtension
|
sophos_ip_journal
|
SophosExtension
|
sophos_network_journal
|
SophosExtension
|
sophos_powershell_events
|
SophosExtension
|
sophos_process_activity
|
SophosExtension
|
sophos_process_journal
|
SophosExtension
|
sophos_process_properties
|
SophosExtension
|
sophos_registry_journal
|
SophosExtension
|
sophos_system_journal
|
SophosExtension
|
sophos_thread_journal
|
SophosExtension
|
sophos_url_journal
|
SophosExtension
|
sophos_windows_events
|
SophosExtension
|
sophos_winsec_journal
|
SophosExtension
|
Data Lake - Endpoint
The sophos data lake for endpoint information leverages the OSQuery ability to automatically run queries on a schedule. To fill the data lake sophos has defined 97 different queries to fill the data lake. Each of these run on their own schedule from as frequently as every 20 seconds (WIndows running processes) to as infrequently as once every 86400 seconds. (once a day). Understanding the frequency of these 'hydration' queries and how they work allows the admin to better understand what is in the data lake and why. The data lake is a subset of information from the endpoint and the best source of truth will always be the device, but if that device is not available or if the administrator is looking for commonality between multiple devices or between devices and other sensor information like the XG FW network data it is best to use a query against the data lake.
Some Notes on teh Data Lake Schema
- Information in the data lake is delayed by atleast several seconds and upto 1 day.
- The data lake is a subset of information available on the endpoint
- how the data lake gets information depends on the queries run to fill it
- Many of the queries filling the data lake are performing conditional checks on the endpoint looking for vulnerabilities or threats and will only have information in the data lake if an IOC was detected
- Data lake 'hydration' queries differ by OS, Differnt queries are run on windows than LINIX and MAC because different information is available.
- Below we show the hydration queries for WIndows and then LINUX. Note They have different queries but often fill the same type of information into the lake
You can run a query to get information and the SQL code used for each of the scheduled queries filling the data lake. In the example below I ran the query on windows and exported the results to get the information in a nice to read table format.
FROM osquery_schedule
name | query | interval |
arp_cache | SELECT address,mac,interface FROM arp_cache; | 3600 |
changed_files_windows_sophos | SELECT replace( sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '\', '')), '' ) AS filename, sfj.pathname AS path, sfj.time AS ctime, sfp.sha1, sfp.sha256, sfp.fileSize, sfp.mlScore, sfp.mlScoreData, sfp.puaScore, sfp.globalRep, sfp.globalRepData, sfp.localRep, sfp.localRepData, sfp.coreFileInfo FROM sophos_file_journal AS sfj JOIN sophos_file_properties AS sfp ON sfp.sha256 = sfj.sha256 WHERE sfj.query_id = 'changed_files_windows_sophos_binaries' AND subject = 'FileBinaryChanges' AND ( sfj.pathname LIKE '%:\Users\%\Desktop\%%' OR sfj.pathname LIKE '%:\Users\%\Appdata\Roaming\%%' OR sfj.pathname LIKE '%:\Users\%\Appdata\Local\Temp\%%' OR sfj.pathname LIKE '%:\Windows\Temp\%%' ) AND ( sfj.pathname LIKE '%%.exe' OR sfj.pathname LIKE '%%.dll' OR sfj.pathname LIKE '%%.tmp' ) AND sfj.sha256 != '' GROUP BY sfj.pathname, sfp.sha256 UNION SELECT replace( sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '\', '')), '' ) AS filename, sfj.pathname AS path, sfj.time AS ctime, sfp.sha1, sfp.sha256, sfp.fileSize, sfp.mlScore, sfp.mlScoreData, sfp.puaScore, sfp.globalRep, sfp.globalRepData, sfp.localRep, sfp.localRepData, sfp.coreFileInfo FROM sophos_file_journal AS sfj JOIN sophos_file_properties AS sfp ON sfp.sha256 = sfj.sha256 WHERE sfj.query_id = 'changed_files_windows_sophos_data' AND subject = 'FileDataChanges' AND ( sfj.pathname LIKE '%:\Users\%\Desktop\%%' OR sfj.pathname LIKE '%:\Users\%\Appdata\Roaming\%%' OR sfj.pathname LIKE '%:\Users\%\Appdata\Local\Temp\%%' OR sfj.pathname LIKE '%:\Windows\Temp\%%' ) AND ( sfj.pathname LIKE '%%.exe' OR sfj.pathname LIKE '%%.dll' OR sfj.pathname LIKE '%%.tmp' ) AND sfj.sha256 != '' GROUP BY sfj.pathname, sfp.sha256 UNION SELECT replace( sfj.pathname, rtrim(sfj.pathname, replace(sfj.pathname, '\', '')), '' ) AS filename, sfj.pathname AS path, sfj.time AS ctime, sfp.sha1, sfp.sha256, sfp.fileSize, sfp.mlScore, sfp.mlScoreData, sfp.puaScore, sfp.globalRep, sfp.globalRepData, sfp.localRep, sfp.localRepData, sfp.coreFileInfo FROM sophos_file_journal AS sfj JOIN sophos_file_properties AS sfp ON sfp.pathname = sfj.pathname WHERE sfj.query_id = 'changed_files_windows_sophos_powershell' AND subject = 'FileOtherChanges' AND sfj.filesize < 52428800 AND ( sfj.pathname LIKE '%:\Users\%\Desktop\%%' OR sfj.pathname LIKE '%:\Users\%\Appdata\Roaming\%%' OR sfj.pathname LIKE '%:\Users\%\Appdata\Local\Temp\%%' OR sfj.pathname LIKE '%:\Windows\Temp\%%' ) AND (sfj.pathname LIKE '%%.ps1') GROUP BY sfj.pathname, sfp.sha256 |
60 |
chrome_extensions | SELECT DISTINCT ce.uid, ce.name, ce.identifier, ce.version, ce.author, ce.path, ce.update_url FROM chrome_extensions AS ce JOIN logged_in_users AS liu ON liu.user=u.username JOIN users AS u ON u.uid=ce.uid; | 14400 |
ie_extensions | SELECT name, version, path FROM ie_extensions; | 14400 |
ioc_windows_registry_malware_sdbot | SELECT DISTINCT srj.time AS event_timestamp, srj.keyName, srj.value, srj.eventType, srj.sophosPID, srj.valueName, 'REG_BINARY' AS valueType, 'SDBbot Remote Access Trojan stores the RAT component in the registry and establishes persistence for the loader component.' AS description FROM sophos_registry_journal AS srj WHERE srj.keyName LIKE "\REGISTRY\MACHINE\SOFTWARE\Microsoft\___" AND srj.query_id = 'ioc_windows_registry_malware_sdbot' AND lower(srj.valueName) = srj.valueName AND LENGTH(srj.valueName) = 1 -- valueType=3 is Binary AND srj.valueType = 3 -- eventType=5 is valueSet AND srj.eventType = 5; |
60 |
listening_ports | SELECT DISTINCT processes.name, listening_ports.address, listening_ports.port, processes.pid, processes.path FROM listening_ports JOIN processes USING (pid) WHERE listening_ports.address NOT LIKE '127%' AND listening_ports.protocol = 6; | 3600 |
network_interfaces | SELECT interface_details.mtu, interface_details.interface, interface_details.mac, interface_addresses.mask, interface_addresses.address, interface_addresses.broadcast, interface_details.ibytes, interface_details.obytes FROM interface_addresses JOIN interface_details ON interface_addresses.interface = interface_details.interface; | 43200 |
open_sockets | SELECT DISTINCT p.name, SUBSTR(p.cmdline, 1, 32766) AS cmdline, p.pid, p.parent, p.path, po.remote_address, po.remote_port, po.local_address FROM process_open_sockets AS po JOIN processes AS p USING (pid) WHERE remote_port != 0 and p.path <> '' and remote_address <> '' and remote_address not like '127%' and remote_address not like '169.254%' and remote_address <> local_address; | 30 |
running_processes_windows_sophos | WITH new_processes( cmdline, pid, parent, path, name, sha1, sha256, time, sophosPID, parentSophosPID, fileSize, mlScore, mlScoreData, puaScore, globalRep, globalRepData, localRep, localRepData, eventType, sid ) AS ( SELECT spj.cmdLine AS cmdline, spj.PID AS pid, spj.parentPID AS parent, spj.pathname AS path, spj.processName AS name, spj.sha1, spj.sha256, spj.processStartTime AS time, spj.sophosPID, spj.parentSophosPID, spj.fileSize, spp.mlScore, spp.mlScoreData, spp.puaScore, spp.globalRep, spp.globalRepData, spp.localRep, spp.localRepData, spj.eventType, spj.sid FROM sophos_process_journal AS spj JOIN sophos_process_properties AS spp ON spp.sophosPID = spj.sophosPID WHERE eventType = 0 AND spj.query_id = 'running_processes_windows_sophos' AND cmdline != 'cmd /c tasklist.exe /NH /FI "IMAGENAME eq wapptunneld.exe"' AND cmdline != 'tasklist.exe /NH /FI "IMAGENAME eq wapptunneld.exe"' AND name != "conhost.exe" ) SELECT DISTINCT SUBSTR(np.cmdline, 1, 32766) AS cmdline, np.pid, np.parent, parents.processName AS parent_name, parents.pathname AS parent_path, np.path, np.name, np.sha1, np.sha256, np.time, np.sophosPID, np.parentSophosPID, np.fileSize, np.mlScore, np.mlScoreData, np.puaScore, np.globalRep, np.globalRepData, np.localRep, np.localRepData, u.username, u.uid, u.gid FROM new_processes AS np LEFT JOIN sophos_process_journal AS parents ON np.parentSophosPID = parents.sophosPID AND replace(np.parentSophosPID, rtrim(np.parentSophosPID, replace(np.parentSophosPID , ':', '')), '')/10000000-11644473600 = parents.time LEFT JOIN users AS u ON np.sid = u.uuid; |
20 |
sophos_ips_windows | WITH split(dns_host, rest) AS ( SELECT '', dns_server_search_order || ',' FROM interface_details UNION ALL SELECT substr(rest, 0, instr(rest, ',')), substr(rest, instr(rest, ',')+1) FROM split WHERE rest <> '' ), dns_ips(ip) AS ( SELECT TRIM(dns_host) as dns_ip FROM split WHERE dns_host <> '' AND dns_host LIKE '%.%.%.%' ORDER BY dns_host ) SELECT GROUP_CONCAT(DISTINCT PID) as pids, GROUP_CONCAT(DISTINCT sophosPID) AS sophosPIDs, source as sourceIp, destination as destinationIp, destinationPort, protocol, GROUP_CONCAT(DISTINCT time) as timestamps FROM sophos_ip_journal WHERE -- Include only IPv4 (sourceIp LIKE '%.%.%.%' AND destinationIp LIKE '%.%.%.%') AND -- Exclude localhost NOT (sourceIp LIKE '127.%.%.%' OR destinationIp LIKE '127.%.%.%') AND -- Exclude RFC1918 IPs NOT (destinationIP LIKE '10.%.%.%' OR destinationIP LIKE '172.16.%.%' OR destinationIP LIKE '192.168.%.%') AND -- Exclude Multicast IPs NOT (destinationIp LIKE '224.0.%.%' OR destinationIp LIKE '224.3.%.%' OR destinationIp LIKE '224.4.%.%' OR destinationIp LIKE '232.%.%.%' OR destinationIp LIKE '233.%.%.%' OR destinationIp LIKE '234.%.%.%' OR destinationIp LIKE '239.%.%.%') AND -- Exclude Broadcast IP NOT (destinationIp = '255.255.255.255') AND -- Exclude self-assigned IPs NOT (destinationIp LIKE '169.254.%.%') AND -- Exclude locally configured DNS ips NOT ( destinationIp IN ( SELECT ip FROM dns_ips ) AND destinationPort IN (53, 443) ) -- Unique by time, pid and ip AND -- Exclude Google public DNS ips NOT ( destinationIp IN ('8.8.8.8', '8.8.4.4') AND destinationPort IN (53, 443) ) AND -- Exclude OpenDNS public DNS ips NOT ( destinationIp IN ('208.67.222.222', '208.67.222.220') AND destinationPort IN (53, 443) ) AND -- Exclude CloudFlare public DNS ips NOT ( destinationIp IN ('1.1.1.1', '1.0.0.1') AND destinationPort IN (53, 443) ) AND query_id = 'sophos_ips_windows' GROUP BY destinationIp, destinationPort ORDER BY time DESC; |
60 |
sophos_urls_windows | WITH raw_urls(pid, sophosPID, domain, cleanUrl, sourceIp, destinationIp, time) AS ( SELECT DISTINCT PID as pid, sophosPID as sophosPID, -- This SUBSTR mess gets the domain. -- Once osquery is on 4.0.1 (or later), we can switch to a REGEX_MATCH SUBSTR(SUBSTR(url, INSTR(url, '//') + 2), 0, INSTR(SUBSTR(url, INSTR(url, '//') + 2), '/')) AS domain, REGEX_SPLIT(url, '\?', 0) as cleanUrl, source AS sourceIp, destination AS destinationIp, time FROM sophos_http_journal WHERE -- Exclude localhost NOT (source LIKE '127.%.%.%' OR destination LIKE '127.%.%.%') AND NOT (source = '0.0.0.0' AND destination = '0.0.0.0') AND -- Exclude RFC1918 IPs NOT (destination LIKE '10.%.%.%' OR destination LIKE '172.16.%.%' OR destination LIKE '192.168.%.%' ) AND -- Exclude self-assigned IPs NOT (destination LIKE '169.254.%.%') AND -- Exclude IPv6 addresses - it appears that IPv6 addresses are surrounded by square brackets: [fe80::] NOT (domain LIKE '[%') AND query_id = 'sophos_urls_windows-http' UNION SELECT DISTINCT PID AS pid, sophosPID AS sophosPID, SUBSTR( SUBSTR(url, INSTR(url, '//') + 2), 0, INSTR(SUBSTR(url, INSTR(url, '//') + 2), '/') ) AS domain, REGEX_SPLIT(url, '\?', 0) AS cleanUrl, NULL AS sourceIp, NULL AS destinationIp, time FROM sophos_url_journal WHERE query_id = 'sophos_urls_windows-url' AND -- Exclude IPv6 addresses - it appears that IPv6 addresses are surrounded by square brackets: [fe80::] NOT (domain LIKE '[%') ), numbered_urls( groupNum, pid, sophosPID, domain, cleanUrl, sourceIp, destinationIp, time ) AS ( SELECT -- Generate a grouping number in for use in aggregation - Currently 100 urls per domain -- or 32766 / 100 = 327 bytes per url (ROW_NUMBER() OVER (PARTITION BY raw_urls.domain)) / 100 AS groupNum, pid, sophosPID, domain, cleanUrl, sourceIp, destinationIp, time FROM raw_urls GROUP BY cleanUrl ) SELECT -- Just in case each row overflows the 32766-byte limit, we truncate it SUBSTR(GROUP_CONCAT(pid), 1, 32766) AS pids, SUBSTR(GROUP_CONCAT(sophosPID), 1, 32766) AS sophosPIDs, domain, -- Using ' ' as a delimiter, as ',' is used unencoded by some sites, like bing SUBSTR(GROUP_CONCAT(REGEX_SPLIT(cleanUrl, domain, 1), ' '), 1, 32766) AS cleanUrls, SUBSTR(GROUP_CONCAT(sourceIp), 1, 32766) AS sourceIps, SUBSTR(GROUP_CONCAT(destinationIp), 1, 32766) AS destinationIps, SUBSTR(GROUP_CONCAT(time), 1, 32766) AS timestamps FROM numbered_urls GROUP BY domain, groupNum; |
60 |
threat_pass_the_hash | SELECT eventid, CAST(JSON_EXTRACT(data, '$.EventData.LogonType') as integer) as logon_type, REPLACE(JSON_EXTRACT(data, '$.EventData.LogonProcessName'), ' ', '') as logon_process, JSON_EXTRACT(data, '$.EventData.IpAddress') AS remote_address, JSON_EXTRACT(data, '$.EventData.IpPort') AS remote_port, JSON_EXTRACT(data, '$.EventData.ProcessName') AS name, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.TargetUserSid') AS target_sid, CAST(JSON_EXTRACT(data, '$.EventData.KeyLength') as integer) AS key_length, provider_name, source FROM sophos_windows_events WHERE source = 'Security' AND (( eventid = 4624 AND logon_type = 3 AND logon_process = 'NtLmSsp' AND key_length = 0 AND target_sid != 'S-1-5-7' ) OR ( eventid = 4648 AND remote_address NOT LIKE '127.%.%.%' AND remote_address NOT IN ('0.0.0.0','::','-','::1') AND remote_port NOT IN (0) AND name NOT LIKE 'C:\Windows\System32\%' AND name != '' AND target_domain != subject_domain AND target_domain != '' )) AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
threat_stickykeys_registry_backdoor | SELECT * FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\%%' and name='Debugger'; | 43200 |
user_accounts | SELECT uid, gid, username, description, directory, shell, type, uuid FROM users; |
43200 |
vulnerability_app_compatibility | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'os_compatibility_target', data ) AS analysis FROM registry WHERE (key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers' OR key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\AppCompatFlags\Layers'); |
3600 |
vulnerability_app_disabled_exception_chain_validation | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'exception_chain_validation_disabled', data != 0 ) AS analysis FROM registry WHERE (key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\%' OR key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\%') AND name = 'DisableExceptionChainValidation' AND data != 0; |
3600 |
vulnerability_app_mitigation_options | WITH mitigation_options_keys( key, path, name, type, data, mtime, process_creation_mitigation_policy_dep_enable, process_creation_mitigation_policy_dep_atl_thunk_enable, process_creation_mitigation_policy_sehop_enable, process_creation_mitigation_policy_force_relocate_images_always_on, process_creation_mitigation_policy_bottom_up_aslr_always_on, process_creation_mitigation_policy_bottom_up_aslr_always_off ) AS ( SELECT key, path, name, type, data, mtime, data & 1 = 1 AS process_creation_mitigation_policy_dep_enable, data & 2 = 2 AS process_creation_mitigation_policy_dep_atl_thunk_enable, data & 4 = 4 AS process_creation_mitigation_policy_sehop_enable, data & 256 = 256 AS process_creation_mitigation_policy_force_relocate_images_always_on, data & 32768 = 32768 AS process_creation_mitigation_policy_bottom_up_aslr_always_on, data & 65526 = 65536 AS process_creation_mitigation_policy_bottom_up_aslr_always_off FROM registry WHERE (key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\%' OR key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Windows NT\CurrentVersion\Image File Execution Options\%') AND name = 'MitigationOptions' AND type = 'REG_QWORD' AND process_creation_mitigation_policy_bottom_up_aslr_always_off = 1 ) SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'process_creation_mitigation_policy_dep_enable', process_creation_mitigation_policy_dep_enable, 'process_creation_mitigation_policy_dep_atl_thunk_enable', process_creation_mitigation_policy_dep_atl_thunk_enable, 'process_creation_mitigation_policy_sehop_enable', process_creation_mitigation_policy_sehop_enable, 'process_creation_mitigation_policy_force_relocate_images_always_on', process_creation_mitigation_policy_force_relocate_images_always_on, 'process_creation_mitigation_policy_bottom_up_aslr_always_on', process_creation_mitigation_policy_bottom_up_aslr_always_on, 'process_creation_mitigation_policy_bottom_up_aslr_always_off', process_creation_mitigation_policy_bottom_up_aslr_always_off ) AS analysis FROM mitigation_options_keys; |
3600 |
vulnerability_applocker_ruleset_enforcement_mode | SELECT key, path, name, type, 0 AS data, mtime, JSON_OBJECT( 'applocker_disabled', 1 ) AS analysis FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\SrpV2\%' AND path NOT IN ( SELECT key AS path FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\SrpV2\%' AND name = 'EnforcementMode' ); |
3600 |
vulnerability_audit_special_groups | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'audit_special_group', 1 ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Audit' AND name NOT IN ('AuditPolicy', 'PerUserAuditing'); |
3600 |
vulnerability_certificate_padding | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'certificate_padding_disabled', data != 1 ) AS analysis FROM registry WHERE (key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\WinTrust\Config' OR key = 'HKEY_LOCAL_MACHINE\SOFTWARE\WOW6432Node\Microsoft\Cryptography\WinTrust\Config') AND name = 'EnableCertPaddingCheck' AND data != 1; |
3600 |
vulnerability_dep | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'dep_opt_out', data LIKE '%NOEXECUTE=OPTOUT%', 'dep_alwayson', data LIKE '%NOEXECUTE=ALWAYSON%', 'dep_opt_in', data LIKE '%NOEXECUTE=OPTIN%' ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control' AND name = 'SystemStartOptions' AND data NOT LIKE '%NOEXECUTE=ALWAYSON%' AND data NOT LIKE '%NOEXECUTE=OPTOUT%'; |
3600 |
vulnerability_developer_mode | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'developer_mode_enabled', data = 1 ) AS analysis FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\AppModelUnlock' AND name = 'AllowDevelopmentWithoutDevLicense' AND data = 1; |
3600 |
vulnerability_disallowed_paths | WITH missing_keys ( count ) AS ( SELECT COUNT(*) AS count FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\0\Paths' ) SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\0' AS key, 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\0\Paths' AS path, 'Paths' AS name, 'subkey' AS type, 0 AS data, STRFTIME('%s') AS mtime, JSON_OBJECT( 'srp_path_rules_missing', 1 ) AS analysis FROM missing_keys WHERE count = 0; |
3600 |
vulnerability_disallowed_paths_item_data | SELECT key, path, name, type, 0 AS data, mtime, JSON_OBJECT( 'srp_path_blacklist_rules_missing', 1 ) AS analysis FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\0\Paths\%' AND path NOT IN ( SELECT key AS path FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\0\Paths\%' AND name = 'ItemData' ); |
3600 |
vulnerability_fontblocking | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'font_blocking_enabled', data = 1000000000000 ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows NT\MitigationOptions' AND name = 'MitigationOptions_FontBlocking' AND data != 1000000000000; |
3600 |
vulnerability_kernel_null_page_access | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'kernel_null_page_access_allowed', data = 1 ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\Memory Management' AND name = 'EnableLowVaAccess' AND data = 1; |
3600 |
vulnerability_opentype_font | SELECT * FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Windows\%' AND name = 'DisableATMFD' AND data != '1'; | 3600 |
vulnerability_outlook_flags | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'outlook_security_key', name, 'outlook_security_value', data = 1 ) AS analysis FROM registry WHERE -- Due to osquery globbing limitations, we need to split the path query into two parts. -- Ideally we could use something like: key LIKE `HKEY_USERS\%\SOFTWARE\Microsoft\Office\%\Outlook\Security\%` -- The inner query grabs everything up to the Office version `...\Office\16.0` and appends '\Outlook\Security' -- The outer query pulls in the rest of the path -- IMPORTANT NOTE: https://github.com/osquery/osquery/pull/6448 will potentially impact this functionality. Need to keep an eye out for whenever this patch makes it to a release key IN (SELECT path || '\Outlook\Security' FROM registry WHERE path LIKE 'HKEY_USERS\%\SOFTWARE\Microsoft\Office\%') AND name IN ('EnableRoamingFolderHomepages', 'NonDefaultStoreScript', 'EnableUnsafeClientMailRules') AND ( (name = 'EnableRoamingFolderHomepages' AND data = 1) OR (name = 'NonDefaultStoreScript' AND data = 1) OR (name = 'EnableUnsafeClientMailRules' AND data = 1) ); |
3600 |
vulnerability_safer_flags_missing | SELECT key, path, name, type, 0 AS data, mtime, JSON_OBJECT( 'safer_flags_key_missing', 1 ) AS analysis FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\%\%\%' AND path NOT IN ( SELECT key AS path FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\%\%\%' AND name = 'SaferFlags' ); |
3600 |
vulnerability_safer_flags_not_enforcing | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'safer_flags_not_enforcing', data != 0 ) AS analysis FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\%\%\%' AND name = 'SaferFlags' AND data != 0; |
3600 |
vulnerability_secureboot | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'secure_boot_enabled', data = 1 ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecureBoot\State' AND name = 'UEFISecureBootEnabled' AND data != 1; |
3600 |
vulnerability_sehop | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'sehop_disabled', data != 1 ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\kernel' AND name = 'KernelSEHOPEnabled' AND data != 1; |
3600 |
vulnerability_sehop_validation | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'sehop_disabled', data != 0 ) AS analysis FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager\kernel' AND name = 'DisableExceptionChainValidation' AND data != 0; |
3600 |
vulnerability_spectre_meltdown | SELECT COUNT(*) AS count FROM patches WHERE hotfix_id = 'KB4056892' GROUP BY hotfix_id HAVING count = 0; | 3600 |
vulnerability_srp_default_level | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'software_restriction_policy_default_unrestricted', data = 262144 ) AS analysis FROM registry WHERE (key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers' OR key LIKE 'HKEY_USERS\%\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers') AND name = 'DefaultLevel' AND data = 262144; |
3600 |
vulnerability_srp_exclude_local_admin | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'software_restriction_policy_enforcement_exclude_local_admin', data != 0 ) AS analysis FROM registry WHERE (key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers' OR key LIKE 'HKEY_USERS\%\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers') AND name = 'PolicyScope' AND data != 0; |
3600 |
vulnerability_srp_transparent | SELECT key, path, name, type, data, mtime, JSON_OBJECT( 'software_restriction_policy_enforcement_transparent_off', data = 0 ) AS analysis FROM registry WHERE (key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers' OR key LIKE 'HKEY_USERS\%\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers') AND name = 'TransparentEnabled' AND data = 0; |
3600 |
vulnerability_uac_disabled | SELECT * FROM registry WHERE path='HKEY_LOCAL_MACHINE\Software\Microsoft\Windows\CurrentVersion\Policies\System\EnableLUA' AND data = '0'; | 3600 |
vulnerability_unrestricted_paths | WITH missing_keys ( count ) AS ( SELECT COUNT(*) AS count FROM registry WHERE key = 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\262144\Paths' ) SELECT 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\262144' AS key, 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\262144\Paths' AS path, 'Paths' AS name, 'subkey' AS type, 0 AS data, STRFTIME('%s') AS mtime, JSON_OBJECT( 'srp_path_rules_missing', 1 ) AS analysis FROM missing_keys WHERE count = 0; |
3600 |
vulnerability_unrestricted_paths_item_data | SELECT key, path, name, type, 0 AS data, mtime, JSON_OBJECT( 'srp_path_whitelist_rules_missing', 1 ) AS analysis FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\262144\Paths\%' AND path NOT IN ( SELECT key AS path FROM registry WHERE key LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\Safer\CodeIdentifiers\262144\Paths\%' AND name = 'ItemData' ); |
3600 |
vulnerability_weak_algorithms | SELECT * FROM registry WHERE path LIKE 'HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\OID\EncodingType 0\CertDllCreateCertificateChainEngine\Config\Default\%' AND name IN ('WeakSha1ThirdPartyFlags','WeakMd5ThirdPartyFlags') AND type = 'REG_DWORD' AND data NOT LIKE '-2%'; | 3600 |
windows_event_audit_log_cleared | SELECT eventid, JSON_EXTRACT(data, '$.UserData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.UserData.SubjectDomainName') AS subject_domain, 'The audit log was cleared.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid IN (1102, 517) AND source = 'Security' AND provider_name != 'AD FS Auditing' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_audit_policy_changed | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.CategoryId') AS category, JSON_EXTRACT(data, '$.EventData.SubcategoryId') AS subcategory, JSON_EXTRACT(data, '$.EventData.AuditPolicyChanges') AS audit_policy_changes, 'System audit policy was changed.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4719 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_disallowed_credentials | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.Package') AS package, JSON_EXTRACT(data, '$.EventData.UserUPN') AS user_upn, JSON_EXTRACT(data, '$.EventData.TargetServer') AS target_server, JSON_EXTRACT(data, '$.EventData.CredType') AS cred_type, 'The requested credentials delegation was disallowed by policy.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 5378 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_dos_attack_detected | SELECT eventid, JSON_EXTRACT(data, '$.EventData.Type') AS type, 'The Windows Filtering Platform has detected a DoS attack and entered a defensive mode.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 5148 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_invalid_logon | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.Status') AS status, JSON_EXTRACT(data, '$.EventData.FailureReason') AS failure_reason, JSON_EXTRACT(data, '$.EventData.SubStatus') AS sub_status, JSON_EXTRACT(data, '$.EventData.LogonType') AS logon_type, JSON_EXTRACT(data, '$.EventData.LogonProcessName') AS logon_process, JSON_EXTRACT(data, '$.EventData.AuthenticationPackageName') AS authentication_package, JSON_EXTRACT(data, '$.EventData.TransmittedServices') AS transmitted_services, JSON_EXTRACT(data, '$.EventData.KeyLength') AS key_length, JSON_EXTRACT(data, '$.EventData.ProcessName') AS name, JSON_EXTRACT(data, '$.EventData.IpAddress') AS remote_address, JSON_EXTRACT(data, '$.EventData.IpPort') AS remote_port, 'An account failed to log on.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4625 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_invalid_logon_brute_force | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.Status') AS status, JSON_EXTRACT(data, '$.EventData.FailureReason') AS failure_reason, JSON_EXTRACT(data, '$.EventData.SubStatus') AS sub_status, JSON_EXTRACT(data, '$.EventData.LogonType') AS logon_type, JSON_EXTRACT(data, '$.EventData.LogonProcessName') AS logon_process, JSON_EXTRACT(data, '$.EventData.AuthenticationPackageName') AS authentication_package, JSON_EXTRACT(data, '$.EventData.TransmittedServices') AS transmitted_services, JSON_EXTRACT(data, '$.EventData.KeyLength') AS key_length, JSON_EXTRACT(data, '$.EventData.ProcessName') AS name, JSON_EXTRACT(data, '$.EventData.IpAddress') AS remote_address, JSON_EXTRACT(data, '$.EventData.IpPort') AS remote_port, 'Source IP is shuffling through 20 or more different usernames, appears to be a brute force attack' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4625 AND source = 'Security' AND remote_address IS NOT NULL AND remote_address NOT LIKE '127.%.%.%' AND remote_address NOT IN ('0.0.0.0','::','-','::1') AND time > STRFTIME('%s', 'NOW') - 3925 GROUP BY remote_address HAVING COUNT(DISTINCT target_username) >= 20; |
3600 |
windows_event_replay_attack | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.RequestType') AS request_type, JSON_EXTRACT(data, '$.EventData.LogonProcessName') AS logon_process, JSON_EXTRACT(data, '$.EventData.AuthenticationPackage') AS authentication_package, JSON_EXTRACT(data, '$.EventData.TransmittedServices') AS transmitted_services, JSON_EXTRACT(data, '$.EventData.ProcessName') AS name, 'A replay attack was detected.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4649 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_scheduled_task_created | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TaskName') AS task_name, JSON_EXTRACT(data, '$.EventData.TaskContent') AS task_content, 'A scheduled task was created.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4698 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_successful_logon | WITH grouped_successful_logins( time, eventid, subject_username, subject_domain, target_username, target_domain, target_logon_id, subject_logon_id, logon_type, logon_process, authentication_package, transmitted_services, key_length, name, remote_address, remote_port, provider_name, source, groupNum ) AS ( SELECT DISTINCT time, eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.TargetLogonId') AS target_logon_id, JSON_EXTRACT(data, '$.EventData.SubjectLogonId') AS subject_logon_id, JSON_EXTRACT(data, '$.EventData.LogonType') AS logon_type, JSON_EXTRACT(data, '$.EventData.LogonProcessName') AS logon_process, JSON_EXTRACT(data, '$.EventData.AuthenticationPackageName') AS authentication_package, JSON_EXTRACT(data, '$.EventData.TransmittedServices') AS transmitted_services, JSON_EXTRACT(data, '$.EventData.KeyLength') AS key_length, JSON_EXTRACT(data, '$.EventData.ProcessName') AS name, JSON_EXTRACT(data, '$.EventData.IpAddress') AS remote_address, JSON_EXTRACT(data, '$.EventData.IpPort') AS remote_port, provider_name, source, (ROW_NUMBER() OVER (ORDER BY time))/15 AS groupNum FROM sophos_windows_events WHERE eventid = 4624 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925 ) SELECT SUBSTR(GROUP_CONCAT(time),1,164) AS event_timestamps, eventid, subject_username, subject_domain, target_username, target_domain, target_logon_id, subject_logon_id, logon_type, logon_process, authentication_package, transmitted_services, key_length, name, remote_address, remote_port, 'A user account was successfully logged on' AS description, provider_name, source FROM grouped_successful_logins GROUP BY groupNum, subject_username, target_username, target_logon_id, subject_logon_id; |
3600 |
windows_event_uac_bypass_journal | SELECT time AS event_timestamp, keyName, value, eventType, sophosPID, CASE WHEN keyName LIKE "\REGISTRY\USER\%\ms-settings\shell\Open\Command" THEN "UAC Bypass fodhelper.exe or ComputerDefaults.exe detected" WHEN keyName LIKE "\REGISTRY\USER\%\mscfile\shell\Open\Command" THEN "UAC Bypass eventvwr.msc detected" WHEN keyName LIKE "\REGISTRY\USER\%\exefile\shell\Runas\Command\isolatedCommand" THEN "UAC Bypass sdclt detected" WHEN keyName LIKE "\REGISTRY\USER\%\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\control.exe" THEN "UAC Bypass Application Path detected" WHEN keyName LIKE "\REGISTRY\USER\%\AppX82a6gwre4fdg3bt635tn5ctqjf8msdd2\shell\Open\Command" THEN "UAC Bypass WSReset.exe Detected" ELSE "UAC Bypass detected" END AS description FROM ( SELECT DISTINCT time, keyName, value, eventType, sophosPID FROM sophos_registry_journal WHERE keyName LIKE "\REGISTRY\USER\%" AND query_id = "windows_event_uac_bypass_journal" AND eventType = 5 AND valueName != "DelegateExecute" ) WHERE keyName LIKE "\REGISTRY\USER\%\ms-settings\shell\Open\Command" OR keyName LIKE "\REGISTRY\USER\%\mscfile\shell\Open\Command" OR keyName LIKE "\REGISTRY\USER\%\exefile\shell\Runas\Command\isolatedCommand" OR keyName LIKE "\REGISTRY\USER\%\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\control.exe" OR keyName LIKE "\REGISTRY\USER\%\AppX82a6gwre4fdg3bt635tn5ctqjf8msdd2\Shell\Open\Command"; |
60 |
windows_event_uac_bypass_registry | SELECT mtime AS event_timestamp, REPLACE(key,"HKEY_USERS\","\REGISTRY\USERS\") AS keyName, data AS value, CASE WHEN key LIKE "HKEY_USERS\%_classes\exefile\shell\Runas\Command\isolatedCommand" THEN "UAC Bypass sdclt detected" WHEN key LIKE "HKEY_USERS\%\Software\Microsoft\Windows\CurrentVersion\App Paths\control.exe" THEN "UAC Bypass Application Path detected" ELSE "UAC Bypass detected" END AS description FROM registry WHERE (key LIKE "HKEY_USERS\%_classes\exefile\shell\Runas\Command\isolatedCommand" OR key LIKE "HKEY_USERS\%\Software\Microsoft\Windows\CurrentVersion\App Paths\control.exe") AND name!="DelegateExecute" AND mtime > STRFTIME("%s","now")-3600; |
3600 |
windows_event_user_account_changed | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectLogonId') AS subject_logon_id, JSON_EXTRACT(data, '$.EventData.UserPrincipalName') AS user_principal_name, JSON_EXTRACT(data, '$.EventData.PrivilegeList') AS privilege_list, JSON_EXTRACT(data, '$.EventData.SamAccountName') AS sam_account_name, JSON_EXTRACT(data, '$.EventData.DisplayName') AS display_name, JSON_EXTRACT(data, '$.EventData.HomeDirectory') AS home_directory, JSON_EXTRACT(data, '$.EventData.HomePath') AS home_path, JSON_EXTRACT(data, '$.EventData.ScriptPath') AS script_path, JSON_EXTRACT(data, '$.EventData.ProfilePath') AS profile_path, JSON_EXTRACT(data, '$.EventData.UserWorkstations') AS user_workstations, JSON_EXTRACT(data, '$.EventData.AccountExpires') AS account_expires, JSON_EXTRACT(data, '$.EventData.AllowedToDelegateTo') AS allowed_to_delegate_to, JSON_EXTRACT(data, '$.EventData.UserAccountControl') AS uac, JSON_EXTRACT(data, '$.EventData.UserParameters') AS user_parameters, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.PasswordLastSet') AS password_last_set, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, 'A User Account was changed' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4738 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_user_account_created | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.PrivilegeList') AS privilege_list, JSON_EXTRACT(data, '$.EventData.SamAccountName') AS sam_account_name, JSON_EXTRACT(data, '$.EventData.DisplayName') AS display_name, JSON_EXTRACT(data, '$.EventData.UserPrincipalName') AS user_principal_name, JSON_EXTRACT(data, '$.EventData.HomeDirectory') AS home_directory, JSON_EXTRACT(data, '$.EventData.HomePath') AS home_path, JSON_EXTRACT(data, '$.EventData.ScriptPath') AS script_path, JSON_EXTRACT(data, '$.EventData.ProfilePath') AS profile_path, JSON_EXTRACT(data, '$.EventData.UserWorkstations') AS user_workstations, JSON_EXTRACT(data, '$.EventData.AccountExpires') AS account_expires, JSON_EXTRACT(data, '$.EventData.AllowedToDelegateTo') AS allowed_to_delegate_to, JSON_EXTRACT(data, '$.EventData.UserAccountControl') AS uac, JSON_EXTRACT(data, '$.EventData.UserParameters') AS user_parameters, 'A user account was created.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4720 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_user_account_deleted | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, JSON_EXTRACT(data, '$.EventData.PrivilegeList') AS privilege_list, 'A user account was deleted.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4726 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_event_user_account_locked_out | SELECT eventid, JSON_EXTRACT(data, '$.EventData.SubjectUserName') AS subject_username, JSON_EXTRACT(data, '$.EventData.SubjectDomainName') AS subject_domain, JSON_EXTRACT(data, '$.EventData.TargetUserName') AS target_username, JSON_EXTRACT(data, '$.EventData.TargetDomainName') AS target_domain, 'A user account was locked out.' AS description, provider_name, source FROM sophos_windows_events WHERE eventid = 4740 AND source = 'Security' AND time > STRFTIME('%s', 'NOW') - 3925; |
3600 |
windows_powershell_script_blocks | SELECT spe.time, spe.script_block_id, spe.script_block_count, SUBSTR(spe.script_text, 1, 32766) AS script_text, CASE WHEN length(spe.script_text) >= 32765 THEN 1 ELSE 0 END script_text_truncated, spe.script_name, spe.script_path FROM sophos_powershell_events AS spe WHERE spe.script_text != 'prompt' -- sophos_powershell_events doesn't require a 300 second buffer as the events appear immediately AND spe.time > STRFTIME('%s', 'NOW') - 625; |
600 |
windows_programs | SELECT name, version, language, install_source, publisher, identifying_number, install_date FROM programs; | 14400 |
windows_services_md5 | SELECT name, display_name, REGEX_SPLIT(description, "[^\x00-\x7f]", 0) AS description, start_type, services.path, sha1, sha256 FROM services JOIN hash WHERE hash.path = services.path AND start_type='AUTO_START' AND services.path NOT LIKE '%Windows%'; | 14400 |
windows_startup_items | WITH unique_autoexec ( source, cmdline, path, name, status ) AS ( SELECT ax.source as source, ax.path as path, svc.path as cmdline, ax.name, 'none' as status FROM autoexec ax JOIN services svc on svc.name = ax.name WHERE 1=1 AND ax.path NOT LIKE ('c:\windows\system32\%.dll') ), unique_signed ( source, cmdline, path, name, status, result, issuer_name, subject_name, sha256 ) AS ( SELECT sources.source, sources.cmdline, sources.path, sources.name, sources.status, ac.result, ac.issuer_name, ac.subject_name, h.sha256 FROM ( SELECT DISTINCT path, source, cmdline, name, status FROM unique_autoexec ) AS sources JOIN authenticode ac on ac.path = sources.path JOIN hash h on ac.path = h.path WHERE 1=1 AND ac.subject_name NOT IN ('Microsoft Windows', 'Microsoft Windows Publisher') ) SELECT ua.source, ua.cmdline, ua.path, ua.name, ua.status, us.result, us.issuer_name, us.subject_name, us.sha256 FROM unique_autoexec AS ua LEFT JOIN unique_signed AS us on ua.path = us.path; |
14400 |
windows_startup_programs_md5 | SELECT PRINTF('%s.exe', REGEX_SPLIT(startup_items.path, '.exe', 0)) AS mod_path, name, hash.path, type, status, username, sfp.sha1, hash.sha256, sfp.fileSize, sfp.mlScore, sfp.mlScoreData, sfp.puaScore, sfp.globalRep, sfp.globalRepData, sfp.localRep, sfp.localRepData, sfp.coreFileInfo FROM startup_items JOIN hash ON startup_items.path = hash.path JOIN sophos_file_properties AS sfp ON sfp.sha256 = hash.sha256 WHERE hash.path = mod_path AND mod_path LIKE '%.exe%' AND mod_path NOT LIKE '%Windows%'; |
14400 |
windows_updates_patch | SELECT hotfix_id,caption,description,installed_by,installed_on FROM patches; | 43200 |
windows_wsl_installed | SELECT file.filename, file.path, file.atime, file.mtime, file.ctime, file.product_version, sfp.sha256 FROM file INNER JOIN sophos_file_properties AS sfp ON sfp.pathname = file.path WHERE file.path = "C:\Windows\System32\wsl.exe"; |
86400 |
LINUX
name | query | interval |
process_events | select count(*) as process_events_count from process_events; | 86400 |
selinux_events | select count(*) as selinux_events_count from selinux_events; | 86400 |
socket_events | select count(*) as socket_events_count from socket_events; | 86400 |
syslog_events | select count(*) as syslog_events_count from syslog_events; | 86400 |
user_events | select count(*) as user_events_count from user_events; | 86400 |
arp_cache | SELECT address,mac,interface FROM arp_cache; | 3600 |
chrome_extensions | SELECT DISTINCT ce.uid, ce.name, ce.identifier, ce.version, ce.author, ce.path, ce.update_url FROM chrome_extensions AS ce JOIN logged_in_users AS liu ON liu.user=u.username JOIN users AS u ON u.uid=ce.uid; | 14400 |
deb_packages | SELECT name, version, arch, revision FROM deb_packages; | 14400 |
listening_ports | SELECT DISTINCT processes.name, listening_ports.address, listening_ports.port, processes.pid, processes.path FROM listening_ports JOIN processes USING (pid) WHERE listening_ports.address NOT LIKE '127%' AND listening_ports.protocol = 6; | 3600 |
network_interfaces | SELECT interface_details.mtu, interface_details.interface, interface_details.mac, interface_addresses.mask, interface_addresses.address, interface_addresses.broadcast, interface_details.ibytes, interface_details.obytes FROM interface_addresses JOIN interface_details ON interface_addresses.interface = interface_details.interface; | 43200 |
open_sockets | SELECT DISTINCT p.name, SUBSTR(p.cmdline, 1, 32766) AS cmdline, p.pid, p.parent, p.path, po.remote_address, po.remote_port, po.local_address FROM process_open_sockets AS po JOIN processes AS p USING (pid) WHERE remote_port != 0 and p.path <> '' and remote_address <> '' and remote_address not like '127%' and remote_address not like '169.254%' and remote_address <> local_address; | 30 |
rpm_packages | SELECT name, version, release, source, arch FROM rpm_packages; | 14400 |
running_processes_linux_events | SELECT GROUP_CONCAT(process_events.pid) AS pids, REPLACE(process_events.path, (SELECT REGEX_SPLIT(process_events.path, "[^\/]+$", 0)), '' ) AS name, SUBSTR(process_events.cmdline, 1, 32766) AS cmdline, GROUP_CONCAT(process_events.parent) AS parents, process_events.path, process_events.gid, process_events.uid, process_events.euid, process_events.egid, hash.sha1, hash.sha256, process_events.time FROM process_events JOIN hash AS hash WHERE hash.path = process_events.path GROUP BY process_events.cmdline, hash.sha1; |
10 |
threat_promisc_interfaces_linux | SELECT interface, mac, flags, flags & (1<<8) AS promisc, flags & (1<<3) AS loopback FROM interface_details WHERE promisc != 0; | 43200 |
user_accounts | SELECT uid, gid, username, description, directory, shell, type, uuid FROM users; |
43200 |
user_events_linux | SELECT uid, pid, message, type, path, address, terminal, time FROM user_events WHERE terminal != "cron" AND pid != 1; | 43200 |