For Sophos Central Live Discover and Data Lake

Practise and learn Sophos SQL

Sophos Central uses SQL to search your devices and the Data Lake. Practise it with exercises written like the tickets you get on shift, on realistic lab data, and see straight away whether your query found the right rows.

Join the waitlist See pricing

SOPHOS · SQL

SELECT meta_hostname, username, cmdline
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
  AND LOWER(parent_name) = 'outlook.exe'
  AND LOWER(name) IN ('powershell.exe', 'cmd.exe')

Questions you’ll answer

Every exercise starts as a ticket. You write the query that answers it.

Is cmd.exe running on this laptop right now?

LIVE DISCOVER

SELECT pid, name, cmdline
FROM processes
WHERE LOWER(name) = 'cmd.exe'

Which devices ran PowerShell this week?

DATA LAKE

SELECT meta_hostname, COUNT(*) AS runs
FROM xdr_data
WHERE query_name = 'running_processes_windows_sophos'
  AND LOWER(name) = 'powershell.exe'
GROUP BY meta_hostname
ORDER BY runs DESC

Where you’ll use it

Live DiscoverAsks devices that are online right now.
Data LakeLooks back over up to 30 days of activity.

What you’ll learn, from beginner to advanced

Twelve modules in three levels. Each one has a short lesson, questions to answer and then exercises.

Beginner

Your first querySELECT, FROM and WHERE on the processes table.
Matching textLIKE, LOWER() and when case matters.
Sorting and limitingORDER BY, LIMIT and DISTINCT.
CountingCOUNT() and GROUP BY.

Intermediate

Live Discover or Data LakeAsk online devices now, or look back up to 30 days.
Data Lake queriesxdr_data, query_name and choosing the time period.
Joining tablesJOIN processes to users, files and network connections.
Real investigationsProcess trees, scheduled tasks and startup items.

Advanced

Readable queriesWITH, CASE and subqueries.
Variables$$Variable$$ values, so one query works for any device or path.
Hunting across the estateHAVING and rare values to spot the odd device out.
Fast queriesA short time period, fewer columns, and a path for heavy tables like file.

Worth knowing before you start

Live Discover only reaches devices that are online. Data Lake looks back up to 30 days.
Text matches in Data Lake are case-sensitive, so wrap names in LOWER().
The tables come from osquery, so what you learn here carries over to osquery anywhere.

Cheat sheet

Sophos SQL cheat sheetThe syntax you use most, on one page, with links to the official docs.

Other languages

Be first in when it launches

Sophos SQL is one of the languages SOC Fluent launches with. Join the waitlist and we’ll email you once, on launch day.

Join the waitlist See pricing

Free to join. Students get 50% off at launch.