How to find and use the Schema for Live Discovery Queries

For query assistance, please see the following Best Practices guide

While we have the schema posted on the EAP community pages, I have had a number of request for how to find it and how to use it.

First how to find the schema(s):

From the Sophos Community: We provide a link to definition of the sophos windows schema on the community form in the documents section. You can downlaod the file with this link: https://community.sophos.com/products/intercept/early-access-program/m/files/9491/download.  When we launch the product in early June we will publish a Knowledge Base Article (KBA) with the current schema, based on the EAP we will be making some changes and the KBA will be the best place to get up-to date information

We also provide a link to the online-OSQuery schema: https://osquery.io/schema/4.2.0

From Live Discover: You can run the query we have to list the schema for all tables that the device can see.  This will return different results on Windows, Mac and Linux.  This is because each OS has different information available and hence has different tables in the schema.  The Table Schema:

SELECT
   osquery_registry.name "table",
   CAST(pti.name AS TEXT) "column",
   CAST(pti.type AS TEXT) "type",
   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'

So what does the schema have and why is it important:

Sophos Live Discover provides access to both current runtime information and historic state of the device.  To perform that trick we started with OSQuery.  OSQuery was a opensource project from face book back in 2014 or so.  The project is now freely available and what it does is provides a SQL lite DB interface to the current device information. That wonderful community of developers and experts have built a fast reliable and extensible tool that allows administrators to query for information on Windows, Mac and Linux. We took a version of OSQuery and made the necessary changes to get it to work on 32 bit operating systems, but other than that it is vanilla OSQuery with bug fixes that we have provided back to the open-source version and configuration options to optimize our use. 

Not all operating systems are the same and each will have some tables unique to it so when writing a new query it is important to understand what operating systems you intend to run it on.

Tables that are common across ALL operating systems (29):

• arp_cache 
• chrome_extensions
• cpuid
• curl
• curl_certificate
• etc_hosts
• etc_protocols
• etc_services
• file
• groups
• hash
• intel_me_info
• interface_addresses
• interface_details
• kernel_info
• listening_ports
• logged_in_users
• os_version
• platform_info
• process_memory_map
• process_open_sockets
• processes
• python_packages
• routes
• system_info
• time
• uptime
• user_groups
• users

OSQuery information tables(7):

• osquery_events
• osquery_extensions
• osquery_flags
• osquery_info
• osquery_packs
• osquery_registry
• osquery_schedule

As you explore OSQuery you will see a number of tables that are UNIQUE to each operating system.

The sophos forensic information is currently unique to Windows but work is planned for the other operating systems. If you want to list all tables in Windows you can run the query below.

IT can be overwhelming and we will be providing online-training on how to use live discover for beginers to experts. For now simply have fun and if you have any questions post them to the community forum.

SELECT name FROM osquery_registry WHERE registry = 'table';

• appcompat_shims
• arp_cache
• authenticode
• autoexec
• bitlocker_info
• carbon_black_info
  
• carves
  
• certificates
  
• chocolatey_packages
  
• chrome_extensions
  
• connectivity
  
• cpu_info
  
• cpuid
  
• curl
  
• curl_certificate
  
• default_environment
  
• disk_info
  
• drivers
  
• etc_hosts
  
• etc_protocols
  
• etc_services
  
• file
  
• firefox_addons
  
• groups
  
• hash
  
• ie_extensions
  
• intel_me_info
  
• interface_addresses
  
• interface_details
  
• kernel_info
  
• kva_speculative_info
  
• listening_ports
  
• logged_in_users
  
• logical_drives
  
• logon_sessions
  
• ntdomains
  
• ntfs_acl_permissions
  
• ntfs_journal_events
  
• os_version
  
• osquery_events
  
• osquery_extensions
  
• osquery_flags
  
• osquery_info
  
• osquery_packs
  
• osquery_registry
  
• osquery_schedule
  
• patches
  
• physical_disk_performance
  
• pipes
  
• platform_info
  
• powershell_events
  
• process_memory_map
  
• process_open_sockets
  
• processes
  
• programs
  
• python_packages
  
• registry
  
• routes
  
• scheduled_tasks
  
• services
  
• shared_resources
  
• ssh_configs
  
• startup_items
  
• system_info
  
• time
  
• uptime
  
• user_groups
  
• user_ssh_keys
  
• users
  
• video_info
  
• winbaseobj
  
• windows_crashes
  
• windows_events
  
• windows_optional_features
  
• windows_security_products
  
• wmi_bios_info
  
• wmi_cli_event_consumers
  
• wmi_event_filters
  
• wmi_filter_consumer_binding
  
• wmi_script_event_consumers
  
• sophos_directory_journal
  
• sophos_dns_journal
  
• sophos_events_details
  
• sophos_events_summary
  
• sophos_file_hash_journal
  
• sophos_file_journal
  
• sophos_file_properties
  
• sophos_http_journal
  
• sophos_image_journal
  
• sophos_ip_journal
  
• sophos_network_journal
  
• sophos_powershell_events
  
• sophos_process_activity
  
• sophos_process_journal
  
• sophos_process_properties
  
• sophos_registry_journal
  
• sophos_system_journal
  
• sophos_thread_journal
  
• sophos_url_journal
  
• sophos_windows_events
  
• sophos_winsec_journal
  

Oh and to see the info on a single table try this:  Just replace processes with the name of the table you are interested in, or better yet create a saved query with a variable. :)

SELECT name, type FROM pragma_table_info('processes');