Related
Recommended
Karl_Ackerman
21 Feb 2021

(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 

Data Lake

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.

SELECT name, query, interval
FROM osquery_schedule
WINDOWS
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
Anonymous
Unfiltered HTML