Sophos Switch: Query device and client data with Live Discover

Use Live Discover to query telemetry from Sophos Switches managed in Sophos Fusion. Data Lake data can, for example, help you investigate devices that were connected to a specific switch. However, it isn’t a live view of the switch’s current state.

Prerequisites

You need at least one Sophos Switch managed in Sophos Fusion and access to the correct tenant and to Threat Analysis Center > Live Discover. Live Discover requires a Sophos EDR, XDR, or MDR license. Beyond that, the documentation specifies neither a particular switch license package nor a particular administrator role. If Live Discover, Switch, or an editing function is missing, have the license assignment, your access rights, and feature availability in the affected tenant checked.

Define the investigation objective beforehand. Useful reference points include the switch ID, device name, serial number, client MAC address, port, VLAN, and time period. You only need Designer Mode to edit or create a query.

Query switch data

Start with a built-in switch query. This gives you a comparison result first, and you only need to switch to the designer if the existing query doesn’t answer your question.

1. In Sophos Fusion, open Threat Analysis Center > Live Discover > Switch.
2. Select a built-in Data Lake query for Sophos Switch. Check its purpose, the visible definition, and the required parameters.
3. If offered, set the investigation period under Select a Time Period, then run the query.
4. Interpret the results using the switch identity, type_of_data, and the time fields. Compare a known switch or test client with the data in switch management.
5. If the built-in query is sufficient, document the query, parameters, time window, and result. Otherwise, turn on Designer Mode and choose one of these approaches:
   • Adapt an existing query: Select the query under Query and click Edit. Save the original definition before changing it.
   • Create a new query: Under Query, click Create new query and select Data Lake as the Source.
6. In the SQL dialog, open Schema in the upper-right corner. The Schema Viewer opens in a new tab. Select NSG Cswitch > nsg_cswitch_data and check the available columns and data types.
7. Only use tables, fields, and values shown by the current Schema Viewer or built-in query. Change only one related part at a time, such as the field selection or the filter for a known switch.
8. First run the adapted query with a narrowly scoped, known switch or client. Compare the result with the unchanged built-in query and the known inventory or connection data.

To find clients of a particular switch, identify the target device unambiguously by device_id or serial number. Then evaluate the client MAC address, device_port, client_vlan, connection status, and time fields together. This restriction prevents mix-ups, but doesn’t yet prove that the client list is complete; the time period and is_full_set must also match.

Select a time window

Select a Time Period is optional for Data Lake queries. If you don’t make a selection, the past 7 days are used. A single query can cover no more than 30 days.

For longer investigations, run multiple queries with separate time windows. For 90 days, for example, Sophos specifies 0–30, 31–60, and 61–90 days. Document each window separately and keep the switch, data type, and other filters unchanged when comparing them.

Fields in the switch schema

The schema covers switch identity, connected clients, and logs. type_of_data identifies the type of data sent, such as client or log data. Therefore, not every row contains all client and log fields at the same time.

Status, subtype, and severity values can vary with the displayed data. Use their exact spelling and meaning from the current schema and query result.

Interpret results correctly

A raw Data Lake row is initially a query result. Whether it contains client or log data follows from type_of_data and the populated fields. If the SQL query combines multiple records, the result row is a calculated summary and not an individual network event. Therefore, don’t apply the meaning of timestamp to an aggregate without checking it.

Identity and tenant

device_id is the unique switch ID. Also compare at least one other reference point, such as device_serial_id, device_name, device_model, or device_ip, with the target device. Names and IP addresses can change or be reused. customer_id associates the data with the tenant; it isn’t a switch, serial, or client ID.

Event and ingestion time

• timestamp is the time at which the event was generated.
• client_event_timestamp is the time at which the device connected.
• ingestion_timestamp is the ingestion time in epoch seconds.
• ingest_date is the ingestion date.

Later ingestion doesn’t automatically mean a later network event. For time series, also check the epoch conversion and the time zone of the query environment used.

Complete and incremental data

is_full_set indicates whether a delivery is complete or incremental. Don’t treat an incremental data set as a complete client inventory. Even a delivery marked as complete doesn’t by itself guarantee that every expected client appears in the period investigated.

Clients and logs

device_port and client_vlan associate a client with a port and VLAN. Whether it is directly connected follows only from the value of direct_end_device, not merely from the field’s presence. The IP address and hostname may be absent or change; also use client_mac and the time fields for attribution. A telemetry record is only an observation at a point in time, not proof of the current state or of a gapless connection history.

For log data, log_id, log_subtype, log_component, log_message, and log_severity provide the context. Severity alone proves neither the cause nor the effect of a network problem.

Verify the result

Before using the result, answer these questions:

• Do customer_id and the switch identity belong to the correct tenant and target device?
• Do type_of_data and the client or log fields that are actually populated match the investigation question?
• Are the event, client, and ingestion times within the expected window, and were they interpreted separately?
• Was is_full_set considered if you are making a statement about completeness?
• For a known test client, do the MAC address, port, VLAN, and value of direct_end_device match the expected connection?
• Does the built-in query return a plausible comparison result for the same target and time window?

A missing record is only missing evidence under the selected conditions. It proves neither that the switch or client doesn’t exist nor, by itself, that there is a telemetry error.

Troubleshoot problems

Switch queries or editing functions are missing

If the Switch section is missing, first check the correct Fusion tenant, a switch managed through Fusion, and the license assignment for Sophos EDR, XDR, or MDR. Then have your access rights and feature availability in the tenant checked. Designer Mode must also be on for Edit and Create new query.

Schema or table is missing

You can open the Schema Viewer from the SQL dialog of an edited or new query. For a new query, Source: Data Lake must be selected. For switch telemetry, use only the path NSG Cswitch > nsg_cswitch_data shown in the viewer and its current fields.

The query returns no rows

First test a built-in switch query for a known switch. Record the tenant, switch filter, expected test record, and time window. Then remove your own filters one at a time and compare the field names with the schema.

Without your own time selection, account for the 7-day default. With all other conditions unchanged, expand the window gradually to no more than 30 days. Use separate, documented windows for longer investigations. Also compare event and ingestion time, and check type_of_data and is_full_set. Record the result as “no rows under the selected query conditions and during the selected period.”

You can also check the operational and synchronization state of the target device with the Operate a Sophos Switch fleet runbook.

Rows are present, but client data is missing

A log row doesn’t have to contain client fields. Therefore, first check type_of_data and is_full_set, then assess client_mac, client_ip, client_hostname, client_event_timestamp, and client_conn_status together. Don’t fill in missing values from inventory or naming conventions.

The time series seems inconsistent

Compare event and ingestion times separately, and check the epoch conversion and time zone. Delayed ingestion isn’t automatically a second network event. You can use message_identifier for deduplication, but its uniqueness is documented only within the ingestion pipeline.

Revert changes and protect data

A query changes its SQL definition or selection, not the switch configuration. If an adapted query is unreliable, stop using it and return to the unchanged built-in query. If necessary, restore the definition you saved previously or discard the new variant using the function offered in your interface. Then use the built-in query to confirm that the normal process still works.

Exported results can contain customer IDs, serial numbers, IP and MAC addresses, hostnames, ports, VLANs, and log messages. Protect and delete exports, screenshots, and notes according to your retention and data-protection requirements. Reverting a query doesn’t remove copies already saved; you must handle them at their respective storage locations.