How to find and use the Schema for Live Discovery Queries

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):

OSQuery information tables(7):

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');

Device ID name type
pid
BIGINT
name
TEXT
path
TEXT
cmdline
TEXT
state
TEXT
cwd
TEXT
root
TEXT
uid
BIGINT
gid
BIGINT
euid
BIGINT
egid
BIGINT
suid
BIGINT
sgid
BIGINT
on_disk
INTEGER
wired_size
BIGINT
resident_size
BIGINT
total_size
BIGINT
user_time
BIGINT
system_time
BIGINT
disk_bytes_read
BIGINT
disk_bytes_written
BIGINT
start_time
BIGINT
parent
BIGINT
pgroup
BIGINT
threads
INTEGER
nice
INTEGER
is_elevated_token
INTEGER
elapsed_time
BIGINT
handle_count
BIGINT
percent_processor_time
BIGINT